RE: Query performance issue
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <mark.powell@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 5 Jun 2008 23:55:32 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Powell, Mark D
Sent: Wednesday, June 04, 2008 3:50 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Schauss, Peter
Sent: Wednesday, June 04, 2008 3:03 PM
To: Mark W. Farnham; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxx]
Sent: Wednesday, June 04, 2008 12:10 PM
To: Schauss, Peter; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Schauss, Peter
Sent: Wednesday, June 04, 2008 10:08 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
