Oracle SQL 学习笔记32 - 执行计划

执行计划

定义

执行计划是优化器（optimizer）执行SQL语句和操作的步骤集合。

查看执行计划的方法

• EXPAIN_PLAN
• SQL Trace
• Statspack
• Automatic Workload Repository
• V$SQL_PLAN
• SQL * PLUS AUTOTRACE

使用执行计划

通过执行计划，用户可以识别索引的效果，决定访问路径，验证索引，以及验证哪个执行计划正在被使用。

使用EXPLAIN PLAN分析执行计划

EXPLAIN PLAN 命令

EXPLAIN PLAN 命令生成优化器执行计划，将计划存放在计划表中，其本身并不执行那些语句。

cmd.JPG

实例

EXPLAN  PLAN
SET  STATEMENT_ID  =  'demo01'  FOR
SELECT  e.last_name,  d.department
FROM  hr.employees  e,  hr.departments  d
WHERE  e.department_id  =  d.department_id;

Explained;

使用DBMS_XPLAN 展现执行计划

DBMS_XPLAN 包总览

DBMS_XPLAN 包提供了一种简单的方式，以展示EXPLAIN PLAN 命令，AWR（Automatic Workload Repository），以及V$SQL_PLAN和V$SQL_PLAN_STATISTICS_ALL等视图的输出。
DBMS_XPLAN 包支持三种表函数，这些函数可以用来获取和展示执行计划：

• DISPLAY
• DISPLAY_CURSOR
• DISPLAY_AWR

实例

SELECT  PLAN_TABLE_OUTPUT  FROM  TABLE(DBMS_XPLAN.DISPLAY());

display.JPG parse_tree.JPG

使用V$SQL_PLAN视图

V$SQL_PLAN 可以用来检查当前执行中的游标的执行计划。V$SQL_PLAN 中的信息和 EXPLAIN PLAN语句的输出类似：

• EXPLAIN PLAN展示的是语句执行时的理论计划
• V$SQL_PLAN 包含实际的执行计划

V$SQL_PLAN 的列

HASH_VALUE	库缓存中的父语句的hash值
ADDRESS	表或者索引的对象号
CHILD_NUMBER	使用执行计划子游标号
POSITION	具有相同PARENT_ID的操作的处理顺序
PARENT_ID	以当前步骤的输出为操作对象的下一个执行步骤的ID
ID	在执行计划中的每一步骤都被赋予一个数字
...	...

查询V$SQL_PLAN

SELECT  PLAN_TABLE_OUTPUT  FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR('47ju6102uvq5q'))

V$SQL_PLAN_STATISTICS视图

V$SQL_PLAN_STATISTICS 视图提供实际执行的统计信息；而V$SQL_PLAN_STATISTICS_ALL启动了对优化器估计的side-by-side比较。

AWR（Automatic Workload Repository）

AWR用于为问题探测和自我诊断收集、处理和维护性能统计信息。
统计信息包括：

• 对象的统计信息
• 时间模式的统计信息
• 一些系统和会话的统计信息
• ASH（Active Session History）统计信息
  AWR为性能数据自动生成快照。

AWR 视图

1. V$ACTIVE_SESSION_HISTORY
2. V$metrics
3. DBA_HIST

• DBA_HIST_ACTIVE_SESS_HISTORY
• DBA_HIST_BASELINE
• DBA_HIST_DATABASE_INSTANCE
• DBA_HIST_SNAPSHOT
• DBA_HIST_SQL_PLAN
• DBA_HIST_WR_CONTROL

查询AWR

SELECT  PLAN_TABLE_OUTPUT  FROM  TABLE
(DBMS_XPLAN.DISPLAY_AWR('47ju6102uvq5q'));

awr_query.JPG

使用SQL * PLUS AUTOTRACE 分析跟踪执行计划

trace.JPG

举例

• 打开tracing语句

set  autrace  on

• 隐藏语句输出

set  autotrace  traceonly

• 只显示执行计划

set autotrace  traceonly  explain

set  autotrace  traceonly  statistics

SELECT  *  FROM  products;