Home » Oracle » Oracle Database » How to enable 10053 trace in Oracle

How to enable 10053 trace in Oracle

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
See also  Must Read Top 100 Hyperion Essbase Interview Questions

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)

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top