Skip navigation

The famous EXPLAIN PLAN FOR command gives you the planned execution plan for a given query.  Here is an example:


explain plan for

  2  select  prod_id,unit_cost,unit_price from costs where unit_price > 700      ;

Plan FOR succeeded.

SQL> SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT                                                                                                                                 

------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1918128980                                                                                                                        

                                                                                                                                                      ---------------------------------------------------------------------------------------------                                                      

| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                      

---------------------------------------------------------------------------------------------                                                      

|   0 | SELECT STATEMENT    |       | 50050 |   684K|   136   (1)| 00:00:01 |       |       |                                                      

|   1 |  PARTITION RANGE ALL|       | 50050 |   684K|   136   (1)| 00:00:01 |     1 |    28 |                                                      

|*  2 |   TABLE ACCESS FULL | COSTS | 50050 |   684K|   136   (1)| 00:00:01 |     1 |    28 |                                                      

---------------------------------------------------------------------------------------------                                                      

Predicate Information (identified by operation id):                                                                                                

---------------------------------------------------                                                                                                

   2 - filter("UNIT_PRICE">700)

 

This is the planned execution plan for my SELECT -- it is what Oracle intends to do to execute my query, after evaluating, to the best of its knowledge (statistics), several execution plans. When you ask for EXPLAIN PLAN FOR, the query is not actually run -- only the intended execution plan is computed.  This comes in handy for queries that last a long time, the performance of which you need to assess.  The resulting execution plan gives you  an idea of how well that query is going to perform if you actually run it.


If you can afford to run the query (if you can wait for it to complete, if you are not on a production system, if you are allowed to modify data...), do it and ask Oracle to show the plan it actually used.  This is better for 2 reasons:

  1. if you actually run the query, you can time it (SQLDeveloper automatically tells you how long your script/query has run at the top of its result tab; or use set timing on in SQL+ ) and get an accurate idea of how well your query performs (in your current environnment)
  2. you get the plan that was actually used by Oracle to execute your query, and some extra information (see below)


Here is how to do it.  This is in SQLCLI and on Oracle 12.1.0.2 but it's the same in SQL+ and on 11g.

 

create table costs2 as select * from costs;

create index fd_up2 on costs2 (unit_price) ;

select TABLE_NAME, NUM_ROWS,LAST_ANALYZED from USER_TABLES where TABLE_NAME like 'COST%' ;

 

explain plan for

select prod_id,unit_cost,unit_price from costs2 where unit_price > 700;

SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY);

 

select /*+ gather_plan_statistics */  prod_id,unit_cost,unit_price from costs2 where unit_price > 700;

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

 

Output:

SQL> create table costs2 as select * from costs;

Table COSTS2 created.

SQL> create index fd_up2 on costs2 (unit_price) ;

Index FD_UP2 created.

SQL> select TABLE_NAME, NUM_ROWS,LAST_ANALYZED from USER_TABLES where TABLE_NAME like 'COST%' ;

TABLE_NAMENUM_ROWS LAST_ANALYZED

------------ ---------- ---------------

COSTS2      82112 29-FEB-16
COSTS       82112 18-OCT-15

 

As as side note, here is demonstrated a nifty 12c feature: table statistics are automatically gathered at creation time.  My COSTS2 table has just been created  and NUM_ROWS is not empty even though I have not run DBMS_STATS.GATHER_TABLE_STATS on it.  Statistics have been gathered for it, as witnessed by today's date in the LAST_ANALYZED column.


explain plan for

  2   select prod_id,unit_cost,unit_price from costs2 where unit_price > 700;

Plan FOR succeeded.

 

SQL> SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT                                                                                                                                 

------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2840313604                                                                                                                        

----------------------------------------------------------------------------                                                                       

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                                       

----------------------------------------------------------------------------                                                                       

|   0 | SELECT STATEMENT  |        | 50050 |   684K|   104   (1)| 00:00:01 |                                                                       

|*  1 |  TABLE ACCESS FULL| COSTS2 | 50050 |   684K|   104   (1)| 00:00:01 |                                                                       

----------------------------------------------------------------------------                                                                       

Predicate Information (identified by operation id):                                                                                                

---------------------------------------------------                                                                                                

   1 - filter("UNIT_PRICE">700)

 

select /*+ gather_plan_statistics */  prod_id,unit_cost,unit_price from costs2 where unit_price > 700;

 

14790.93

1190.69

... thousands of result lines here ....

21808.861026.61
14790.931190.69
141001.751219.13

 

6,161 rows selected

Elapsed: 410

 

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

PLAN_TABLE_OUTPUT                                                                                                                                 

------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  74rkrtfzxh4yk, child number 0                                                                                                              

-------------------------------------                                                                                                              

select /*+ gather_plan_statistics */  prod_id,unit_cost,unit_price from                                                                            

costs2 where unit_price > 700                                                                                                                      

                                                                                                                                                   

Plan hash value: 2840313604                                                                                                                        

--------------------------------------------------------------------------------------                                                             

| Id  | Operation         | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                                             

--------------------------------------------------------------------------------------                                                             

|   0 | SELECT STATEMENT  |        |      1 |        |   6161 |00:00:00.01 |     777 |                                                             

|*  1 |  TABLE ACCESS FULL| COSTS2 |      1 |  50050 |   6161 |00:00:00.01 |     777 |                                                             

--------------------------------------------------------------------------------------                                                             

Predicate Information (identified by operation id):                                                                                                

---------------------------------------------------                                                                                                

   1 - filter("UNIT_PRICE">700)

 

The top execution plan is the result of our EXPLAIN PLAN FOR command.  It contains, as displayed by our SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY), the id, operation, name, rows, bytes, cost, and time columns.

While the bottom execution plan is the one that was actually executed by Oracle, as it appears after the execution of our query, as displayed by our SELECT *  FROM  TABLE (DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST')).  It contains the Id, operation, name, starts, e-rows, a-rows, a-time, and buffers columns.  The e-rows column contains the same data as the rows column of the 1st SELECT, as it is the estimated rows.  The a-rows column is the actual rows.  So not only do you get the actual plan but you also discover whether your statistics are pertinent.


So how do you get that actual plan?  You must implement 2 things:

  1. as you can see in the example above, you must add the /*+ gather_plan_statistics */ hint to your query.  This tells Oracle to gather extra execution statistics as it executes the query.  It does add a small overhead, so use with caution.
  2. set serverouput must be set to off, as the SELECT *  FROM  TABLE (DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST')) will display the statistics of the query that was last run in that session. And set serveroutput on adds an extra call to to DBMS_OUTPUT.