如何获取执行计划

最新推荐文章于 2024-04-19 10:23:56 发布

iteye_19583

最新推荐文章于 2024-04-19 10:23:56 发布

阅读量146

点赞数

文章标签：数据库

执行计划是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行。