Does the Explain Plan command really show the execution plan that will be used?

When it comes to SQL tuning we often need to look at the execution plan for a SQL statement to determine where the majority of the time is spent. But how we generate that execution plan can have a big impact on whether or not the plan we are looking at is really the plan that is used.

The two most common methods used to generate the execution plan for a SQL statement are:

EXPLAIN PLAN command – This displays an execution plan for a SQL statement without actually executing the statement.

V$SQL_PLAN A dynamic performance view introduced in Oracle 9i that shows the execution plan for a SQL statement that has been compiled into a cursor and stored in the cursor cache.

My preferred method is always to use V$SQL_PLAN (even though it requires the statement to at least begin executing) because under certain conditions the plan shown by the EXPLAIN PLAN command can be different from the plan that will actually be used when the query is executed.

So, what can cause the plans to differ?

Bind Variables

When a SQL statement contains bind variables, the plan shown using EXPLAIN PLAN is not aware of bind variable values while the plan shown in V$SQL_PLAN takes the bind variable values into account in the plan generation process. Let’s look at a simple example, using the customers tables, which has 1,018 rows and an index on the C_ZIPCODE column.

SELECT COUNT(*) 
FROM   customers;
 
  COUNT(*)
----------
      1018
 
SELECT   c_zipcode, COUNT(*) 
FROM     customers 
GROUP BY c_zipcode;
 
 C_ZIPCODE   COUNT(*)
---------- ----------
     20001	  290
      2111	   81
     10018	  180
     90034	  225
     94102	  225
     94065	   17
 
 
var n NUMBER;
exec :n :=94065;
 
PL/SQL PROCEDURE successfully completed.
 
SELECT COUNT(c_email) 
FROM   customers 
WHERE  c_zipcode=:n;
 
COUNT(C_EMAIL)
--------------
	    17
 
SELECT * 
FROM TABLE(DBMS_XPLAN.display_cursor(format=>'typical +peeked_binds'));
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
SQL_ID	bjj643zga3mfu, child NUMBER 0
-------------------------------------
SELECT COUNT(c_email) FROM customers WHERE c_zipcode=:n
 
Plan hash VALUE: 4213764942
 
----------------------------------------------------------------------
| Id  | Operation		      | Name	    | Rows  |  Bytes| 
----------------------------------------------------------------------
|   0 | SELECT STATEMENT	      |		    |	    |       |	  
|   1 |  SORT AGGREGATE               |		    |	  1 |   24  |	
|   2 |   TABLE ACCESS BY INDEX ROWID | CUSTOMERS   |	 17 |       | 
|*  3 |    INDEX RANGE SCAN	      | IND_CUST_ZIP|	 17 |	    |	  
----------------------------------------------------------------------
 
Peeked Binds (identified BY position):
--------------------------------------
   1 - :N (NUMBER): 94065 
 
Predicate Information (identified BY operation id):
---------------------------------------------------
   3 - access("C_ZIPCODE"=:N)
 
20 rows selected.
 
EXPLAIN PLAN FOR 
SELECT COUNT(c_email) 
FROM customers 
WHERE c_zipcode=:n;
 
Explained.
 
SQL> 
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display());
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------
Plan hash VALUE: 296924608
----------------------------------------------------------------
| Id  | Operation	   | Name      | Rows  | Bytes | Cost |
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |	       |     1 |    24 |  7   | 
|   1 |  SORT AGGREGATE    |	       |     1 |    24 |      |	  
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |   170 |  4080 |  7   | 
-----------------------------------------------------------------
 
Predicate Information (identified BY operation id):
---------------------------------------------------
   2 - filter("C_ZIPCODE"=TO_NUMBER(:N))

When we query the actual plan used at execution from V$SQL_PLAN via the DBMS_XPLAN.DISPLAY_CURSOR command we get an index access plan and the cardinality estimate (estimated number of rows returned) is accurate at 17 rows.

However when we use the EXPLAIN PLAN command for our statement, we get a full table scan plan and a cardinality estimate of 170 rows.

The first indication that the EXPLAIN PLAN command is not bind aware can be seen in the predicate information under the plan. There you will see the addition of a TO_NUMBER function to our bind variable :N, even though we declare the variable as a number.

Since no bind peeking occurs the optimizer can’t used the histogram on the c_zipcode column. Therefore the optimizer has to assume a uniform data distribution in the c_zipcode column and it calculates the cardinality estimate as NUM_ROWS / NDV or 1018/6 = 169.66, which rounded up is 170 rows.

