Good questions. In all cases, I have compared the EXPLAIN and there is not
a major change. A good example that I can recall was when we changed an 'IN'
statemet, using a subselect, to a 'NOT IN'. The difference was from minutes
to days!!!
I have opened a PMR with the IBM lab on this item but am wondering if anyone
else has run into this kind of behavior.
And yes, we are in a EEE environment using UDB 6.1.
Amir
>From: Paul Turpin <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: [EMAIL PROTECTED]
>Subject: Re: DB2EUG: UDB Query Performance Problem
>Date: Fri, 14 Sep 2001 13:28:52 -0400
>
>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
>
_________________________________________________________________
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
