dbms_xplan包 - display 函数

   display 函数

display函数返回计划表中存储的执行计划。返回值是dbms_xplan_type_table集合的实例。集合的元素是dbms_xplan_type对象类型的实例。这种对象类型的唯一属性，即plan_table_output,其类型是VARCHAR2(300)。此函数有以下输入参数。

Ø  table_name指定计划表的名称。默认值是plan_table。如果指定了NULL,则使用默认值。

Ø  statement_id指定SQL语句的名称，当执行EXPLAIN PLAN语句的时候，作为一个可选参数给出。默认值是NULL。如果使用了默认值，则显示最近一次插入计划表的执行计划(如果没有指定filter_preds参数)。

Ø  format指定在输出中提供哪些信息。可以使用基本值(basic、typical、serial、all及advanced),想要精细控制，可以向它们添加额外的修饰符(adaptive、alias、bytes、cost、note、outline、parallel、partition、peeked_binds、predicate、projection、remote、report及rows)。

a.如果有应该添加的信息，可以选择使用+这个字符作为前缀的修饰符(例如，basic +predicate)。

b.如果有应该移除的信息，可以选择使用-这个字符作为前缀的修饰符(例如，typical -bytes)。

c.可以同时指定多个修饰符(例如，typical +alias -bytes -cost)。

表10-2和表10-3分别完整描述了基本值和修饰符。默认值是typical。

Ø  filter_preds指定查询计划表时应用的限制条件。此限制条件为基于计划表中的一个列的常规SQL谓词(例如，statement_id='test3')。默认值是NULL。如果使用了默认值，则会显示最近一次插入到计划表的执行计划。

表10-2 format参数接受的基本值

表10 - 3format参数接受的修饰符

要使用display函数，调用者只需要在该包上有EXECUTE权限并在计划表上拥有select权限。

下面的查询显示了相同的执行计划，展示了在基本值basic、typical以及advanced之间的主要区别。以下是对display.sq1脚本生成输出的一段摘录：

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

select * from table(dbms_xplan.display(null,null,'typical'));

select * from table(dbms_xplan.display(null,null,'advanced'));

下面的查询展示了如何使用修饰符从基本值basic和typical生成的默认输出中添加或移除信息。因为它们基于与之前例子相同的查询，你可以对比输出结果来查看有何不同之处。以下是一段来自display.sql脚本输出的摘录：

select * from table(dbms_xplan.display(null,null,'basic +predicate'));

select * from table(dbms_xplan.display(null,null,'typical -bytes -note'));

将current_schema会话参数设置为一个拥有默认名称的计划表的模式时，如果你使用EXPLAIN PLAN语句和display函数，则必须在EXPLAIN PLAN语句的INTO子句中和display函数的table_name参数中加入该模式名称。如果不这么做就会导致display函数引发一个错误。下面的例子演示了该行为：

ALTER SESSION SET current_schema = franco;

EXPLAIN PLAN FOR select * FROM t;

select * FROM table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Error: cannot fetch last explain plan from PLAN_TABLE

EXPLAIN PLAN INTO franco.plan_table FOR select * FROM t;

select * FROM table(dbms_xplan.display(table_name=>'franco.plan_table'));

通过display函数也可以查询一张拥有基于v$sql_plan_statistics_all视图结构的计划表。在想要保存那些被有意设计为只在库缓存中短暂存在的信息时，这个特性就派上用场了。因为这样的计划表中包含额外的信息，当通过display函数查询它时，format参数支持接下来的部分描述的额外修饰符，详见表10-4。下面的例子展示了如何利用这个特性保存关于执行最后一条SQL语句时的信息：

SELECT /*+ gather_plan_statistics */ count(*) FROM t;

CREATE TABLE my_plan_table

AS

SELECT cast(1 AS VARCHAR2(30)) AS plan_id, p.*

FROM v$sql_plan_statistics_all p

WHERE (sql_id, child_number) = (SELECT prev_sql_id, prev_child_number

                                FROM v$session

                                WHERE sid = sys_context('userenv','sid'));

SELECT * FROM table(dbms_xplan.display('my_plan_table', NULL, 'iostats'));

表10-4 format参数接受的修饰符