Re: index with very high LIO
From: Jeremy Schneider <jeremy.schneider_at_ardentperf.com>
Date: Fri, 18 Nov 2011 11:51:06 -0600
Message-ID: <4EC69B0A.5070407_at_ardentperf.com>
This is a very busy system, lots happened during that 10 hours. I don't have session-specific stats, so the best I could do was look at some AWR stats over a 30-minute window - but this particular instance wasn't very busy, so luckily these statistics mainly reflect this query. I looked at the very last 30 minutes since I figured it would be the worst-performing time.
1,379,540 764.95 undo change vector size
Date: Fri, 18 Nov 2011 11:51:06 -0600
Message-ID: <4EC69B0A.5070407_at_ardentperf.com>
This is a very busy system, lots happened during that 10 hours. I don't have session-specific stats, so the best I could do was look at some AWR stats over a 30-minute window - but this particular instance wasn't very busy, so luckily these statistics mainly reflect this query. I looked at the very last 30 minutes since I figured it would be the worst-performing time.
This SQL statement accounted for 90% of buffer gets on the instance during the snapshot. Here are the statistics Jonathan mentioned:
AWR snap coveres 30:04 == 1804s
Total Logical Reads: 10,823.1/s
SQL 8suhywrkmpj5c: 17,617,831 gets ... 9,765 gets/sec
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?
-Jeremy
-- http://www.ardentperf.com +1 312-725-9249 Jeremy Schneider Chicago On 11/17/2011 08:20 AM, Jonathan Lewis wrote:Received on Fri Nov 18 2011 - 11:51:06 CST
> Jeremy,
>
> How much other activity was going on in the 10 hours ?
>
> Before checking anything else I would look at the session stats (or instance
> activity stats if you don't have session stats) for undo application:
> data blocks consistent reads - undo records applied
> CR blocks created
> and the two relating to transaction table consistent reads.
>
> Each "undo record applied" will increment "consistent gets - examination".
>
> There are a couple of related observations here:
> http://jonathanlewis.wordpress.com/2011/05/08/consistent-gets-3/
>
> If you've got the AWR or statspack reports or the 10 hours you could check these
> figures hour by hour to see if they increase over time (a classic problem with
> long running queries is that the longer they take to run the more work they do
> generating constistent read copies, which makes them take longer to run, which
> ...)
>
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
>
> ----- Original Message -----
> From: "Jeremy Schneider" <jeremy.schneider_at_ardentperf.com>
> To: <oracle-l_at_freelists.org>
> Sent: Thursday, November 17, 2011 11:34 AM
> Subject: index with very high LIO
>
>
> Anyone have ideas why an index with blevel 3 is averaging 13 LIOs per
> lookup/row? I'm working on the query below, which ran for about 10
> hours - the majority of which was spent doign 13 logical IOs per row in
> this index. (BT_TWO_VARCHAR2_INDEX below... 121M buffers / 8792K starts
> = 13 IOs per start) Performance on this query has been degrading
> rapidly over the past month or two.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-l