Platform:	Oracle	App:		Tool:
DB Ver:	9.2	App Ver:		Tool Ver:
Revision Date:	06-Nov-2006	Keywords:	HOWTO, CBO 10053 TRACE
Goal
To 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:

1. Column statistics
2. Single Access Paths
3. Join Costs
4. Table Joins Considered
5. 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:

1. Parameters used by the optimizer
2. Index statistics