Without the explain for the SQL statement that is causing this behavior
it would be very hard to diagnose. When you say slight change to the
SQL what do you mean? Was a predicate added to the SQL, was the
value it is matching on different, etc.? Data skew could cause some performance
issues, but I wouldn't guess it would run for days, unless a huge amount of
data had to be relocated. I'm guessing this is EEE you are working with.
Do you have an explain for the SQL that runs in seconds that can be compared
to the one that runs for hours?
Amir Sadeghi wrote:
> Hi Team,
>
> >From time to time, we run into queries that perform extremely slow in our
> Production database. The problem is that a query that runs in seconds,
> with a slight change could run for hours, and even days sometimes. This
> happens mostly when the size of the result set is large.
>
> I have taken some snapshot at the time and most nodes seem to be waiting to
> either send or receive on tablequeues. I have pasted an example below.
>
> Any ideas what the bottleneck is and how it can be improved.
>
> Thanks for any help or suggestion.
>
> Subsection number = 1
> Subsection node number = 5
> Subsection status = Waiting to receive on
> tablequeue
> Node for which waiting on tablequeue = 6
> Tablequeue ID on which agent is waiting = 2
> Execution elapsed time (seconds) = 87
> Total user CPU time (sec.ms) = 0.000000
> Total system CPU time (sec.ms) = 0.000000
> Current number of tablequeue buffers overflowed = 0
> Total number of tablequeue buffers overflowed = 0
> Maximum number of tablequeue buffers overflowed = 0
> Rows received on tablequeues = 61909
> Rows sent on tablequeues = 0
> Rows read = 0
> Rows written = 0
> Number of agents working on subsection = 1
>
> Agent process/thread ID = 112838
>
> Subsection number = 2
> Subsection node number = 5
> Subsection status = Waiting to send on
> tablequeue
> Node for which waiting on tablequeue = Any node
> Tablequeue ID on which agent is waiting = 2
> Execution elapsed time (seconds) = 87
> Total user CPU time (sec.ms) = 0.000000
> Total system CPU time (sec.ms) = 0.000000
> Current number of tablequeue buffers overflowed = 0
> Total number of tablequeue buffers overflowed = 0
> Maximum number of tablequeue buffers overflowed = 0
> Rows received on tablequeues = 1316
> Rows sent on tablequeues = 2917
> Rows read = 0
> Rows written = 0
> Number of agents working on subsection = 1
>
> Agent process/thread ID = 131402
>
> Subsection number = 3
> Subsection node number = 5
> Subsection status = Waiting to send on
> tablequeue
> Node for which waiting on tablequeue = Any node
> Tablequeue ID on which agent is waiting = 3
> Execution elapsed time (seconds) = 87
> Total user CPU time (sec.ms) = 0.000000
> Total system CPU time (sec.ms) = 0.000000
> Current number of tablequeue buffers overflowed = 0
> Total number of tablequeue buffers overflowed = 0
> Maximum number of tablequeue buffers overflowed = 0
> Rows received on tablequeues = 82572
>
> Amir
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
>
> =====
> To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
> For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
