index with very high LIO
- From: Jeremy Schneider <jeremy.schneider@xxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 17 Nov 2011 05:34:56 -0600
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
