RE: Query performance issue

From: Powell, Mark D <>
Date: Wed, 4 Jun 2008 15:49:54 -0400
Message-ID: <>

You can run a query that counts the rows per block per file by extracting this from the rowid. Then compare the information to the extent map for the table. If there are no or only a few rows in extent 1, 2, 3, ... then the from of the table is empty or nearly so.

If the rows per block is low compared to how many a block could hold then your pctused is probably set low (if not ASSM tablespace).

  • Mark D Powell -- Phone (313) 592-5148

-----Original Message-----

[] On Behalf Of Schauss, Peter Sent: Wednesday, June 04, 2008 3:03 PM
To: Mark W. Farnham; Subject: RE: Query performance issue


I ran the query on both servers with "set autotrace statistics on". QA had 22 db block gets and Production had 23. Table statistics show 580 empty blocks on QA and 3140 on Production. How can I tell if the empty blocks are in the middle of the data or the end of the allocated space?

Peter Schauss

-----Original Message-----

From: Mark W. Farnham [] Sent: Wednesday, June 04, 2008 12:10 PM
To: Schauss, Peter; 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.



-----Original Message-----

On Behalf Of Schauss, Peter
Sent: Wednesday, June 04, 2008 10:08 AM
Subject: Query performance issue

Oracle - 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:

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?

Peter Schauss   



-- Received on Wed Jun 04 2008 - 14:49:54 CDT

Original text of this message