Why is my query slow?

The execution plan is your window into how Oracle actually runs a query. Understanding it is the single most important skill for Oracle performance tuning. Every DBA and developer should know how to read one.

Recipe #1 - EXPLAIN PLAN

The quickest way to see а plan without actually running the query:

EXPLAIN PLAN FOR
SELECT e.ename, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE e.sal > 2000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Recipe #2 - Actual execution statistics

EXPLAIN PLAN shows the estimated plan. To see what actually happened, use GATHER_PLAN_STATISTICS:

SELECT /*+ GATHER_PLAN_STATISTICS */ e.ename, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE e.sal > 2000;

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

This shows actual row counts alongside estimates, making it easy to spot cardinality misestimates.

Recipe #3 - Key things to look for

The most important columns in an execution plan:

• Cost — Oracle's estimated cost (lower is better, but only meaningful when comparing plans for the same query)
• Rows — Estimated number of rows at each step
• Bytes — Estimated data volume
• Operation — The type of operation (TABLE ACCESS FULL, INDEX RANGE SCAN, etc.)

Common operations ranked from best to worst:

INDEX UNIQUE SCAN      — Best: single row lookup via primary key
INDEX RANGE SCAN       — Good: finding a range of rows via index
INDEX FULL SCAN        — OK: scanning entire index in order
INDEX FAST FULL SCAN   — OK: scanning entire index without order
TABLE ACCESS FULL      — Expensive: reading every row in the table

Recipe #4 - Find the execution plan of a running query

If a query is already running and you need its plan:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

You can find the sql_id from v$session or v$sql:

SELECT sql_id, sql_text
FROM v$sql
WHERE sql_text LIKE '%your_table%'
  AND sql_text NOT LIKE '%v$sql%';

Recipe #5 - Compare plans with DBMS_XPLAN.DISPLAY_AWR

For historical analysis, retrieve plans from the AWR repository:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

Learning to read execution plans takes practice, but it's the most direct way to understand why a query is fast or slow.