• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » How to enable 10053 trace in Oracle

How to enable 10053 trace in Oracle

March 16, 2023 by techgoeasy Leave a Comment

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

Table of Contents

  • How to enable 10053 trace in Oracle in your own session
  • How to enable 10053 trace in a different session
  • Location of the optimizer trace files
  • Levels of Optimizer trace
  • Restriction of Optimizer trace
  • How to search for the optimizer trace file
  • Details about Optimizer Trace

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

Filed Under: Oracle, Oracle Database

Reader Interactions

Leave a Reply Cancel reply

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

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • find segment name from block in oracle
  • How the sql query is executed in Oracle
  • How to do sql query tuning in Oracle
  • How to enable 10053 trace in Oracle
  • How to find the bind variable of the sql id

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us