Review SQL Execution Plans Before Deployment Using SQL Developer

The problem: a query that works in development but slows production

You finish a SQL change, test it against a small development table, and promote it. In production, the same query runs for minutes instead of seconds. The difference is usually data volume, statistics, or bind values, not the syntax. Reviewing the execution plan before promotion catches table scans, missing indexes, and unexpected join orders early.

SQL Developer’s SQL Worksheet includes two related tools: Explain Plan and Autotrace. Explain Plan asks the optimizer for an estimated plan without running the statement. Autotrace executes the statement and appends actual runtime statistics. Using both gives you a pre-deployment checkpoint for SQL performance.

Privileges and setup you need

Both features require an active Oracle connection. Explain Plan typically needs access to PLAN_TABLE and DBMS_XPLAN. Autotrace additionally needs privileges to query dynamic performance views such as V$SQL_PLAN, depending on the database version and SQL Developer release. If the plan pane is blank, the missing piece is usually a grant rather than a SQL Developer bug.

Ask your DBA to confirm these before you start:

• SELECT, INSERT, UPDATE, DELETE on PLAN_TABLE or a private plan table
• EXECUTE on DBMS_XPLAN
• SELECT on V$SQL_PLAN or related dictionary views for Autotrace

SQL Developer writes Explain Plan output to PLAN_TABLE by default. Older releases display classic PLAN_TABLE rows; newer releases often format output through DBMS_XPLAN.DISPLAY_CURSOR. The underlying behavior is the same: you are reading an optimizer estimate, not a guaranteed production result.

Worked example: review a SELECT before deployment

Open a SQL Worksheet connected to the target schema. Highlight only the SELECT statement you plan to promote. Do not highlight surrounding comments or multiple statements.

SELECT o.order_id, c.customer_name, o.order_date
FROM   orders o
JOIN   customers c ON c.customer_id = o.customer_id
WHERE  o.order_date >= :start_date
AND    o.status = 'OPEN';

Run Explain Plan from the worksheet toolbar or context menu. SQL Developer populates the plan pane with operation, cost, cardinality, and predicate rows. Look for:

• TABLE ACCESS FULL on a large table where an index range scan is expected
• High cost relative to similar queries in the same workload
• Predicate information showing implicit conversion or functions on indexed columns

To cross-check what SQL Developer read, run this in the same worksheet:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

If the plan looks acceptable, run Autotrace on the same highlighted statement. Autotrace executes the query and adds actual rows, consistent gets, and physical reads. This is useful for comparing logical I/O before and after a change, but it means the statement runs against real data.

Where to run and what to expect

Step	Where	Required permission	Expected check
Explain Plan	SQL Worksheet, highlighted SELECT	PLAN_TABLE access, DBMS_XPLAN	Plan grid shows operations, cost, predicates
Autotrace	SQL Worksheet, highlighted SELECT	V$ views or equivalent, PLAN_TABLE	Actual rows, consistent gets, physical reads appear
Cross-check	SQL Worksheet	SELECT on DBMS_XPLAN.DISPLAY	DBMS_XPLAN output matches plan pane

Limitations and risks

Explain Plan is an estimate. Bind peeking, data skew, and partitioned tables can produce a different plan in production than in your worksheet. Autotrace runs the statement, so do not use it against large production tables or DML unless you accept the performance impact. SQL Developer Autotrace output also differs from SQL*Plus SET AUTOTRACE; do not assume identical statistics or formatting.

If Autotrace executes a long-running query, cancel it in the SQL Developer status bar rather than letting it finish. The plan pane may remain incomplete if your connection lacks V$ privileges.

Practical closing checklist

1. Highlight one SELECT statement in the SQL Worksheet.
2. Run Explain Plan and confirm cost, operation, and predicate rows appear.
3. Run SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY) to cross-check the plan.
4. Run Autotrace only on representative data volumes you can afford to execute.
5. Save or copy the plan output and attach it to the pull request or change ticket.

These steps will not guarantee identical production performance, but they turn a guess into a reviewable artifact before the SQL reaches production.