How to Analyze Execution Plans in Oracle

Galaxy Glossary

How do I analyze execution plans in Oracle to speed up SQL?

Generate and interpret Oracle execution plans to identify and fix SQL performance bottlenecks.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why analyze an Oracle execution plan?

Execution plans reveal exactly how the optimizer will fetch rows—table access paths, join methods, and predicate filters. Reading them lets you spot full-table scans, missing indexes, or inefficient joins before they hurt production.

How do I generate a plan quickly?

Use EXPLAIN PLAN FOR to store the plan, then query DBMS_XPLAN.DISPLAY(). With ALLSTATS LAST you also see actual run-time statistics.

Step-by-step code

EXPLAIN PLAN FOR SELECT * FROM Orders o JOIN OrderItems oi ON oi.order_id = o.id JOIN Products p ON p.id = oi.product_id WHERE o.order_date > SYSDATE - 30; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'TYPICAL'));

What does each column mean?

OPERATION shows the action (e.g., TABLE ACCESS). OPTIONS refines it (FULL vs INDEX). OBJECT_NAME lists the table or index. COST and CARDINALITY estimate work and rows. BYTES estimates data volume.

How can I spot problems fast?

Full-table scans on large tables, high COST values, or nested-loop joins against big row counts signal trouble. Create or rewrite indexes, add selective predicates, or replace nested loops with hash joins.

Which plan formats help most?

TYPICAL is concise for daily tuning. ALL adds predicates. ALLSTATS LAST injects actual run-time metrics when the statement is executed with GATHER_PLAN_STATISTICS.

Best practices for reliable plans

Gather fresh table statistics before tuning. Use bind variables to avoid skew. Compare estimated vs actual rows after running with SET AUTOTRACE TRACEONLY or DBMS_XPLAN.DISPLAY_CURSOR().

Can I view the plan while the query runs?

Yes. Query V$SESSION_LONGOPS or DBMS_XPLAN.DISPLAY_CURSOR(format => 'TYPICAL +PEEKED_BINDS') using the SQL_ID in V$SQL.

Common mistakes to avoid

Don’t rely on raw COST alone; always check estimated rows. Don’t ignore filter predicates at deeper plan levels—they indicate where rows explode.

Why How to Analyze Execution Plans in Oracle is important

How to Analyze Execution Plans in Oracle Example Usage


-- Identify expensive full-table scans in the last 30 days of orders
EXPLAIN PLAN FOR
SELECT o.id, o.order_date, p.name, oi.quantity
FROM   Orders o
JOIN   OrderItems oi ON oi.order_id = o.id
JOIN   Products p    ON p.id = oi.product_id
WHERE  o.order_date >= SYSDATE - 30
  AND  p.stock < 10;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY(format => 'TYPICAL'));

How to Analyze Execution Plans in Oracle Syntax


-- Store a hypothetical plan
EXPLAIN PLAN SET STATEMENT_ID = 'recent_orders' FOR
SELECT o.id, o.order_date, c.name, c.email, o.total_amount
FROM   Orders     o
JOIN   Customers  c  ON c.id = o.customer_id
WHERE  o.order_date >= TRUNC(SYSDATE) - 7;

-- Display the saved plan
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY(statement_id => 'recent_orders',
                                format       => 'TYPICAL'));

-- Get the real execution plan after running a query once
ALTER SESSION SET statistics_level = ALL;
SELECT /*+ gather_plan_statistics */ p.name, SUM(oi.quantity)
FROM   OrderItems oi
JOIN   Products   p ON p.id = oi.product_id
GROUP  BY p.name;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

Common Mistakes

Mistake: Trusting the COST column alone. The optimizer’s cost is relative, not absolute. Fix: Compare estimated vs actual rows using ALLSTATS LAST to see true bottlenecks.
Mistake: Leaving statistics stale. Old stats mislead the optimizer and produce bad plans. Fix: Run DBMS_STATS.GATHER_TABLE_STATS regularly or schedule nightly jobs.