Database Reference
In-Depth Information
*********************************
Number of join permutations tried: 2
*********************************
Consider using bloom filter between ORD[ORDERS] and OL[ORDER_LINE]
kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000) rejected because no single-tables
predicates
(newjo-save) [0 1 ]
ColGroup Usage:: PredCnt: 3 Matches Full: Partial:
GROUP BY adjustment factor: 0.500000
GROUP BY cardinality: 150.000000, TABLE cardinality: 42592863.000000
SORT ressource Sort statistics
Sort width: 6142 Area size: 1048576 Max Area size: 1073741824
Degree: 1
Blocks to Sort: 245087 Row size: 47 Total Rows: 42592863
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 48667303854
Total Temp space used: 0
Trying or-Expansion on query block SEL$1 (#1)
Transfer Optimizer annotations for query block SEL$1 (#1)
id=0 frofkksm[i] (sort-merge/hash) predicate="OL"."OL_O_ID"="ORD"."O_ID"
id=0 frofkksm[i] (sort-merge/hash) predicate="OL"."OL_W_ID"="ORD"."O_W_ID"
id=0 frofkksm[i] (sort-merge/hash) predicate="OL"."OL_D_ID"="ORD"."O_D_ID"
id=0 frosand (sort-merge/hash) predicate="OL"."OL_D_ID"="ORD"."O_D_ID" AND "OL"."OL_W_
ID"="ORD"."O_W_ID" AND "OL"."OL_O_ID"="ORD"."O_ID"
ColGroup Usage:: PredCnt: 3 Matches Full: Partial:
GROUP BY adjustment factor: 1.000000
Final cost for query block SEL$1 (#1) - All Rows Plan:
Best join order: 1
Cost: 259483.2873 Degree: 1 Card: 42592863.0000 Bytes: 1405564479
Resc: 259483.2873 Resc_io: 257313.0000 Resc_cpu: 69197757272
Resp: 259483.2873 Resp_io: 257313.0000 Resc_cpu: 69197757272
kkoqbc-subheap (delete addr=0x2b25e4dc7a40, in-use=55944, alloc=59112)
kkoqbc-end:
:
call(in-use=186432, alloc=259112), compile(in-use=322912, alloc=370520), execution(in-use=4104,
alloc=8088)
kkoqbc: finish optimizing query block SEL$1 (#1)
apadrv-end
The trace output from event 10053 illustrates how the CBO actually performs the cost computation process
during the generation of an execution plan for a query.
For example, in the above illustration, the first join order is similar to what was specified in the SQL query.
Join order[1]: ORDERS[ORD]#0 ORDER_LINE[OL]#1
Subsequently, Oracle creates a different join order and calculates its cost of its execution path.
Join order[2]: ORDER_LINE[OL]#1 ORDERS[ORD]#0
Search WWH ::




Custom Search