RE: Query performance issue

While what Mr. Powell suggested is effective, a simple way to get the rough edge on empty front is querying v$mystats for the sum of the various block gets before and after select * from <table> where rownum < 2; Under many conditions just looking at consistent gets is sufficient.

If the difference is much more than 1 you've probably got empty blocks at the beginning of the table.

You can also get quite an exact answer by comparing the block ranges from dba_extents to the count(*) group by the block portion of the rowids from rows returned for the amount of the table you want to examine. I should have mentioned sparseness in addition to honeycomb, btw. Empty front being 1 or more blocks in the scan before you get to the first block containing a row, honeycomb being empty blocks in the scan after you've gotten at least one row, and sparseness being blocks with relatively few rows compared to the number of rows you expected for the pctfree you set. There are usually a few sparse blocks just from parallel inserts, but if you have heavily deleted and the now nearly empty blocks are far down the insert chain, they can stay pretty empty for pretty long.

mwf

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

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark D
Sent: Wednesday, June 04, 2008 3:50 PM
To: oracle-l_at_freelists.org
Subject: RE: Query performance issue

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

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Schauss, Peter Sent: Wednesday, June 04, 2008 3:03 PM
To: Mark W. Farnham; oracle-l_at_freelists.org Subject: RE: Query performance issue

Mark,

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?

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

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jun 05 2008 - 22:55:32 CDT