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.

-Jeremy

PS... 8k block size; segment of index in question is 100G.  Query is
pulling 7 million rows from a join of two billion row tables...



SQL> select * from
table(dbms_xplan.display_cursor('8suhywrkmpj5c',null,'ALLSTATS'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8suhywrkmpj5c, child number 0
-------------------------------------
SELECT LOTS_OF_FIELDS...

Plan hash value: 4164942971

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                           |
Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                               
|      1 |        |   8792K|09:54:06.81 |     142M|   8552K|
|   1 |  NESTED LOOPS                 |                               
|      1 |     65 |   8792K|09:54:06.81 |     142M|   8552K|
|   2 |   PARTITION RANGE SINGLE      |                               
|      1 |     65 |   8792K|00:26:50.23 |      11M|    367K|
|   3 |    TABLE ACCESS BY INDEX ROWID| BIG_TABLE_ONE                 
|      1 |     65 |   8792K|00:26:44.48 |      11M|    367K|
|*  4 |     INDEX RANGE SCAN          | BT_ONE_DATE_INDEX             
|      1 |     63 |   8792K|00:16:39.09 |    2637K|    288K|
|   5 |   TABLE ACCESS BY INDEX ROWID | BIG_TABLE_TWO                 
|   8792K|      1 |   8792K|09:26:57.84 |     130M|   8185K|
|*  6 |    INDEX UNIQUE SCAN          | BT_TWO_VARCHAR2_INDEX         
|   8792K|      1 |   8792K|08:55:58.31 |     121M|   7908K|
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("BT1"."LAST_MODIFIED_DATETIME">=TO_DATE(' 2011-11-13
07:15:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "BT1"."LAST_MODIFIED_DATETIME"<TO_DATE(' 2011-11-14
07:15:00', 'syyyy-mm-dd hh24:mi:ss'))
   6 - access("BT2"."JOIN_VARHAR2"="BT1"."JOIN_VARCHAR2")

====================================================================

INDEX_NAME                    : BT_TWO_VARCHAR2_INDEX
INDEX_TYPE                    : NORMAL
TABLE_NAME                    : BIG_TABLE_TWO
TABLE_TYPE                    : TABLE
UNIQUENESS                    : UNIQUE
COMPRESSION                   : DISABLED
PREFIX_LENGTH                 :
TABLESPACE_NAME               : INDEX01
INI_TRANS                     : 2
MAX_TRANS                     : 255
INITIAL_EXTENT                : 131072
NEXT_EXTENT                   : 1048576
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  :
PCT_THRESHOLD                 :
INCLUDE_COLUMN                :
FREELISTS                     :
FREELIST_GROUPS               :
PCT_FREE                      : 10
LOGGING                       : YES
BLEVEL                        : 3
LEAF_BLOCKS                   : 9268569
DISTINCT_KEYS                 : 1107885846
AVG_LEAF_BLOCKS_PER_KEY       : 1
AVG_DATA_BLOCKS_PER_KEY       : 1
CLUSTERING_FACTOR             : 1107885846
STATUS                        : VALID
NUM_ROWS                      : 1107885846
SAMPLE_SIZE                   : 138537
LAST_ANALYZED                 : 07-aug-2011 14:00:56
DEGREE                        : 1
INSTANCES                     : 1
PARTITIONED                   : NO
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
BUFFER_POOL                   : DEFAULT
FLASH_CACHE                   : DEFAULT
CELL_FLASH_CACHE              : DEFAULT
USER_STATS                    : NO
DURATION                      :
PCT_DIRECT_ACCESS             :
ITYP_OWNER                    :
ITYP_NAME                     :
PARAMETERS                    :
GLOBAL_STATS                  : YES
DOMIDX_STATUS                 :
DOMIDX_OPSTATUS               :
FUNCIDX_STATUS                :

====================================================================

TABLE_NAME                    : BIG_TABLE_TWO
COLUMN_NAME                   : JOIN_VARCHAR2
DATA_TYPE                     : VARCHAR2
DATA_TYPE_MOD                 :
DATA_TYPE_OWNER               :
DATA_LENGTH                   : 36
DATA_PRECISION                :
DATA_SCALE                    :
NULLABLE                      : Y
COLUMN_ID                     : 12
DEFAULT_LENGTH                :
DATA_DEFAULT                  :
NUM_DISTINCT                  : 1093215934
LOW_VALUE                     :
30303030303030382D316634372D313165302D623932312D3030393066623236
HIGH_VALUE                    :
66666666666666632D366236352D313165302D393933312D3030393066623263
DENSITY                       : .000000000914844154395423
NUM_NULLS                     : 0
NUM_BUCKETS                   : 254
LAST_ANALYZED                 : 06-aug-2011 12:33:29
SAMPLE_SIZE                   : 13496
CHARACTER_SET_NAME            : CHAR_CS
CHAR_COL_DECL_LENGTH          : 36
GLOBAL_STATS                  : YES
USER_STATS                    : NO
AVG_COL_LEN                   : 37
CHAR_LENGTH                   : 36
CHAR_USED                     : B
V80_FMT_IMAGE                 : NO
DATA_UPGRADED                 : YES
HISTOGRAM                     : HEIGHT BALANCED


-- 
http://www.ardentperf.com
+1 312-725-9249

Jeremy Schneider
Chicago

--
http://www.freelists.org/webpage/oracle-l


Other related posts: