RE: Query performance issue

Mark,

The 10042 trace shows that almost all of the 28 minutes is accounted for by the sequential read waits. How do I measure the honeycombed and
"empty front" conditions?

Thanks,
Peter Schauss

-----Original Message-----
From: Mark W. Farnham [mailto:mwf_at_rsiz.com] Sent: Wednesday, June 04, 2008 12:10 PM
To: Schauss, Peter; oracle-l_at_freelists.org Subject: RE: Query performance issue

The 50% extra in row count is very unlikely to account for the 10-fold difference. IF there is a 10-fold difference in the number of blocks holding rows, then you've just observed one an actual justification for rebuilding a table that has become honeycombed or has an extensive
"empty front." Since the big table is index accessed, honeycombed would
be more likely than "empty front." If that turns out to be true, you might consider also whether the rebuild would be well-served being in the order of the index used in this query.

IF there is not a corresponding 10-fold difference in the number of blocks required to fetch the rows you need, then the next most likely thing is that your QA server's disk farm is mostly serving this query while your production query must serve all the many simultaneous requests for this database and any other databases being served by the production SAN that are entangled with the storage of the production database. Or if production updates are taking place you could be driving a lot of read consistent block retrievals from UNDO that don't take place in the relatively quiescent QA database. You'd have to test for that difference running the production query against a relatively idle time on prod with respect to updates to the tables involved.

I just realized I leaped to a time presumption: you noted the difference in counts of waits. Is the wait time difference from this event the biggest difference and a significant fraction of the 26 minute differential you are looking for? IF NOT, look at the biggest time differential.

It is also probably worth the few minutes it will take to count the block gets from a full table scan of each of the tables that are scanned.
Infrequently (but potentially disasterously) 8.1 and prior databases created an empty front condition such that many blocks are scanned before you get to the first row in the table. If QA was created from an import or other non-clone copy, the problem would automagically not be present in QA. A scan where rownum < 2 is sufficient to detect a truly empty front, while a scan with no stopkey gets you the full honeycomb and empty information to the highwater mark.

Regards,

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Schauss, Peter
Sent: Wednesday, June 04, 2008 10:08 AM
To: oracle-l_at_freelists.org
Subject: Query performance issue

Oracle 8.1.7.4 - Solaris 5.9 - Cost based optimizer

I have a query which takes 28 minutes to run on my production system and about a two minutes to run on my QA database.

The query looks like this:

<snip>
Row counts for w_day_d, w_lov_d, and w_region_d within 10% of each other on both databases.
The row count for w_activity_f is 3x10**6 on production and 2x10**6 on qa.

The 10046 trace for production lists 3x10**5 db file file sequential read waits while the trace for QA lists 2x10**4.

Would the 50% difference in the size of my w_activity_f table be sufficient to account for the 10-fold increase in the number of sequential read waits and the corresponding 10-fold difference in run time or should I be looking for hardware or other issues?

Thanks,
Peter Schauss   

--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l