如何获取执行计划
- EXPLAIN PLAN - 通过该命令生成sql语句的执行计划，但是该语句并没有真正执行
- 字典视图- 通过oracle字典视图我们可以查询缓存在内存中的已经执行的sql语句的执行计划.
- 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行。
使用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行。
通过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行。
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。