Ask Maclean

Oracle SQL解释执行计划 execution plan

本文永久链接地址：https://www.askmac.cn/archives/sql-execution-plan-1.html

 

  • 收集执行计划
  • 显示执行计划
  • 解释执行计划

什么是执行计划

  • SQL 语句的执行计划由多个小构建块组成，这些小构建块称为串行执行计划的行源。
  • 语句的行源组合称为执行计划。
  • 可以使用父 – 子关系，以树状结构（文本或图形方式）
    显示执行计划。

执行计划是优化程序的输出，供执行引擎实施。它指出了执行引擎必须执行的操作，从而使其能以最有效的方式检索查询所需的数据。

EXPLAIN PLAN 语句收集 Oracle 优化程序为 SELECT、UPDATE、INSERT 和 DELETE 语句选择的执行计划。执行计划的步骤并不按编号顺序执行。步骤之间存在父 – 子关系。行源树是执行计划的核心，它显示下列信息：

  • 语句所引用的表的顺序
  • 语句中提到的每个表的访问方法
  • 受语句中的联接操作影响的表的联接方法
  • 数据操作，如过滤、排序或聚集

除行源树（或并行操作的数据流树）外，计划表还包含有关以下事项的信息：

  • 优化，如每项操作的成本和基数
  • 分区，如一组访问分区
  • 并行执行，如联接输入的分布方法

借助 EXPLAIN PLAN 的结果，您可以确定优化程序是否选择了特定执行计划，如嵌套循环联接。

 

在哪里可以找到执行计划

 

  • PLAN_TABLE（EXPLAIN PLAN 或 SQL*Plus 自动跟踪）
  • V$SQL_PLAN（库高速缓存）
  • V$SQL_PLAN_MONITOR (11g)
  • DBA_HIST_SQL_PLAN (AWR)
  • STATS$SQL_PLAN (Statspack)
  • SQL 管理库（SQL 计划管理基线）
  • SQL 优化集
  • 由 DBMS_MONITOR 生成的跟踪文件
  • 事件 10053 跟踪文件
  • 进程状态转储跟踪文件（从 10gR2 开始，可以使用此
    文件）

可使用多种方式在数据库中检索执行计划。此幻灯片列出了最广为人知的方法：

  • 使用 EXPLAIN PLAN 命令可以查看优化程序可能用于执行 SQL 语句的执行计划。此命令十分有用，它提供优化程序可能使用的计划的概览，并将计划插入名为 PLAN_TABLE 的表中且不会执行 SQL 语句。
  • V$SQL_PLAN 提供了一种方法来检查最近所执行游标的执行计划。V$SQL_PLAN 中的信息与 EXPLAIN PLAN 语句的输出十分类似。但是，EXPLAIN PLAN 显示的是执行相应语句时可以使用的理论计划，而 V$SQL_PLAN 包含实际使用的计划。
  • V$SQL_PLAN_MONITOR 显示 V$SQL_MONITOR 中每条 SQL 语句的计划级监视统计信息。V$SQL_PLAN_MONITOR 中的每一行对应于所监视的执行计划中的一项操作。
  • 自动工作量资料档案库 (AWR) 基础结构和 statspack 报表存储顶级 SQL 的计划。计划记录在 DBA_HIST_SQL_PLAN 和 STATS$SQL_PLAN 中。
  • 计划和行源操作被转储到由 DBMS_MONITOR 生成的跟踪文件中。
  • SQL 管理库 (SMB) 是驻留在 SYSAUX 表空间中的数据字典的一部分。它存储语句日志、计划历史记录、SQL 计划基线和 SQL 概要文件。
  • 事件 10053 用于转储基于成本的优化程序 (CBO) 的计算结果，其中可能包含计划。
  • 从 Oracle Database 10gR2 开始，转储进程状态（或进程的错误堆栈）时，执行计划将被添加到生成的跟踪文件中。

 

查看执行计划

  • 在 EXPLAIN PLAN 命令后接：

–SELECT from PLAN_TABLE

–DBMS_XPLAN.DISPLAY()

  • SQL*Plus 自动跟踪：SET AUTOTRACE ON
  • DBMS_XPLAN.DISPLAY_CURSOR()
  • DBMS_XPLAN.DISPLAY_AWR()
  • DBMS_XPLAN.DISPLAY_SQLSET()
  • DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE()

DBMS_XPLAN 程序包提供五个表函数：

  • DISPLAY：格式化和显示计划表的内容
  • DISPLAY_AWR：格式化和显示 AWR 中存储的 SQL 语句的执行计划的内容
  • DISPLAY_CURSOR：格式化和显示任何加载游标的执行计划的内容
  • DISPLAY_SQL_PLAN_BASELINE：显示由 SQL 句柄标识的 SQL 语句的一个或多个执行计划
  • DISPLAY_SQLSET：格式化和显示 SQL 优化集中存储的语句的执行计划的内容

 

EXPLAINPLAN 命令

  • 生成优化程序执行计划
  • 将计划存储在 PLAN_TABLE 中
  • 不执行语句本身

EXPLAINPLAN 命令用于生成执行计划，优化程序使用相应的执行计划来执行 SQL 语句。该命令并不执行语句，只是生成可用计划，并将相应计划插入表中。如果查看计划，则可看到 Oracle 服务器如何执行相应语句。

使用 EXPLAIN PLAN

  • 首先使用 EXPLAIN PLAN 命令解释 SQL 语句。
  • 然后通过查询 PLAN_TABLE 来检索计划步骤。

PLAN_TABLE 是一个自动创建的全局临时表，用于存放所有用户执行的 EXPLAIN PLAN 语句的输出内容。PLAN_TABLE 是默认的示例输出表，EXPLAIN PLAN 语句将描述执行计划的行插入该表。

注：如果要长期保留执行计划信息，可以使用 $ORACLE_HOME/rdbms/admin/utlxplan.sql 脚本创建自己的 PLAN_TABLE。

 

EXPLAINPLAN 命令

 

此命令在计划表中为执行计划的每个步骤插入一行。
在本幻灯片的语法图表中，斜体字段的含义如下：

 

EXPLAIN PLAN 命令：示例

SQL> EXPLAIN PLAN

  2  SET STATEMENT_ID = 'demo01' FOR

  3  SELECT e.last_name, d.department_name
  4  FROM hr.employees e, hr.departments d 

  5  WHERE e.department_id = d.department_id;

 

Explained.

 

SQL>

 

 

 

注：EXPLAIN PLAN 命令实际上并不执行语句。

 

此命令在计划表中插入相应 SQL 语句的执行计划，并且添加可选的 demo01 名称标记供将来参考。也可以使用以下语法：

 

EXPLAIN PLAN

FOR

SELECT e.last_name, d.department_name
FROM hr.employees e, hr.departments d

WHERE e.department_id =d.department_id;

 

PLAN_TABLE

  • PLAN_TABLE：

–是自动创建的，用于存放 EXPLAIN PLAN 的输出内容。

– 可以使用 utlxplan.sql 创建自己的计划表。

–优点：不执行 SQL

–缺点：可能不是实际的执行计划

  • PLAN_TABLE 是按分层结构组织的。
  • 使用 ID 和 PARENT_ID 列建立分层结构。

收集执行计划的方法有多种。目前仅介绍了 EXPLAIN PLAN 语句。此 SQL 语句收集某个 SQL 语句的执行计划（但不执行它），然后将结果输出到 PLAN_TABLE 表中。无论使用哪种方法来收集和显示解释计划，基本格式和目标都是相同的。但是，PLAN_TABLE 只是显示一个计划，此计划可能并非优化程序选择的那个计划。PLAN_TABLE 是一个自动创建的全局临时表，所有用户都可以查看。PLAN_TABLE 是默认的示例输出表，EXPLAIN PLAN 语句将描述执行计划的行插入该表。PLAN_TABLE 按树状结构组织，可以在 SELECT 语句的 CONNECT BY 子句中同时使用 ID 和 PARENT_ID 列来检索该结构。尽管系统会为每位用户自动创建 PLAN_TABLE 表，但您也可以使用 utlxplan.sql SQL 脚本在自己的方案中手动创建一个本地 PLAN_TABLE，并使用它存储 EXPLAIN PLAN 的结果。此脚本的确切名称和位置取决于操作系统。在 UNIX 上，它位于 $ORACLE_HOME/rdbms/admin 目录。如果升级了数据库的版本，建议您删除本地的 PLAN_TABLE 表并重建它，因为列可能发生变化。如果您指定该表，则可能导致脚本或 TKPROF 执行失败。

