执行计划是DBA诊断问题常用的辅助工具，利用执行计划我们可以发现资源是如何消耗的或者优化器是否选择了正确的执行路径。获取执行计划的方式总体上来书可以分为两类：
- EXPLAIN PLAN - 通过该命令生成sql语句的执行计划，但是该语句并没有真正执行
- 字典视图- 通过oracle字典视图我们可以查询缓存在内存中的已经执行的sql语句的执行计划.
在某些情况下，通过explain plan和查询字典视图获取的执行计划是不同的，例如在存在绑定变量时，两者的处理方式就会存在差异，下面我们会一一讨论。在oracle9i后，通过功能不断强化的plsql包，我们可以非常容易的查询执行计划，并且获取执行计划的方式也是多种多样，例如我们可以从如下位置获取执行计划：
- EXPLAIN PLAN command
- V$SQL_PLAN
- Automatic Workload Repository (AWR)
- SQL Tuning Set (STS)
- SQL Plan Baseline (SPM)
使用EXPLAIN PLAN
SQL> explain plan for select count(*) from products;
已解释。
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 |
------------------------------------------------------------------
已选择10行。
如果使用绑定变量，explain plan的输出结果
SQL> var v number
SQL> exec :v := 145
PL/SQL 过程已成功完成。
SQL> explain plan for select count(*) from products where prod_id = :v;
已解释。
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)) --进行了类型转换
已选择14行。
从上面可以看出，explain plan对绑定变量不仅仅没有执行BIND PEEKING ,而且进行了数据类型转换，只是因为explain plan始终认为绑定变量为varchar2类型
使用V$SQL_PLAN
通过DBMS_XPLAN.DISPLAY_CURSOR我们可以访问存放在V$SQL_PLAN 中的执行计划，语法格式如下
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE 参数名称 类型 输入/输出默认值? ------------------------------ ----------------------- ------ -------- SQL_ID VARCHAR2 IN DEFAULT CURSOR_CHILD_NO NUMBER(38) IN DEFAULT FORMAT VARCHAR2 IN DEFAULT
如果省略SQL_ID,CURSOR_CHILD_NO则默认查询当前session中最后执行的sql执行计划
SQL> var v number
SQL> exec :v := 144
PL/SQL 过程已成功完成。
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 --在EXPLAIN PLAN时，是没有该段内容的
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
2 - access("PROD_ID"=:V)
已选择24行。
通过AWR
与通过v$sql_plan相似，也可以通过awr来获取执行计划，采用的过程时DBMS_XPLAN.DISPLAY_AWR(),该函数的语法格式如下：
FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE 参数名称 类型 输入/输出默认值? ------------------------------ ----------------------- ------ -------- SQL_ID VARCHAR2 IN PLAN_HASH_VALUE NUMBER(38) IN DEFAULT DB_ID NUMBER(38) IN DEFAULT FORMAT VARCHAR2 IN DEFAULT
示例如下：
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行。
通过sqlplanbaseline
通过sqlplan baseline，也可以获取执行计划。sqlplan baseline是在11g中引入的新技术，主要用来支持SPM。下面看一下如何通过sqlplan baseline来获取执行计划。
SQL> alter session set optimizer_capture_sql_plan_baselines=true;
会话已更改。
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 |
----------------------------------------------------------------------
已选择20行。