Re: index with very high LIO

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