查询执行计划所需的权限

这里的执行计划是指游标中的实际执行计划。

1
-- 例如对于HR用户，需要赋予以下权限：
2
grant SELECT_CATALOG_ROLE to HR
3
grant SELECT ANY DICTIONARY to HR

执行计划简介

执行计划就是Oracle基于成本（Cost）、算法和统计信息，最终得到资源消耗最低的SQL执行步骤的组合。其中，成本的值是一个估算值，包括访问路径、关联方式、I/O、CPU和内存等。

执行计划操作本身无法区分经过良好调整的语句和执行欠佳的语句。

该计划由一系列步骤组成。 每个步骤要么从数据库中物理检索数据行，要么为发出语句的用户准备它们。 以下计划显示了员工表和部门表的联接：

24
1
SQL_ID  g9xaqjktdhbcd, child number 0
2
-------------------------------------
3
SELECT employee_id, last_name, first_name, department_name from
4
employees e, departments d WHERE e.department_id = d.department_id and
5
last_name like 'T%' ORDER BY last_name
6

7
Plan hash value: 1219589317
8

9
----------------------------------------------------------------------------------------
10
| Id | Operation                    | Name        |Rows | Bytes |Cost (%CPU)| Time     |
11
----------------------------------------------------------------------------------------
12
|  0 | SELECT STATEMENT             |             |     |       |    5 (100)|          |
13
|  1 |  NESTED LOOPS                |             |   5 |   190 |    5   (0)| 00:00:01 |
14
|  2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |   5 |   110 |    2   (0)| 00:00:01 |
15
|* 3 |    INDEX RANGE SCAN          | EMP_NAME_IX |   5 |       |    1   (0)| 00:00:01 |
16
|* 4 |   TABLE ACCESS FULL          | DEPARTMENTS |   1 |    16 |    1   (0)| 00:00:01 |
17
----------------------------------------------------------------------------------------
18

19
Predicate Information (identified by operation id):
20
---------------------------------------------------
21

22
   3 - access("LAST_NAME" LIKE 'T%')
23
       filter("LAST_NAME" LIKE 'T%')
24
   4 - filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

row source tree（行源树）是执行计划的核心。 该树显示以下信息：

• 语句引用的表的join order(连接顺序)

  在上述计划中，员工表是外部行源，部门表是内部行源。

• 语句中提到的每个表的access path(访问路径)

  在上述计划中，优化器选择使用索引扫描访问员工表，使用完整扫描访问部门表。

• 语句中受连接操作影响的表的join method(连接方法)

  在前面的计划中，优化器选择嵌套循环连接。

• 数据操作，例如过滤、排序或聚合

  在前面的计划中，优化器筛选以 T 开头的姓氏,并与 Department_id 匹配。

除了行源树之外，计划表还包含以下信息：

• 优化，例如每个操作的成本和基数

• 分区，例如访问的分区集合

• 并行执行，例如join输入的分配方式

About PLAN_TABLE

PLAN_TABLE 是默认示例输出表，EXPLAIN PLAN 语句将在其中插入用于描述执行计划的行。

Oracle 数据库自动在 SYS 模式中,创建全局临时表 PLAN_TABLE$，并创建 PLAN_TABLE 作为同义词。 PLAN_TABLE 的所有必要权限均授予 PUBLIC。 因此，每个会话都会在其临时表空间中获得自己的 PLAN_TABLE 私有副本。

您可以使用 SQL 脚本 catplan.sql 手动创建全局临时表和 PLAN_TABLE 同义词。 该脚本的名称和位置取决于您的操作系统。 在 UNIX 和 Linux 上，该脚本位于 $ORACLE_HOME/rdbms/admin 目录中。 例如，启动 SQL*Plus 会话，使用 SYSDBA 权限连接，然后运行脚本，如下所示：

1
1
@$ORACLE_HOME/rdbms/admin/catplan.sql

示例输出表 PLAN_TABLE 的定义,可在分发介质上的 SQL 脚本中找到。 您的输出表必须具有与此表相同的列名称和数据类型。 该脚本的通用名称是 utlxplan.sql。 确切的名称和位置取决于您的操作系统。

方法1：EXPLAIN PLAN

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

EXPLAIN PLAN 的输出显示，在解析 SQL 语句时，数据库将如何运行该 SQL 语句。 由于执行环境和解释计划环境的差异，解释计划可能与语句执行期间使用的实际计划不同。

当发出 EXPLAIN PLAN 语句时，优化器选择一个执行计划，然后将描述执行计划每个步骤的行，插入到指定的计划表PLAN TABLE中。 您还可以发出 EXPLAIN PLAN 语句，作为 SQL 跟踪工具的一部分。

EXPLAIN PLAN 语句是 DML 语句，而不是 DDL 语句。 因此，Oracle 数据库不会隐式提交 EXPLAIN PLAN 语句所做的更改。

8
1
优点 ：
2
1. 无须真正执行 SQL，快捷方便。
3

4
缺点：
5
1 ．没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）;
6
2 . 无法判断处理了多少行；
7
3 . 无法判断表被访问了多少次。
8
确实啊，这毕竟都没有真正执行又如何得知真实运行产生的统计信息。

语法格式

1
1
explain plan [set STATEMENT_ID='testplan'] for {select..|insert..|update..}

25
1
-- EXPLAIN PLAN FOR语句用于将获取到的执行计划存储到plan_table中。
2
SQL> explain plan for select e.empno,e.job,d.dname from scott.emp e,scott.dept d
3
  2  where e.deptno=d.deptno
4
  3  and d.dname = 'ACCOUNTING';
5
Explained.
6

7
-- 使用dbms_xplan.display读取plan_table中最新的内容
8
SQL> select * from table(dbms_xplan.display(NULL,NULL,'ADVANCED'));
9
PLAN_TABLE_OUTPUT
10
---------------------------------------------------------------------------
11
Plan hash value: 615168685
12
---------------------------------------------------------------------------
13
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
14
---------------------------------------------------------------------------
15
|   0 | SELECT STATEMENT   |      | 4 |   216 | 6   (0)| 00:00:01 |
16
|*  1 |  HASH JOIN     |      | 4 |   216 | 6   (0)| 00:00:01 |
17
|*  2 |   TABLE ACCESS FULL| DEPT | 1 |    22 | 3   (0)| 00:00:01 |
18
|   3 |   TABLE ACCESS FULL| EMP  |    12 |   384 | 3   (0)| 00:00:01 |
19
---------------------------------------------------------------------------
20

21
-- 执行完上述SQL语句后，执行计划将暂时存储在PLAN_TABLE中。
22
-- 或者使用utlxpls.sql，执行 utlxpls.sql 脚本会显示您指定的 SELECT 语句的执行计划。
23
-- 要查看执行计划的结果，执行以下脚本：
24
@utlxpls.sql
25
-- 注意：utlxpls.sql 可以在 $ORACLE_HOME/rdbms/admin 目录中找到。

