Execution planning is a commonly used assistant tool for DBA diagnostic problems. Using it, we can find out how resources are consumed or whether the optimizer has chosen the right execution path. Generally speaking, there are two kinds of ways to obtain the implementation plan:

1. EXPLAIN PLAN - Generates an execution plan for sql statements through this command, but the statement is not actually executed
   
2. Dictionary View - Through the oracle dictionary view, we can query the execution plan of the sql statements that have been executed in memory.

In some cases, the execution plans obtained through explain plan and query dictionary view are different. For example, when there are binding variables, there will be differences in the way they are handled. We will discuss them one by one. After Oracle 9i, through the plsql package with enhanced functions, we can easily query the execution plan and obtain the execution plan in a variety of ways. For example, we can get the execution plan from the following locations:

1. EXPLAIN PLAN command
   
2. V$SQL_PLAN
   
3. Automatic Workload Repository (AWR)
   
4. SQL Tuning Set (STS)
   
5. SQL Plan Baseline (SPM)
   

Using EXPLAIN PLAN

SQL> explain plan for select count(*) from products;

//Explanation has been made.

SQL> select * from table(dbms_xplan.display('','','basic'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 589338964

------------------------------------------------------------------
| Id  | Operation		      | Name			 |
------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 			 |
|   1 |  SORT AGGREGATE 	      | 			 |
|   2 |   BITMAP CONVERSION COUNT     | 			 |
|   3 |    BITMAP INDEX FAST FULL SCAN| PRODUCTS_PROD_STATUS_BIX |
------------------------------------------------------------------

//Ten lines have been selected.

If a bound variable is used, the output of explain plan

SQL> var v number
SQL> exec :v := 145

PL/SQL The process has been successfully completed.

SQL> explain plan for select count(*) from products where prod_id = :v;

//Explanation has been made.

SQL> select * from table(dbms_xplan.display('','','TYPICAL +PEEKED_BINDS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2065297493

----------------------------------------------------------------------------------
| Id  | Operation	   | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |		 |     1 |     4 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |		 |     1 |     4 |	      | 	 |
|*  2 |   INDEX UNIQUE SCAN| PRODUCTS_PK |     1 |     4 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("PROD_ID"=TO_NUMBER(:V))  --Type conversion was performed

//Fourteen rows have been selected.

As you can see from the above, explain plan not only does not perform BIND PEEKING on the bound variables, but also does data type conversion because explain plan always considers the bound variables varchar2.

Using V$SQL_PLAN

Through DBMS_XPLAN.DISPLAY_CURSOR, we can access the execution plan stored in V$SQL_PLAN in the following grammatical format

FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
//Parameter Name, Type, Input/Output Default Value?
------------------------------ ----------------------- ------ --------
 SQL_ID 			VARCHAR2		IN     DEFAULT
 CURSOR_CHILD_NO		NUMBER(38)		IN     DEFAULT
 FORMAT 			VARCHAR2		IN     DEFAULT

If sql_ID is omitted, CURSOR_CHILD_NO defaults to query the sql execution plan last executed in the current session

SQL> var v number
SQL> exec :v := 144

PL/SQL The process has been successfully completed.

SQL> select count(*) from products where prod_id = :v;

  COUNT(*)
----------
	 1

SQL> select * from table(dbms_xplan.display_cursor('','','typical +PEEKED_BINDS'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID	9gnda3r7m7jvw, child number 0
-------------------------------------
select count(*) from products where prod_id = :v

Plan hash value: 2065297493

-----------------------------------------------------------------------
| Id  | Operation	   | Name	 | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |		 |	 |	 |     1 (100)|
|   1 |  SORT AGGREGATE    |		 |     1 |     4 |	      |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   INDEX UNIQUE SCAN| PRODUCTS_PK |     1 |     4 |     0   (0)|
-----------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :V (NUMBER): 144   --stay EXPLAIN PLAN When, there is no content of this paragraph.

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
   2 - access("PROD_ID"=:V)


//Twenty-four rows have been selected.

Through AWR

Similar to using v$sql_plan, an execution plan can also be obtained through awr. The procedure used is DBMS_XPLAN.DISPLAY_AWR(). The grammatical format of the function is as follows:

FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
//Parameter Name, Type, Input/Output Default Value?
------------------------------ ----------------------- ------ --------
 SQL_ID 			VARCHAR2		IN
 PLAN_HASH_VALUE		NUMBER(38)		IN     DEFAULT
 DB_ID				NUMBER(38)		IN     DEFAULT
 FORMAT 			VARCHAR2		IN     DEFAULT

Examples are as follows:

SQL> select * from table(dbms_xplan.display_awr('1v44r7vam2wbt'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1v44r7vam2wbt
--------------------
delete from WRH$_IOSTAT_FUNCTION tab where (:beg_snap <= tab.snap_id
and	    tab.snap_id <= :end_snap and	 dbid = :dbid)	  and
not exists (select 1 from WRM$_BASELINE b		     where
(tab.dbid = b.dbid) and 			 (tab.snap_id >=
b.start_snap_id) and			      (tab.snap_id <=
b.end_snap_id))

Plan hash value: 1772041547


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
| Id  | Operation		       | Name			 | Rows  | Bytes | Cost (%CPU)| Time	 |
----------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT	       |			 |	 |	 |     4 (100)| 	 |
|   1 |  DELETE 		       | WRH$_IOSTAT_FUNCTION	 |	 |	 |	      | 	 |
|   2 |   FILTER		       |			 |	 |	 |	      | 	 |
|   3 |    INDEX RANGE SCAN	       | WRH$_IOSTAT_FUNCTION_PK |     1 |    17 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| WRM$_BASELINE		 |     1 |    33 |     2   (0)| 00:00:01 |
|   5 |      INDEX RANGE SCAN	       | WRM$_BASELINE_PK	 |     1 |	 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------


//22 rows have been selected.

Through sqlplanbaseline

The execution plan can also be obtained through sqlplan baseline. Sqlplan baseline is a new technology introduced in 11g, which is mainly used to support SPM. Let's see how to get the execution plan through sqlplan baseline.

SQL> alter session set optimizer_capture_sql_plan_baselines=true;

//The session has changed.

SQL> select count(*) from t1;

  COUNT(*)
----------
     74008

SQL> /

  COUNT(*)
----------
     74008

SQL> select sql_handle,plan_name,accepted from dba_sql_plan_baselines where sql_text like 'select count(*) from t1';

SQL_HANDLE		       PLAN_NAME		      ACC
------------------------------ ------------------------------ ---
SQL_e208a16bb98b6a04	       SQL_PLAN_f4251dfwsquh4dcd11e45 YES

SQL> select * from table(dbms_xplan.display_sql_plan_baseline('SQL_e208a16bb98b6a04'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_e208a16bb98b6a04
SQL text: select count(*) from t1
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_f4251dfwsquh4dcd11e45	  Plan id: 3704692293
Enabled: YES	 Fixed: NO	Accepted: YES	  Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 129980005

----------------------------------------------------------------------
| Id  | Operation	      | Name | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |	   1 |	  46   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |      |	   1 |		  |	     |
|   2 |   INDEX FAST FULL SCAN| I1   | 74008 |	  46   (0)| 00:00:01 |
----------------------------------------------------------------------

//Twenty rows have been selected.