index with very high LIO

From: Jeremy Schneider <jeremy.schneider_at_ardentperf.com>
Date: Thu, 17 Nov 2011 05:34:56 -0600
Message-ID: <4EC4F160.40903_at_ardentperf.com>

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

Received on Thu Nov 17 2011 - 05:34:56 CST