注：如果需要一个具有其它名称的输出表，请首先使用 utlxplan.sql 脚本手动创建 PLAN_TABLE，然后使用 RENAME SQL 语句重命名该表。

 

显示 PLAN_TABLE 中的内容：典型

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'demo01' FOR SELECT * FROM emp 
  2  WHERE ename = 'KING'; 
 
Explained. 
 
SQL> SET LINESIZE 130 
SQL> SET PAGESIZE 0  
SQL> select * from table(DBMS_XPLAN.DISPLAY()); 
 
Plan hash value: 3956160932 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    37 |     3   (0)| 00:00:01 | 
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    37 |     3   (0)| 00:00:01 | 
--------------------------------------------------------------------------

Predicate Information (identified by operation id): 
---------------------------------------------------
   1 - filter("ENAME"='KING')

 

 

在此幻灯片示例中，EXPLAIN PLAN 命令将 SQL 语句的执行计划插入到 PLAN_TABLE 中，并添加了可选的 demo01 名称标记供将来参考。需要注意的一点是，SELECT 语句本身并未执行，只是对其进行了优化，以便确定相应上下文中的最佳执行计划。DBMS_XPLAN 程序包的DISPLAY 函数可用于格式化和显示 PLAN_TABLE 中存储的最后一条语句。用以下语法进行检索也可得到同一结果：SELECT * FROM TABLE(dbms_xplan.display(‘plan_table’,’demo01′,’typical’,null));

输出的内容与幻灯片中显示的内容相同。在本例中，可使用另一个计划表的名称替代 PLAN_TABLE，demo01 代表语句 ID。TYPICAL 模式显示计划中相关性最高的信息：操作 ID、名称和选项、行数、字节数和优化程序成本。DISPLAY 函数的最后一个参数是与 filter_preds 对应的参数。此参数代表过滤谓词，用于限制从存储计划的表中选择的行集。如果值为空（默认），显示的计划将对应于最后一次执行的解释计划。此参数可以引用存储计划的表的任何列，可以包含任何 SQL 构造，例如子查询或函数
调用。

注：也可以运行位于 ORACLE_HOME/rdbms/admin/ 目录的 utlxpls.sql（或用于并行查询的 utlxplp.sql）脚本，来显示 PLAN_TABLE 中存储的最后一条经过解释的语句的执行计划。此脚本使用 DBMS_XPLAN 程序包的 DISPLAY 表函数。

 

 

显示 PLAN_TABLE 中的内容：ALL

 

 

SQL> select * from table(DBMS_XPLAN.DISPLAY(null,null,'ALL')); 
Plan hash value: 3956160932 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    37 |     3   (0)| 00:00:01 | 
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    37 |     3   (0)| 00:00:01 | 
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id): 
-------------------------------------------------------------
   1 - SEL$1 / EMP@SEL$1 
Predicate Information (identified by operation id): 
---------------------------------------------------
   1 - filter("ENAME"='KING') 
Column Projection Information (identified by operation id): 
-----------------------------------------------------------
 1 - "EMP"."EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "EMP"."JOB"[VARCHAR2,9],  
     "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22], 
     "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]

 

此处使用的 EXPLAIN PLAN 命令示例与上一张幻灯片相同。在 DISPLAY 函数中使用 ALL 选项可以最大限度地输出用户级别的信息。此选项输出的信息不仅包含在 TYPICAL 级别显示的信息，还包含诸如 PROJECTION、ALIAS 等附加信息以及有关 REMOTE SQL 的信息（如果此操作是分布式操作）。

要更精细地控制显示输出，可将以下关键字添加到格式参数中来定制其默认行为。每个关键字或代表计划表列的逻辑组（如 PARTITION），或代表基本计划表输出的逻辑附加项（如 PREDICATE）。必须使用逗号或空格分隔格式关键字：

  • ROWS：如果相关，显示优化程序估计的行数
  • BYTES：如果相关，显示优化程序估计的字节数
  • COST：如果相关，显示优化程序成本信息
  • PARTITION：如果相关，显示分区修剪信息
  • PARALLEL：如果相关，显示 PX 信息（分布方法和表队列信息）
  • PREDICATE：如果相关，显示谓词部分
  • PROJECTION：如果相关，显示映射部分
  • ALIAS：如果相关，显示“Query Block Name/Object Alias”部分
  • REMOTE：如果相关，显示分布式查询的信息（例如，串行分布的远程信息和远程 SQL 信息）
  • NOTE：如果相关，显示解释计划的注释部分

如果目标计划表还存储着计划统计信息列（例如，该表是用于捕获固定视图 V$SQL_PLAN_STATISTICS_ALL 的内容的表），则可以使用附加的格式关键字来指定使用 DISPLAY 函数时显示哪类统计信息。这些附加的格式关键字是 IOSTATS、MEMSTATS、ALLSTATS 和 LAST。

注：可在格式关键字添加前缀“-”符号来排除指定信息。例如，“-PROJECTION”排除映射信息。

 

 

显示 PLAN_TABLE 中的内容：ADVANCED

select plan_table_output from table(DBMS_XPLAN.DISPLAY(null,null,'ADVANCED -PROJECTION -PREDICATE -ALIAS')); 
Plan hash value: 3956160932 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    37 |     3   (0)| 00:00:01 | 
|   1 |  TABLE ACCESS FULL| EMP  |     1 |    37 |     3   (0)| 00:00:01 | 
--------------------------------------------------------------------------
Outline Data 
------------- 
  /*+ 
      BEGIN_OUTLINE_DATA 
      FULL(@"SEL$1" "EMP"@"SEL$1") 
      OUTLINE_LEAF(@"SEL$1") 
      ALL_ROWS 
      DB_VERSION('11.1.0.6') 
      OPTIMIZER_FEATURES_ENABLE('11.1.0.6') 
      IGNORE_OPTIM_EMBEDDED_HINTS 
      END_OUTLINE_DATA 
  */

 

只有在 Oracle Database 10g R2 和更新版本中，才能使用 ADVANCED 格式。

此输出格式包含 ALL 格式的所有部分，还包含概要数据，此数据表示用于重新生成特定计划的提示集。

如果要在不同环境重新生成特定执行计划，此部分可能十分有用。

事件 10053 的跟踪文件也显示此部分。

注：如果在 V$SQL_PLAN 中使用 ADVANCED 格式，会增加一个名为“Peeked Binds”的部分（按位置来标识）。

 

AUTOTRACE

  • AUTOTRACE 是一个 SQL*Plus 工具。
  • 在 Oracle7.3 中引入
  • 需要 PLAN_TABLE
  • 需要拥有 PLUSTRACE 角色才可以从某些 V$ 视图中检索统计信息
  • 默认情况下，它在运行查询后生成执行计划和统计信息。
  • 使用绑定扫视（递归 EXPLAIN PLAN）时，生成的计划可能不是实际计划

使用 SQL*Plus 运行 SQL 语句时，可以自动获得有关执行计划和语句执行统计信息的报表。会在成功执行 SQL DML（即 SELECT、DELETE、UPDATE 和 INSERT）语句后生成此报表。此报表对于监视和优化这些语句的性能十分有用。

要使用此功能，您的方案中必须有 PLAN_TABLE，且您必须拥有 PLUSTRACE 角色。要授予 PLUSTRACE 角色，需要拥有数据库管理员 (DBA) 权限。通过运行提供的 $ORACLE_HOME/sqlplus/admin/plustrce.sql 脚本会创建 PLUSTRACE 角色并将其授予 DBA 角色。

在某些版本和平台上，此操作是通过运行数据库创建脚本来完成的。如果您的平台不属于这种情况，请以 SYSDBA 的身份连接，并运行 plustrce.sql 脚本。

PLUSTRACE 角色包含在三个 V$ 视图上执行 SELECT 操作的权限。对于生成 AUTOTRACE 统计信息，这些权限是必需的。

AUTOTRACE 是用来优化 SQL 语句的最佳诊断工具。它完全是说明性的，比 EXPLAIN PLAN 更容易使用。

注：对于执行日期绑定变量隐式类型转换的语句，此系统不支持 EXPLAIN PLAN。
一般来说，使用绑定变量时，EXPLAIN PLAN 输出不代表实际的执行计划。

 

 

AUTOTRACE 语法

可以使用本幻灯片显示的语法，以不同的方式启用 AUTOTRACE。命令选项如下：

  • OFF：对 SQL 语句禁用自动跟踪
  • ON：对 SQL 语句启用自动跟踪
  • TRACE 或 TRACE[ONLY]：对 SQL 语句启用自动跟踪，并隐藏语句输出
  • EXPLAIN：显示执行计划，但不显示统计信息
  • STATISTICS：显示统计信息，但不显示执行计划

注：如果同时省略 EXPLAIN 和 STATISTICS 命令选项，则默认方式是显示执行计划和统计信息。

 

 

AUTOTRACE：示例

 

  • 使用 AUTOTRACE 启动语句跟踪：
SQL> set autotrace on

 

  • 仅显示执行计划，不执行语句：
SQL> set autotrace traceonly explain

 

  • 显示行和统计信息：

 

SQL> set autotrace on statistics

 

  • 仅显示计划和统计信息（隐藏行）：
SQL> set autotrace traceonly

 

可以通过设置 AUTOTRACE 系统变量来控制报表。下面是一些示例：

  • SET AUTOTRACE ON：AUTOTRACE 报表包括优化程序执行计划和 SQL 语句执行统计信息。
  • SET AUTOTRACE TRACEONLY EXPLAIN：AUTOTRACE 报表仅显示优化程序执行路径，不执行语句。
  • SET AUTOTRACE ON STATISTICS：AUTOTRACE 报表显示 SQL 语句执行统计信息和行。
  • SET AUTOTRACE TRACEONLY：此语句类似于 SET AUTOTRACE ON，但是它隐藏用户的查询输出（如果有）。如果启用了 STATISTICS，则仍将提取查询数据，但不会打印出来。
  • SET AUTOTRACE OFF：不生成 AUTOTRACE 报表。这是默认设置。

 

AUTOTRACE：统计信息

SQL> show autotrace 
autotrace OFF 
SQL> set autotrace traceonly statistics 
SQL> SELECT * FROM oe.products; 
 
288 rows selected. 
 
Statistics 
-------------------------------------------------------- 
	       1334  recursive calls 
          0  db block gets 
        686  consistent gets 
        394  physical reads 
          0  redo size 
     103919  bytes sent via SQL*Net to client 
        629  bytes received via SQL*Net from client 
         21  SQL*Net roundtrips to/from client 
         22  sorts (memory) 
          0  sorts (disk) 
        288  rows processed

 

服务器会在执行语句时记录下统计信息，这些统计信息表明执行语句所需的系统资源。结果包含以下统计信息：

  • recursive calls 指在用户级别和系统级别生成的递归调用数量。Oracle DB 维护用于内部处理的表。Oracle DB 需要更改这些表时，会在内部生成内部 SQL 语句，后者则生成递归调用。
  • db block gets 是请求 CURRENT 块的次数。
  • consistent gets 是对块请求一致读取的次数。
  • physical reads 是从磁盘读取的数据块的总数。此数量等于“物理直接读取”值加上读入缓冲区高速缓存的总数量。
  • redo size 是生成的重做总量（字节数）。
  • bytes sent via SQL*Net to client 是前台进程发送给客户机的总字节数。
  • bytes received via SQL*Net from client 是通过 Oracle Net 从客户机接收的字节总数。
  • SQL*Net roundtrips to/from client 是 Oracle Net 发送给客户机以及从客户机接收的消息总数。

 

注：AUTOTRACE 打印的统计信息是从 V$SESSTAT 中检索来的。

  • sorts (memory) 是不需要执行任何磁盘写入、完全在内存中执行的排序操作的数量。
  • sorts (disk) 是至少需要执行一次磁盘写入的排序操作的数量。
  • rows processed 是操作期间处理的行数。

统计信息中所指的客户机是 SQL*Plus。Oracle Net 指 SQL*Plus 与服务器之间的一般进程通信，不考虑是否安装了 Oracle Net。您不能更改统计信息报表的默认格式。

注： db block gets 指示从数据库中读取当前块的次数。consistent gets 是读取必须符合特定系统更改号 (SCN) 的块的次数。physical reads 指示从磁盘读取块的次数。通常会监视 db block gets 和 consistent gets 这两条统计信息。与检索的行数相比，这两个值应该较小。应在内存中执行排序，而不应在磁盘上执行排序。

 

 

使用 V$SQL_PLAN 视图

  • V$SQL_PLAN 提供了一种方法，可用于检查仍位于库高速缓存的游标的执行计划。
  • V$SQL_PLAN 与 PLAN_TABLE 十分类似：

–PLAN_TABLE 显示执行相应语句时可以使用的理论计划。

–V$SQL_PLAN 包含实际使用的计划。

  • 它包含库高速缓存中每个游标的执行计划（包括子游标）。
  • 链接到 V$SQL：

–ADDRESS、HASH_VALUE 和 CHILD_NUMBER

 

此视图提供了一种方法，可用于检查仍位于库高速缓存的游标的执行计划。此视图中的信息与 PLAN_TABLE 视图中的信息非常类似。但是，EXPLAIN PLAN 显示的是执行相应语句时可以使用的理论计划，而V$SQL_PLAN 包含实际使用的计划。通过 EXPLAIN PLAN 语句获取的执行计划与用来执行游标的执行计划可能有所不同。原因在于，也许已经用不同的会话参数值编译了游标。

V$SQL_PLAN 显示一个游标的计划，并非与一个 SQL 语句相关联的所有游标的计划。区别在于，一个 SQL 语句可能包含多个与其相关联的游标，而每个游标都由 CHILD_NUMBER 标识。例如，如果某语句引用的对象在不同方案中，那么，不同用户执行此同一语句时所关联的游标会不同。同样，不同的提示会导致不同的游标。V$SQL_PLAN 表可用于查看同一语句不同子游标的不同计划。

注：另一有用的视图是 V$SQL_PLAN_STATISTICS，此视图为每个缓存的游标的执行计划中的每项操作提供执行统计信息。另外，V$SQL_PLAN_STATISTICS_ALL 视图将 V$SQL_PLAN 中的信息与 V$SQL_PLAN_STATISTICS 和 V$SQL_WORKAREA 中的执行统计信息连接在一起。

 

 

V$SQL_PLAN 的列

 

HASH_VALUE 库高速缓存中的父语句的散列值
ADDRESS 此游标父项的句柄地址
CHILD_NUMBER 使用此执行计划的子游标编号
POSITION 拥有同一 PARENT_ID 的所有操作的处理顺序
PARENT_ID 对当前步骤的输出进行操作的下一执行步骤的 ID
ID 指定给执行计划中每个步骤的编号
PLAN_HASH_VALUE 游标的 SQL 计划的数字表达形式

注：此处仅列出了一部分列。  

 

此视图几乎包含 PLAN_TABLE 中的所有列，还包含其它一些列。与 PLAN_TABLE 共有的列具有相同的值：

  • ADDRESS
  • HASH_VALUE

可以使用 ADDRESS 和HASH_VALUE 列与 V$SQLAREA 联接，以便添加特定于游标的
信息。

可以使用 ADDRESS、HASH_VALUE 和 CHILD_NUMBER 列与 V$SQL 联接，以便添加特定于子游标的信息。

PLAN_HASH VALUE 列是游标的 SQL 计划的数字表达形式。通过比较两个计划的 PLAN_HASH_VALUE，可以轻松地确定这两个计划是否相同（而不必逐行比较这两个计划）。

注：从 Oracle Database 10g 起，V$SESSION 中的 SQL_HASH_VALUE 被 SQL_ID 取代，在许多其它V$ 视图中都可以检索出后者。SQL_HASH_VALUE 是 32 位的值，在存储 AWR 数据的大型资料档案库中，使用它不足以唯一地标识对象。SQL_ID 是 64 位的散列值，唯一性更高，其中的后 32 位为 SQL_HASH_VALUE。通常用字符串表示该值，以便简化管理。

 

V$SQL_PLAN_STATISTICS 视图

  • V$SQL_PLAN_STATISTICS 提供实际执行统计信息：

–STATISTICS_LEVEL 设置为 ALL

–GATHER_PLAN_STATISTICS 提示

  • 使用 V$SQL_PLAN_STATISTICS_ALL，您可以一一对比优化程序的估计值与实际执行的统计值。

