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.
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|
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-lReceived on Thu Nov 17 2011 - 05:34:56 CST