10053 trace is the optimizer trace. It externalizes most of the information that the optimizer uses in generating a plan for a query. In this post, we will check out How to enable 10053 trace in Oracle
How to enable 10053 trace in Oracle in your own session
You can turn on the optimizer trace using the below event
alter session set max_dump_file_size = unlimited;
ALTER SESSION SET EVENTS '10053 trace name context forever level 1';
or
ALTER SESSION SET EVENTS '10053 trace name context forever level 2';
You can turn off the tracing using the below sql
ALTER SESSION SET EVENTS '10053 trace name context off'
How to enable 10053 trace in a different session
We can turn on the trace using
exec SYS.DBMS_SYSTEM.SET_EV (<sid>,<serial#> , 10053, 1, '') or exec SYS.DBMS_SYSTEM.SET_EV (<sid>,<serial#> , 10053, 2, '')
We can turn it off using the below command
exe SYS.DBMS_SYSTEM.SET_EV (<sid>,<serial#> , 10053, 0, '');
Location of the optimizer trace files
The trace file is generated at the same location as the normal sql trace file.
Levels of Optimizer trace
there are two levels
- Level 1 produces more details
- level 2 produces fewer details
Restriction of Optimizer trace
- This event has no impact on queries optimized by the Rule Based Optimizer (RBO).
- For a 10053 trace to be produced, the QUERY must be using the CBO and must be reparsed with the event in place.
How to search for the optimizer trace file
To identify the correct trace file, search for the relevant –SQL STATEMENT–.
This will be followed by a section headed “PARAMETERS USED BY THE OPTIMIZER”.
Details about Optimizer Trace
Optimizer trace contains these details for each query
• Parameters used by the optimizer
• Base Statistical Information
• Base Table Access Cost
• General Plans
• Recosting for the special feature
I hope you like this content on Optimizer trace
Related Articles
Optimizer mode
Optimizer hints
Leave a Reply