Re: index with very high LIO

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 17 Nov 2011 14:20:55 -0000


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:

If you've got the AWR or statspack reports or the 10 hours you could check 
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 


Jonathan Lewis

----- 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. 


Other related posts: