Run snapshots to see if any of the following are occurring: - sort overflows s/b 0 otherwise increase sortheap (this impacts available memory) - buffpage (logical - physical) / logical s/b > 90 - We have seen degradations with large result sets (1000 rows=15 seconds vs 4000 takes 40 seconds for a terrabyte database). Are you using Internet Explorer 5.0 (it works faster with large result sets). Most DBMSs have had problems with "Not in". The "IN" function is supposed to be more efficient in 7.x, but other methods can still be more efficient (ie: creating a temp table, then merging). Good Luck! -----Original Message----- From: Amir Sadeghi [mailto:[EMAIL PROTECTED]] Sent: Friday, September 14, 2001 4:28 PM To: [EMAIL PROTECTED] Subject: Re: DB2EUG: UDB Query Performance Problem 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 ===== To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
RE: DB2EUG: UDB Query Performance Problem
Fortier, Christi (USPC.PCT.Hopewell) Mon, 17 Sep 2001 07:44:27 -0700
- DB2EUG: UDB Query Performance Problem Amir Sadeghi
- Re: DB2EUG: UDB Query Performanc... Paul Turpin
- Re: DB2EUG: UDB Query Performanc... Amir Sadeghi
- Fortier, Christi (USPC.PCT.Hopewell)
