eunora_ora_11958_10053_RBO.trc

/u01/app/oracle/product/10.2.0/db_1/admin/eunora/udump/eunora_ora_11958_10053_RBO.trc

*** TRACE DUMP CONTINUED FROM FILE /u01/app/oracle/product/10.2.0/db_1/admin/eunora/udump/eunora_ora_11958.trc ***

*** SESSION ID:(147.28) 2009-11-20 23:32:48.874
Registered qb: SEL$1 0xb72acb6c (PARSER)
  signature (): qb_name=SEL$1 nbfros=2 flg=0
    fro(0): flg=4 objn=52906 hint_alias="T1"@"SEL$1"
    fro(1): flg=4 objn=52906 hint_alias="T2"@"SEL$1"
**************************
Predicate Move-Around (PM)
**************************
CBQT: Validity checks failed for c2j5vtrwtnu5k.
CVM: Considering view merge in query block SEL$1 (#0)
Query block (0xb72acb6c) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT /*+ RULE */ "T1"."OBJECT_NAME" "OBJECT_NAME","T2"."OBJECT_NAME" "OBJECT_NAME" FROM "SCOTT"."BIG_TABLE" "T1","SCOTT"."BIG_TABLE" "T2" WHERE "T1"."OBJECT_ID"="T2"."OBJECT_ID" AND "T1"."OWNER"='WMSYS' AND ROWNUM<1000
Query block (0xb72acb6c) unchanged
CBQT: Validity checks failed for c2j5vtrwtnu5k.

**************************
Predicate Move-Around (PM)
**************************
FPD: Considering simple filter push in SEL$1 (#0)
FPD:   Current where clause predicates in SEL$1 (#0) :
         "T1"."OBJECT_ID"="T2"."OBJECT_ID" AND "T1"."OWNER"='WMSYS' AND ROWNUM<1000
apadrv-start: call(in-use=368, alloc=16360), compile(in-use=35720, alloc=38832)
apadrv-end: call(in-use=372, alloc=16360), compile(in-use=36872, alloc=38832)
 
sql_id=c2j5vtrwtnu5k.
Current SQL statement for this session:
select /*+ RULE */ t1.object_name, t2.object_name
from scott.big_table t1, scott.big_table t2
  where t1.object_id= t2.object_id and t1.owner='WMSYS' and rownum < 1000
 
============
Plan Table
============
-----------------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name               | Rows  | Bytes | Cost  | Time      |
-----------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT              |                    |       |       |       |           |
| 1   |  COUNT STOPKEY                |                    |       |       |       |           |
| 2   |   TABLE ACCESS BY INDEX ROWID | BIG_TABLE          |       |       |       |           |
| 3   |    NESTED LOOPS               |                    |       |       |       |           |
| 4   |     TABLE ACCESS FULL         | BIG_TABLE          |       |       |       |           |
| 5   |     INDEX RANGE SCAN          | BIG_TABLE_OWNER_IDX|       |       |       |           |
-----------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter(ROWNUM<1000)
2 - filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
5 - access("T1"."OWNER"='WMSYS')
 
Content of other_xml column
===========================
  db_version     : 10.2.0.1
  parse_schema   : SCOTT
  plan_hash      : 2140694810
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      RBO_OUTLINE
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      INDEX(@"SEL$1" "T1"@"SEL$1" ("BIG_TABLE"."OWNER"))
      LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
      USE_NL(@"SEL$1" "T1"@"SEL$1")
    END_OUTLINE_DATA
  */
 
Query Block Registry:
*********************
SEL$1 0xb72acb6c (PARSER) [FINAL]
Optimizer State Dump: call(in-use=6544, alloc=32736), compile(in-use=56024, alloc=103756)
Dumping Hints
=============
  atom_hint=(@=0xb72a1624 err=0 resol=1 used=0 token=455 org=1 lvl=2 txt=RULE )

**************************
Predicate Move-Around (PM)
**************************
CBQT: Validity checks failed for 74whnf9gqba87.
CVM: Considering view merge in query block SEL$1 (#0)
Query block (0x29587134) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT /*+ RULE */ "T1"."OBJECT_NAME" "OBJECT_NAME","T2"."OBJECT_NAME" "OBJECT_NAME" FROM "SCOTT"."BIG_TABLE" "T1","SCOTT"."BIG_TABLE" "T2" WHERE "T1"."OBJECT_ID"="T2"."OBJECT_ID" AND "T1"."OWNER"='WMSYS' AND ROWNUM<1000
Query block (0x29587134) unchanged
CBQT: Validity checks failed for 74whnf9gqba87.

***************
Subquery Unnest
***************
SU: Considering subquery unnesting in query block SEL$1 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: Considering set-join conversion in SEL$1 (#0).
**************************
Predicate Move-Around (PM)
**************************
FPD: Considering simple filter push in SEL$1 (#0)
FPD:   Current where clause predicates in SEL$1 (#0) :
         "T1"."OBJECT_ID"="T2"."OBJECT_ID" AND "T1"."OWNER"='WMSYS' AND ROWNUM<1000
apadrv-start: call(in-use=368, alloc=16360), compile(in-use=31812, alloc=32640)
apadrv-end: call(in-use=372, alloc=16360), compile(in-use=32968, alloc=36696)
 
sql_id=74whnf9gqba87.
Current SQL statement for this session:
EXPLAIN PLAN SET STATEMENT_ID='PLUS1627' FOR select /*+ RULE */ t1.object_name, t2.object_name
from scott.big_table t1, scott.big_table t2
  where t1.object_id= t2.object_id and t1.owner='WMSYS' and rownum < 1000
 
============
Plan Table
============
-----------------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name               | Rows  | Bytes | Cost  | Time      |
-----------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT              |                    |       |       |       |           |
| 1   |  COUNT STOPKEY                |                    |       |       |       |           |
| 2   |   TABLE ACCESS BY INDEX ROWID | BIG_TABLE          |       |       |       |           |
| 3   |    NESTED LOOPS               |                    |       |       |       |           |
| 4   |     TABLE ACCESS FULL         | BIG_TABLE          |       |       |       |           |
| 5   |     INDEX RANGE SCAN          | BIG_TABLE_OWNER_IDX|       |       |       |           |
-----------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter(ROWNUM<1000)
2 - filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
5 - access("T1"."OWNER"='WMSYS')
 
Content of other_xml column
===========================
  db_version     : 10.2.0.1
  parse_schema   : SCOTT
  plan_hash      : 2140694810
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      RBO_OUTLINE
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      INDEX(@"SEL$1" "T1"@"SEL$1" ("BIG_TABLE"."OWNER"))
      LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
      USE_NL(@"SEL$1" "T1"@"SEL$1")
    END_OUTLINE_DATA
  */
 
Query Block Registry:
*********************
SEL$1 0x29587134 (PARSER) [FINAL]
Optimizer State Dump: call(in-use=6544, alloc=32736), compile(in-use=57724, alloc=106580)
Dumping Hints
=============
  atom_hint=(@=0x2962cca4 err=0 resol=1 used=0 token=455 org=1 lvl=2 txt=RULE )
Registered qb: SEL$E112F6F0 0xb72a8718 (PARSER)
  signature (): qb_name=SEL$E112F6F0 nbfros=1 flg=0
    fro(0): flg=4 objn=0 hint_alias="KOKBF$"@"SEL$E112F6F0"
Registered qb: SEL$1 0xb72acb6c (PARSER)
  signature (): qb_name=SEL$1 nbfros=1 flg=0
    fro(0): flg=5 objn=0 hint_alias="from$_subquery$_001"@"SEL$1"