除了可以使用dbms_xplan.display读取plan_table中最新的内容，还可以使用以下内置脚本：

• utlxpls.sql

  该脚本显示串行处理的计划表输出

• utlxplp.sql

  该脚本显示计划表输出，包括并行执行列。

DBMS_XPLAN.DISPLAY table function

此函数接受用于显示计划表输出的选项。 您可以指定：

• 计划表名称（如果您使用的表与 PLAN_TABLE 不同）

• 语句 ID（如果您已使用 EXPLAIN PLAN 设置了语句 ID）

• 确定详细程度的格式选项：BASIC、SERIAL、TYPICAL 和 ALL

使用 DBMS_XPLAN 显示 PLAN_TABLE 输出的示例包括：

12
1
-- 形式1
2
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
3

4
-- 形式2指定格式
5
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));
6

7
-- 形式3
8
-- Using EXPLAIN PLAN with the STATEMENT ID Clause
9
EXPLAIN PLAN
10
  SET STATEMENT_ID = 'st1' FOR
11
  SELECT last_name FROM employees;
12
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));

建立plan_table表

1
1
?/rdbms/admin/utlxplan.sql

手动查询plan_table内容

8
1
-- 生成
2
explain plan set STATEMENT_ID = 'testplan' for select * from dual;
3

4
-- 手动查询
5
select lpad('',5*(level-1))||operation operation,options,object_name,cost,position
6
from plan_table
7
    start with id=0 and STATEMENT_ID = 'testplan'
8
    connect by prior id=parent_id;

在 EXPLAIN PLAN 中指定语句 ID：示例

对于多个语句，您可以指定语句标识符并使用它来标识您的特定执行计划。

在使用 SET STATEMENT ID 之前，请删除该语句 ID 的任何现有行。 在以下示例中，st1 被指定为语句标识符。

Example 6-1 Using EXPLAIN PLAN with the STATEMENT ID Clause

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

删除指定的执行计划数据

1
1
delete from plan_table where STATEMENT_ID = 'st1';

为 EXPLAIN PLAN 输出指定不同位置：示例

EXPLAIN PLAN 的 INTO 子句指定一个不同的表来存储输出。

如果您不想使用名称 PLAN_TABLE，请在运行 catplan.sql 脚本后创建一个新的同义词。 例如：

1
1
CREATE OR REPLACE PUBLIC SYNONYM my_plan_table for plan_table$

以下语句将输出定向到 my_plan_table：

3
1
EXPLAIN PLAN
2
  INTO my_plan_table FOR
3
  SELECT last_name FROM employees;

使用 INTO 子句时可以指定语句 ID，如以下语句所示：

4
1
EXPLAIN PLAN
2
   SET STATEMENT_ID = 'st1'
3
   INTO my_plan_table FOR
4
   SELECT last_name FROM employees;

方法2：SET AUTOTRACE

SQL*Plus 中的 AUTOTRACE 命令生成执行计划和有关查询性能的统计信息。 该命令提供磁盘读取和内存读取等统计信息。 请参阅 SQL*Plus 用户指南和参考。

• 以sys连接

• 运行$ORACLE_HOME/sqlplus/plustrace.sql脚本

• grant plustrace tu public对所有用户授权

• set autotrace on将自动跟踪sql的执行计划以及统计信息

31
1
SQL> set autot 
2
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
3
SQL> set autotrace on explain -- 只显示优化器执行路径报告
4
SQL> set autotrace on statistics  -- 只显示执行统计信息
5
SQL> set autotrace traceonly -- 只会显示SQL执行结果的数量，不显示执行结果的内容，适用于刷屏的SQL，还会显示执行计划和资源消耗。
6
SQL> set autotrace on -- 打开后，所有执行过的 SQL 在执行结果后，会显示该条 SQL 的执行计划和资源消耗统计信息。
7
SQL> set autotrace traceonly explain -- 返回的内容里只有该条 SQL 的执行计划。
8
SQL> set autotrace traceonly statistics -- 只会显示 SQL 的结果数量和资源的消耗量。
9

10
SQL> select e.empno,e.job,d.dname from scott.emp e,scott.dept d
11
  2  where e.deptno=d.deptno;
12
12 rows selected.
13
Execution Plan
14
----------------------------------------------------------
15
Plan hash value: 615168685
16
---------------------------------------------------------------------------
17
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
18
---------------------------------------------------------------------------
19
|   0 | SELECT STATEMENT   |      |    12 |   648 | 6   (0)| 00:00:01 |
20
|*  1 |  HASH JOIN     |      |    12 |   648 | 6   (0)| 00:00:01 |
21
|   2 |   TABLE ACCESS FULL| DEPT | 4 |    88 | 3   (0)| 00:00:01 |
22
|   3 |   TABLE ACCESS FULL| EMP  |    12 |   384 | 3   (0)| 00:00:01 |
23
---------------------------------------------------------------------------
24

25
-- SET AUTOTRACE ON
26
-- 优点。
27
1. 可以输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况），
28
2. 虽然必须要等语句执行完毕后才可以输出执行计划 ， 但是可以有 traceonly 开关来控制返回结果不打屏输出。
29
-- 缺陷 
30
1. 必须要等到语句真正执行完毕后，才可以出结果，
31
2. 无法看到表被访问了多少次。

注意：默认情况只有sys用户有权限

• 以sys用户连接

• 运行$ORACLE_HOME/sqlplus/plustrace.sql

• grant plustrace to public,所有用户有效

方法3：statistics_level=all

58
1
-- 步骤 1: alter session set statistics_level=all ;
2
-- 步骤 2 ：在此处执行你的 SQL
3
-- 步骤 3: select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
4

5
alter session set statistics_level=all;
6
select * from emp where empno=7788;
7
     EMPNO ENAME      JOB              MGR HIREDATE                  SAL       COMM     DEPTNO
8
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
9
      7788 SCOTT      ANALYST         7566 19-APR-87                3000                    20
10

11
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
12

13
PLAN_TABLE_OUTPUT
14
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
15
SQL_ID  2cv6qqj01b9wu, child number 0
16
-------------------------------------
17
select * from emp where empno=7788
18

19
Plan hash value: 2949544139
20

21
---------------------------------------------------------------------------------------------------------
22
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
23
---------------------------------------------------------------------------------------------------------
24
|   0 | SELECT STATEMENT            |        |      1 |        |      1 |00:00:00.01 |       2 |      2 |
25
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |      1 |00:00:00.01 |       2 |      2 |
26
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |      1 |      1 |      1 |00:00:00.01 |       1 |      1 |
27
---------------------------------------------------------------------------------------------------------
28

29
Predicate Information (identified by operation id):
30
---------------------------------------------------
31

32
   2 - access("EMPNO"=7788)
33

