How to Enable Trace 10053 for a SQL Query in Oracle

Oracle execution plans often show what plan was chosen, but not why.
When a query performs poorly and the plan looks correct, trace 10053 helps explain the optimizer’s decisions.

The 10053 optimizer trace records how the Cost-Based Optimizer (CBO) evaluates statistics, estimates rows, and selects access paths.

What Is Trace 10053?

Trace 10053 is an optimizer trace.
It captures internal decisions made during SQL optimization.

It is useful when:

• Indexes are ignored
• Cardinality estimates are wrong
• Execution plans change unexpectedly

How to Enable Trace 10053

Enable the trace at session level.
Use it only for the SQL you want to analyze.

ALTER SESSION SET tracefile_identifier = '10053_order_query';
ALTER SESSION SET timed_statistics = TRUE;
ALTER SESSION SET statistics_level = ALL;
ALTER SESSION SET max_dump_file_size = UNLIMITED;
ALTER SESSION SET events '10053 trace name context forever, level 1';

Important Notes (Read This)

• tracefile_identifier
  Adds a readable name to the trace file.
  Always use a meaningful value.
• statistics_level = ALL
  Required for complete optimizer details.
• level 1
  Standard and recommended trace level.
• max_dump_file_size = UNLIMITED
  Prevents trace truncation.

Run the SQL Query

Run only one SQL statement while the trace is enabled.

SELECT *
FROM   orders o
JOIN   customers c
ON     o.customer_id = c.customer_id
WHERE  o.order_date >= DATE '2024-01-01'
AND    c.country = 'IN';

How to Disable Trace 10053 (No Confusion)

ALTER SESSION SET events '10053 trace name context off';

Important Clarification

• 10053 trace name context is fixed Oracle syntax
• Do not modify or shorten it
• Only the last word changes:
  • forever, level 1 → enable
  • off → disable

Real Example: Index Ignored by Optimizer

Problem

An index exists on orders(order_date).
Oracle still chooses a full table scan.

What Trace 10053 Shows

• High row count estimate for the date filter
• Low predicate selectivity
• Index cost higher than full table scan
• Optimizer rejects the index

Fix

Refresh table statistics or create histograms.
The issue is cardinality estimation, not indexing.

What Trace 10053 Shows

• Query transformations
• Statistics and row estimates
• Access path decisions
• Join order and join method
• Cost comparison of plans
• Partition pruning logic

When to Use Trace 10053

• Execution plan looks fine but performance is slow
• Indexes are not used as expected
• Cardinality estimates are incorrect
• Oracle Support requests optimizer diagnostics

Best Practices

• Use only for targeted SQL tuning
• Avoid running in busy production systems
• Trace one query per session
• Disable the trace immediately after use

Summary

Trace 10053 explains why Oracle chooses a plan.
It is an advanced tool, but essential when execution plans are not enough.