Unlocking Oracle Performance in SQL Developer: From Explain Plan to SQL Tuning Advisor

Why Oracle Queries Sometimes Bite Back

When you run a SELECT that looks simple, Oracle can still spend minutes or hours finding the data. The optimizer picks a plan, but the plan may not be what you expect. The two most common reasons for a slow query are:

• Missing or outdated statistics cause the optimizer to mis‑estimate cardinalities.
• The chosen join order or access path is suboptimal for the current data distribution.

SQL Developer gives you two built‑in tools to diagnose and fix these issues without leaving the IDE: Explain Plan and SQL Tuning Advisor. The former shows you what Oracle decided to do; the latter tells you how to make Oracle decide better.

Getting the Plan in a Click

Open a worksheet, write a query, and right‑click the statement. Choose Explain Plan. SQL Developer will execute the optimizer’s EXPLAIN PLAN FOR behind the scenes and display a visual graph or a text table.

/* Example query */
SELECT e.employee_id, e.first_name, d.department_name
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id
WHERE  e.salary > 100000;

In the graph, each node represents a step (TABLE ACCESS, INDEX SCAN, NESTED LOOP, etc.). Hovering over a node shows the estimated rows, cost, and actual rows after execution. If the plan uses a full table scan on a large table, you’ll know immediately.

Checking the Text Output

Under the Explain Plan tab you’ll see a SQL*Plus‑style table:

Id	Operation	Options	Object Name	Rows	Bytes
1	SELECT STATEMENT			10	640
2	NESTED LOOPS			10	640
3	TABLE ACCESS FULL	EMPLOYEES	EMPLOYEES	100000	640000
4	INDEX RANGE SCAN	DEPT_IDX	DEPARTMENTS	10	640

Notice the full table scan on EMPLOYEES. That’s a red flag.

Letting the Advisor Do the Heavy Lifting

Right‑click the same query and pick Tuning Advisor. SQL Developer launches the Tuning tab, which runs the SQL Tuning Advisor engine. The advisor uses AWR data, optimizer statistics, and the current session’s workload to generate a report.

1. It evaluates the query’s cost and execution time.
2. It searches for missing indexes, materialized views, or better join strategies.
3. It may suggest a SQL Profile to force a particular plan.

When the advisor finishes, you’ll see a summary panel:

• Estimated Cost: 5000
• Suggested Plan: Use INDEX SCAN on EMPLOYEES
• SQL Profile: Create profile EMP_TUNED

To apply the profile, click Apply. The profile is stored in the database and will be used automatically for future executions of the same statement.

Verifying the Improvement

Run the query again. In the Explain Plan graph you should now see an INDEX SCAN node for EMPLOYEES instead of a full scan. The Rows estimate should drop from 100,000 to maybe 1,000, and the Cost will be lower.

Measure the actual runtime: SET TIMING ON in SQL*Plus or the Time column in the Explain Plan tab. A drop from 12 seconds to 0.5 seconds confirms the advisor’s recommendation.

Trade‑Offs and Limitations

• Statistical Accuracy: The advisor relies on current statistics. If you’ve just loaded data, run DBMS_STATS.GATHER_TABLE_STATS first.
• AWR Retention: The advisor needs AWR data. In a minimal installation, AWR may be disabled, limiting recommendations.
• Version Differences: Oracle 23c introduced new cardinality estimation. A plan that looks good on 19c may not hold on 23c.
• SQL Profile Overhead: Profiles can lock plans; if the data distribution changes, the plan may become stale.

Actionable Checklist

1. Run SELECT * FROM USER_TAB_STATISTICS to confirm stats are recent.
2. Use Explain Plan to spot expensive steps.
3. Run Tuning Advisor and review the suggested SQL Profile.
4. Apply the profile and re‑run the query to verify performance gains.
5. Monitor the plan over time; if it degrades, re‑run the advisor.

With these steps, you can turn a slow, opaque query into a well‑understood, high‑performance statement—all from within SQL Developer.