Re: index with very high LIO
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 17 Nov 2011 14:20:55 -0000
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
Other related posts:
