Oracle-sql执行计划解析

参照

https://docs.oracle.com/cd/E11882_01/server.112/e41573/ex_plan.htm#PFGRF94708

Oracle的执行计划是sql调优的基础

执行计划是optimizer（优化器）为SELECT、UPDATE、INSERT、DELETE语句选择的，一个语句的执行计划是数据库运行语句时各项操作的顺序。

执行计划的源

获取执行计划信息的来源有如下两个地方（常用的）

explain plan

PLAN_TABLE是一个全局临时表的同义词，这个全局临时表会为所有用户保存explain plan的输出结果。

在sql语句之前加上explain plan for子句，该sql语句的执行计划信息默认就会被写入到表plan_table。

当有多个sql语句时，可以指定id用以区分。

性能视图

单独的执行计划操作无法区分调优良好的语句和性能较差的语句。例如，一个explain plan输出显示了语句使用了索引，并不意味着这条语句执行效率高。有时索引（indexes）会导致效率低，这种情况下，需要检查如下：

• 被用到索引的columns
• 索引们的selectivity（fraction of table being accessed）

使用explain plan的最佳方式是，先评估，然后再测试这个语句的实际资源消耗。而除了使用explain plan之外，还可以使用性能视图v$sql_plan，显示sql语句的执行计划。

sql语句执行之后，可以通过查询v$sql_plan来显示执行计划。v$sql_plan包含了存储在shared SQL area的所有语句的执行计划。视图中的字段定义类似plan_table。

v$sql_plan的优势是不用知道当时执行sql语句时的compilation environment信息，而explain plan要想精确再现当时的执行计划，需要当时的compilation environment信息。

而v$sql_plan_statistics为计划中的每个操作提供了实际的执行统计信息。

执行计划如何读

解析执行计划的显示

对于执行计划的显示，应该怎么看，总体原则是从下往上、从右往左。

Operation在下方的先执行，Operation在右侧的先执行。

explain plan set STATEMENT_ID='ep2' for

select * from test where id=1

union all

select * from test where id=123

SELECT PLAN_TABLE_OUTPUT

FROM TABLE(DBMS_XPLAN.DISPLAY('','ep2','TYPICAL'));

解析plan_table字段

对于plan_table中的一些字段，解析如下

字段	说明
statement_id	explain plan语句中指定的参数值，可选的
plan_id	数据库中一个plan的唯一标识
timestamp	一个explain plan生成时的日期和时间
remarks	varchar2(80)的备注，可以使用update更新plan_table进行调整
operation	操作的内部名称，包括：DELETE STATEMENT、INSERT STATEMENT、SELECT STATEMENT、UPDATE STATEMENT
options	对operation的描述
object_node	dblink的名称，被用来引用对象（表、视图）
object_owner	拥有包含相应table或index的schema的用户
object_name	table或index的名称
object_type	对象的描述性信息，例如，NON-UNIQUE for indexes.
id	执行计划中分配给每个步骤的一个数字,越大越先执行，0最后执行
parent_id	id步骤的上一步的id
depth	plan展现的row source tree操作的depth（深度），这个值被用来缩进plan table report中的每行
cost	按照查询优化器的方式评估operation的成本，这个成本并不能决定访问表的实际成本，这个值并没有特定的计量单位。它仅仅只是一个用来比较执行计划成本的权重值。这个值是参照cpu_cost、io_cost，通过函数计算得出。
cardinality	基数，查询优化器评估的，operation要访问的行的数量
bytes	字节数，查询优化器评估的，operation要访问的字节的个数
cpu_cost	按照查询优化器的方法评估出的operation的cpu成本，这个值和operation所需的机器周期（cpu周期）值成正比。如果语句使用基于规则的方法（评估），那么该列为空。
io_cost	按照查询优化器的方法评估出的operation的io成本，这个值和operation所读的data blocks数量成正比。如果语句使用基于规则的方法（评估），那么该列为空。
temp_space	以bytes为单位，按照查询优化器的方法评估出的operation所需的临时表空间。如果语句使用基于规则的方法（评估），或者没有使用任何临时表空间，那么该列为空。
access_predicates	Predicates used to locate rows in an access structure. For example, start or stop predicates for an index range scan.
filter_predicates	Predicates used to filter rows before producing them.
time	以秒为单位，按照查询优化器的方法评估出的operation所消耗的时间。如果语句使用基于规则的方法（评估），那么该列为空。