Database Reference
In-Depth Information
Event 10053
Sometimes, when a query is being tuned and after generating the explain plan and trace files, turning on event 10046,
looking at wait statistics, and so forth, questions can arise. For example, why is the execution plan being generated
in this fashion? Or why is the runtime performance different from executing this from a SQL*Plus session during
query tuning?
Under these circumstances, turning on event 10053 will capture the actual steps that the CBO had taken to
generate the execution plan.
Enabling event 10053 is similar to enabling any other event, such as using an
ALTER SYSTEM
or
ALTER SESSION
statement. However, unlike event 10046, which was discussed in the previous section, event 10053 can generate trace
information only at two levels, level 1 and level 2.
ALTER SYSTEM SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
The following is a partial output of the optimizer behavior generated from event 10053 at level 1:
....................................................
....................................................
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: ORDERS[ORD]#0 ORDER_LINE[OL]#1
***************
Now joining: ORDER_LINE[OL]#1
***************
NL Join
Outer table: Card: 4299997.00 Cost: 5458.23 Resp: 5458.23 Degree: 1 Bytes: 11
Access path analysis for ORDER_LINE
Access Path: index (index (FFS))
Index: IORDL
resc_io: 178117.88 resc_cpu: 9843318719
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Inner table: ORDER_LINE Alias: OL
Access Path: index (FFS)
NL Join: Cost: 767233834757.07 Resp: 767233834757.07 Degree: 1
Cost_io: 765906333586.00 Cost_cpu: 42326241606806912
Resp_io: 765906333586.00 Resp_cpu: 42326241606806912
OPTIMIZER PERCENT INDEX CACHING = 0
ColGroup Usage:: PredCnt: 3 Matches Full: Partial:
ColGroup Usage:: PredCnt: 3 Matches Full: Partial:
Access Path: index (IndexOnly)
Index: IORDL
resc_io: 3.00 resc_cpu: 24214
Search WWH ::
Custom Search