Re: index with very high LIO

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:
> 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@xxxxxxxxxxxxxx>
> To: <oracle-l@xxxxxxxxxxxxx>
> 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


Other related posts: