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.
Trace files are located in the below location
<diagnostic_dest>/diag/rdbms/<dbname>/<instance name>/trace
Where diagnostic_dest is specified in the initialization parameter diagnostic_dest.
This can be obtained by running the SQL:
SELECT value FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';
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
SQL trace in Oracle: SQL trace or 10046 event is being used to trace the session activity in Oracle.
sql tuning advisor: How to run sql tuning advisor for “SQL_ID” in the Cursor cache, how is the sql tuning task created and executed to get the recommendation
Autotrace in Oracle: Autotrace in Oracle, What is autotrace, how to set up it, Understanding Autotrace Output, Autotrace options, statistics displayed by autotrace
How to Obtain Tracing of Optimizer Computations (EVENT 10053) (Doc ID 225598.1)
