@@ @@ Original Statement @@ SELECT A.VERSION FROM PSRECDEFN A, PSVERSION B WHERE A.VERSION > B.VERSION AND B.OBJECTTYPENAME = 'RDM' @@ @@ Pseudo Indexing @@ PSRECDEFN A VERSION PSVERSION B OBJECTTYPENAME VERSION @@ @@ Original Execution Plan - SASCNJ (Ignite) @@ ******************************** BEGIN PLAN ******************************** SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Cardinality=185 Bytes=2035) NESTED LOOPS (Cost=3 Cardinality=185 Bytes=2035) TABLE ACCESS (BY INDEX ROWID) OF PSVERSION (TABLE) (Cost=1 Cardinality=1 Bytes=8) INDEX (UNIQUE SCAN) OF PS_PSVERSION (INDEX (UNIQUE)) (Cost=0 Cardinality=1) INDEX (RANGE SCAN) OF PSDPSRECDEFN (INDEX) (Cost=2 Cardinality=185 Bytes=555) @@ @@ Original Execution Plan - SASCNJ (SQLPLus) @@ --@@ Explain Time Plan ID PRNT QUERY_PLAN --- ---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Cardinality=185 Bytes=2,035) 1 0 NESTED LOOPS (Cost=3 Cardinality=185 Bytes=2,035) 2 1 TABLE ACCESS BY INDEX ROWID PSVERSION (Cost=1 Cardinality=1 Bytes=8) 3 2 INDEX UNIQUE SCAN PS_PSVERSION (Cost=0 Cardinality=1 Bytes=0 Columns=1) 4 1 INDEX RANGE SCAN PSDPSRECDEFN (Cost=2 Cardinality=185 Bytes=555 Columns=1) '--@@DBMSXPLAN' ----------------- --@@ DBMS xPlan PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1996347817 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 185 | 2035 | 3 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 185 | 2035 | 3 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| PSVERSION | 1 | 8 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | PS_PSVERSION | 1 | | 0 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | PSDPSRECDEFN | 185 | 555 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / B@SEL$1 3 - SEL$1 / B@SEL$1 4 - SEL$1 / A@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("B"."OBJECTTYPENAME"='RDM') 4 - access("A"."VERSION">"B"."VERSION") Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) "A"."VERSION"[NUMBER,22] 2 - "B"."VERSION"[NUMBER,22] 3 - "B".ROWID[ROWID,10] 4 - "A"."VERSION"[NUMBER,22] @@ @@ @@ Original Execution Plan - ????? (SQLPLus) @@ @@ @@ Table/Index Info - SASCNJ @@ PSRECDEFN,PSVERSION @@ @@ Non/tables in SASCNJ at 09-MAR-11 14:14:28 ============================================ @@ Tables and Indexes in SASCNJ at 09-MAR-11 14:14:30 ===================================================== TABLE_NAME TBL_OWNER INDEX_NAME Unique COLUMN_NAME POS Function ----------------------------------- -------------------- ----------------------------------- ------ ----------------------------------- ---------- ----------------------------------- PSRECDEFN SYSADM PSAPSRECDEFN N PARENTRECNAME 1 RECNAME 2 RECDESCR 3 PSBPSRECDEFN N RELLANGRECNAME 1 PSCPSRECDEFN N RECNAME 1 VERSION 2 PSDPSRECDEFN N VERSION 1 PSEPSRECDEFN N RECTYPE 1 RECNAME 2 PS_PSRECDEFN Y RECNAME 1 PSVERSION SYSADM PS_PSVERSION Y OBJECTTYPENAME 1 @@ Table Statistics in SASCNJ at 09-MAR-11 14:14:31 ================================================= Sample_% LAST_ANALY OWNER TABLE_NAME PARTITION_NAME NUM_ROWS BYTES BLOCKS AVG_ROW_LEN ---------- ---------- -------------------- ----------------------------------- ----------------------------------- ---------- ---------- ---------- ----------- 15 03/05/2011 SYSADM PSRECDEFN 44747 15728640 1920 344 100 03/05/2011 SYSADM PSVERSION 91 65536 4 8 @@ Index Statistics in SASCNJ at 09-MAR-11 14:14:36 ================================================= Sample_% LAST_ANALY OWNER TABLE_NAME INDEX_NAME PARTITION_NAME CLUSTER_HEALTH BYTES CLSTRFCTR LEAF_BLOCKS NUM_ROWS DISTINCT_KEYS AVGLFBLCKSKEY ---------- ---------- -------------------- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------- ---------- ---------- ----------- ---------- ------------- ------------- 100 03/05/2011 SYSADM PSRECDEFN PSAPSRECDEFN BAD CLUSTER-Leaf_Blcks/Clstr_fctr < .10 3145728 20332 329 44800 44800 1 100 03/05/2011 SYSADM PSRECDEFN PSBPSRECDEFN BAD CLUSTER-Leaf_Blcks/Clstr_fctr < .10 917504 6160 95 44800 5771 1 100 03/05/2011 SYSADM PSRECDEFN PSCPSRECDEFN BAD CLUSTER-Leaf_Blcks/Clstr_fctr < .10 310378496 18456 763 44800 44800 1 100 03/05/2011 SYSADM PSRECDEFN PSDPSRECDEFN BAD CLUSTER-Leaf_Blcks/Clstr_fctr < .10 109051904 1913 89 44800 1 89 100 03/05/2011 SYSADM PSRECDEFN PSEPSRECDEFN BAD CLUSTER-Leaf_Blcks/Clstr_fctr < .10 2097152 20819 180 44800 44800 1 100 03/05/2011 SYSADM PSRECDEFN PS_PSRECDEFN BAD CLUSTER-Leaf_Blcks/Clstr_fctr < .10 2097152 18047 157 44800 44800 1 100 03/05/2011 SYSADM PSVERSION PS_PSVERSION 65536 1 1 91 91 1 @@ Block Inefficiency in SASCNJ at 09-MAR-11 14:14:42 ==================================================== OWNER TABLE_NAME SEGMENT_TYPE TABLE_KB NUM_ROWS BLOCKS EMPTY_BLOCKS HIGHWATER_MARK AVG_USED_BLOCKS BLOCK_INEFFICIENCY CHAIN_PCT MAX_EXTENT_PCT EXTENTS MAX_EXTENTS C NEXT_EXTENT MAX_FREE_SPACE TABLESPACE_NAME -------------------- ----------------------------------- ------------------ ---------- ---------- ---------- ------------ -------------- --------------- ------------------ ---------- -------------- ---------- ----------- - ----------- -------------- ------------------------------ SYSADM PSVERSION TABLE 64 91 8 0 7 3 57.14 0 0 1 2147483645 Y 106496 4849664 PTLOCK @@ Missing Ref Indexes in SASCNJ at 09-MAR-11 14:14:52 ==================================================== @@ @@ Table/Index Info - ????? @@ @@ @@ Selectivity Checks - SASCNJ @@ @@ @@ Get Values @@ @@ @@ Original Metrics - SASCNJ @@ --@@ EXECUTIONS CPU LOGICAL PHYSICAL ELAPSED ROWS --@@ ---------- ----------- -------- -------- ---------- -------- --@@ Explain time plan in SASCNJ at 09-MAR-11 14:16:57 ID PRNT QUERY_PLAN --- ---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Cardinality=185 Bytes=2,035) 1 0 NESTED LOOPS (Cost=3 Cardinality=185 Bytes=2,035) 2 1 TABLE ACCESS BY INDEX ROWID PSVERSION (Cost=1 Cardinality=1 Bytes=8) 3 2 INDEX UNIQUE SCAN PS_PSVERSION (Cost=0 Cardinality=1 Bytes=0 Columns=1) 4 1 INDEX RANGE SCAN PSDPSRECDEFN (Cost=2 Cardinality=185 Bytes=555 Columns=1) --@@ RunTime Stats in SASCNJ at 09-MAR-11 14:16:58 1 .840372167 12041 9830.5 65.2602478 0 --@@ DBMS xPlan Actual in SASCNJ at 09-MAR-11 14:16:58 SQL_ID 714sak05fchhj, child number 0 ------------------------------------- SELECT /* pid0003 030911 01 */ A.VERSION FROM PSRECDEFN A , PSVERSION B WHERE A.VERSION > B.VERSION AND B.OBJECTTYPENAME = 'RDM' Plan hash value: 1996347817 ------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.04 | 12046 | | 1 | NESTED LOOPS | | 1 | 185 | 0 |00:00:00.04 | 12046 | | 2 | TABLE ACCESS BY INDEX ROWID| PSVERSION | 1 | 1 | 1 |00:00:00.01 | 2 | |* 3 | INDEX UNIQUE SCAN | PS_PSVERSION | 1 | 1 | 1 |00:00:00.01 | 1 | |* 4 | INDEX RANGE SCAN | PSDPSRECDEFN | 1 | 185 | 0 |00:00:00.04 | 12044 | ------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("B"."OBJECTTYPENAME"='RDM') 4 - access("A"."VERSION">"B"."VERSION") @@ @@ Create Indexing @@ show parameter optimizer_use_invisible_indexes alter session set optimizer_use_invisible_indexes=true / create index ctriplett.tune_PSVERSION_01 on sysadm.PSVERSION ( OBJECTTYPENAME , VERSION ) tablespace psindex invisible compute statistics / --@@ Explain time plan in SASCNJ at 15-MAR-11 14:21:31 ID PRNT QUERY_PLAN --- ---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Cardinality=256 Bytes=3,072) 1 0 NESTED LOOPS (Cost=3 Cardinality=256 Bytes=3,072) 2 1 INDEX RANGE SCAN TUNE_PSVERSION_01 (Cost=1 Cardinality=1 Bytes=8 Columns=1) 3 1 INDEX RANGE SCAN PSDPSRECDEFN (Cost=2 Cardinality=256 Bytes=1,024 Columns=1) --@@ RunTime Stats in SASCNJ at 15-MAR-11 14:21:31 1 .024996 12044 0 .024913 0 --@@ DBMS xPlan Actual in SASCNJ at 15-MAR-11 14:21:31 SQL_ID 4nc0hpb6u3nxj, child number 0 ------------------------------------- SELECT /* pid0003 031511 02 */ A.VERSION FROM PSRECDEFN A , PSVERSION B WHERE A.VERSION > B.VERSION AND B.OBJECTTYPENAME = 'RDM' Plan hash value: 650071179 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.02 | 12044 | | 1 | NESTED LOOPS | | 1 | 256 | 0 |00:00:00.02 | 12044 | |* 2 | INDEX RANGE SCAN| TUNE_PSVERSION_01 | 1 | 1 | 1 |00:00:00.01 | 1 | |* 3 | INDEX RANGE SCAN| PSDPSRECDEFN | 1 | 256 | 0 |00:00:00.02 | 12043 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("B"."OBJECTTYPENAME"='RDM') 3 - access("A"."VERSION">"B"."VERSION")