Using SQL Developer Explain Plan to Spot Query Bottlenecks Without Running the Statement

Problem: you need to tune a query but running it on a production copy is risky

When a SELECT statement looks slow, the safest first step is to see what the Oracle optimizer thinks it will do, without actually fetching rows. SQL Developer’s Explain Plan feature lets you generate that estimate directly in the SQL Worksheet, using the same optimizer that runs the statement in the database.

Thesis: Explain Plan gives you a quick, safe view of the optimizer’s chosen access paths, and you can export or reuse that information for further analysis or automation.

1. Invoking Explain Plan and choosing a view

Open a SQL Worksheet, connect to the target schema, and type or paste the statement you want to inspect. For example:

SELECT e.employee_id, e.last_name, d.department_name
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id
WHERE  e.salary > :min_salary;

With the cursor anywhere in the worksheet, press Ctrl+E or click the Explain Plan toolbar icon (a small chart). SQL Developer runs EXPLAIN PLAN FOR behind the scenes and populates the lower pane with the plan.

You can switch between three representations using the tabs at the top of the plan pane:

• Graphical – a tree‑like diagram showing operations as nodes.
• Hierarchical – an indented text outline that mirrors the graphical tree.
• Raw XML – the exact XML output from DBMS_XPLAN.DISPLAY, useful for diffing or programmatic parsing.

2. Worked example: comparing graphical and textual views

Assume the statement above is executed with :min_salary bound to 5000. After pressing Ctrl+E, the graphical view might show:

• TABLE ACCESS FULL EMPLOYEES (cost 12)
• HASH JOIN (cost 18)
• TABLE ACCESS FULL DEPARTMENTS (cost 4)

Switching to the Hierarchical tab yields the same information in text:

SELECT STATEMENT
  HASH JOIN
    TABLE ACCESS FULL EMPLOYEES
    TABLE ACCESS FULL DEPARTMENTS

Both views display the same operation IDs, object names, and estimated costs, confirming consistency.

3. Exporting and reusing the plan

To keep a record or share the plan with a teammate, click the Export button (floppy‑disk icon) in the plan pane and choose HTML. The generated file contains a formatted table that matches the hierarchical view exactly.

If you need to reproduce the same plan in SQL*Plus or a script, you can run:

EXPLAIN PLAN FOR
  SELECT e.employee_id, e.last_name, d.department_name
  FROM   employees e
  JOIN   departments d ON e.department_id = d.department_id
  WHERE  e.salary > :min_salary;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

The output should match what you saw in SQL Developer, because both tools call DBMS_XPLAN.DISPLAY internally.

4. Trade‑offs and limitations

Explain Plan shows an estimated plan based on current statistics. If table statistics are stale or the query uses bind variables whose values cause different peeked plans, the displayed plan may diverge from the actual runtime plan. In such cases, consider:

• Gathering fresh statistics with DBMS_STATS.GATHER_TABLE_STATS.
• Using DBMS_XPLAN.DISPLAY_CURSOR after the statement runs to see the real plan.
• Checking the V$SQL_PLAN view for the cursor that actually executed.

Very wide graphical plans can be truncated in the default window; zoom out or switch to the Hierarchical or XML view to see the full tree.

Actionable closing

Next time you suspect a query is inefficient, open a SQL Worksheet, hit Ctrl+E, and review the plan before you run the statement. Export the HTML if you need to archive it, and compare the graphical and textual views to ensure you’re seeing the complete picture. When statistics are questionable, refresh them or verify with DBMS_XPLAN.DISPLAY_CURSOR after execution to close the loop between estimate and reality.