34
/* 
35
1. 如果你用 /*+ gather_plan_statistics */ 的方法，可以省略步骤 1 ，直接执行步骤 2 , 3 。
36
2 . 关键字解读，
37
Starts 为该 SQL 执行的次数。
38
E-Rows 为执行计划预计的行数。
39
A-Rows 为实际、返回的行数。A-Rows 和 E -Rows 做比较，就可以确定哪一步执行计划出了问题。
40
A-Time 为每一步实际执行的时间(HH:MM:SS.FF)，根据这一行可以知道该 SQL 耗时在哪个地方。
41
Buffers 为每一步实际执行的逻辑读或致性读。Reads 为物理读。
42
OMem ：当前操作完成所有内存工作区（Work Aera）操作所总共使用私有内存（PGA）中工作区的大小，
43
这个数据是由优化器统计数据以及前一次执行的性能数据估算得出的。
44
lMem ：当工作区大小无法满足操作所需的大小时 ， 需要将部分数据写入临时磁盘空间中（如果仅需要写入一次就可以
45
完成操作 ， 就称一次通过，One-Pass；否则为多次通过,Multi-Pass ）。该列数据为语旬最后一次执行中，单次写磁盘所需要的内存大小 ， 这个是由优化器统计数据以及前一次执行的性能数据估算得出的。User -Mem ：语旬最后一次执行中，当前操作所使用的内存工作区大小 ， 括号里面为（发生磁盘交换的次数 ， 1 次即为One-Pass，大于 1 次则为 Multi-Pass，如果没有使用滋盘，则显示 OPTIMAL)
46
OMem、 lMem 为执行所需的内存评估值，OMem 为最优执行模式所需内存的评估值， lMem 为 one-pass 模式所需内
47
存的评估值。0/1/M 为最优／one-pass/multipass 执行的次数。Used-Mem 为消耗的内存.*//*
48

49
--优点： 
50
1. 可以清晰地从 ST阻TS 得出表被访问多少次；
51
2. 可以清晰地从 E-ROWS 和 A-ROWS 中得到预测的行数和真实的行数，从而可以准确判断 Oracle 评估是否准确；
52
3. 虽然没有专门的输出运行时的相关统计信息，但是执行计划中的 BUFFERS 就是真实的逻辑读的数值。
53

54
--缺陷 
55
1. 必须要等到语句真正执行完毕后，才可以出结果，
56
2. 无法控制记录打屏输出，不像 autotrace 有 traceonly 可以控制不将结果打屏输出；
57
3. 看不出递归调用的次数，看不出物理读的数值(不过逻辑读才是重点).
58
*/

方法4：DBMS_XPLAN.DISPLAY_CURSOR

• 在执行每个查询之前，您需要查询 DBMS_XPLAN.DISPLAY_PLAN 以查看默认计划，即优化器在应用其自适应机制之前选择的计划。

• 执行每个查询后，您想要查询 DBMS_XPLAN.DISPLAY_CURSOR 以查看最终计划和自适应查询计划。

DBMS_XPLAN.DISPLAY

使用dbms_xplan.display读取plan_table中最新的内容

此函数接受用于显示计划表输出的选项。 您可以指定：

• 指定plan_table的名称(如果你没有使用默认的PLAN_TABLE的话)

• 语句ID(如果你在EXPLAIN PLAY设置了语句ID)

• 指定输出的格式选项：basic,sereial,typical,all,在某些情况下还有 ADAPTIVE

  默认情况下，DISPLAY 函数使用 TYPICAL 格式设置。 在这种情况下，计划中最相关的信息是：

  operation id, name and option, rows, bytes and optimizer cost。 Pruning(修剪)、parallel (并行)和predicate information(谓词信息)仅在适用时显示。

32
1
-- 形式1
2
select * from table(dbms_xplan.display(NULL,NULL,'ADVANCED'));
3

4
-- 形式2
5
select * from table(dbms_xplan.display)
6

7
-- 形式3
8
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
9

10
-- 形式4
11
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));
12
SELECT * FROM table(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'));
13
-- 此示例的重要特征是我使用 `FORMAT=>'ALL +OUTLINE'`。 
14
-- 你们中的一些人可能遇到过未记录的选项，`FORMAT=>'ADVANCED'`。 我在这里没有使用它，因为它的输出内容在不同版本之间可能会有所不同，但没有根本原因不能使用它。 
15
-- 记录了“`ALL`”格式并简要提到了“`OUTLINE`”； 其基本内容在不同版本之间不太可能发生变化。
16

17
-- 形式5
18
-- Using EXPLAIN PLAN with the STATEMENT ID Clause
19
EXPLAIN PLAN
20
  SET STATEMENT_ID = 'st1' FOR   -- 指定语句ID
21
  SELECT last_name FROM employees;
22
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE(自定义的plan_table)', 'st1','TYPICAL'));
23

24
-- 以下示例说明使用语句 ID ex_plan1 查询员工，然后查询 PLAN_TABLE：
25
EXPLAIN PLAN 
26
  SET statement_id = 'ex_plan1' FOR
27
  SELECT phone_number 
28
  FROM   employees
29
  WHERE  phone_number LIKE '650%';
30

31
SELECT PLAN_TABLE_OUTPUT 
32
  FROM TABLE(DBMS_XPLAN.DISPLAY(statement_id => 'ex_plan1'));

DBMS_XPLAN.DISPLAY_CURSOR

用于获取内存中shared_pool游标缓存。

查询内存中的执行计划(也叫游标执行计划)，即已经执行过的SQL语句的执行计划。

15
1
/*
2
1. 还有一个方法， select * from table(dbms_xplan.display_awr('&sql_id'); （这是从 awr 性能视图里获取）
3
2. 如果有多个执行计划，则可以用类似方法查出 ：
4
select * from table(dbms_xplan.display_cursor(’cyzznbykb509s’,0));
5
select * from table(dbms_xplan.display_cursor('cyzznbykb509s’,1));
6

7
---优点，
8
l．知道 sql_id 立即可得到执行计划，它和 explain plan for 一样无须执行，
9
2. 可以得到真实的执行计划。
10
---缺陷 
11
1. 没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）
12
2. 无法判断处理了多少行；
13
3. 无法判断表被访问了多少次。
14
4. 如果执行过后时间较长，执行计划存在被替出的可能。
15
*/

29
1
-- 查询内存中的执行计划(也叫游标执行计划)
2
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
3
SELECT *
4
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(
5
            SQL_ID=>'the_SQL_ID', 
6
            CHILD_NUMBER=>the_child_number, 
7
            FORMAT=>'ALL +OUTLINE'));
8

9
select * from table(dbms_xplan.DISPLAY_CURSOR('sql_id','null',ADVANCED ALLSTATS LAST'));
10
--sql_id
11
--child_number，null表示显示所有子游标
12
--format： ALLSTATS LAST 、 ADVANCED ALLSTATS LAST
13

14
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR('2wnhpatm9a24s',null,'ADVANCED ALLSTATS LAST'));
15
PLAN_TABLE_OUTPUT
16
---------------------------------------------------------------------------
17
SQL_ID  2wnhpatm9a24s, child number 0
18
-------------------------------------
19
select e.empno,e.job,d.dname from scott.emp e,scott.dept d where
20
e.deptno=d.deptno
21
Plan hash value: 615168685
22
--------------------------------------------------------------------------
23
| Id  | Operation      | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
24
---------------------------------------------------------------------------
25
|   0 | SELECT STATEMENT   |      |    |       |     6 (100)|      |       |       |          |
26
|*  1 |  HASH JOIN     |      | 12 |   648 |     6   (0)| 00:00:01 |  1695K|  1695K| 1021K (0)|
27
|   2 |   TABLE ACCESS FULL| DEPT |  4 |    88 |     3   (0)| 00:00:01 |       |       |          |
28
|   3 |   TABLE ACCESS FULL| EMP  | 12 |   384 |     3   (0)| 00:00:01 |       |       |          |
29
---------------------------------------------------------------------------

DBMS_XPLAN.DISPLAY_AWR

用于获取AWR基表WRH$_SQL_PLAN。

7
1
select * from table(dbms_xplan.display_awr('sql_id',plan_hash_value,db_id,'format'))
2
-- sql_id
3
-- NULL表示显示所有
4
-- 默认获取本地v$database中的值
5
-- ALLSTATS LAST 、 ADVANCED ALLSTATS LAST
6

7
select * from table(dbms_xplan.display_awr('as3uq6ggb3gx6',null,null,'ADVANCED'));

方法5：AWR SQL report

7
1
-- 步骤 1: @?/rdbms/admin/awrsqrpt.sql
2
-- 步骤 2: 选择你要的断点（ begin snap 和 end·snap)
3
-- 步骤 3: 输入你的 sql_id
4

5
select max(snap_id) from dba_hist_snapshot;
6
Exec dbms_workload_repository.create_snapshot();  -- 手动生成快照
7
@?/rdbms/admin/awrsqrpt.sql

AWR SQL report 使用的脚本为 $ORACLE_HOME/rdbms/admin/awrsqrpt.sql、awrsqrpi.sql。通常使用的脚本为 awrsqrpt.sql，该脚本可生成 SQL 在 AWR 信息内保存的执行计划等信息。而 awrsqrpi.sql 可以配合 awr 信息的导出与导入的功能，在被导入的库中生成相关的 AWR 报告。导入与导出的脚本同样在如上的路径里，为 awrextr、awrload，本次不进行演示。

报告会生成在当前目录中。

与 AWR 报告采集相同，因为性能数据的记录是累计的值，所以两个 snap_id 间如果重启过数据库会报错。如果在选择的 snap_id 间没有该条 SQL 的信息同样会报错。

通常会用于问题诊断时对问题 SQL 的总体概况进行了解，因为可以查看 SQL 的多个执行计划及各执行计划的执行统计信息。

优势：可以在报告中查看执行计划的执行统计信息；

劣势：需要生成报告；缺少执行计划的谓词信息；需要权限较高。

方法6：10046 trace

13
1
-- 步骤 1: 激活10046事件
2
-- 方法1
3
alter session set events '10046 trace name context forever,level 12'; （开启跟踪）
4
-- 方法2：oradebug setmypid/oradebug setospid SPID;
5
oradebug  event 10046 trace name context forever, level 12;
6

7
步骤 2 执行 SQL 语旬
8

9
步骤 3: alter session set events '10046 trace name context off'; （关闭跟踪）
10

11
步骤 4 找到跟踪后产生的文件,这个trc文件会记录SQL的执行计划和资源消耗，命名格式“实例名_ora_当前session的spid.trc”。
12

13
步骤 5: tkprof trc 文件 目标文件 sys=no sort=prsela,exeela , fchela （格式化命令）

13
1
/*
2
---优点：
3
1. 可以看出 SQL 语旬对应的等待事件，
4
2. 如果 SQL 语旬中有函数调用，函数中又有 SQL ，将会被列出，无处遁形，
5
3. 可以方便地看出处理的行数，产生的物理逻辑读，
6
4. 可以方便地看出解析时间和执行时间 i
7
5. 可以跟踪整个程序包。
8

9
---缺陷 
10
1. 步骤烦琐，比较麻烦；
11
2. 无法判断表被访问了多少次，
12
3. 执行计划中的谓词部分不能清晰地展现出来。
13
*/

查看10046产生的trc文件名和路径的方法：

1. show parameter USER_DUMP_DEST显示trc文件存储的路径 -> 查找对应当前session的trc文件(若当前是单用户，则是最新产生的文件)。

2. 如果是使用ordebug产生trc文件，可以用oradebug tracefile_name得到trc文件名和路径。

方法7：PLSQL-DEV

按F5

其他方法：sqlhc

和 explain plan for 一样的还可以使用 PL/SQL developer 工具的 F5 键也可查看执行计划，SQL Monitor 工具也可以查看，当然更高级的 sqlhc 工具，这里顺便说说 sqlhc 工具，这个工具收集的信息非常全面，值得大家尝试。

上传 sqlhc 文件，公众号后台回复【sqlhc】获取，输入 T 和 sqlid 即可生成。sqlhc 是 SQL health check的简称，能够收集sql相关的表、索引、统计信息、优化器参数、SQL执行情况、等待事件等信息，可以帮你检查SQL存在的问题并优化 SQL。

12
1
-- 执行方法：sqlplus / as sysdba
2
SQL> @/home/oracle/tmp/sqlhc.sql T 9a4tv1dduu9u4
3

4
-- 或者
5
SQL> @/home/oracle/tmp/sqlhc.sql
6
Parameter 1:
7
Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
8
Enter value for 1: T
9
PL/SQL procedure successfully completed.
10
Parameter 2:
11
SQL_ID of the SQL to be analyzed (required)
12
Enter value for 2: 9a4tv1dduu9u4       <----输入 sql_id 等待 5 分钟左右

有可能更长或者更短(根据 AWR 保存周期、字典表大小不同相差较大，一般系统应该在 5 分钟以内能够完成)，对数据库没有影响。执行过程有 log，也有屏显。执行过程会 insert 数据到 plan_table 表，执行结束会 rollback。

结束后生成的文件名以 sqlhc 开头，依次是日期、时间、sql_id。类似这样：sqlhc_20211125_1810_9a4tv1dduu9u4.zip

其中 4 个 html 文件和 log.zip 是通常存在的。

10053 trace 文件的生成需要 11.2 版本以上，sql_id 仍在 library cache 内的情况下。

如果 sql_monitor.zip 也包含在 sqlhc 压缩包内，说明你的 SQL 执行时间超过了 5s，或者是并行的 SQL，而且收集 sqlhc 时仍保留在 sql monitor 的内存。sql monitor 对分析 sql 执行计划有很大帮助，如果遇到问题收集 sqlhc 信息及时，就非常有可能收集到 sql monitor 文件。如果一个 sql 执行完后超过半小时没有收集 sqlhc，sql monitor 信息就就非常有可能被刷出内存。

