Re: index with very high LIO
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 18 Nov 2011 23:32:14 -0000
The most dramatic feature is the 7.9M transaction tables undo records applied
to
get 160 transaction table searches read-consistent.
Assume a transaction updated one of your target objects some time close to the
start of your query and issued a commit after the block had been flushed from
memory so that the block was not subject to commit cleanout.
Assume your query reaches the updated block a few hours later - when a lot of
other activity has been going on.
Your query can see that the block changed at some time in the past, but has to
check the undo segment header transaction table slot to see whether it
committed
before or after the query started. Since many transactions have committed since
that moment the transaction table slot will have been overwritten many times
and
previous versions of the slot recorded in the undo segment. This means the
session has to walk through a chain of undo blocks to take the transaction
table
back in time.
According to these figures, you have done this 160 times in the 30 minutes, and
had to read 7.9 M undo blocks (a lot of history) while doing so. (It's quite
possible that a lot of those reads were physical reads of the undo segment, of
course). This is probably a large component of your overhead - but the figures
for this 30 minutes don't seem to be representative of the figures for the
whole
query.
You have another 7M examinations to account for - but only 168,000 undo records
applied for read consistency; this suggests that most of the 7M are from the
unique access to the table, which (assuming 4 buffer gets per row) would be
about 1.75M of the 8.7M rows accessed. It would be good to check the figures
across the whole interval (Anything that looks like "%consistent%" as well as
the stuff you've got so far, and "table fetch by rowid".
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
----- Original Message -----
From: "Jeremy Schneider" <jeremy.schneider@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, November 18, 2011 5:51 PM
Subject: Re: index with very high LIO
TOTAL PER-SECOND STATISTIC
148,983 82.61 CR blocks created
15,406,266 8,542.71 consistent gets - examination
168,222 93.28 data blocks consistent reads - undo records applied
21,173 11.74 db block gets
160 0.09 transaction tables consistent read rollbacks
7,929,413 4,396.82 transaction tables consistent reads - undo
records applied
1,379,540 764.95 undo change vector size
I'm not an expert on this... does this say that, per second:
- the query is only actually processing 11 data blocks
- it's applying 8.5 thousand undo records to get them
- this requires 9.7 thousand actual block accesses
What other stats would be interesting? Am I reading these statistics
correctly? Any further observations that can be made?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
