查询执行计划 -- 生成和显示执行计划

最新推荐文章于 2023-05-02 09:07:31 发布

cowboy90

最新推荐文章于 2023-05-02 09:07:31 发布

阅读量963

点赞数

分类专栏： ORACLE_SQL Tuning 文章标签： oracle 12C 性能调优生成和显示执行计划

本文链接：https://blog.csdn.net/cowboy90/article/details/96103235

版权

ORACLE_SQL Tuning 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

参考文档：《Oracle® Database SQL Tuning Guide》

1，执行计划简介

Oracle数据库用于执行语句的步骤组合为执行计划。这些步骤要么从数据库中物理地检索数据行，要么为执行用户准备数据行。执行计划包含了SQL访问表时的访问路径，以及使用适当的联接方法对表进行排序（联接顺序）。

2，执行计划的生成和显示

EXPLAIN PLAN语句显示优化器为SELECT、UPDATE、INSERT、DELETE语句选择的执行计划。

2.1，执行计划的说明

一条语句的执行计划是数据库为运行语句而执行的操作序列。行源树是执行计划的核心。该树显示了以下信息:

- 语句引用的表的顺序
- 语句中涉及到的每个表的访问方法
- 语句中受联接操作影响的表的联接方法
- 数据操作，如筛选、排序或聚合

除了行源树外，plan表还包含以下信息:

- 优化，例如每个操作的成本和基数
- 分区，例如访问的分区集
- 并行执行，例如连接输入的分布方法

可以使用EXPLAIN PLAN结果来确定优化器是否选择了特定的执行计划，例如嵌套循环连接。这些结果可以帮助理解优化器的决策，比如为什么优化器选择嵌套循环连接而不是散列连接。

2.2，执行计划改变的原因

执行计划可以随着底层优化器输入的更改而更改。EXPLAIN PLAN显示的是在解释语句时数据库将如何运行SQL语句。由于执行环境和explain plan环境的不同，此计划可能与SQL语句实际使用的执行计划不同。

注：为了避免执行计划更改可能导致的SQL性能退化，可以考虑使用SQL计划管理。

2.2.1，schema不同

造成schema不同的原因主要有以下几种：

- 执行和解析计划发生在不同的数据库上。
- 解析语句的用户与运行语句的用户不同。
两个用户可能指向同一个数据库中的不同对象，从而导致不同的执行计划。
- 两个操作之间的schema更改(通常是索引的更改)。

2.2.2，costs不同

即使schema相同，优化器也可以在成本不同时选择不同的执行计划。影响成本的因素包括:

- 数据量和统计信息
- 绑定变量类型和值
- 初始化参数在全局或会话级别的设置

2.3，数据行的丢弃

通过执行计划可以看到数据行丢弃的现象。数据库在如下情况下会丢弃数据行：

- 全扫描
- 非选择性范围扫描
- 后期谓词过滤
- 连接顺序错误
- 后期过滤操作

在示例6-1所示的计划中，最后一步是一个非选择性的范围扫描，执行了76563次，访问了11432983行数据，丢弃了99%的数据行，只保留76563行。为什么会出现只需要76563行数据，却访问了11432983行数据的情况呢？

Example 6-1    Looking for Thrown-Away Rows in an Explain Plan
Rows      Execution Plan
--------  ----------------------------------------------------
      12  SORT AGGREGATE
       2   SORT GROUP BY
   76563    NESTED LOOPS
   76575     NESTED LOOPS
      19      TABLE ACCESS FULL CN_PAYRUNS_ALL
   76570      TABLE ACCESS BY INDEX ROWID CN_POSTING_DETAILS_ALL
   76570       INDEX RANGE SCAN (object id 178321)
   76563     TABLE ACCESS BY INDEX ROWID CN_PAYMENT_WORKSHEETS_ALL
11432983      INDEX RANGE SCAN (object id 186024)

2.4，使用EXPLAIN PLAN评估执行计划

仅通过执行计划无法判断SQL语句执行性能的优劣。比如，一条SQL的执行计划显示它走了索引，但这并不意味着这条SQL就是高效的。有时候SQL走索引反而低效，为了确认走索引是否高效，可以做如下检查：

- 正在使用的索引列
- 它们的选择性(访问表的数据量与总量的比例)

最好使用EXPLAIN PLAN来确定访问计划，然后通过测试证明它是最优的计划。在评估计划时，检查语句的实际资源消耗。

2.5，使用V$SQL_PLAN视图来评估执行计划

除了运行EXPLAIN PLAN命令并显示计划之外，还可以通过查询V$SQL_PLAN视图来显示计划。V$SQL_PLAN包含存储在共享SQL区域中的所有语句的执行计划。它的定义类似于PLAN_TABLE。

与EXPLAIN PLAN相比，V$SQL_PLAN的优势在于不需要知道用于执行特定语句的编译环境。而EXPLAIN PLAN就要设置一个相同的环境来获得相同的计划。

V$SQL_PLAN_STATISTICS视图为计划中的每个操作提供了实际的执行统计信息，比如输出行数和运行时间。所有统计数据(输出行数除外)都是累积的。例如，join操作的统计信息就包括其两个输入的统计信息。在STATISTICS_LEVEL初始化参数设置为ALL时编译的游标可以使用V$SQL_PLAN_STATISTICS中的统计信息。

V$SQL_PLAN_STATISTICS_ALL视图支持同时比较优化器提供的行数和运行时间的估计值。这个视图结合了所有游标的V$SQL_PLAN和V$SQL_PLAN_STATISTICS信息。

2.6，EXPLAIN PLAN限制

当数据绑定变量使用了类型隐式转换时，数据库就不支持该SQL语句执行EXPLAIN PLAN。对于一般的绑定变量，EXPLAIN PLAN输出可能并不代表真正的执行计划。

TKPROF不能通过SQL语句文本来确定绑定变量的类型，因此它会先假定绑定变量类型为VARCHAR，当VARCHAR这个假设有问题时它才报错。可以通过在SQL语句中使用适当的类型转换来避免这种限制。

2.7，创建 PLAN_TABLE

PLAN_TABLE是全局临时表SYS.PLAN_TABLE$的同义词，这个同义词是数据库自动创建的。这个表可以存所有用户的EXPLAIN PLAN语句的输出。而且EXPLAIN PLAN语句输出的执行计划信息默认是写入表PLAN_TABLE的。

虽然PLAN_TABLE是数据库自动设置给所有用户使用的，但也是可以通过数据库脚本$ORACLE_HOME/rdbms/admin/catplan.sql(文件名和路径可能因操作系统不同而有差异)来手动创建全局临时表和同义词PLAN_TABLE的。例如，开起一个 SQL*Plus进程，使用 SYSDBA权限登录，执行如下语句即可：

@$ORACLE_HOME/rdbms/admin/catplan.sql

Oracle建议在升级数据库版本之后重建本地PLAN_TABLE表，因为列可能会发生更改。如果列发生更改，就可能导致在使用PLAN_TABLE表时脚本执行失败或TKPROF失败。当然，如果不想使用PLAN_TABLE 这个同义词，可以在执行完catplan.sql脚本后创建一个新的同义词：

CREATE OR REPLACE PUBLIC SYNONYM my_plan_table for sys.plan_table$

3，用 EXPLAIN PLAN语句生成执行计划信息

可以通过EXPLAIN PLAN语句查看优化器为SQL语句选择的执行计划。

3.1，解析SQL语句：基本步骤

EXPLAIN PLAN会将为SQL语句解析的计划信息存放在PLAN_TABLE表中，如果没有这个表，可以执行脚本catplan.sql来生成。执行EXPLAIN PLAN语句需要的权限：

- 需要具有将计划信息数据行插入到输出表的权限。
- 需要有执行要查看执行计划信息的SQL的权限，而且要有SQL中所级联的表和视图的权限，如果级联的视图中还有级联，仍然需要有那些级联对象的权限。

另外，查询存计划信息的表时也需要有相应的查询权限。

解析SQL语句的步骤：

1）启动SQL*Plus或SQL Developer，并以具有必要权限的用户身份登录到数据库。

2）在SQL语句前加上EXPLAIN PLAN FOR来解析SQL语句，如下为解析查询employees表的SQL的例子：

EXPLAIN PLAN FOR
  SELECT e.last_name, d.department_name, e.salary
  FROM   employees e, departments d
  WHERE  salary < 3000
  AND    e.department_id = d.department_id
  ORDER BY salary DESC;

3）执行完EXPLAIN PLAN语句后，使用数据库自带的脚本或者包查看存入计划表中的最新的执行计划信息。
如下为使用DBMS_XPLAN.DISPLAY函数查看：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));

4）检查具体的计划信息，如下为hash join的执行计划信息：

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));
Plan hash value: 3556827125
-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |     4 |   124 |     5  (20)| 00:00:01 |
|   1 |  SORT ORDER BY      |             |     4 |   124 |     5  (20)| 00:00:01 |
|*  2 |   HASH JOIN         |             |     4 |   124 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| EMPLOYEES   |     4 |    60 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / D@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   3 - filter("SALARY"<3000)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) INTERNAL_FUNCTION("E"."SALARY")[22],"E"."LAST_NAME"[VARCHAR2,25], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
   2 - (#keys=1) "E"."LAST_NAME"[VARCHAR2,25], "SALARY"[NUMBER,22],"D"."DEPARTMENT_NAME"[VARCHAR2,30], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
   3 - "E"."LAST_NAME"[VARCHAR2,25], "SALARY"[NUMBER,22],"E"."DEPARTMENT_ID"[NUMBER,22]
   4 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
Note
-----
   - this is an adaptive plan

EXPLAIN PLAN输出的计划信息的执行顺序是，向右缩进越多就越先执行，缩进最多的最先执行，当有多行计划信息有同样的缩进时，先执行上面的一行计划。

另外，由于数据库的配置不同，可能导致优化器选择的执行计划不一样。

3.2，执行EXPLAIN PLAN时使用Statement ID

表PLAN_TABLE的第一个字段就是statement_id，当连续执行EXPLAIN PLAN生成了多条SQL的执行计划时，可以通过设置statement_id的初始值为一个标识字符串，这样便于查看不同SQL的执行计划。在SET STATEMENT_ID前需要确认PLAN_TABLE表中不存在相同的statement_id标识串，可以先删除PLAN_TABLE表中该标识串的行。如下为设置SQL的标识串为'st1'：

EXPLAIN PLAN
  SET STATEMENT_ID = 'st1' FOR
  SELECT last_name FROM employees;

3.3，将执行EXPLAIN PLAN输出的计划信息指定输出到特定的表

使用'INTO'关键字来将信息指定输出到特定表。如下为将计划信息输出到表my_plan_table：

EXPLAIN PLAN
  INTO my_plan_table FOR
  SELECT last_name FROM employees;

指定特定表的同时，设置STATEMENT_ID标识串：

EXPLAIN PLAN
   SET STATEMENT_ID = 'st1'
   INTO my_plan_table FOR
   SELECT last_name FROM employees;

4，展示PLAN_TABLE表中的信息

解析完计划后，使用Oracle数据库提供的SQL脚本或PL/SQL包显示计划表中最近的一条SQL的计划信息：

- utlxpls.sql
此脚本显示串行处理的计划信息。示例6-4是使用utlxpls.sql脚本时计划表输出的一个示例。

- utlxplp.sql
此脚本显示包含并行执行列的计划信息。

- DBMS_XPLAN.DISPLAY表函数
此函数可以根据人为指定的选项来显示特定的信息：
1)当不使用PLAN_TABLE存放计划信息时，需要指定存放表名
2）执行EXPLAIN PLAN时，如果设置了statement_id，需要指定标识串
3)格式选项需要指定详细的级别：BASIC,SERIAL,TYPICAL,ALL

使用DBMS_XPLAN来显示PLAN_TABLE中的计划信息：

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

SELECT PLAN_TABLE_OUTPUT 
  FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));

4.1，展示执行计划：示例

执行 EXPLAIN PLAN生成SQL执行计划：

示例6-3：
EXPLAIN PLAN FOR
  SELECT e.employee_id, j.job_title, e.salary, d.department_name
  FROM   employees e, jobs j, departments d
  WHERE  e.employee_id < 103
  AND    e.job_id = j.job_id 
  AND    e.department_id = d.department_id;

如下输出是优化器选择的示例6-3中SQL的执行计划信息：

示例6-4：
-----------------------------------------------------------------------------------
| Id  | Operation                     |  Name        | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     3 |   189 |    10  (10)|
|   1 |  NESTED LOOPS                 |              |     3 |   189 |    10  (10)|
|   2 |   NESTED LOOPS                |              |     3 |   141 |     7  (15)|
|*  3 |    TABLE ACCESS FULL          | EMPLOYEES    |     3 |    60 |     4  (25)|
|   4 |    TABLE ACCESS BY INDEX ROWID| JOBS         |    19 |   513 |     2  (50)|
|*  5 |     INDEX UNIQUE SCAN         | JOB_ID_PK    |     1 |       |            |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPARTMENTS  |    27 |   432 |     2  (50)|
|*  7 |    INDEX UNIQUE SCAN          | DEPT_ID_PK   |     1 |       |            |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("E"."EMPLOYEE_ID"<103)
   5 - access("E"."JOB_ID"="J"."JOB_ID")
   7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"
------------------------------------------------------------------------------------------------
| Id | Operation                       | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                |               |     3 |   189 |     8  (13)| 00:00:01 |
|  1 |  NESTED LOOPS                   |               |       |       |            |          |
|  2 |   NESTED LOOPS                  |               |     3 |   189 |     8  (13)| 00:00:01 |
|  3 |    MERGE JOIN                   |               |     3 |   141 |     5  (20)| 00:00:01 |
|  4 |     TABLE ACCESS BY INDEX ROWID | JOBS          |    19 |   513 |     2   (0)| 00:00:01 |
|  5 |      INDEX FULL SCAN            | JOB_ID_PK     |    19 |       |     1   (0)| 00:00:01 |
|* 6 |     SORT JOIN                   |               |     3 |    60 |     3  (34)| 00:00:01 |
|  7 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     3 |    60 |     2   (0)| 00:00:01 |
|* 8 |       INDEX RANGE SCAN          | EMP_EMP_ID_PK |     3 |       |     1   (0)| 00:00:01 |
|* 9 |    INDEX UNIQUE SCAN            | DEPT_ID_PK    |     1 |       |     0   (0)| 00:00:01 |
| 10 |   TABLE ACCESS BY INDEX ROWID   | DEPARTMENTS   |     1 |    16 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("E"."JOB_ID"="J"."JOB_ID")
       filter("E"."JOB_ID"="J"."JOB_ID")
   8 - access("E"."EMPLOYEE_ID"<103)
   9 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

4.2，自定义 PLAN_TABLE表的信息输出

如果指定了STATEMENT_ID语句标识符，则可以编写自己的脚本来查询PLAN_TABLE