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:
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

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:
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