Cursor_Sharing = FORCE

By setting the initialization parameter CURSOR_SHARING to FORCE, you are asking Oracle to replace the literal values in your SQL statements with system generated bind variables (commonly known as literal replacement). The intent of literal replacement is to reduce the number of cursors generated in the shared pool. In the best-case scenario, only one cursor will be built for all statements that only differ in the literal value used.

Let’s take our original example and replace our bind variable :N with the literal value 94065 and see what happens when CURSOR_SHARING is set to FORCE and we use the EXPLAIN PLAN command.

ALTER SYSTEM SET cursor_sharing = force;
 
System altered.
 
SELECT COUNT(c_email) 
FROM   customers 
WHERE  c_zipcode=94065;
 
COUNT(C_EMAIL)
--------------
	    17
 
SELECT * 
FROM TABLE(DBMS_XPLAN.display_cursor(format=>'typical +peeked_binds'));
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
SQL_ID	djn0jckqvy2gk, child NUMBER 0
-------------------------------------
SELECT COUNT(c_email) FROM customers WHERE c_zipcode=:"SYS_B_0"
 
Plan hash VALUE: 4213764942
 
---------------------------------------------------------------------
| Id  | Operation		     | Name	     | Rows  |Bytes | 
---------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		    |	    |	    |	  
|   1 |  SORT AGGREGATE 	     |		    |	  1 |	 24 |	
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |	 17 |	408 |	  
|*  3 |    INDEX RANGE SCAN	     | IND_CUST_ZIP |	 17 |	    |	  
---------------------------------------------------------------------
 
Peeked Binds (identified BY position):
--------------------------------------
   1 - :SYS_B_0 (NUMBER): 94065
 
Predicate Information (identified BY operation id):
---------------------------------------------------
   3 - access("C_ZIPCODE"=:SYS_B_0)
 
25 rows selected.
 
EXPLAIN PLAN FOR 
SELECT COUNT(c_email) 
FROM customers 
WHERE c_zipcode=94065;
 
Explained.
 
SELECT * FROM TABLE(DBMS_XPLAN.display());
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
Plan hash VALUE: 4213764942
--------------------------------------------------------------------
| Id  | Operation		     | Name	     | Rows  |Bytes| 
--------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |		    |	    |	   |	  
|   1 |  SORT AGGREGATE              |		    |	  1 |	 24|	
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |	 17 |	408|	  
|*  3 |    INDEX RANGE SCAN	     | IND_CUST_ZIP |	 17 |	   |	  
--------------------------------------------------------------------
Predicate Information (identified BY operation id):
---------------------------------------------------
   3 - access("C_ZIPCODE"=94065)

This time the plan is the same in both cases but if you look at the predicate information under both plans, you will notice that the explain plan command did not do the literal replacement. It still shows the predicate as C_ZIPCODE=94065 instead of C_ZIPCODE=:SYS_B_0.

So, why didn’t the explain plan command do the literal replacement?

The cursor generated by an EXPLAIN PLAN command is not shareable by design. Since the cursor isn’t shared there is no point in doing the literal replacement that would allow the cursor to be shared. Therefore the explain plan command does not replace the literals.

To demonstrate that the EXPLAIN PLAN command cursors are not shared, I ran our example queries two more times and then queried V$SQL.

SELECT sql_id, sql_text, executions, child_number
FROM   v$sql
WHERE  sql_text LIKE '%SELECT count(c_email)%';
 
SQL_ID        SQL_TEXT                               EXECUTIONS CHILD_NUMBER
------------- -------------------------------------- ---------- ------------
djn0jckqvy2gk SELECT COUNT(c_email) FROM customers            3            0
              WHERE c_zipcode=:"SYS_B_0"
 
78h277aadmkku EXPLAIN PLAN FOR SELECT COUNT(c_email)          1            0
               FROM customers WHERE c_zipcode=94065
 
78h277aadmkku EXPLAIN PLAN FOR SELECT COUNT(c_email)          1            1
               FROM customers WHERE c_zipcode=94065
 
78h277aadmkku EXPLAIN PLAN FOR SELECT COUNT(c_email)          1            2
 
4 rows selected.

You will notice that the actual query had it’s literal value replaced by the system generated bind :SYS_B_0 and only a single cursor (child_number 0) was generated, which was executed 3 times.

