Oracle 10053 Optimizer Trace: Seeing Inside the Cost-Based Optimizer :

Introduction :

Every time Oracle performs a hard parse of a SQL statement, the Cost-Based Optimizer (CBO) goes through a complex calculation process: reading statistics, evaluating access paths, considering query transformations, estimating cardinality, and finally calculating costs to pick a plan. Normally this process is invisible. The 10053 event makes it visible.

10053 does not trace SQL execution. It traces the optimizer’s decision-making process during parsing. It records which statistics the optimizer read, what cost it assigned to each candidate access path, which query transformations it considered, and ultimately why it discarded an alternative that you believe should have been better .

The key limitation is: 10053 only produces output during hard parsing. If the SQL is already cached in the shared pool, enabling 10053 and running it again will produce almost nothing. You must force a hard parse either by adding a comment to change the SQL text, or by flushing the shared pool .

Theoretical Foundation :

What 10053 Actually Records :

During a hard parse, the optimizer goes through several stages, and 10053 captures each one:

How Cost is Calculated :

The CBO calculates cost using System Statistics (CPU speed, I/O seek time, single-block read time, multi-block read time, etc.) combined with object statistics (number of blocks, cardinality, clustering factor). The 10053 trace shows the arithmetic: Scan IO Cost, Scan CPU Cost, Total Scan Cost, and for each access path, the final Cost value .

When you see a full table scan chosen over an index, the 10053 trace will show you the cost the optimizer assigned to both. If the index cost is higher than the full scan cost, you can then see why is the clustering factor too high? Is the index not being considered at all due to a predicate issue? Is the cardinality estimate wrong?

10053 vs 10046 :

Do not confuse the two. 10053 answers “why this plan”; 10046 answers “how did it run” .

Step 1 – Prepare the Session :

Set a Trace File Identifier :

This makes the trace file easy to find among hundreds of others in the trace directory.

ALTER SESSION SET tracefile_identifier = 'MY_10053_TEST';

Verify TIMED_STATISTICS :

SHOW PARAMETER timed_statistics;

If it is FALSE, set it to TRUE at session level. Without it, timing information in the trace is meaningless .

Enable 10053 :

ALTER SESSION SET events '10053 trace name context forever, level 1';

Level 1 is the standard level. Higher levels produce more output but are rarely necessary.

Step 2 – Force a Hard Parse :

This is the step most people get wrong. If you simply run the SQL again, Oracle will do a soft parse and 10053 will produce almost no output. You must force a hard parse.

Method A – Add a Comment :

SELECT /*+ my_10053_test */ * FROM orders WHERE customer_id = 100;

The comment changes the SQL text, so Oracle treats it as a new statement and performs a hard parse .

Method B – Flush the Shared Pool (Use with Caution) :

ALTER SYSTEM FLUSH SHARED_POOL;

This forces every cached SQL to be re-parsed. On a busy production system, this can cause a brief performance spike. Use Method A whenever possible.

Confirm the Trace File is Being Written :

SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

Run this after executing the SQL. The path returned is where the trace file is located. If you set tracefile_identifier, the file name will contain that string .

Step 3 – Disable 10053 Immediately :

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

Leaving 10053 enabled has minimal overhead when no hard parses occur, but it is good practice to disable it as soon as you have captured the parse you need.

Step 4 – Read the Trace File : 

You Cannot Use TKPROF :

This is important: TKPROF cannot format a 10053 trace file. You must read the raw .trc file directly. Open it in any text editor .

Key Sections to Look For :

1. PARAMETERS USED BY THE OPTIMIZER :

This section lists the optimizer parameters and system statistics in effect. Check optimizer_mode, optimizer_index_cost_adj, optimizer_index_caching, and the system statistics (MBRC, MREADTIM, SREADTIM) if they are set.

2. BASE STATISTICAL INFORMATION :

This shows the table and index statistics the optimizer read. Verify that the numbers match what you expect. If a table shows 100 rows but actually has 10 million, the statistics are stale and that is your root cause, not the optimizer .

3. SINGLE TABLE ACCESS PATH :

For each table, the trace shows the cost of each access path. For example, from a real 10053 trace:

Access path: Table Scan Cost: 430.000000
Access path: Index Scan Cost: 409.000000

Here the index is cheaper (409 vs 430), but if the optimizer still chose the full scan, the trace will show why perhaps the index was not considered due to a predicate, or the cost calculation was adjusted by a parameter .

4. QUERY TRANSFORMATIONS :

This section shows which transformations were considered (sub-query, un-nesting, view merging, predicate push down, etc.) and their costs. If you suspect a transformation is causing the problem, this section tells you whether it was applied and what it cost.

5. FINAL PLAN :

The trace ends with the selected plan and its total cost. Compare the costs of the rejected alternatives to understand the optimizer’s reasoning.

Step 5 – Interpret a Common Scenario :

The Symptom :

An index exists on orders(customer_id). A query WHERE customer_id = 100 returns 5 rows out of 1 million. The optimizer chooses a full table scan.

What 10053 Might Show :

Table: ORDERS #Rows: 1000000  Blocks: 20000
Index: IDX_ORDERS_CUST Clustering Factor: 950000

Access path: Table Scan Cost: 4500
Access path: Index Scan Cost: 8500

The Diagnosis :

The clustering factor is almost equal to the number of rows (950,000 out of 1,000,000). This means the rows for a given customer_id are scattered across nearly every block in the table. Even though only 5 rows match, the index scan requires reading 5 different blocks plus the index blocks, while the full table scan reads sequentially. The optimizer’s cost calculation is correct the index is genuinely worse for this data distribution.

The Fix :

The root cause is the data layout, not the optimizer. Options: rebuild the table with a different organization (e.g., partition by customer), use a composite index that covers the query, or accept the full scan. 10053 told you the truth: the index is not the answer here.

Practical Considerations :

Overhead and Risk :

10053 generates a large trace file for complex SQL with many joins. It also adds parsing overhead. Never enable 10053 at the system level on a production database. Use session-level tracing only, and only for the specific SQL you are investigating .

Trace File Location :

On Oracle 11g and later, trace files are managed by ADR. The default location is $DIAGNOSTIC_DEST/diag/rdbms/<dbname>/<instance>/trace/. Use V$DIAG_INFO to find the exact path for your session .

When 10053 is Not Enough :

If 10053 shows that the cost calculation is correct given the statistics, but the statistics themselves are wrong, the problem is statistics gathering, not the optimizer. If the statistics are correct but the cardinality estimate is still wrong, the issue may be in the predicate selectivity calculation 10053 will show you the estimated selectivity and the actual predicate used.

Summary :

10053 is the tool of last resort for plan issues. When SQL Monitor shows a cardinality mis-estimate, and statistics are accurate, 10053 is the only way to see the optimizer’s internal arithmetic. It does not fix anything it tells you the truth about why the plan is what it is. With that truth, you can decide whether the fix is a better index, a SQL rewrite, a plan baseline, or an optimizer parameter change.