主要分析的的 3 个 html 文件是：

3
1
*_health_check.html
2
*_diagnostics.html
3
*_execution_plan.html

如何选择方法

选择时一般遵循以下规则：

1. 如果 sql 执行很长时间才出结果或返回不了结果，用方法1：explain plan for

2. 跟踪某条 sql 最简单的方法是方法1：explain plan for，其次是方法2：set autotrace on

3. 如果相关查询某个 sql 多个执行计划的情况，只能用方法4：dbms_xplan.display_cursor 或方法6：awrsqrpt.sql

4. 如果 sql 中含有函数，函数中有含有 sql，即存在多层调用，想准确分析只能用方法5：10046 追踪

5. 想法看到真实的执行计划，不能用方法1：explain plan for 和方法 2：set autotrace on

6. 想要获取表被访问的次数，只能用方法 3：statistics_level = all

获取真实消耗资源

执行计划是SQL语句执行前基于当前的统计信息生成的，日中rows、bytes、cost、time等为评估值，为了获取更为准确的实际值，即A_ROWS,A_TIME等，数据库在执行SQL语句时需要做额外的收集。

31
1
-- 会话
2
SQL> alter session set STATISTICS_LEVEL=ALL;
3
Session altered.
4
SQL> select * from scott.dept d,scott.emp e where d.deptno=e.deptno and d.dname='SALES';
5
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null,null,'ADVANCED ALLSTATS LAST'));
6

7
PLAN_TABLE_OUTPUT
8
--------------------------------------------------------------------------------
9
SQL_ID  bcym3bsvz65x7, child number 0
10
-------------------------------------
11
select * from scott.dept d,scott.emp e where d.deptno=e.deptno and
12
d.dname='SALES'
13
Plan hash value: 615168685
14
---------------------------------------------------------------------------
15

16
| Id  | Operation| Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time  | A-Rows |A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
17

18
---------------------------------------------------------------------------
19

20
| 0 | SELECT STATEMENT   |    |  1 |        |       |     6 (100)|
21
    |      6 |00:00:00.02 |  15 |       |       |          |
22

23
|*  1 |  HASH JOIN     |      |  1 |      4 |   468 |     6   (0)| 00:00
24
:01 |      6 |00:00:00.02 |  15 |  1476K|  1476K|  471K (0)|
25
|*  2 |   TABLE ACCESS FULL| DEPT |  1 |      1 |    30 |     3   (0)| 00:00
26
:01 |      1 |00:00:00.01 |   7 |       |       |          |
27
|   3 |   TABLE ACCESS FULL| EMP  |  1 |     12 |  1044 |     3   (0)| 00:00
28
:01 |     12 |00:00:00.01 |   8 |       |       |          |
29
---------------------------------------------------------------------------
30
-- 语句
31
select /*+ gather_plan_statistics */ .......

常用的执行计划查看语句

38
1
-- 查看当前SQL在内存中的最后一次执行计划，命令如下：
2
SELECT RPAD('Inst: ' || v.inst_id, 9) || ' ' ||
3
       RPAD('Child: ' || v.child_number, 11) inst_child,
4
       t.plan_table_output
5
  FROM gv$sql v,
6
       TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all',
7
                                NULL,
8
                                'ADVANCED ALLSTATS LAST -Projection -Outline -Note',
9
                                'inst_id = ' || v.inst_id ||
10
                                ' AND sql_id = ''' || v.sql_id ||
11
                                ''' AND child_number = ' || v.child_number)) t
12
 WHERE v.sql_id = '&SQL_ID'
13
   AND v.loaded_versions > 0;
14

15
-- 查看当前SQL在内存中的所有执行计划，命令如下：
16
SELECT RPAD('Inst: ' || v.inst_id, 9) || ' ' ||
17
       RPAD('Child: ' || v.child_number, 11) inst_child,
18
       t.plan_table_output
19
  FROM gv$sql v,
20
       TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all',
21
                                NULL,
22
                                'ADVANCED ALLSTATS -Projection -Outline -Note',
23
                                'inst_id = ' || v.inst_id ||
24
                                ' AND sql_id = ''' || v.sql_id ||