V$SQL_PLAN_STATISTICS 视图提供计划中每个操作的实际执行统计信息，例如输出行数和用时。除输出行数外，所有统计信息都是累积结果。例如，联接操作的统计信息还包含其两个输入的统计信息。如果游标是在 STATISTICS_LEVEL 初始化参数设置为 ALL 或使用  GATHER_PLAN_STATISTICS 提示的条件下编译的，则可以在 V$SQL_PLAN_STATISTICS 中查看其统计信息。

V$SQL_PLAN_STATISTICS_ALL 视图包含使用 SQL 内存的行源的内存使用情况统计信息（排序或散列联接）。此视图将 V$SQL_PLAN 中的信息与 V$SQL_PLAN_STATISTICS 和 V$SQL_WORKAREA 中的执行统计信息连接在一起。

 

重要动态性能视图之间的联系

V$SQLAREA 显示共享 SQL 区域的统计信息，在此视图中每个 SQL 字符串对应一行。它显示内存中的、经过分析的和准备执行的 SQL 语句的统计信息：

  • SQL_ID 是库高速缓存中父游标的 SQL 标识符。
  • VERSION_COUNT 是高速缓存中此父游标下的子游标的数量。

V$SQL 列出共享 SQL 区域的统计信息，在此视图中输入的原始 SQL 文本的每个子项对应一行：

  • ADDRESS 代表此游标父项的句柄地址。
  • HASH_VALUE 是库高速缓存中父语句的值。
  • SQL_ID 是库高速缓存中父游标的 SQL 标识符。
  • PLAN_HASH_VALUE 是此游标的 SQL 计划的数字表达形式。通过比较两个计划的 PLAN_HASH_VALUE，可以轻松地确定这两个计划是否相同（而不必逐行比较这两个计划）。
  • CHILD_NUMBER 是此子游标的编号。

 

V$SQL 中显示的统计信息通常在查询执行结束时更新。但对于长时间运行的查询而言，此信息会每五秒钟更新一次。这样，在长时间运行的 SQL 语句执行过程中，您仍可以轻松地看到语句的影响。

V$SQL_PLAN 包含加载到库高速缓存的每个子游标的执行计划信息。可以使用 ADDRESS、HASH_VALUE 和 CHILD_NUMBER 列与 V$SQL 联接，以便添加特定于子游标的信息。

V$SQL_PLAN_STATISTICS 提供每个子游标的行源级别的执行统计信息。可以使用 ADDRESS 和 HASH_VALUE 列与 V$SQLAREA 联接，以便定位父游标。可以使用 ADDRESSHASH_VALUE 和 CHILD_NUMBER 列与 V$SQL 联接，以便定位使用此区域的子游标。

V$SQL_PLAN_STATISTICS_ALL 包含使用 SQL 内存的行源的内存使用情况统计信息（排序或散列联接）。此视图将 V$SQL_PLAN 中的信息与 V$SQL_PLAN_STATISTICS 和 V$SQL_WORKAREA 中的执行统计信息连接在一起。

V$SQL_WORKAREA 显示有关 SQL 游标使用的工作区的信息。V$SQL 视图中列出了共享池中存储的每个 SQL 语句包含的一个或多个子游标。V$SQL_WORKAREA 列出这些子游标需要的所有工作区。V$SQL_WORKAREA 可与 V$SQLAREA 联接（使用 ADDRESS 和 HASH_VALUE 列），也可与 V$SQL 联接（使用 ADDRESS、HASH_VALUE 和 CHILD_NUMBER 列）。

您可以使用此视图来获得以下问题的答案：

  • 哪 10 个工作区需要的高速缓存区最大？
  • 对于以 AUTO 模式分配的工作区，运行时占用内存最多的工作区占百分之几？

V$SQLSTATS 显示 SQL 游标的基本性能统计信息，每一行显示了 SQL 文本与优化程序计划的唯一组合的数据（即 SQL_ID 和PLAN_HASH_VALUE 的唯一组合）。V$SQLSTATS 中列的定义与 V$SQL 和V$SQLAREA 视图的列定义无异。但是，V$SQLSTATS 视图与V$SQL 和 V$SQLAREA 的不同之处在于，它运行得更快，可伸缩性更高，保留数据的能力更强（即使游标过期，已经从共享池释放，统计信息仍可能出现在此视图中）。请注意，V$SQLSTATS 包含 V$SQL 和V$SQLAREA 中的部分列。

 

 

查询 V$SQL_PLAN

 

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


SQL_ID  47ju6102uvq5q, child number 0 
------------------------------------- 
SELECT e.last_name, d.department_name 
FROM hr.employees e, hr.departments d WHERE 
e.department_id =d.department_id 
 
