Explain Plan vs. Autotrace in SQL Developer: Stop Tuning on Predictions

The rewrite that \"worked\" — until it didn't

You rewrite a slow query, hit the Explain Plan button in SQL Developer, and the plan tree shows a lovely index range scan where a full table scan used to be. You ship it. A week later the report is still slow, because the plan the database actually executes is not the plan Explain Plan showed you.

This is one of the most common tuning mistakes in Oracle shops, and SQL Developer hands you both the trap and the way out of it in the same worksheet toolbar. The trap is Explain Plan: an optimizer prediction produced without running the query. The way out is Autotrace: real execution statistics collected after the statement actually runs. Knowing which button answers which question is the difference between tuning and guessing.

What each button really does

Explain Plan asks the optimizer, \"what would you do with this statement?\" It never executes anything. That's both its strength and its weakness:

• It's completely safe. You can run it against a destructive DELETE or a 12-hour query on production and nothing happens.
• It's a forecast. Bind variable peeking, stale statistics, and adaptive cursor sharing can all make the runtime plan differ from the estimate. If your query uses bind variables (:1-style placeholders), treat the explained plan with extra suspicion.

Autotrace executes the statement for real, then reports what happened: consistent gets (logical buffer reads), physical reads, rows processed, sorts, and the plan that was actually used. It answers the question Explain Plan can't: \"did my change actually reduce the work?\"

The catch: Autotrace runs the statement. Never point it at large DML or anything you wouldn't execute manually, and be thoughtful about running heavy queries on a busy production system.

A workflow that catches bad rewrites

Here's the loop I recommend before any query rewrite leaves your machine:

1. Paste the query into a worksheet and run Explain Plan (F6 in most builds; check your version under Tools > Preferences > Shortcut Keys if that doesn't work). Look for the obvious red flags: unexpected full table scans on large tables, Cartesian joins, or a missing index you assumed existed.
2. Fix the obvious plan-shape problems first. There's no point measuring logical I/O on a query that's accidentally joining a million rows to itself.
3. Run Autotrace on the original query and note the consistent gets. This is your baseline.
4. Apply your rewrite — add the index, restructure the join, replace the function on the indexed column — and Autotrace again.
5. Keep the change only if both improve: the plan shape looks sensible and consistent gets dropped meaningfully. A prettier plan with the same buffer gets is cosmetic.

Consistent gets are the metric to anchor on because they measure logical work regardless of whether data happened to be cached. Physical reads fluctuate with cache warmth; buffer gets tell you what the query costs at steady state.

Reading the plan tree without lying to yourself

SQL Developer renders plans as an indented tree, read innermost-first: the most deeply indented operation happens first, and results flow outward. A common misreading is assuming top-to-bottom equals execution order — it doesn't. When comparing two plans, compare the shape: which table is accessed first, which join method is used (nested loops vs. hash join), and where the rows get filtered.

A genuinely useful and underused feature: right-click in the plan output and you can save a plan and later compare another plan against it. Save the plan of your tuned query today, and after the next statistics gathering job or database upgrade, re-explain and diff. Plan regressions after stats refreshes are a classic source of \"it was fine last month\" incidents, and this gives you a cheap regression check.

When the GUI isn't enough: DBMS_XPLAN

Everything the GUI shows comes from the same underlying instrumentation, so you can reproduce it in scripts and code review. After running a query, this shows the actual plan with real row counts per operation:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

Run this in the same worksheet session immediately after executing the statement. The ALLSTATS LAST format adds columns like A-Rows (actual rows) next to E-Rows (estimated rows). A big gap between the two — say, estimated 10 rows, actual 2 million — tells you the optimizer is working from bad statistics, which is often the real problem behind a bad plan. Note that row-source statistics require the appropriate gathering to be enabled (it typically is via statistics_level defaults, but verify on your system) and your user needs privileges to view cursor statistics.

Limitations worth knowing before you rely on this

• Privileges matter. Explain Plan needs a PLAN_TABLE accessible to your user; Autotrace needs tracing privileges. On locked-down schemas the buttons may error or quietly do nothing. If a click produces no output, check for a privilege error in the log panel before assuming the feature is broken.
• Versions differ. Menu labels, default shortcuts, and plan-tree rendering have changed across SQL Developer releases. Older tutorials' screenshots may not match your install — confirm against Help > About and your own Preferences.
• Autotrace is not free. It executes the statement, so it consumes real resources and real time. On a busy system, prefer testing against a representative copy.

The takeaway

Explain Plan tells you what the optimizer intends; Autotrace tells you what it did. Use the first to catch structural blunders safely, the second to prove a rewrite reduced logical I/O, and DBMS_XPLAN.DISPLAY_CURSOR when you need evidence you can paste into a ticket. Next time a rewrite \"looks better,\\\" run Autotrace before and after and compare consistent gets — if the number didn't move, neither did your problem.