Oracle Execution Plans

Oracle Execution Plans

I have the privilege of working with a really great DB architect who taught me a superior method for viewing the detailed execution plan used to satisfy an Oracle query. I'm posting the details here for anyone else that needs to get such a detailed execution plan.

-- configure sqlplus output
set linesize 120
set pagesize 100
set serveroutput off

-- alter session to grab all stats for execution plan
alter session set statistics_level = 'ALL';

-- run your query of interest here
-- SELECT * FROM ???

-- revert your session
alter session set statistics_level = 'TYPICAL';

-- output the execution plan
select * from table (dbms_xplan.display_cursor(null, 0, 'ALLSTATS'));

Here's a sample of what you'll see when viewing execution plans using the strategy outline above:

SQL> -- output the execution plan
SQL> select * from table (dbms_xplan.display_cursor(null, 0, 'ALLSTATS'));

PLAN_TABLE_OUTPUT                                                                                                       
------------------------------------------------------------------------------------------------------------------------
SQL_ID  bj0mxk7kf0dyn, child number 0                                                                                   
-------------------------------------                                                                                   
WARNING: User has no SELECT privileges on V$SQL                                                                         
                                                                                                                        
Plan hash value: 2024250754                                                                                             
                                                                                                                        
---------------------------------------------------------------------------------------------------                     
| Id  | Operation             | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                     
---------------------------------------------------------------------------------------------------                     
|   1 |  NESTED LOOPS         |                 |      1 |      2 |      2 |00:00:00.01 |       6 |                     
|   2 |   INDEX FAST FULL SCAN| SYS_C0046475723 |      1 |      2 |      2 |00:00:00.01 |       4 |                     
|*  3 |   INDEX UNIQUE SCAN   | SYS_C0046475720 |      2 |      1 |      2 |00:00:00.01 |       2 |                     
---------------------------------------------------------------------------------------------------                     
                                                                                                                        
Predicate Information (identified by operation id):                                                                     
---------------------------------------------------                                                                     
                                                                                                                        
   3 - access("B"."FOO_ID"="F"."FOO_ID")                                                                     

There are a few things you should note in the execution plan above:

• The Operation column shows you, at a high level, what sorts of operations were used to satisfy your query. You'll want to be on the look out for slow operations, like full table scans.
• A large discrepancy between the estimated (E-Rows) and actual (A-Rows) rows can often reveal that oracle selected a poor execution plan because of stale statistics. In such cases, you may get some relief by running the ANALYZE TABLE command to recalculate table statistics.