For the EXPLAIN PLAN version of the statement, no literal replace occurred and each execution created a new child cursor (0,1,2). Demonstrating that no cursor sharing occurs with the explain plan command.

So, what I have a few extra cursors. What’s the big deal?

The big deal is if you want to use any plan stability features like SQL plan baselines then you will not see the effect of these feature with EXPLAIN PLAN when CURSOR_SHARING is set to FORCE. Assuming you created the SQL plan baseline for the statement with the system generated bind :SYS_B_0 but then check which plan will be used with EXPLAIN PLAN not literal replace occurred, therefore there’s no corresponding baseline will be found for the statement. You can see an example of this in a recent AskTOM question I answered.

Adaptive Plans

In Oracle Database 12c Adaptive Plans enable the optimizer to defer the final plan decision for a statement, until execution time.

The optimizer instruments it’s chosen plan (the default plan), with statistics collectors so that at runtime, it can detect if its cardinality estimates, differ greatly from the actual number of rows seen by the operations in the plan. If there is a significant difference, then the plan or a portion of it can be automatically adapted to avoid suboptimal performance on the first execution of a SQL statement.

Currently only the join method or the parallel query distribution methods can adapt.

By default, the explain plan command will show only the initial or default plan chosen by the optimizer. Whereas the DBMS_XPLAN.DISPLAY_CURSOR function displays the final plan used by the query or the complete adaptive plan with the additional format parameter ‘+adaptive’.

Let’s look at a simple of example of a two table join that has an adaptive plans to understand the difference in what you will see between explain plan and DBMS_XPLAN.DISPLAY_CURSOR function.

EXPLAIN PLAN FOR
SELECT /*+ gather_plan_statistics*/ p.product_name
FROM   order_items2 o, product_information p
WHERE  o.unit_price = 15
AND    o.quantity > 1
AND    p.product_id = o.product_id;
 
Explained.
 
SELECT * FROM TABLE(DBMS_XPLAN.display());
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
Plan hash VALUE: 983807676
-----------------------------------------------------------------------
| Id  | Operation		     | Name		      | Rows  | 
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |			      |     4 |   
|   1 |  NESTED LOOPS		     |			      |     4 |   
|   2 |   NESTED LOOPS		     |			      |     4 |   
|*  3 |    TABLE ACCESS FULL	     | ORDER_ITEMS2	      |     4 |    
|*  4 |    INDEX UNIQUE SCAN	     | PRODUCT_INFORMATION_PK |     1 |       
|   5 |   TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION    |     1 |    
-----------------------------------------------------------------------
 
Predicate Information (identified BY operation id):
---------------------------------------------------
 
   3 - filter("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1)
   4 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
 
Note
-----
   - this IS an adaptive plan
 
22 rows selected.
 
SELECT /*+ gather_plan_statistics*/ p.product_name
FROM   order_items2 o, product_information p
WHERE  o.unit_price = 15
AND    o.quantity > 1
AND    p.product_id = o.product_id;
 
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor());
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
SQL_ID	d3mzkmzxn264d, child NUMBER 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ p.product_name FROM order_items2
o, product_information p WHERE o.unit_price = 15   AND o.quantity > 1
AND p.product_id = o.product_id
 
Plan hash VALUE: 2886494722
 
------------------------------------------------------------------
| Id  | Operation	   | Name		 | Rows  | Bytes | 
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |			 |	 |	 |     
|*  1 |  HASH JOIN	   |			 |     4 |   128 |     
|*  2 |   TABLE ACCESS FULL| ORDER_ITEMS2	 |     4 |    48 |
|   3 |   TABLE ACCESS FULL| PRODUCT_INFORMATION |     1 |    20 |     
------------------------------------------------------------------
 
Predicate Information (identified BY operation id):
---------------------------------------------------
 
   1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
   2 - filter(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
 
Note
-----
   - this IS an adaptive plan
 
 
27 rows selected.

As you can see the initial plan the optimizer came up with was a NESTED LOOP join, when the final plan was in fact a HASH JOIN. If you only use the EXPLAIN PLAN command you would never know a completely different join method was used.

So, my advice is to use use V$SQL_PLAN when reviewing the execution plan for a SQL statement, as it will also show the play actually used by the statement.

This entry was posted in Beginners, Optimizer and tagged , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *