HOW TO trace the CBO working out the execution path (event 10053)
GoalTo trace the CBO working out the execution path (event 10053)
Facts
Solution
To start the CBO trace enter the following command:
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
Run the SQL that you wanto trace the CBO optimizer on, e.g.
SELECT *
FROM oe_order_headers_v
WHERE header_id = 999
/
When the query has completed, run the following command to switch the trace off:
ALTER SESSION SET EVENTS '10053 trace name context off';
There appear to 2 levels to the trace:
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS='10053 trace name context forever, level 2';
Level 2 is a subset of Level 1 and includes:
Column statistics
Single Access Paths
Join Costs
Table Joins Considered
Join Methods Considered (NL/MS/HA)
but Level 1 is the more detailed of the two; as well as ALL of level 2, it also includes:
Parameters used by the optimizer
Index statistics
页:
[1]