25
                                ''' AND child_number = ' || v.child_number)) t
26
 WHERE v.sql_id = '&SQL'
27
   AND v.loaded_versions > 0
28
   AND v.executions > 1;
29

30
-- 查看指定SQL的历史执行计划，包括记录在快照点中执行计划，命令如下：
31
SELECT t.plan_table_output
32
  FROM (SELECT DISTINCT sql_id, plan_hash_value, dbid
33
          FROM dba_hist_sql_plan
34
         WHERE sql_id = '&SQL') v,
35
       TABLE(DBMS_XPLAN.DISPLAY_AWR(v.sql_id,
36
                                    v.plan_hash_value,
37
                                    null,
38
                                    'ADVANCED ALLSTATS')) t;

示例

查询预计的执行计划，尚未执行

19
1
-- EXPLAIN PLAN FOR语句用于将获取到的执行计划存储到plan_table中。
2
SQL> explain plan for select e.empno,e.job,d.dname from scott.emp e,scott.dept d
3
  2  where e.deptno=d.deptno
4
  3  and d.dname = 'ACCOUNTING';
5
Explained.
6

7
-- 使用dbms_xplan.display读取plan_table中最新的内容
8
SQL> select * from table(dbms_xplan.display(NULL,NULL,'ADVANCED'));
9
PLAN_TABLE_OUTPUT
10
---------------------------------------------------------------------------
11
Plan hash value: 615168685
12
---------------------------------------------------------------------------
13
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
14
---------------------------------------------------------------------------
15
|   0 | SELECT STATEMENT   |      | 4 |   216 | 6   (0)| 00:00:01 |
16
|*  1 |  HASH JOIN     |      | 4 |   216 | 6   (0)| 00:00:01 |
17
|*  2 |   TABLE ACCESS FULL| DEPT | 1 |    22 | 3   (0)| 00:00:01 |
18
|   3 |   TABLE ACCESS FULL| EMP  |    12 |   384 | 3   (0)| 00:00:01 |
19
---------------------------------------------------------------------------

查询已经执行过的sql语句的执行计划

10
1
select e.ename,r.rname
2
from   employees  e
3
join   roles       r on (r.id = e.role_id)
4
join   departments d on (d.id = e.dept_id)
5
where  e.staffno <= 10
6
and    d.dname in ('Department Name 1','Department Name 2');
7

8
-- 执行以上语句之后，查询其执行计划
9
SELECT *
10
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALL +OUTLINE'));

示例 20-8 文字导致不同的执行计划

11
1
-- 假设您执行以下语句，这些语句使用不同的文字（101、120 和 165）执行三个不同的语句，然后显示每个语句的执行计划：
2
SET LINESIZE 167
3
SET PAGESIZE 0
4
SELECT SUM(salary) FROM hr.employees WHERE employee_id < 101;
5
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
6

7
SELECT SUM(salary) FROM hr.employees WHERE employee_id < 120;
8
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
9

10
SELECT SUM(salary) FROM hr.employees WHERE employee_id < 165;
11
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

Example 20-9 Bind Variables Result in Cursor Reuse

33
1
-- 此示例重写示例 20-8 中执行的查询，以使用绑定变量而不是文字。 将相同的值（101、120 和 165）绑定到绑定变量 :emp_id，然后显示每个值的执行计划：
2
VAR emp_id NUMBER
3

4
EXEC :emp_id := 101;
5
SELECT SUM(salary) FROM hr.employees WHERE employee_id < :emp_id;
6
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
7

8
EXEC :emp_id := 120;
9
SELECT SUM(salary) FROM hr.employees WHERE employee_id < :emp_id;
10
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
11

12
EXEC :emp_id := 165;
13
SELECT SUM(salary) FROM hr.employees WHERE employee_id < :emp_id;
14
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
15

16
-- 执行计划如下
17
SELECT SUM(salary) FROM hr.employees WHERE employee_id < :emp_id
18

19
Plan hash value: 2410354593
20

21
-------------------------------------------------------------------------------------
22
| Id | Operation                            | Name      |Rows|Bytes|Cost (%CPU)|Time|
23
-------------------------------------------------------------------------------------
24
|  0 | SELECT STATEMENT                     |               | |  |2 (100)|          |
25
|  1 |  SORT AGGREGATE                      |               |1|8 |       |          |
26
|  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES     |1|8 |  2 (0)| 00:00:01 |
27
|* 3 |    INDEX RANGE SCAN                  | EMP_EMP_ID_PK |1|  |  1 (0)| 00:00:01 |
28
-------------------------------------------------------------------------------------
29

30
Predicate Information (identified by operation id):
31
---------------------------------------------------
32

33
   3 - access("EMPLOYEE_ID"<:EMP_ID)

查询从游标缓存中获取计划信息：

24
1
SELECT *
2
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(
3
            SQL_ID=>'the_SQL_ID', 
4
            CHILD_NUMBER=>the_child_number, 
5
            FORMAT=>'ALL +OUTLINE'));
6
-- 将需要所需查询的 SQL_ID 和 CHILD_NUMBER。 
7
-- 有很多方法可以做到这一点，但如果您有 DBA 权限，那么您可以在 V$SQL 中搜索该语句：
8
select /* MY_TEST_QUERY */
9
       e.ename,r.rname
10
from   employees  e
11
join   roles       r on (r.id = e.role_id)
12
join   departments d on (d.id = e.dept_id)
13
where  e.staffno <= 10
14
and    d.dname in ('Department Name 1','Department Name 2');
15

16
select sql_id, child_number, sql_text
17
from   v$sql 
18
where  sql_text like '%MY_TEST_QUERY%'
19
and    sql_text not like '%v$sql%';
20

21
/*
22
上面的计划不包含任何运行时信息，因此您将看不到计划的每个部分执行需要多长时间或实际处理了多少行。 
23
例如，“Rows”是一个估计值； 它不会告诉您实际处理了多少行。
24
*/

使用提示来收集运行时信息：

28
1
select /*+ gather_plan_statistics */
2
       e.ename,r.rname
3
from   employees  e
4
join   roles       r on (r.id = e.role_id)
5
join   departments d on (d.id = e.dept_id)
6
where  e.staffno <= 10
7
and    d.dname in ('Department Name 1','Department Name 2');
8

9
SELECT *
10
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ALL +OUTLINE'));
11
/*
12
这将向您显示统计信息，例如实际处理的行数（A-Rows），而不仅仅是估计值（E-Rows）。 
13
它还包括一个名为 Starts 的列，它告诉您每个步骤执行了多少次。 
14
如果您想了解计划，A-Rows、E-Rows 和 Starts 都非常有用。
15
*/
16

17
-- 如果您不想更改查询文本来添加提示，可以设置一个参数：
18
alter session set statistics_level='ALL';
19

20
select e.ename,r.rname
21
from   employees  e
22
join   roles       r on (r.id = e.role_id)
23
join   departments d on (d.id = e.dept_id)
24
where  e.staffno <= 10
25
and    d.dname in ('Department Name 1','Department Name 2');
26

27
SELECT *
28
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ALL +OUTLINE'));

SQL监控报告

DBMS_XPLAN 'ALLSTATS LAST' 不会在执行查询时为您提供运行时统计信息的连续视图，但 SQL Monitor 解决了这个问题。 它需要 Oracle Tuning Pack，因此请务必检查您的数据库版本的许可证用户指南。 该工具对于生成计划和监控 SQL 非常有用，并且可以通过 Performance Hub 中的 Enterprise Manager 来使用。 在我介绍这一点之前，您也可以在命令行上使用它（这一事实经常由于某种原因被错过或忘记）：

26
1
select /*+ MONITOR */
2
       e.ename,r.rname
3
from   employees  e
4
join   roles       r on (r.id = e.role_id)
5
join   departments d on (d.id = e.dept_id)
6
where  e.staffno <= 10
7
and    d.dname in ('Department Name 1','Department Name 2');
8

9
-- Get the SQL ID of the query we just executed
10
select prev_sql_id
11
from   v$session
12
where  sid=userenv('sid')
13
and    username is not null
14
and    prev_hash_value <> 0;
15

16
PREV_SQL_ID
17
-------------
18
an05rsj1up1k5
19

20
set linesize 250 pagesize 0 trims on tab off long 1000000
21
column report format a220
22

23
select
24
   DBMS_SQL_MONITOR.REPORT_SQL_MONITOR
25
        (sql_id=>'an05rsj1up1k5',report_level=>'ALL') report
26
from dual;

SQL_ID 参数是可选的，但我通常会显式设置它，因为系统中可能有多个长时间运行的查询，因此默认报告有时会选取与我正在试验的 SQL 语句不同的 SQL 语句。 数据库自动使长时间运行的查询可供 SQL Monitor 使用，但在本例中我使用了 MONITOR 提示，因为查询非常快并且通常不会显示。 在查询执行时对其进行监视非常有用，因为您可以观察其进度并从中学习。 这就是 SQL Monitor 真正有用的地方，因为您可以在另一个会话中观察查询并查看其统计信息不断更新。 您不一定要等待它完成才能确定查询的哪一部分花费了很长时间，因此有时可以避免等待完成。 请注意，您可以在执行查询时获取“ALL +OUTLINE”计划详细信息 - 只需使用上面的示例 B。 您甚至可以使用命令行生成活动的 HTML 报告！ 这是捕获 SQL 执行计划并稍后以交互方式探索它的好方法。 只需像这样运行报告：

6
1
-- spool output to a file, then…
2
select DBMS_SQL_MONITOR.REPORT_SQL_MONITOR
3
        (sql_id       =>'an05rsj1up1k5',
4
         report_level =>'all', 
5
         type         =>'ACTIVE') report
6
from dual;

注意：浏览器需要 Internet 访问，因为 HTML 报告会下载一些外部资源。

执行计划解析

前置概念

• 伪列-ROWID

  ROWID是一个伪列，是系统自动生成的。每个表都有一个ROWID的伪列，但是表中并不物理存储ROWID列的值。ROWID在改行的生命周期内唯一，即使该行产生行迁移，其ROWID也不会发生改变。

• Recursive SQL

  有时为了执行用户发出的SQL语句，oracle必须执行一些额外的语句，这些额外的语句被称为recursive calls或recursive sql statments。例如当发出一个DDL语句后，oracle总是隐含的发出一些recursive sql来修改数据字典信息。

  当需要的数据字典信息没有在共享内存中时，经常会发生recursive calls，其会将数据字典信息读入内存中。用户无需关心recursive sql的执行情况，在需要时，oracle自动在内部执行。

  此外DML语句也可能引发recursive sql，简单说，我们可以将触发器视为recursive sql

• Row Source and Predicate

  • 行源：用在查询中，由上一操作返回的符合条件的行的集合，既可以是表的全部行的集合，也可以是表的部分行的集合，还可以是为了对2个row source进行连接操作(例如join)后得到的行数据集合。

  • 谓词：一个查询中的WHERE限制条件

• Driving Table

  驱动表：又称为外层表(outer table)。此概念用于嵌套与HASH连接中。如果该row source返回了较多的行数据，则对所有的后续操作有负面影响。注意，虽然翻译为驱动表，但实际上应该是驱动行源（driving row source）更为确切。

  一般来说是应用查询的限制条件后，返回较少行源的表作为驱动表，因此如果一个大表有WHERE条件（例如等值限制），则该大表作为驱动表也是合适的，所以并非只有较小的表可以作为驱动表。正确说法应该是，应用查询的限制条件后，返回较少行源的表作为驱动表。在执行计划中，应该为靠上的那个row source。

• Probed Table

  被探查表：又称为内层表（inner table）。在我们从驱动表得到具体一行的数据后，在该表中寻找符合连接条件的行。所以该表应当为大表（实际上应该为返回较多row source的表），且相应的列上应该有索引。

• 组合索引（concatenated index）

  由多个列构成的索引，例如create index idx_emp on emp(c1,c2,c3,...)，则我们称idx_emp索引为组合索引。在组合索引中有一个重要概念：引导列(leading column)，在前面的例子中，c1列为引导列。当我们进行查询时可使用where c1=?，也可以使用where c1=? and c2=?，这样的限制条件都会使用索引，但是where c2=?就不会使用该索引。所以限制条件中包含引导列时，该限制条件才会使用组合索引。

• 可选择性selectivity

  比较一下列中唯一键的数量和表中的行数，就可以判断该列的可选择性。如果该列的【唯一键的数量/表中的行数】的比值越接近1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也就越高。在可选择性高的列上进行查询时，返回的数据就越少，比较适合使用索引查询。

数据访问路径

• 全表扫描

  通过设置DB_FILE_MULTIBLOCK_READ_COUNT参数设置一次IO能够读取的块数，从而可以有效减少全表扫描时的IO总次数。只有全表扫描情况下才使用多块读操作。

• 通过ROWID的表存取（通过rowid存取表，rowid lookup）

  由于rowid中记录了行存储的位置，所以这是oracle存取单行数据的最快方法

• index scan（索引扫描）

  在索引中，除了存储每个索引的值外，索引还存储具有此值的行所对应的rowid值，索引扫描分为两步：1.扫描索引得到rowid；2.通过rowid读取具体数据。每步都是单独的一次IO，所以如果数据经过限制条件过滤后的总量大于原表总行数的5%-10%，则使用索引扫描效率会下降很多。而如果结果数据能够全部在索引中找到，则可以避免第二步操作，从而加快索引速度。

  根据索引类型与WHERE限制条件的不同，有4种类型的索引扫描：

  • index unique scan(索引唯一扫描)：存在unique或者primary key的情况下，返回单个rowid数据内容

  • index range scan(索引范围扫描)：

    • 在唯一索引上使用了range操作符(>,<,<>,>=,<=,between)

    • 在组合索引上，只使用部分列进行查询；对非唯一索引上的列进行的查询

  • index full scan(索引全扫描)：需要查询的数据从索引中可以全部得到

  • index fast full scan(索引快速扫描)：与index full scan类似，但这种方式下不对结果进行排序。

索引唯一扫描

通过唯一索引查找一个数值经常返回单个ROWID，如果存在unique或primary key约束，oracle经常实现唯一性扫描。

41
1
SYS@orcl> set autotrace on
2
SYS@orcl> select empno,ename from scott.emp where empno=7369;
3

4
     EMPNO ENAME
5
---------- --------------------
6
      7369 SMITH
7

8

9
Execution Plan
10
----------------------------------------------------------
11
Plan hash value: 2949544139
12

13
--------------------------------------------------------------------------------------
14
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
15
--------------------------------------------------------------------------------------
16
|   0 | SELECT STATEMENT            |        |     1 |    10 |     1   (0)| 00:00:01 |
17
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    10 |     1   (0)| 00:00:01 |
18
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
19
--------------------------------------------------------------------------------------
20

21
Predicate Information (identified by operation id):
22
---------------------------------------------------
23

24
   2 - access("EMPNO"=7369)
25

26

27
Statistics
28
----------------------------------------------------------
29
          1  recursive calls
30
          0  db block gets
31
          2  consistent gets
32
          0  physical reads
33
          0  redo size
34
        597  bytes sent via SQL*Net to client
35
        524  bytes received via SQL*Net from client
36
          2  SQL*Net roundtrips to/from client
37
          0  sorts (memory)
38
          0  sorts (disk)
39
          1  rows processed
40

41
SYS@orcl> 

索引范围扫描

使用一个索引存取多行数据，在唯一索引上使用索引范围扫描的典型情况是：在谓词（where限制条件）中使用了范围操作符。

42
1
SYS@orcl> select empno,ename from scott.emp where empno>7876 order by empno;
2

3
     EMPNO ENAME
4
---------- --------------------
5
      7900 JAMES
6
      7902 FORD
7
      7934 MILLER
8

9

10
Execution Plan
11
----------------------------------------------------------
12
Plan hash value: 169057108
13

14
--------------------------------------------------------------------------------------
15
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
16
--------------------------------------------------------------------------------------
17
|   0 | SELECT STATEMENT            |        |     1 |    10 |     2   (0)| 00:00:01 |
18
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    10 |     2   (0)| 00:00:01 |
19
|*  2 |   INDEX RANGE SCAN          | PK_EMP |     1 |       |     1   (0)| 00:00:01 |
20
--------------------------------------------------------------------------------------
21

22
Predicate Information (identified by operation id):
23
---------------------------------------------------
24

25
   2 - access("EMPNO">7876)
26

27

28
Statistics
29
----------------------------------------------------------
30
          1  recursive calls
31
          0  db block gets
32
          4  consistent gets
33
          0  physical reads
34
          0  redo size
35
        672  bytes sent via SQL*Net to client
36
        524  bytes received via SQL*Net from client
37
          2  SQL*Net roundtrips to/from client
38
          0  sorts (memory)
39
          0  sorts (disk)
40
          3  rows processed
41

42
SYS@orcl> 

在非唯一索引上都使用索引范围扫描。使用index range scan的3种情况：

1. 在唯一索引列上使用了范围操作符

2. 在组合索引上，只使用了部分列进行查询，导致查询出多行

3. 对非唯一索引列上进行的任何查询

索引全扫描

与全表扫描对应，也有相应的索引全扫描，而且此时查询出的数据都必须从索引种可以直接得到。

13
1
SQL_ID  94t4a20h8what, child number 0
2
-------------------------------------
3
select department_id, department_name from departments order by department_id
4

5
Plan hash value: 4179022242
6

7
------------------------------------------------------------------------
8
|Id | Operation                 | Name     |Rows|Bytes|Cost(%CPU)|Time |
9
------------------------------------------------------------------------
10
|0| SELECT STATEMENT            |            |   |   |2 (100)|         |
11
|1|  TABLE ACCESS BY INDEX ROWID|DEPARTMENTS |27 |432|2   (0)|00:00:01 |
12
|2|   INDEX FULL SCAN           |DEPT_ID_PK  |27 |   |1   (0)|00:00:01 | -- INDEX FULL SCAN 
13
------------------------------------------------------------------------

索引快速扫描

扫描索引中的所有数据块，但与index full scan不同的是，其不对查询结果进行排序，即数据不是以排序顺序被返回。在这种方法中，可以使用多块读功能，也可以使用并行读取，以便获得最大吞吐量和缩短执行时间。

15
1
SELECT /*+ INDEX_FFS(departments dept_id_pk) */ COUNT(*)
2
FROM   departments;
3

4
SQL_ID  fu0k5nvx7sftm, child number 0
5
-------------------------------------
6
select /*+ index_ffs(departments dept_id_pk) */ count(*) from departments
7

8
Plan hash value: 3940160378
9
--------------------------------------------------------------------------
10
| Id | Operation             | Name       | Rows  |Cost (%CPU)| Time     |
11
--------------------------------------------------------------------------
12
|  0 | SELECT STATEMENT      |            |       |    2 (100)|          |
13
|  1 |  SORT AGGREGATE       |            |     1 |           |          |
14
|  2 |   INDEX FAST FULL SCAN| DEPT_ID_PK |    27 |    2   (0)| 00:00:01 |
15
--------------------------------------------------------------------------

表连接

排序合并连接（sort merge join,SMJ）.

本质上是嵌套循环连接的变体。

1. 首先生成row source1所需的数据，然后对这些数据按照连接操作关联列（例如 A.col3）进行排序

2. 随后生成row source2所需的数据，然后对这些数据按照与sort source1对应的连接操作关联列（例如 B.col4）进行排序

3. 最后两边已排序的行被放在一起执行合并操作，即将2个row source按照连接条件连接起来。

• 对于非等值连接，这种连接方式的效率是比较高的

• 如果在关联的列上都有索引，效果更好

• 对于将两个较大的row source做连接，该连接方法比NL连接要好一些

• 但是如果sort merge返回的row source过大，则又会导致使用过多的rowid在表中查询数据时，数据库性能下降，因为产生过多的IO

嵌套循环连接（nested loops,NL）

此连接方法有驱动表（外部表）的概念，其实，该连接过程就是一个2层嵌套循环，因此外层循环的次数越少越好，这也是为什么将小表或返回较少row source的表作为驱动表（用于外层循环）的理论依据。但是该理论只是一般指导原则，因为遵循这个理论并不能总保证使该语句产生的IO次数最少。有时不遵循该理论反而会获得更好的效率。如果使用这种连接方法，那么决定使用哪个表作为驱动表就很重要。

• 如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一索引，或有高选择性非唯一索引时，该连接方法可以得到较好的效率。

• nested loops拥有其他连接方法所没有的一个优点：可以先返回已经连接的行，而不必等待所有的连接操作处理完成才返回数据，这可以实现快速的响应时间。

哈希连接（hash join,HJ)

哈希连接在oracle7.3以后引入，从理论上来说比NL和SMJ都更高效，而且只用在CBO优化器中。

较小的row source被用来构建hash table与bitmap，第二个row source被用来被hansed，并与第一个row source生成的hash table进行匹配，以便进行进一步的连接。bitmap被用来作为一种比较快的查找方法，来检查在hash table中是否有匹配的行。特别的，当hash table比较大而不能全部放在内存中时，这种查找方法更为有用。这种连接方法也有NL连接中所谓的驱动表的概念，被构建为hash table与bitmap的表作为驱动表，当被构建的hash table和bitmap可以被容纳在内存中时，这种连接方法效率极高。

• 此连接方法在oracle7才引入的，理论比较先进，一般来说其效率好于SMJ和NL，但这种连接方法只能用在CBO优化器中，并且需要设置合适的hash_area_size参数才能取得较好性能。

• 在两个较大的row source之间连接时，会取得相对较好的效率，在一个row source较小时则效率更高

• 只能在等值连接中使用。

笛卡尔积连接

两个row source做连接，但是它们之间没有关联条件时，就会在两个row source中做笛卡尔乘积，这通常应该是误操作（忘记写连接条件）。

笛卡尔积是一个表的每一行一次与另一个表中的所有行匹配。在特殊情况下可以使用笛卡尔积连接，比如星形连接。

hints提示

不要乱用hints，因为oracle有时基于CBO的计划看似不是最优的计划，但在CBO模式下会被oracle认为有可能就是最优的执行计划。

开发者在没有得到充分的SQL执行计划和相关统计信息时，请勿使用hints

优化器提示

• Rule：让oracle为查询应用基于规则的优化模式，在怀疑CBO优化模式时，可使用rule提示，其将忽略表和索引的统计信息

  1
  1
  /*+ rule */
  

• All rows：基于成本的优化方法。目的是提供最佳的吞吐量和最小的资源消耗。倾向于全表扫描，不适用于OLTP系统；依赖于表和索引的统计信息。

  1
  1
  /*+ all_rows*/
  

• First_rows：基于成本的优化方法。目的是提供最快的反映时间。依赖于表和索引的统计信息

  1
  1
  /*+ first_rows*/