Re: index with very high LIO

From: Jeremy Schneider <jeremy.schneider_at_ardentperf.com>
Date: Mon, 28 Nov 2011 05:52:18 -0600
Message-ID: <4ED375F2.9020903_at_ardentperf.com>



I got a little swamped with wrapping up some work... but I didn't forget about this. :) I finally looked closer at the AWR data. Since the report ran for 10 hours, I have 20 snapshots. When I graphed it, I found an interesting characteristic: the number of consistent gets per row rises over 40 at one point, then decreases back down to 10 by the end of the run. However, the ratio of transaction table rollback vs records applied just steadily increases... as expected. So I'm no expert, but this looks less like undo is causing all the extra logical I/O on that one specific index. But I'm at a loss for what else it could be...

For each 30-minute period, rows vs logical I/O from DBA_HIST_SQLSTAT: www.ardentperf.com/wp-content/uploads/2011/11/more-research_html_7e8de68c.jpg

For each 30-minute period, trans table rollback vs undo records applied: http://www.ardentperf.com/wp-content/uploads/2011/11/more-research_html_7559fa03.jpg

FYI, for each 30-minute period, consistent gets for this SQL vs the entire instance:
http://www.ardentperf.com/wp-content/uploads/2011/11/more-research_html_m5273695a.jpg

-Jeremy

-- 
http://www.ardentperf.com
+1 312-725-9249

Jeremy Schneider
Chicago


On 11/18/2011 05:32 PM, Jonathan Lewis wrote:

> 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_at_ardentperf.com>
> To: <oracle-l_at_freelists.org>
> 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?
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-l
Received on Mon Nov 28 2011 - 05:52:18 CST

Original text of this message