oracle 执行计划-1 获取执行计划

ORACLE 执行计划

要处理执行计划，有三步。获取执行计划，解释执行计划，判定执行计划效率。这里我们首先研究获取执行计划。

1. 获取执行计划

获取执行计划主要有四种方法。

*执行SQL语句 explain plan，然后查询结果输出表

*查询动态性能视图表，显示缓存在库缓存中的执行计划

*查询AWR或者查询statspack表，显示存储在资料库中的执行计划

*启动执行计划跟踪功能

1.1 SQL语句explain plan

explain plan语句是以一条sql语句作为输入，得到这条语句的执行计划以及相关信息，然后将他们作为输出存储到计划表(plan table)中。

1.1.1 explain plan的语法及权限问题

其语法图如下

关于 explain plan 要注意的两点是：

a.这条语句是DML语句，而不是DDL语句，所以不会对当前的transaction进行隐式提交。

b.如果要处理的SQL语句中有视图，那么当前用户不但要对视图有查询权限，对视图基于的基表或视图，也要有查询权限，因为explain plan语句要去查询一些基表的统计信息。

1.1.2 计划表

计划表是存储explain plan语句输出的地方。也就是上面explain plan语句图中INTO schema.table @ dblink这一部分指定的表。默认情况下，如果在explain plan语句中不指定这个表，那么就会把explain plan的输出存储到sys下面的计划表中，该表在sys下有一个public的同义词，供普通用户访问。如果普通用户想要创建一个自己的计划表，则可以通过$ORACLE_HOME/rdbms/admin下的utlxplan.sql脚本创建。不过这种手动创建的计划表在数据库升级的时候一定要重新创建一次，以防新版本的数据库会对计划表有改动。用户需要对计划表有INSERT SELECT 权限才能通过explain plan语句使用计划表，通常最好还加上delete权限。10g中的默认计划表是全局临时表，多个并发用户可以互不影响的工作。

1.2 动态性能视图

其实也许你会有疑问，获取执行计划的方法了解一个不就够了吗？其实不同方法获取的执行计划各不相同。比如通过动态性能视图获取的执行计划，可以获得一些运行时的统计信息。通常情况下我们通过explain plan获取的执行计划，从某种角度来说，只是查询优化器的一个评估，未必就是真正的执行计划。所以通过动态性能获取执行计划也很重要。

用来获取执行计划的动态性能视图主要有四个：

a. v$sql_plan该视图提供最基础的信息，与计划表相同。唯一不同的地方在于，有一些字段可以帮助定位执行计划相关的游标。

b.v$sql_plan_statistics该视图为v$sql_plan中的每一个操作提供了运行时信息。这一部分信息非常重要，v$sql_plan只显示解析时查询优化器的评估。因为要收集运行时统计信息要付出额外的开销，所以默认情况下不收集它们。如果要收集这些信息，可以将初始化参数statistics_level 设置为all 或者在sql语句中使用提示 gather_plan_statistics.

c.v$sql_workarea该视图提供了用来执行游标的内存工作区域的信息

d.v$sql_plan_statistics_all该试图连接了前三个视图

1.3 dbms_xplan包的display函数和display_cursor函数

我们掌握了explain plan 方法和查询动态性能视图的方法来获取执行计划。那么获取的执行计划如何以一定的格式展示出来呢？

通过display函数我们可以展示explain plan方法获得的执行计划，通过display_cursor函数可以展示动态性能视图中获得的执行计划。

先看display函数，其参数如下：

(1) table_name 指定计划表的名字，默认值为sys模式下的plan_table

(2) statement_id 指定SQL语句的ID。默认值为NULL。在使用默认值的时候，函数显示最近插入计划表的执行计划。

(3) format 指定输出那些内容。其格式为基本值 +、-修饰符。如，typical - bytes。基本值和修饰符的说明如下：第一个表是基本值，第二个表是描述符。

值	描述
basic	显示操作和操作对象，显示信息最少
typical	显示大部分内容，不现实别名，提纲，字段投影等
serial	和typical类似，但不显示并行操作
all	显示除了提纲的所有信息
advanced	显示所有信息

值	描述
alias	查询块名，对象别名部分
bytes	bytes字段
cost	cost字段
note	note部分
outline	outline 提纲部分
parallel	并行信息部分
partition	分区部分
peeked_binds	绑定变量窥测部分
predicate	谓词部分
projection	字段投影部分
remote	远程执行sql部分
rows	rows字段

(4) filter指定在查询计划表时添加一个约束。约束的内容是基于计划表中某个字段的一个SQL谓词。如 statement_id=test123。

再看display_cursor函数

该函数返回存储在库缓存中的执行计划，参数信息如下：

(a) sql_id 默认为NULL，如果采用默认值，则返回当前回话最后一条SQL语句的执行计划

(b) cursor_child_no 默认为0，如果设置为NULL,则返回父游标下所有子游标的执行计划

(c) format 指定显示那些信息。display函数的format参数在这里都可以用。另外，如果打开了运行时统计（也就是在SQL语句中使用了 gather_plan_statistics 或者把初始化参数 statistics_level 设置为all），那么下面的修饰符一样可以使用。

值	描述
allstats *	iostats memstats的快捷方式
iostats *	控制 i/o统计的显示
last*	默认显示所有统计，如果指定了last则只显示最后一次的统计
memstats	控制PGA统计显示
runstats_last	只用与10g r1 和isotats_last相同
runstats_tot	只用于10g r1 和ioststs相同