Re: index with very high LIO
- From: Jeremy Schneider <jeremy.schneider@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 18 Nov 2011 11:51:06 -0600
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.
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
