概念
简单解释：执行计划描述了数据库引擎执行SQL语句时实时的操作。
详细解释：数据库执行SQL是按照一定顺序、分步骤完成的。至于采用什么顺序和方法访问数据是由优化器决定的。一旦优化器决定了一个它认为最高效的执行方法，这一系列的顺序、步骤就称之为执行计划。
我的解释：优化器对SQL语句进行的一系列的步骤就是执行计划。
Q:对于执行计划，需要做什么？
A:获取，解释，评估效率。
Q: 获取执行计划的方法
A: 直接获取一个给定的SQL语句关联的执行计划的五种方法
- 执行EXPLAIN PLAN，查询其输出所写入的表
- 查询动态性能视图来显示在库缓存中的执行计划
- 通过**实时监控（Real-time Monitoring）**来获取正在执行、刚执行完的SQL语句信息
- 查询自动工作负载存储库（AWR）或statspack表来显示再存储库中的执行计划
- 激活跟踪功能提供执行计划
EXPLAIN PLAN
EXPLAIN PLAN 概念
EXPLAIN PLAN需要接受一个SQL语句作为输入，然后提供他的执行计划和相关信息。
格式
explain plain [set statemment_id='<ID>'] -- ID 用来区分不超过30字符的计划计划的名称
[into table <table_name>] -- table_name 指定表名称，默认为plan_table,也可以是schema.table@dblink
for <sql_statement> -- 支持update、merge、delete、create table、create index alter index
性质
DML语句，数据库操作语句，不会对事务执行提交。只是简单的将数据插入到计划表中。
计划表概念
计划表是EXPLAIN PLAN语句输出、写入内容的地方。表不存在就抛错。默认归SYS所有。默认使用plan_table，该表是存储到会话结束的临时表。
查询
SQL> EXPLAIN PLAN FOR SELECT * FROM IDT where id=1;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3713188089
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 72 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| IDT | 8 | 72 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("ID"=1)
13 rows selected.
注
-
默认情况下绑定变量会以VARCHAR2类型声明，可能会自动添加隐式转换，这样会改变执行计划。
-
使用了绑定变量的SQL语句，通过EXPLAIN PLAN的输出可能有问题。因为在EXPLAIN PLAIN中使用绑定变量中不会使用绑定变量扫视技术，且没有解决方案。
动态性能视图
下列4个视图会显示出现在库缓存中的游标信息。
v$sql_plan
这个视图提供与计划表基本相同的信息。v$sql_plan包含用于标志与库缓存中的执行计划的游标的列，这是唯一差别。
这个视图只显示查询优化器在解析阶段做出的估算和决定。
v$sql_plan_statistics
这个视图为v$sql_plan视图中的每个操作提供执行统计，比如消耗时间和产生的行数。默认不采集，要将statistics_level初始化参数设置为all，或者将gath_plan_statics这个hint指定在sql中。但可能有负载。
v$sql_workarea
这个视图提供执行游标所需的内存信息。它给出运行时内存以及估算高效执行操作需要的内存总量。
v$sql_plan_statistics_all
这个视图将vsql_plan、vsql_plan_statistics_all、v$sql_workarea视图通过一个单独的视图展现出来。
查看子游标
游标通过address、child_number列来标识。通过address可以标志父游标。两个列一起标志子游标。
SQL> select status,sql_id,sql_child_number from v$session where username='TENGFEI';
STATUS SQL_ID SQL_CHILD_NUMBER
-------- ------------- ----------------
ACTIVE 4ravrg0b33b56 0
包含sql_text和游标的视图查询
select sql_id,sql_text,child_number from v$sql where sql_text like '%1644%'
查询动态性能视图
使用dbms_xplan包的display_cursor函数(通过v$sql_plan_statistics_all视图)。但需要SQL_ID和子游标两个参数。
SQL> select * from table(dbms_xplan.display_cursor('bsxn4u3tv2crs',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID bsxn4u3tv2crs, child number 0
-------------------------------------
insert into idt values(05,21,1644)
-------------------------------------------------
| Id | Operation | Name | Cost |
-------------------------------------------------
| 0 | INSERT STATEMENT | | 1 |
| 1 | LOAD TABLE CONVENTIONAL | | |
-------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- cpu costing is off (consider enabling it)
16 rows selected.
AWR和Statspack
捕获某个快照时,AWR和Statspack能够收集执行计划。
SQL> select * from table(dbms_xplan.display_awr('bsxn4u3tv2crs'));
当捕获一个大于等于6级快照时Statspack将执行计划存储在stats$spl_plan中。
