dbms_xplan包中常用函数：
1、dbms_xplan.display
2、dbms_xplan.display_cursor
3、dbms_xplan.display_awr
--查看dbms_xplan包的函数信息
SQL> desc dbms_xplan1、dbms_xplan.display
dbms_xplan.display函数用来查看解释计划，而非执行计划，当SQL语句中绑定变量时，解释是计划不可靠的。
--查看dbms_xplan.display函数说明
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT
DBMS_XPLAN.DISPLAY table function
This function accepts options for displaying the plan table output. You can specify:
■A plan table name if you are using a table different than PLAN_TABLE
■A statement ID if you have set a statement ID with the EXPLAIN PLAN
■A format option that determines the level of detail: BASIC, SERIAL, and TYPICAL, ALL,ADVANCED
--Some examples of the use of DBMS_XPLAN to display PLAN_TABLE output are:
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));TABLE_NAME参数：
缺省为：PLAN_TABLE
STATEMENT_ID参数：
缺省为NULL
----自己指定STATEMENT_ID的值
SQL> explain plan set statement_id = 'zml' for select ename from scott.emp where
ename = 'KING';
已解释。
SQL> select statement_id from plan_table where statement_id = 'zml';
STATEMENT_ID
------------------------------
zml
zml
SQL> select PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','zml','TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 7 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("ENAME"='KING')
Note
-----
- dynamic sampling used for this statement (level=2)
已选择17行。
SQL>FORMAT参数:
参数选项：
BASIC ..... displays minimum information
TYPICAL ... displays most relevant information
SERIAL .... like TYPICAL but without parallel information
ALL ....... displays all information（显示除提纲之外的所有信息）
ADVANCED......显示所有信息
FORMAT 参数修饰符
alias 控制包含查询块与别名的显示部分
bytes 控制执行计划表中字段bytes的显示
cost 控制执行计划表中字段cost的显示
note 控制包含注释信息的显示部分
outline 控制包含提纲信息的显示部分
parallel 控制包含并行处理信息的提示
partition 控制并行处理信息的显示，尤其是执行计划表中字段TQ、IN-OUT、PQ Distrib的显示
peeked_binds 控制包含绑定变量窥探部分的显示。仅当生成执行计划时使用了绑定变量是可见
predicate 控制包含谓词filter和access显示部分
projection 控制包含投影信息的显示部分
remote 控制远程执行的SQL语句的显示
rows 控制执行计划表中字段rows的显示
--FORMAT参数选项和参数修饰符的使用
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'BASIC'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| EMP |
----------------------------------
已选择8行。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'BASIC +ALIAS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| EMP |
----------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
已选择13行。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 7 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("ENAME"='KING')
Note
-----
- dynamic sampling used for this statement (level=2)
已选择17行。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'TYPICAL -PREDICATE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 1 | 7 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- dynamic sampling used for this statement (level=2)
已选择12行。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'TYPICAL -PREDICATE -NOTE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 1 | 7 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
已选择8行。
SQL>2、dbms_xplan.display_cursor
用dbms_xplan.display_cursor来查看库高缓存中的执行计划（实际的执行计划）。
--查看dbms_xplan.display_cursor的函数说明
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN DEFAULT
CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULTSQL_ID参数：
又称父游标，即V$SQL表中的SQL_ID，默认值为NULL。当为默认值时，显示最近执行的SQL语句的执行计划。
CURSOR_CHILD_NO参数：
子游标序号，即V$SQL表中的CHILD_NUMBER，默认值为0。若设定为NULL，则对应SQL_ID下的所有子游标的执行计划都将返回。
FORMAT参数：
同dbms_xplan.display函数的FORMAT参数。
dbms_xplan.display_cursor参数FORMAT的另外几个参数修饰符（只有在hint 'gather_plan_statistics' is used for the statement or parameter 'statistics_level' is set to 'ALL',at session or system level时才起作用）：
iostats 控制I/O统计的显示
last 默认，显示所有执行计算过的统计。如果指定该值，则只显示最后一次执行的统计信息
memstats 控制pga相关统计的显示
allstats 此为iostats memstats的快捷方式，即allstats包含了iostats和memstats
--dbms_xplan.display_cursor函数使用实例
-------------最近一次执行SQL语句的实际执行计划------------------
SQL> select ename from scott.emp where ename = 'KING';
ENAME
----------
KING
SQL> select plan_table_output from table(dbms_xplan.display_cursor(null,null,'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID f9hmq3mbj4jna, child number 0
-------------------------------------
select ename from scott.emp where ename = 'KING'
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 7 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='KING')
Note
-----
- dynamic sampling used for this statement (level=2)
已选择22行。
SQL>
--若要得到已经执行过的某条SQL语句的执行计划，只需指定SQL_ID和CURSOR_CHILD_NO即可，此处不演示实例----------
--/*若要在查看实际执行计划的同时查看统计信息，需要在执行SQL语句的时候用hint'gather_plan_statistics'
--或者设置参数'statistics_level’为'ALL',实例演示如下：*/
--不使用hint'gather_plan_statistics'，也不设置'statistics_level'为'ALL',note信息中出现warning
SQL> select ename from scott.emp where ename = 'KING';
ENAME
----------
KING
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID f9hmq3mbj4jna, child number 0
-------------------------------------
select ename from scott.emp where ename = 'KING'
Plan hash value: 3956160932
-------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | |
|* 1 | TABLE ACCESS FULL| EMP | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='KING')
Note
-----
- dynamic sampling used for this statement (level=2)
- Warning: basic plan statistics not available. These are only collected when
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l
已选择25行。
SQL>
---使用hint'gather_plan_statistics'
SQL> select /*+ gather_plan_statistics */ ename from scott.emp where ename = 'KING';
ENAME
----------
KING
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID ghrnstu8y68zh, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ ename from scott.emp where ename = 'KING'
Plan hash value: 3956160932
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffe
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
rs |
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 |
8 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 1 |00:00:00.01 |
8 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='KING')
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- dynamic sampling used for this statement (level=2)
已选择23行。
SQL>
--设置statistics_level
SQL> show parameters statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SQL> alter session set statistics_level = 'all';
会话已更改。
SQL> show parameters statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string all
SQL> select ename from scott.emp where ename = 'KING';
ENAME
----------
KING
SQL> select plan_table_output from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID f9hmq3mbj4jna, child number 1
-------------------------------------
select ename from scott.emp where ename = 'KING'
Plan hash value: 3956160932
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffe
rs |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 |
8 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 1 |00:00:00.01 |
8 |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='KING')
Note
-----
- dynamic sampling used for this statement (level=2)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
已选择22行。
SQL>statistics_level的三个值：BASIC（收集基本信息）/TYPICAL（收集大部分信息）/ALL（收集所有信息）
3.dbms_xplan.display_awr
暂时还没用过，待续。
622
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