Plan hash value: 2933537672 
-------------------------------------------------------------------------------- 
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU| 
-------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |             |       |       |     6 (100| 
|   1 |  MERGE JOIN                  |             |   106 |  2862 |     6  (17| 
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0| 
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0| 
|*  4 |   SORT JOIN                  |             |   107 |  1177 |     4  (25| 
|   5 |    TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1177 |     3   (0| 
-------------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
----------------------------------------------------
   4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") 
       filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") 
 
24 rows selected.

 

可以使用 DBMS_XPLAN.DISPLAY_CURSOR() 函数查询 V$SQL_PLAN，来显示当前或最后一次执行的语句（如本例所示）。可以将语句的 SQL_ID 值作为参数传递，从而获得给定语句的执行计划。SQL_ID 是游标高速缓存中 SQL 语句的 SQL_ID。可以通过查询 V$SQLAREA 或 V$SQL 的SQL_ID 列来检索相应的值。您也可以针对特定会话，在 V$SESSION 中对 PREV_SQL_ID 列执行 SELECT 操作。此参数默认为空，此时，将显示相应会话执行的最后一个游标的计划。要获得 SQL_ID，请执行下列查询：

SELECT e.last_name, d.department_name

FROM hr.employees e, hr.departments d
WHERE  e.department_id =d.department_id;

 

SELECT SQL_ID, SQL_TEXT FROM V$SQL

WHERE SQL_TEXT LIKE ‘%SELECT e.last_name,%’ ;

 

13saxr0mmz1s3  select SQL_id, sql_text from v$SQL …

47ju6102uvq5q  SELECT e.last_name, d.department_name …

CHILD_NUMBER 是所显示游标的子项编号。如果未提供，将显示与提供的 SQL_ID 参数匹配的所有游标的执行计划。只有指定 SQL_ID 时，才能指定 CHILD_NUMBER。

 

FORMAT 参数控制计划的详细程度。除标准值（BASIC、TYPICAL、SERIAL、ALL 和 ANDVANCED）外，还支持用以下值显示游标的运行时统计信息：

  • IOSTATS：此格式假定执行 SQL 语句时收集基本的计划统计信息（使用 GATHER_PLAN_STATISTICS 提示，或将 statistics_level 参数设置为 ALL），显示游标各次执行的 I/O 统计信息（适用于ALL 或仅适用于 LAST）。
  • MEMSTATS：此格式假设启用了程序全局区 (PGA) 内存管理（即 pga_aggregate_target 参数被设置为非零值），显示内存管理统计信息（例如，运算符的执行模式，使用的内存量，溢入磁盘的字节数等）。这些统计信息仅适用于内存密集型操作，如散列联接、排序或一些位图运算符。
  • ALLSTATS：IOSTATSMEMSTATS 的快捷方式
  • LAST：默认情况下，会为游标的所有执行显示计划统计信息。可以指定 LAST 关键字，以便仅显示最后一次执行的统计信息。

 

自动工作量资料档案库 (AWR)

  • 为了执行问题检测和自动优化功能而收集、处理和维护性能统计信息
  • 统计信息包括：

–对象统计信息

–时间模型统计信息

–某些系统和会话统计信息

–活动会话历史记录 (ASH) 统计信息

  • 自动生成性能数据的快照

AWR 是 Oracle Database 10g 引入的智能基础结构的一部分。此基础结构供许多组件使用，如用于分析的自动数据库诊断监视器 (ADDM)。AWR 为了执行问题检测和自动优化功能而自动收集、处理和维护系统性能统计信息，并将统计信息永久存储在数据库中。

AWR 收集和处理的统计信息包括：

  • 对象统计信息，用于确定数据库段的访问和使用情况统计信息
  • 基于活动的时间使用情况的时间模型统计信息，显示在 V$SYS_TIME_MODEL 和 V$SESS_TIME_MODEL 视图中
  • V$SYSSTAT 和 V$SESSTAT 视图收集了部分系统和会话统计信息
  • 系统负载最高的 SQL 语句（根据用时、CPU 时间和缓冲区获取次数等标准确定）
  • ASH 统计信息，代表最近会话的历史记录

数据库每小时都自动生成一次性能数据的快照，并将统计信息收集在工作量资料档案库中。ADDM 此后将分析快照间隔内的数据。ADDM 比较不同快照之间的差异，并根据对系统负载的影响确定要捕获的 SQL 语句。这将减少一段时间内需要捕获的 SQL 语句的数量。

注：通过使用 PL/SQL 程序包，如 DBMS_WORKLOAD_REPOSITORY，或使用 Oracle Enterprise Manager，可以管理在 AWR 中存储 SQL 的频率和保留期。

 

 

使用 PL/SQL 管理 AWR

  • 创建快照：
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ('ALL');
  • 删除快照：
SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE – 
    (low_snap_id => 22, high_snap_id => 32, dbid => 3310949047);
  • 管理快照设置：
SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (retention => 43200, interval => 30, dbid => 3310949047);

管理 AWR 的主要方式是 Oracle Enterprise Manager，但也可使用 DBMS_WORKLOAD_REPOSITORY 程序包中的过程管理监视功能。

系统会自动为 Oracle DB 生成快照，但您可以使用 DBMS_WORKLOAD_REPOSITORY 过程手动创建、删除和修改供 ADDM 使用的快照和基线。快照和基线是用于比较性能的特定时间段的历史数据集。要调用这些过程，用户必须具有 DBA 角色。

创建快照

如果要在快照自动生成时间以外的时间点捕获统计信息，您可以使用 CREATE_SNAPSHOT 过程手动创建快照。下面是一个示例：

Exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (‘ALL’);

在本例中，将刷新级别指定为 ALL 后，将立即创建一个实例快照，默认的刷新级别为 TYPICAL。可在 DBA_HIST_SNAPSHOT 视图中查看此快照。

 

删除快照

您可以使用 DROP_SNAPSHOT_RANGE 过程删除某一范围内的快照。要查看快照 ID 连同数据库 ID 的列表，请查看 DBA_HIST_SNAPSHOT 视图。例如，可以删除以下范围内的快照：

Exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE – (low_snap_id => 22, high_snap_id => 32, dbid => 3310949047);

在本例中，指定的快照 ID 删除范围是 22 到 32。3310949047 是可选的数据库标识符。如果不指定 dbid 的值，会默认使用本地数据库标识符。

调用 DROP_SNAPSHOT_RANGE 过程时，也会清除快照范围对应的时间段的 ASH 数据。

修改快照设置

可以调整为指定数据库 ID 生成快照的间隔和保留期。但要注意，这会影响 Oracle 诊断工具的精确度。

INTERVAL 设置指定自动生成快照的频率（分钟）。RETENTION 设置指定快照在工作量资料档案库的存储时间（分钟）。要调整设置，请使用 MODIFY_SNAPSHOT_SETTINGS 过程，如下例所示：

Exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( -retention => 43200, interval => 30, dbid => 3310949047);

在本例中，保留期被指定为 43,200 分钟（30 天），两个快照之间的时间间隔被指定为 30 分钟。如果指定 NULL，将沿用现有值。3310949047 是可选的数据库标识符。如果不指定 dbid 的值，会默认使用本地数据库标识符。可以使用 DBA_HIST_WR_CONTROL 视图检查数据库实例的当前设置。

 

重要的 AWR 视图

  • V$ACTIVE_SESSION_HISTORY
  • V$ 度量视图
  • 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

 

 

可以在 Oracle Enterprise Manager 屏幕或 AWR 报表中查看 AWR 数据。但也可以直接从以下视图查看统计信息：

V$ACTIVE_SESSION_HISTORY：此视图显示处于活动状态的数据库会话活动，每秒钟采样一次。

V$ 度量视图提供用于跟踪系统性能的度量数据。度量视图划分为不同的组，如事件、事件类、系统、会话、服务、文件和表空间度量。V$METRICGROUP 视图中标识了这些组。

DBA_HIST 视图包含在数据库中存储的历史记录数据。这组视图包括：

  • DBA_HIST_ACTIVE_SESS_HISTORY 针对最近系统活动的内存活动会话采样历史记录，显示其内容的历史记录。
  • DBA_HIST_BASELINE 显示有关系统中捕获的基线的信息。
  • DBA_HIST_DATABASE_INSTANCE 显示有关数据库环境的信息。
  • DBA_HIST_SNAPSHOT 显示有关系统中的快照的信息。
  • DBA_HIST_SQL_PLAN 显示 SQL 执行计划。
  • DBA_HIST_WR_CONTROL 显示用于控制 AWR 的设置。

 

查询 AWR

  • 检索在特定 SQL_ID 下存储的所有执行计划。
SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE (DBMS_XPLAN.DISPLAY_AWR('454rug2yva18w'));  
 
 
PLAN_TABLE_OUTPUT 
---------------------------------------------------------------------------------- SQL_ID 454rug2yva18w  
----------------------------------------------------------------------------------
select /* example */ * from hr.employees natural join hr.departments 
 
Plan hash value: 4179021502 
 
----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time    | 
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |       |       |     6 (100)|         | 
|   1 |  HASH JOIN         |             |    11 |   968 |     6  (17)| 00:00:01 | 
|   2 |   TABLE ACCESS FULL| DEPARTMENTS |    11 |   220 |     2   (0)| 00:00:01 | 
|   2 |   TABLE ACCESS FULL| DEPARTMENTS |    11 |   220 |     2   (0)| 00:00:01 | 
|   3 |   TABLE ACCESS FULL| EMPLOYEES   |   107 |  7276 |     3   (0)| 00:00:01 | 
----------------------------------------------------------------------------------

 

显示包含“JF.”的所有语句的所有执行计划。

 

SELECT tf.* FROM DBA_HIST_SQLTEXT ht, table 
    (DBMS_XPLAN.DISPLAY_AWR(ht.sql_id,null, null,  'ALL' )) tf  
 WHERE ht.sql_text like '%JF%';

 

可以使用 DBMS_XPLAN.DISPLAY_AWR() 函数显示 AWR 中存储的所有计划。在本
幻灯片的示例中，将 SQL_ID 作为参数传递。SQL_ID 是游标高速缓存中 SQL 语句的 SQL_ID。DISPLAY_AWR() 函数也使用 PLAN_HASH_VALUE、DB_ID 和 FORMAT
参数。

完成此示例的步骤如下：

  1. 执行 SQL 语句：

SQL> select /* example */ * from hr.employees natural
join hr.departments;

  1. 查询 V$SQL_TEXT 以便获得 SQL_ID：

SQL> select sql_id, sql_text from v$SQL

where sql_text

like ‘%example%’;

SQL_ID        SQL_TEXT

————- ——————————————-

F8tc4anpz5cdb select sql_id, sql_text from v$SQL …

454rug2yva18w select /* example */ * from …

 

 

  1. 使用 SQL_ID 确认已在 DBA_HIST_SQLTEXT 字典视图中捕获了该语句。如果查询没有返回行，则表明该语句尚未加载到 AWR 中。

SQL> SELECT SQL_ID, SQL_TEXT FROM dba_hist_sqltext WHERE SQL_ID =’ 454rug2yva18w’;

no rows selected

可以手动捕获 AWR 快照，而不必一直等待系统捕获下一张快照（每小时一次）。然后在 DBA_HIST_SQLTEXT 中检查是否已捕获快照：

SQL> exec dbms_workload_repository.create_snapshot;

 

PL/SQL procedure successfully completed.

 

SQL> SELECT SQL_ID, SQL_TEXT FROM dba_hist_sqltext WHERE SQL_ID =’ 454rug2yva18w’;

SQL_ID          SQL_TEXT

————–  ——————————-

454rug2yva18w   select /* example */ * from …

 

  1. 使用 DBMS_XPLAN.DISPLAY_AWR () 函数来检索执行计划：

SQL>SELECT PLAN_TABLE_OUTPUT FROM TABLE (DBMS_XPLAN.DISPLAY_AWR(‘454rug2yva18w’));

 

 

根据 AWR 数据生成 SQL 报表

 

SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt 
Specify the Report Type … 
Would you like an HTML report, or a plain text report?Specify the number of days of snapshots to choose from 
Specify the Begin and End Snapshot Ids … 
Specify the SQL Id … 
Enter value for sql_id:6g1p4s9ra6ag8Specify the Report Name …

 

从 Oracle Database 10gR2 开始，有了基本上等同于 Statspack 的 sqrepsql.sql 文件，您可以根据 AWR 数据生成 SQL 报表。在 10.1.0.4.0 中，AWR 中不存在等同于 sprepsql.sql 的文件。但在 10gR2 中提供了等同于 sprepsql.sql 的文件。在 10gR2 中，可以通过调用 $ORACLE_HOME/rdbms/admin/awrsqrpt.sql 文件生成 AWR SQL 报表。

使用 dbms_xplan PL/SQL 程序包中的 display_awr 表函数，可以显示 AWR 中的计划信息。

例如，下面的语句可显示 AWR 中 SQL_ID 对应的计划信息：
select * from table(dbms_xplan.display_awr(‘6g1p4s9ra6ag8’));

可通过查询 DBA_HIST_SQLTEXT 列中的 SQL_ID，检索自己感兴趣的 SQL 语句的相应
ID 值。

 

 

SQL 监视：概览

 

将 STATISTICS_LEVEL 初始化参数设置为 ALL 或 TYPICAL（默认值）时，会默认启用 SQL 监视功能。

由于 SQL 监视是 Oracle 数据库优化包的功能，所以还必须将 CONTROL_MANAGEMENT_PACK_ACCESS 参数设置为 DIAGNOSTIC+TUNING
（默认值）。

默认情况下，当 SQL 语句并行运行时，或在单个执行时 CPU 或 I/O 用时至少达五秒钟时，将自动启动 SQL 监视。

如上所述，默认情况下，SQL 监视处于活动状态。但可以使用两个语句级别的提示来强制或阻止对 SQL 语句进行监视。要强制 SQL 监视，请使用 MONITOR 提示。使用 NO_MONITOR 提示可阻止系统对加提示的 SQL 语句进行监视。

可以用 V$SQL_MONITOR 和 V$SQL_PLAN_MONITOR 视图，监视 SQL 语句执行的统计信息。

启动监视后，会将一个条目添加到 V$SQL_MONITOR 动态性能视图。此条目跟踪为执行收集的关键性能度量，包括用时、CPU 时间、读写次数、I/O 等待时间和其它各类等待时间。在执行语句时，将近乎实时地刷新这些统计信息，一般每秒钟刷新一次。

 

执行结束后，并不立即删除监视信息，而会在 V$SQL_MONITOR 视图中至少将其保留一分钟。最终将删除此条目，这样在监视新语句时，可以回收其占用的空间。

V$SQL_MONITOR 和V$SQL_PLAN_MONITOR 视图可与下列视图一起使用，以便对所监视的执行获取附加信息：

V$SQL、V$SQL_PLAN、V$ACTIVE_SESSION_HISTORY、V$SESSION_LONGOPS 和 V$SESSION

也可以使用 SQL 监视报表来查看 SQL 监视数据。

还可以通过 Oracle Enterprise Manager，在 GUI 版本中查看 SQL 监视报表。

 

SQL 监视报表：示例

 

在另一个会话中:

 

SQL> select count(*) from sales;

 

 

 

SQL> set long 10000000 
SQL> set longchunksize 10000000 
SQL> set linesize 200 
SQL> select dbms_sqltune.report_sql_monitor from dual; 
 
SQL Monitoring Report 
 
SQL Text 
-------------------------- 
select count(*) from sales 
 
Global Information 
 Status              :  EXECUTING 
 Instance ID         :  1 
 Session ID          :  125 
 SQL ID              :  fazrk33ng71km 
 SQL Execution ID    :  16777216 
 Plan Hash Value     :  1047182207 
 Execution Started   :  02/19/2008 21:01:18 
 First Refresh Time  :  02/19/2008 21:01:22 
 Last Refresh Time   :  02/19/2008 21:01:42 
------------------------------------------------------------ 
| Elapsed |   Cpu   |    IO    |  Other   | Buffer | Reads | 
| Time(s) | Time(s) | Waits(s) | Waits(s) |  Gets  |       | 
------------------------------------------------------------ 
|      22 |    3.36 |     0.01 |       19 |   259K |  199K | 
------------------------------------------------------------

 

本例假设您在对 SALES 表执行 SELECT 语句时，不在用来打印 SQL 监视报表的会话中，而在另一会话中。

DBMS_SQLTUNE.REPORT_SQL_MONITOR 函数接受多个输入参数，这些参数用于指定执行、报表的详细程度和报表类型（TEXT、HTML 或 XML）。默认情况下，如果未指定任何参数，会为监视的最后一个执行生成文本报表，如本幻灯片中的示例所示。

在 SELECT 语句启动后进行执行阶段时，您在另一个会话中打印 SQL 监视报表。

在报表中可以看到当前执行的 SELECT 语句。

“Global Information”部分列出了一些重要信息：

  • 为了唯一地标识同一 SQL 语句的两次执行，会生成一个名为执行键的组合键。执行键包含三个属性，这三个属性分别对应于 V$SQL_MONITOR 中的列：

-标识 SQL 语句的 SQL 标识符 (SQL_ID)

-用于确保主键唯一的内部生成的标识符 (SQL_EXEC_ID)

-开始执行的时间戳 (SQL_EXEC_START)

该报表还显示到目前为止计算出的一些重要统计信息。

 

SQL 监视报表：示例

 
SQL Plan Monitoring Details 
==================================================================================== 
| Id   |      Operation       | Name  |  Rows   | Cost  |   Time    | Start  | 
|      |                      |       | (Estim) |       | Active(s) | Active | 
==================================================================================== 
|    0 | SELECT STATEMENT     |       |         | 78139 |           |        | 
|    1 |   SORT AGGREGATE     |       |       1 |       |           |        | 
| -> 2 |    TABLE ACCESS FULL | SALES |  53984K | 78139 |        23 |     +1 | 
|      |                      |       |         |       |           |        | 
==================================================================================== 
 
 
================================================================================== 
Starts |   Rows   | Activity  |           Activity Detail            | Progress  | 
       | (Actual) | (percent) |              (sample #)              |           | 
     1 |          |           |                                      |           | 
     1 |          |           |                                      |           | 
     1 |   42081K |    100.00 | Cpu (4)                              | 74%       | 
==================================================================================

 

此报表接着显示语句当前使用的执行路径。SQL 监视显示出计划执行的当前操作。这样，您可以检测出计划中最费时的部分，从而集中精力分析这些部分。正在运行的操作由报表 ID 列中的箭头标记。

“Time Active(s)”列显示操作处于活动状态的时间（首次活动时间与最后活动时间之间相差的秒数）。

“Start Active”列显示执行计划中的操作相对于 SQL 语句开始执行时间的启动时间（秒）。在此报表中，ID 2 处的全表扫描操作第一个启动，其“Start Active”为“+1s”，到目前为止运行了最初的 23 秒。

“Starts”列显示执行计划中的相应操作的执行次数。

“Rows (Actual)”列指示生成的行数，“Rows (Estim)”列显示优化程序估计的基数。

“Activity (percent)”和“Activity Detail (sample #)”列是通过联接 V$SQL_PLAN_MONITOR 和 V$ACTIVE_SESSION_HISTORY 视图推导而来的。前者显示执行计划每个操作占用的数据库时间百分比。后者显示活动的性质（例如 CPU 或等待事件）。

 

 

在此报表中，“Activity Detail (sample #)”列显示，操作 ID 2（对 SALES 表的全表扫描）占用了大部分数据库时间，占用率达到 100%。到目前为止，此活动由 4 个采样组成，这些采样都仅针对 CPU。

最后一列是“Progress”，显示来自 V$SESSION_LONGOPS 视图的操作进度监视信息。在此报表中，它显示到目前为止，TABLE ACCESS FULL 操作完成了 74%。经过一段时间后，报表中才会显示出此列；仅为装备行源显示此列。

注：此特定报表没有显示“Memory”和“Temp”列，这两列指明执行计划的相应操作占用的内存量和临时空间量。

 

解释执行计划

将其转换为树状结构。

解释计划输出是行源树的表示形式。

每个步骤（执行计划中的行，或树中的节点）代表行源。

解释计划实用程序会缩进子节点，表明它们是其上父节点的子节点。

父节点之下节点的顺序指明相应级别中节点的执行顺序。如果两个步骤的缩进级别相同，则首先执行第一个步骤。

在树格式中，树中每个级别最左端的叶节点最先执行。

执行计划的步骤并不按编号顺序执行。步骤之间存在父子关系。

在 PLAN_TABLE 和V$SQL_PLAN 中，用于检索树结构的重要元素是 ID、PARENT_ID 和 POSITION 列。在跟踪文件中，这些列分别对应于 id、pid 和 pos 字段。

读取执行计划的一种方法是将其转换为以树结构表示的图。您可以从顶部的 id=1 开始，这是树的根节点。接下来，必须查找为根节点提供数据的操作。这由 parent_id 或 pid 的值为 1 的操作完成。

注：本课程重点讨论串行计划，不讨论并行执行计划。

 

要将计划绘成树形结构，请执行以下操作：

1.找出编号最小的 ID，将其置于顶部。

2.查找 PID（父 ID）等于此值的行。

3.将这些行置于树中的父节点之下，按从左到右的顺序，先放置 POS 值最小的节点，最后放置 POS 值最大的节点。

4.找到与父节点对应的所有 ID 后，下移至下一 ID 并重复此过程，查找具有同一 PID 的新行。

在解释计划中，首先要确定哪个节点最先执行。本幻灯片中的方法说明了这一点，但对于复杂计划，这有时很难办到，也难以按这些步骤一直操作到最后。大计划只是条目较多，除此之外，它与小型计划完全相同，都应用相同的基本规则。始终都可以将计划折叠起来，隐藏树中占用资源不多的分支。

标准解释计划的解释过程：

1.从顶部开始。

2.在行源中一直下移，直至找到一个生成数据且不使用任何数据的行源。这便是起始行源。

3.查看此行源的同级行源。接下来执行这些行源。

4.执行子行源后，接着执行父行源。

5.完成此父行源及其子行源后，在树中向上退一级，查看相应父行源的同级行源和父行源。按前述方式执行。

6.在计划中不断上移，直至用完所有行源为止。

标准树的解释过程：

1.从顶部开始。

2.在树中向左下移，直至到达左节点。首先执行此节点。

3.查看此行源的同级行源。接下来执行这些行源。

4.执行子行源后，接着执行父行源。

5.完成此父行源及其子行源后，在树中向上退一级，查看相应父行源的同级行源和父行源。按前述方式执行。

6.在树中不断上移，直至用完所有行源为止。

只要记住解释计划的一些基本规则，并拥有一定经验，就可以轻松地读懂大多数计划了。

 

 

执行计划的解释过程：示例 1

SELECT /*+ RULE */ ename,job,sal,dnameFROM emp,dept 
WHERE dept.deptno=emp.deptno and not exists(SELECT * 
                                            FROM salgrade 
                                            WHERE emp.sal between losal and hisal); 

-------------------------------------------------- | Id  | Operation                     | Name     | 
-------------------------------------------------- |   0 | SELECT STATEMENT              |          | 
|*  1 |  FILTER                       |          | 
|   2 |   NESTED LOOPS                |          | 
|   3 |    TABLE ACCESS FULL          | EMP      | 
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPT     | 
|*  5 |     INDEX UNIQUE SCAN         | PK_DEPT  | 
|*  6 |   TABLE ACCESS FULL           | SALGRADE | 
--------------------------------------------------
 
Predicate Information (identified by operation id): 
--------------------------------------------------
 
   1 - filter( NOT EXISTS    (SELECT 0 FROM "SALGRADE" "SALGRADE" WHERE 
     "HISAL">=:B1 AND "LOSAL"<=:B2)) 
   5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO") 
   6 - filter("HISAL">=:B1 AND "LOSAL"<=:B2)

 

首先介绍一个示例查询，以便说明如何解释执行计划。本幻灯片显示了一个查询及其相关联的执行计划，还以树形格式显示了同一计划。

此查询尝试查找薪金不在薪金等级表中的薪金范围内的雇员。此查询包含一条检索两个表的 SELECT 语句，同时包含一个子查询，子查询根据另一个表查找薪金等级。

看一下此查询的执行顺序。按照前一张幻灯片中的规则，本幻灯片中示例的执行顺序为
3 – 5 – 4 – 2 – 6 – 1：

  • 3：此计划首先对 EMP 执行全表扫描 (ID=3)。
  • 5：这些行传递回控制嵌套循环联接步骤 (ID=2)，此步骤在 ID=5 的操作中使用这些行在索引 PK_DEPT 中查找行。
  • 4：在 ID=4 的操作中使用来自索引的 ROWID 在 DEPT 表中查找其它信息。
  • 2：ID=2，即嵌套循环联接步骤，将执行到完成为止。
  • 6：在 ID=2 的操作用尽其行源后，ID=6 的操作（与 ID=2 的操作位于树的同一级别，二者是同级关系）对 SALGRADE 执行全表扫描。
  • 1：用于对来自 ID2 和 ID6 的行进行过滤。

注意，子项先于父项执行，因此，虽然联接结构必须在子项执行前设置，但子项被标记为首先执行。最简单的方式是按执行的完成顺序进行考虑，因此，对于 ID=2 处的 NESTED LOOPS 联接，只有其两个子项 {ID=3 和 ID=4（及其子项）} 完成执行后，才可以完成 ID=2 的操作。

 

 

执行计划的解释过程：示例 1

SQL> alter session set statistics_level=ALL; 
 
Session altered. 
 
SQL> select /*+ RULE to make sure it reproduces 100% */ ename,job,sal,dname 
from emp,dept where dept.deptno = emp.deptno and not exists (select * from salgrade where emp.sal between losal and hisal); 
 
no rows selected 
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL IOSTATS LAST')); 
 
SQL_ID  274019myw3vuf, child number 0 
------------------------------------- 
… 
Plan hash value: 1175760222 
-------------------------------------------------------------------------------- 
| Id  | Operation                     | Name     | Starts | A-Rows | Buffers |  
-------------------------------------------------------------------------------- 
|*  1 |  FILTER                       |          |      1 |      0 |      61 | 
|   2 |   NESTED LOOPS                |          |      1 |     14 |      25 | 
|   3 |    TABLE ACCESS FULL          | EMP      |      1 |     14 |       7 | 
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPT     |     14 |     14 |      18 | 
|*  5 |     INDEX UNIQUE SCAN         | PK_DEPT  |     14 |     14 |       4 | 
|*  6 |   TABLE ACCESS FULL           | SALGRADE |     12 |     12 |      36 | 
--------------------------------------------------------------------------------

 

本幻灯片中的示例是在 STATISTICS_LEVEL 设置为 ALL 的情况下从 V$SQL_PLAN 转储的计划。与 EXPLAIN PLAN 命令的输出相比，此报表显示了一些重要的附加信息：

  • “A-Rows”对应于相应行源生成的行数。
  • “Buffers”对应于行源执行的一致读取数。
  • “Starts”指示处理相应操作的次数。

系统获取 EMP 表中每一行的 ENAME、SAL、JOB 和 DEPTNO。

此后，系统通过 DEPT 表的唯一索引 (PK_DEPT) 访问该表，以便使用来自上一结果集的 DEPTNO 获得 DNAME。

仔细观察统计信息将发现，EMP 表上的 TABLE ACCESS FULL 操作 (ID=3) 启动了一次。但是，ID5 和ID4 操作启动了 14 次；对每个 EMP 行执行一次。在 ID=2 的步骤中，系统获得了所有的 ENAME、SAL、JOB 和 DNAME。

此时，系统必须过滤出其薪金不在薪金等级表中的薪金范围内的雇员。为此，系统针对来自 ID2 的每一行，使用 FULL TABLE SCAN 操作访问 SALGRADE 表，以便检查雇员的薪金是否不在薪金范围内。在本例中，由于在运行时系统检查每个不同的薪金，而 EMP 表中有 12 个不同的薪金，因此此操作只需执行 12 次。

 

执行计划的解释过程：示例 2

 

SQL> select /*+ USE_NL(d) use_nl(m) */ m.last_name as dept_manager 
  2  ,      d.department_name 
  3  ,      l.street_address 
  4  from   hr.employees m   join 
  5         hr.departments d on (d.manager_id = m.employee_id) 
  6         natural join 
  7         hr.locations l 
  8  where  l.city = 'Seattle'; 

0   SELECT STATEMENT                             
1 0   NESTED LOOPS                                
2 1    NESTED LOOPS                               
3 2     TABLE ACCESS BY INDEX ROWID LOCATIONS     
4 3      INDEX RANGE SCAN           LOC_CITY_IX   
5 2     TABLE ACCESS BY INDEX ROWID DEPARTMENTS 
6 5      INDEX RANGE SCAN           DEPT_LOCATION_IX    
7 1    TABLE ACCESS BY INDEX ROWID  EMPLOYEES     
8 7     INDEX UNIQUE SCAN           EMP_EMP_ID_PK

 

 

 

此查询检索所在部门位于西雅图并有经理的雇员的姓名、地址和所在部门的名称。

出于格式方面的原因，此解释计划将 ID 置于第一个列，将 PID 置于第二列。位置通过缩进反映出来。此执行计划显示存在两个嵌套循环联接操作。

按照前一示例中的步骤执行：

1.从顶部开始。ID=0

2.在行源中一直下移，直至找到一个生成数据且不使用任何数据的行源。在本例中，ID0、ID1、ID2 和 ID3 使用数据。ID=4 的操作是第一个不使用任何数据的行源。这便是起始行源。首先执行 ID=4 的操作。索引范围扫描生成 ROWID，ROWID 用于在 ID=3 的操作中查找 LOCATIONS 表。

3.查看此行源的同级行源。接下来执行这些行源。与 ID=3 的操作处于同一级别的同级行源是 ID=5 的操作。ID=5 的节点有一个 ID=6 的子节点，后者先于前者执行。这将再执行一个生成 ROWID 的索引范围扫描，以便在 ID=5 的操作中查找 DEPARTMENTS 表。

4.执行子操作后，接着执行父操作。接下来执行 ID=2 处的 NESTED LOOPS 联接，以便将底层数据联接起来。

 

  1. 完成此父行源及其子行源后，在树中向上退一级，查看父行源的同级行源和父行源。按前述方式执行。在此计划中，与 ID=2 的操作处于同一级别的同级行源是 ID=7 的操作。此同级行源有一个 ID=8 的子行源，后者先于前者执行。索引范围扫描生成 ROWID，ROWID 用于在 ID=7 的操作中查找 EMPLOYEES 表。
  2. 在计划中不断上移，直至用完所有行源为止。最后使用 ID1 处的 NESTED LOOPS 将这些数据联接起来，ID1 将结果传回 ID0。
  3. 执行顺序是： 4 – 3 – 6 – 5 – 2 – 8 – 7 – 1 – 0

此计划的完整说明如下：

首先，将 LOCATIONS 用作驱动表，使用 CITY 列上的索引，执行内部嵌套循环。这样操作是因为您仅搜索设在西雅图的部门。

使用 LOCATION_ID 联接列上的索引，将此结果与 DEPARTMENTS 表联接；第一个联接操作的结果是第二个嵌套循环联接的驱动行源。

第二个联接探测 EMPLOYEES 表的 EMPLOYEE_ID 列上的索引。系统可以做到这一点，因为它从第一个联接了解到设在西雅图的部门的所有经理的下属雇员 ID。注意，由于基于主键，所以这是一个唯一扫描。

最后访问 EMPLOYEES 表，来检索姓氏。

 

执行计划的解释过程：示例 3

select /*+ ORDERED USE_HASH(b) SWAP_JOIN_INPUTS(c) */ max(a.i) 
from t1 a, t2 b, t3 c 
where a.i = b.i and a.i = c.i; 

0   SELECT STATEMENT 
1    SORT AGGREGATE 
2 1   HASH JOIN 
3 2    TABLE ACCESS FULL T3 
4 2    HASH JOIN 
5 4     TABLE ACCESS FULL T1 
6 4     TABLE ACCESS FULL T2

请观察本幻灯片中的执行计划。试着找出计划的执行顺序，并推断联接顺序，即系统联接表的顺序。和之前一样，ID 在第一列，PID 在第二列。位置通过缩进反映出来。要在 10053 事件跟踪文件中查找计划，识别执行计划的联接顺序是十分重要的。

此计划的解释过程如下：

  • 系统首先将 T3 表（操作 ID=3）以散列形式加载到内存中。
  • 然后将 T1 表（操作 ID=5）以散列形式加载到内存中。
  • 接下来开始扫描 T2 表（操作 ID=6）。
  • 系统从 T2 选取一行，并探测 T1 (T1.i=T2.i)。
  • 如果此行保留下来，系统将探测 T3 (T1.i=T3.i)。
  • 如果此行保留下来，系统将其发送到下一操作。
  • 系统输出上一结果集中的最大值。

即执行顺序为： 3 – 5 – 6 – 4 – 2 – 1

联接顺序为：T1 – T2 – T3

 

可使用 Oracle Enterprise Manager 了解执行计划，一个重要原因是 Oracle Enterprise Manager 显示了“Order”列。

注：使用了一个特殊提示来确保在计划中首先执行 T3。

 

 

理解更为复杂的执行计划

1

左侧的计划来自幻灯片中对数据字典的查询。此计划十分冗长，很难用前面介绍的方法解释它，也很难找到第一个操作。

始终可以将计划折叠起来，以便于理解。右侧说明了这一点，从中可以看到同一计划折叠后的效果。如幻灯片中所示，在使用 Oracle Enterprise Manager 图形界面时，这易如反掌。可以清楚地看出，此计划是对两个分支执行 UNION ALL。利用掌握的数据字典知识，您认识到这两个分支对应于字典管理的表空间和本地管理的表空间。基于您对数据库的了解，您知道不存在字典管理的表空间。因此，如果存在问题，它必然在第二个分支上。为了确认，您必须查看每个行源的计划信息和执行统计信息，找出计划中占用大部分资源的部分。然后，您只需要展开要调查的分支（在这上面花费时间）。要使用此方法，您必须查看执行统计信息，这些信息通常可在 V$SQL_PLAN_STATISTICS 或根据跟踪文件生成的 tkprof 报表中找到。例如，tkprof 会累积每个父操作自身执行时间与其所有子操作执行总时间之和。

 

 

复查执行计划

  • 将过滤性最强的表作为驱动表。
  • 请检查以下事项：

–驱动表具有最强的过滤器

–返回给下一步骤的行数最少

–就返回的行数而言，相应的联接方法是适合的

–正确使用视图

–预料之外的笛卡尔积

–高效地访问表

 

在联机事务处理 (OLTP) 环境中优化 SQL 语句时，目标是将过滤性最强的表作为驱动表。这意味着，传递给下一步骤的行数较少。如果下一步骤执行联接，这意味着联接的行数较少。检查访问路径是否最佳。在检查优化程序执行计划时，请检查以下事项：

  • 在计划中，驱动表具有最强的过滤器。
  • 每个步骤的联接顺序都可保证返回给下一步的行数最少（即，联接顺序应使系统转到尚未使用的最强过滤器）。
  • 就返回的行数而言，相应的联接方法是适合的例如，返回的行很多时，使用索引的嵌套循环联接可能不是最佳方法。
  • 高效地使用视图。查看 SELECT 列表，确定访问的视图是否必需。
  • 是否存在预料之外的笛卡尔积（即使对于小表，也是如此）。
  • 高效地访问每个表：考虑 SQL 语句中的谓词和表的行数。查找可疑活动，例如对行数很多的表执行全表扫描（在 WHERE 子句中有谓词）。而对于小表，或根据返回的行数利用更好的联接方法（例如 hash_join）时，全表扫描也许更有效。

如果这些条件中的任何一个都不是最佳的，请考虑调整 SQL 语句或表上的索引。

 

不局限于执行计划

  • 仅凭执行计划不足以判断一个计划的优劣。
  • 可能需要进行附加的测试和优化：

–SQL 优化指导

–SQL 访问指导

–SQL 性能分析器

–SQL 监视

–跟踪

 

仅凭执行计划不足以判断语句是优化得当还是性能不佳。例如，EXPLAIN PLAN 输出显示某条语句使用了索引，但这并不一定意味着此语句运行效率高。有时，索引非常低效。

最好使用 EXPLAIN PLAN 来确定访问计划，然后通过测试来证明计划是否最佳。评估计划时，应该检查语句实际的资源占用情况。

本课程的其余部分将阐述实现此目标的各种方法。

 

相关课程可以参考 【视频教学：性能优化】Maclean Liu的Oracle性能优化讲座第一回《真正读懂Oracle SQL执行计划》 https://www.askmac.cn/archives/read-sql-execution-plan.html

 

Posted

in

by

mac

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *