finally: "T1"."OBJECT_ID"="T2"."OBJECT_ID" AND "T1"."OWNER"='WMSYS' AND ROWNUM<1000
/u01/app/oracle/product/10.2.0/db_1/admin/eunora/udump/eunora_ora_11909_10053_CBO.trc
*** TRACE DUMP CONTINUED FROM FILE /u01/app/oracle/product/10.2.0/db_1/admin/eunora/udump/eunora_ora_11909.trc ***
*** SESSION ID:(144.150) 2009-11-20 23:31:42.038
Registered qb: SEL$1 0xb72aa6b4 (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)
**************************
PM: Considering predicate move-around in SEL$1 (#0).
PM: Checking validity of predicate move-around in SEL$1 (#0).
CBQT: Validity checks failed for 6u9s8c0j8468g.
CVM: Considering view merge in query block SEL$1 (#0)
Query block (0xb72aa6b4) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "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 (0xb72aa6b4) unchanged
CBQT: Validity checks failed for 6u9s8c0j8468g.
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#0).
PM: Checking validity of predicate move-around in SEL$1 (#0).
PM: PM bypassed: Outer query contains no views.
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
kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0)
predicates with check contraints: "T1"."OBJECT_ID"="T2"."OBJECT_ID" AND "T1"."OWNER"='WMSYS' AND 1000>ROWNUM
after transitive predicate generation: "T1"."OBJECT_ID"="T2"."OBJECT_ID" AND "T1"."OWNER"='WMSYS' AND ROWNUM<1000
finally: "T1"."OBJECT_ID"="T2"."OBJECT_ID" AND "T1"."OWNER"='WMSYS' AND ROWNUM<1000
apadrv-start: call(in-use=368, alloc=16360), compile(in-use=35240, alloc=38000)
Using WORKLOAD Stats
CPUSPEED: 916 millions instructions/sec
SREADTIM: 9 milliseconds
MREADTIM: 2 millisecons
MBRC: 16.000000 blocks
MAXTHR: 2163712 bytes/sec
SLAVETHR: -1 bytes/sec
Table: BIG_TABLE Alias: T2
#Rows: 10017630 #Blks: 147572 AvgRowLen: 99.00
Column (#5): OBJECT_ID(NUMBER)
AvgLen: 5.00 NDV: 42474 Nulls: 0 Density: 2.3544e-05 Min: 258 Max: 52906
Index: BIG_TABLE_OWNER_IDX Col#: 2
LVLS: 2 #LB: 23290 #DK: 14 LB/K: 1663.00 DB/K: 20937.00 CLUF: 293120.00
Index: BIG_TABLE_PK Col#: 1
LVLS: 2 #LB: 21740 #DK: 9817090 LB/K: 1.00 DB/K: 1.00 CLUF: 154320.00
-# Table Stats & Index Stats of T1
Table: BIG_TABLE Alias: T1
#Rows: 10017630 #Blks: 147572 AvgRowLen: 99.00
Column (#5): OBJECT_ID(NUMBER)
AvgLen: 5.00 NDV: 42474 Nulls: 0 Density: 2.3544e-05 Min: 258 Max: 52906
Index: BIG_TABLE_OWNER_IDX Col#: 2
LVLS: 2 #LB: 23290 #DK: 14 LB/K: 1663.00 DB/K: 20937.00 CLUF: 293120.00
Index: BIG_TABLE_PK Col#: 1
LVLS: 2 #LB: 21740 #DK: 9817090 LB/K: 1.00 DB/K: 1.00 CLUF: 154320.00
Column (#2): OWNER(VARCHAR2)
AvgLen: 6.00 NDV: 14 Nulls: 0 Density: 0.071429
Table: BIG_TABLE Alias: T1
Card: Original: 10017630 Rounded: 715545 Computed: 715545.00 Non Adjusted: 715545.00
Access Path: TableScan
Cost: 32665.18 Resp: 32665.18 Degree: 0
Cost_io: 32283.00 Cost_cpu: 3297736444
Resp_io: 32283.00 Resp_cpu: 3297736444
Access Path: index (AllEqRange)
Index: BIG_TABLE_OWNER_IDX
resc_io: 22604.00 resc_cpu: 471168580
ix_sel: 0.071429 ix_sel_with_filters: 0.071429
Cost: 22658.60 Resp: 22658.60 Degree: 1
Best:: AccessPath: IndexRange Index: BIG_TABLE_OWNER_IDX
Cost: 22658.60 Degree: 1 Resp: 22658.60 Card: 715545.00 Bytes: 0
Table: BIG_TABLE Alias: T2
Card: Original: 10017630 Rounded: 10017630 Computed: 10017630.00 Non Adjusted: 10017630.00
Access Path: TableScan
Cost: 32671.82 Resp: 32671.82 Degree: 0
Cost_io: 32283.00 Cost_cpu: 3354980044
Resp_io: 32283.00 Resp_cpu: 3354980044
Best:: AccessPath: TableScan
Cost: 32671.82 Degree: 1 Resp: 32671.82 Card: 10017630.00 Bytes: 0
Considering cardinality-based initial join order
************************************************
Join order[1]: BIG_TABLE[T1]#0 BIG_TABLE[T2]#1
************************************************
Now joining: BIG_TABLE[T2]#1
************************************************
1. NL Join
Outer table: Card: 715545.00 Cost: 22658.60 Resp: 22658.60 Degree: 1 Bytes: 37
Inner table: BIG_TABLE Alias: T2
Access Path: TableScan
NL Join: Cost: 23377014118.44 Resp: 23377014118.44 Degree: 0
Cost_io: 23098799080.00 Cost_cpu: 2400639666523586
Resp_io: 23098799080.00 Resp_cpu: 2400639666523586
Best NL cost: 23377014118.44
resc: 23377014118.44 resc_io: 23098799080.00 resc_cpu: 2400639666523586
resp: 23377014118.44 resp_io: 23098799080.00 resp_cpu: 2400639666523586
Join Card: 168763597.93 = outer (715545.00) * inner (10017630.00) * sel (2.3544e-05)
Join Card - Rounded: 168763598 Computed: 168763597.93
2. SM Join
Outer table:
resc: 22658.60 card 715545.00 bytes: 37 deg: 1 resp: 22658.60
Inner table: BIG_TABLE Alias: T2
resc: 32671.82 card: 10017630.00 bytes: 31 deg: 1 resp: 32671.82
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 91 Area size: 131072 Max Area size: 16357376
Degree: 1
Blocks to Sort: 4468 Row size: 51 Total Rows: 715545
Initial runs: 3 Merge passes: 1 IO Cost / pass: 2422
Total IO sort cost: 6890 Total CPU sort cost: 745655415
Total Temp space used: 68985000
SORT resource Sort statistics
Sort width: 91 Area size: 131072 Max Area size: 16357376
Degree: 1
Blocks to Sort: 55191 Row size: 45 Total Rows: 10017630
Initial runs: 28 Merge passes: 1 IO Cost / pass: 29896
Total IO sort cost: 85087 Total CPU sort cost: 11864141950
Total Temp space used: 804578000
SM join: Resc: 150608.53 Resp: 150608.53 [multiMatchCost=1839.74]
SM cost: 150608.53
resc: 150608.53 resc_io: 146864.00 resc_cpu: 32310542788
resp: 150608.53 resp_io: 146864.00 resp_cpu: 32310542788
3. HA Join
Outer table:
resc: 22658.60 card 715545.00 bytes: 37 deg: 1 resp: 22658.60
Inner table: BIG_TABLE Alias: T2
resc: 32671.82 card: 10017630.00 bytes: 31 deg: 1 resp: 32671.82
using dmeth: 2 #groups: 1
Cost per ptn: 22284.17 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 79454.33 Resp: 79454.33 [multiMatchCost=1839.74]
HA cost: 79454.33
resc: 79454.33 resc_io: 76899.00 resc_cpu: 22049195240
resp: 79454.33 resp_io: 76899.00 resp_cpu: 22049195240
Best:: JoinMethod: Hash
Cost: 79454.33 Degree: 1 Resp: 79454.33 Card: 168763597.93 Bytes: 68
***********************
Best so far: Table#: 0 cost: 22658.6047 card: 715545.0000 bytes: 26475165
Table#: 1 cost: 79454.3263 card: 168763597.9270 bytes: 11475924664
*********************************
Number of join permutations tried: 1
*********************************
(newjo-save) [1 0 ]
Final - All Rows Plan: Best join order: 1
Cost: 79454.3263 Degree: 1 Card: 168763598.0000 Bytes: 11475924664
Resc: 79454.3263 Resc_io: 76899.0000 Resc_cpu: 22049195240
Resp: 79454.3263 Resp_io: 76899.0000 Resc_cpu: 22049195240
kkoipt: Query block SEL$1 (#0)
SINGLE TABLE ACCESS PATH (First K Rows)
Table: BIG_TABLE Alias: T1
Card: Original: 70 Rounded: 5 Computed: 5.00 Non Adjusted: 5.00
Access Path: TableScan
Cost: 2.00 Resp: 2.00 Degree: 0
Cost_io: 2.00 Cost_cpu: 29943
Resp_io: 2.00 Resp_cpu: 29943
Access Path: index (AllEqRange)
Index: BIG_TABLE_OWNER_IDX
resc_io: 4.00 resc_cpu: 31066
ix_sel: 0.071429 ix_sel_with_filters: 0.071429
Cost: 4.00 Resp: 4.00 Degree: 1
Best:: AccessPath: IndexRange Index: BIG_TABLE_OWNER_IDX
Cost: 4.00 Degree: 1 Resp: 4.00 Card: 5.00 Bytes: 37
***************************************
SINGLE TABLE ACCESS PATH (First K Rows)
Table: BIG_TABLE Alias: T2
Card: Original: 8486306 Rounded: 8486306 Computed: 8486306.00 Non Adjusted: 8486306.00
Access Path: TableScan
Cost: 27678.38 Resp: 27678.38 Degree: 0
Cost_io: 27349.00 Cost_cpu: 2842130080
Resp_io: 27349.00 Resp_cpu: 2842130080
Best:: AccessPath: TableScan
Cost: 27678.38 Degree: 1 Resp: 27678.38 Card: 8486306.00 Bytes: 31
First K Rows: unchanged join prefix len = 1
***********************
Join order[1]: BIG_TABLE[T1]#0 BIG_TABLE[T2]#1
***************
Now joining: BIG_TABLE[T2]#1
***************
1. NL Join
Outer table: Card: 5.00 Cost: 4.00 Resp: 4.00 Degree: 1 Bytes: 37
Inner table: BIG_TABLE Alias: T2
Access Path: TableScan
NL Join: Cost: 138386.90 Resp: 138386.90 Degree: 0
Cost_io: 136740.00 Cost_cpu: 14210681467
Resp_io: 136740.00 Resp_cpu: 14210681467
Best NL cost: 138386.90
resc: 138386.90 resc_io: 136740.00 resc_cpu: 14210681467
resp: 138386.90 resp_io: 136740.00 resp_cpu: 14210681467
Join Card: 999.00 = outer (5.00) * inner (8486306.00) * sel (2.3544e-05)
Join Card - Rounded: 999 Computed: 999.00
2. SM Join
Outer table:
resc: 22658.60 card 715545.00 bytes: 37 deg: 1 resp: 22658.60
Inner table: BIG_TABLE Alias: T2
resc: 32671.82 card: 10017630.00 bytes: 31 deg: 1 resp: 32671.82
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 91 Area size: 131072 Max Area size: 16357376
Degree: 1
Blocks to Sort: 4468 Row size: 51 Total Rows: 715545
Initial runs: 3 Merge passes: 1 IO Cost / pass: 2422
Total IO sort cost: 6890 Total CPU sort cost: 745655415
Total Temp space used: 68985000
SORT resource Sort statistics
Sort width: 91 Area size: 131072 Max Area size: 16357376
Degree: 1
Blocks to Sort: 55191 Row size: 45 Total Rows: 10017630
Initial runs: 28 Merge passes: 1 IO Cost / pass: 29896
Total IO sort cost: 85087 Total CPU sort cost: 11864141950
Total Temp space used: 804578000
SM join: Resc: 148768.80 Resp: 148768.80 [multiMatchCost=0.00]
SM cost: 148768.80
resc: 148768.80 resc_io: 146864.00 resc_cpu: 16435945988
resp: 148768.80 resp_io: 146864.00 resp_cpu: 16435945988
3. HA Join
Outer table:
resc: 22658.60 card 715545.00 bytes: 37 deg: 1 resp: 22658.60
Inner table: BIG_TABLE Alias: T2
resc: 27678.38 card: 8486306.00 bytes: 31 deg: 1 resp: 27678.38
using dmeth: 2 #groups: 1
Cost per ptn: 19132.85 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 69469.84 Resp: 69469.84 [multiMatchCost=0.00]
HA cost: 69469.84
resc: 69469.84 resc_io: 68853.00 resc_cpu: 5322508170
resp: 69469.84 resp_io: 68853.00 resp_cpu: 5322508170
First K Rows: copy A prefix plans, len = 1
Best:: JoinMethod: Hash
Cost: 69469.84 Degree: 1 Resp: 69469.84 Card: 999.00 Bytes: 68
***********************
Best so far: Table#: 0 cost: 22658.6047 card: 715545.0000 bytes: 26475165
Table#: 1 cost: 69469.8364 card: 999.0001 bytes: 67932
***********************
Join order[2]: BIG_TABLE[T2]#1 BIG_TABLE[T1]#0
***************
Now joining: BIG_TABLE[T1]#0
***************
1. NL Join
Outer table: Card: 10017630.00 Cost: 32671.82 Resp: 32671.82 Degree: 1 Bytes: 31
Inner table: BIG_TABLE Alias: T1
Access Path: TableScan
NL Join: Cost: 327859418287.58 Resp: 327859418287.58 Degree: 0
Cost_io: 323382902926.00 Cost_cpu: 38626597630795120
Resp_io: 323382902926.00 Resp_cpu: 38626597630795120
Access Path: index (AllEqJoin)
Index: BIG_TABLE_OWNER_IDX
resc_io: 22603.00 resc_cpu: 471162308
ix_sel: 0.071429 ix_sel_with_filters: 0.071429
NL Join: Cost: 226975525860.50 Resp: 226975525860.50 Degree: 1
Cost_io: 226428523173.00 Cost_cpu: 4719933029675726
Resp_io: 226428523173.00 Resp_cpu: 4719933029675726
Best NL cost: 226975525860.50
resc: 226975525860.50 resc_io: 226428523173.00 resc_cpu: 4719933029675726
resp: 226975525860.50 resp_io: 226428523173.00 resp_cpu: 4719933029675726
Join Card: 168763597.93 = outer (10017630.00) * inner (715545.00) * sel (2.3544e-05)
Join Card - Rounded: 168763598 Computed: 168763597.93
2. SM Join
Outer table:
resc: 32671.82 card 10017630.00 bytes: 31 deg: 1 resp: 32671.82
Inner table: BIG_TABLE Alias: T1
resc: 22658.60 card: 715545.00 bytes: 37 deg: 1 resp: 22658.60
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 91 Area size: 131072 Max Area size: 16357376
Degree: 1
Blocks to Sort: 55191 Row size: 45 Total Rows: 10017630
Initial runs: 28 Merge passes: 1 IO Cost / pass: 29896
Total IO sort cost: 85087 Total CPU sort cost: 11864141950
Total Temp space used: 804578000
SORT resource Sort statistics
Sort width: 91 Area size: 131072 Max Area size: 16357376
Degree: 1
Blocks to Sort: 4468 Row size: 51 Total Rows: 715545
Initial runs: 3 Merge passes: 1 IO Cost / pass: 2422
Total IO sort cost: 6890 Total CPU sort cost: 745655415
Total Temp space used: 68985000
SM join: Resc: 150608.53 Resp: 150608.53 [multiMatchCost=1839.74]
SM cost: 150608.53
resc: 150608.53 resc_io: 146864.00 resc_cpu: 32310542788
resp: 150608.53 resp_io: 146864.00 resp_cpu: 32310542788
3. HA Join
Outer table:
resc: 32671.82 card 10017630.00 bytes: 31 deg: 1 resp: 32671.82
Inner table: BIG_TABLE Alias: T1
resc: 22658.60 card: 715545.00 bytes: 37 deg: 1 resp: 22658.60
using dmeth: 2 #groups: 1
Cost per ptn: 22230.26 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 79508.23 Resp: 79508.23 [multiMatchCost=1947.54]
4. HA Join (swap)
Outer table:
resc: 22658.60 card 715545.00 bytes: 37 deg: 1 resp: 22658.60
Inner table: BIG_TABLE Alias: T2
resc: 32671.82 card: 10017630.00 bytes: 31 deg: 1 resp: 32671.82
using dmeth: 2 #groups: 1
Cost per ptn: 22284.17 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 79454.33 Resp: 79454.33 [multiMatchCost=1839.74]
HA cost: 79454.33
resc: 79454.33 resc_io: 76899.00 resc_cpu: 22049195240
resp: 79454.33 resp_io: 76899.00 resp_cpu: 22049195240
Best:: JoinMethod: Hash
Cost: 79454.33 Degree: 1 Resp: 79454.33 Card: 168763597.93 Bytes: 68
***********************
Best so far: Table#: 1 cost: 32671.8155 card: 10017630.0000 bytes: 310546530
Table#: 0 cost: 79454.3263 card: 168763597.9270 bytes: 11475924664
============
Plan Table
============
------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 24K | |
| 1 | COUNT STOPKEY | | | | | |
| 2 | HASH JOIN | | 161M | 16G | 24K | 00:04:50 |
| 3 | TABLE ACCESS BY INDEX ROWID | BIG_TABLE | 699K | 25M | 22K | 00:04:34 |
| 4 | INDEX RANGE SCAN | BIG_TABLE_OWNER_IDX| 699K | | 1684 | 00:00:16 |
| 5 | TABLE ACCESS FULL | BIG_TABLE | 60 | 1860 | 2 | 00:00:01 |
------------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter(ROWNUM<1000)
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
4 - access("T1"."OWNER"='WMSYS')
Content of other_xml column
===========================
db_version : 10.2.0.1
parse_schema : SCOTT
plan_hash : 1257529114
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
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_HASH(@"SEL$1" "T1"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/