读书笔记-《基于Oracle的SQL优化》-第二章-1

第二章：Oracle里的执行计划

2.1 什么是执行计划

Oracle用来执行目标SQL语句的这些步骤的组合就被称为执行计划。

执行计划可以分为如下三个部分：

1、目标SQL的正文、SQL ID和其执行计划所对应的的PLAN HASH VALUE。

2、执行计划的主体部分。

可以看到Oracle在执行目标SQL时所用的内部执行步骤，这些步骤的执行顺序，所对应的的谓词信息、列信息，优化器评估出来执行这些步骤后返回结果集的Cardinality、成本等内容。

执行计划行前*字符指执行步骤有对应的驱动或者过滤查询条件，这个星号对应的具体的驱动或过滤查询条件可以从执行计划的“Predicate Information(identified y operation id)”中找到。实际上，这部分内饿哦那个就是上述执行步骤所对应的谓词信息。access表示驱动查询条件。

3、执行计划的额外补充信息。

是否使用动态采样(dynamic sampling)

是否使用Cardinality Feedback(Oracle 11g中引入的修正执行计划中返回结果集的Cardinality的一种技术手段)

是否使用SQL Profile(Oracle 10g中引入的调整、稳定执行计划的一种方法)。

2.2 如何查看执行计划

(1)、explain plan命令

按F5，PL/SQL Developer就调用explain plan命令，F5只是explain plan命令上的一层封装而已。

语法：

explain plan for + 目标SQL

select * from table(dbms_xplan.display)

执行explain plan命令，则Oracle就将解析目标SQL所产生的执行计划的具体执行步骤写入PLAN_TABLE$，随后执行的select * from table(dbms_xplan.display)只是从PLAN_TABLE$中将这些具体执行步骤以格式化的方式显示出来。PLAN_TABLE$是一个ON COMMIT PRESERVE ROWS的GLOBAL TEMPORARY TABLE，所以这里Oracle可以做到各个session只能看到自己执行的SQL所产生的执行计划，并且各个session往PLAN_TABLE$写入执行计划的过程互不干扰。

SQL>  select dbms_metadata.get_ddl('TABLE', 'PLAN_TABLE$', 'SYS') from dual;

  CREATE GLOBAL TEMPORARY TABLE "SYS"."PLAN_TABLE$"

   (    "STATEMENT_ID" VARCHAR2(30),

        "PLAN_ID" NUMBER,

        "TIMESTAMP" DATE,

        "REMARKS" VARCHAR2(4000),

        "OPERATION" VARCHAR2(30),

        "OPTIONS" VARCHAR2(255),

        "OBJECT_NODE" VARCHAR2(128),

        "OBJECT_OWNER" VARCHAR2(30),

        "OBJECT_NAME" VARCHAR2(30),

        "OBJECT_ALIAS" VARCHAR2(65),

        "OBJECT_INSTANCE" NUMBER(*,0),

        "OBJECT_TYPE" VARCHAR2(30),

        "OPTIMIZER" VARCHAR2(255),

        "SEARCH_COLUMNS" NUMBER,

        "ID" NUMBER(*,0),

        "PARENT_ID" NUMBER(*,0),

        "DEPTH" NUMBER(*,0),

        "POSITION" NUMBER(*,0),

        "COST" NUMBER(*,0),

        "CARDINALITY" NUMBER(*,0),

        "BYTES" NUMBER(*,0),

        "OTHER_TAG" VARCHAR2(255),

        "PARTITION_START" VARCHAR2(255),

        "PARTITION_STOP" VARCHAR2(255),

        "PARTITION_ID" NUMBER(*,0),

        "OTHER" LONG,

        "OTHER_XML" CLOB,

        "DISTRIBUTION" VARCHAR2(30),

        "CPU_COST" NUMBER(*,0),

        "IO_COST" NUMBER(*,0),

        "TEMP_SPACE" NUMBER(*,0),

        "ACCESS_PREDICATES" VARCHAR2(4000),

        "FILTER_PREDICATES" VARCHAR2(4000),

        "PROJECTION" VARCHAR2(4000),

        "TIME" NUMBER(*,0),

        "QBLOCK_NAME" VARCHAR2(30)

   ) ON COMMIT PRESERVE ROWS

Oracle 10g及其以上版本，explain plan命令在执行后确实将解析目标SQL所产生的执行计划的具体步骤写入了PLAN_TABLE$，随后执行的select * from table(dbms_xplan.display)只是从PLAN_TABLE$中将具体执行步骤以格式化的方式显示出来。

SQL> select count(*) from sys.plan_table$;

  COUNT(*)
----------
         0

SQL> select sid from v$mystat where rownum < 2;
      SID
----------
      1178

SQL> select count(*) from v$mystat;
  COUNT(*)
----------
       604

SQL> select saddr from v$session where sid=1178;  
SADDR
----------------
00000001EEC37778

SQL> select count(*) from v$transaction where ses_addr='00000001EEC37778';
  COUNT(*)
----------
         0

SQL> select count(*) from v$locked_object;
  COUNT(*)
----------
         0

SQL> explain plan for select empno, ename, dname from scott.emp, scott.dept where emp.deptno=dept.deptno;
Explained.

SQL> set long 90000
SQL> set heading off
SQL> set serveroutput on size 1000000

SQL> select operation, options, object_name, id, cardinality, cost from sys.plan_table$;
OPERATION
------------------------------
OPTIONS
--------------------------------------------------------------------------------
OBJECT_NAME                            ID CARDINALITY       COST
------------------------------ ---------- ----------- ----------
SELECT STATEMENT
                                        0          14          6
MERGE JOIN
                                        1          14          6
OPERATION
------------------------------
OPTIONS
--------------------------------------------------------------------------------
OBJECT_NAME                            ID CARDINALITY       COST
------------------------------ ---------- ----------- ----------

TABLE ACCESS
BY INDEX ROWID
DEPT                                    2           4          2

INDEX
FULL SCAN

OPERATION
------------------------------
OPTIONS
--------------------------------------------------------------------------------
OBJECT_NAME                            ID CARDINALITY       COST
------------------------------ ---------- ----------- ----------
PK_DEPT                                 3           4          1
SORT
JOIN
                                        4          14          4
TABLE ACCESS
OPERATION
------------------------------
OPTIONS
--------------------------------------------------------------------------------
OBJECT_NAME                            ID CARDINALITY       COST
------------------------------ ---------- ----------- ----------
FULL
EMP                                     5          14          3
6 rows selected.

SQL> select count(*) from v$transaction where ses_addr='00000001EEC37778';
         1

SQL> select count(*) from v$locked_object;
         1

SQL> select object_id from v$locked_object;

      5003

SQL> select owner, object_name from dba_objects where object_id=5003; 

SYS

PLAN_TABLE$

Oracle 10g：

SQL> select version from v$instance;
10.2.0.4.0

SQL> desc t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                                  NUMBER

SQL> select operation, options, object_name, id, cardinality, cost from sys.plan_table$;
SELECT STATEMENT
                                        0    10013476        483
PX COORDINATOR
                                        1
PX SEND
QC (RANDOM)
:TQ10000                                2    10013476        483
PX BLOCK
ITERATOR
                                        3    10013476        483
TABLE ACCESS
FULL
T                                       4    10013476        483

SQL> select count(*) from v$transaction where ses_addr='00000000A5A07A70';
         1

SQL> select count(*) from v$locked_object;
         0

SQL> select table_name, degree from user_tables;

TABLE_NAME                     DEGREE
------------------------------ --------------------

T                                       8

alter table t parallel(degree 1);

SQL> select table_name, degree from user_tables;

TABLE_NAME                     DEGREE
------------------------------ --------------------

T                                       1

SQL> select operation, options, object_name, id, cardinality, cost from sys.plan_table$;
SELECT STATEMENT
                                        0    10013476       3483
TABLE ACCESS
FULL
T                                       1    10013476       3483

(2)、DBMS_XPLAN包

select * from table(dbms_xplan.display);

select * from table(dbms_xplan.display_cursor(null, null, 'advanced'));

advanced比all显示结果更详细一些。

select * from table(dbms_xplan.display_cursor('sql_id/hash_value', child_cursor_number, 'advanced'));

select * from table(dbms_xplan.display_awr('sql_id'));

(3)、SQLPLUS中的AUTOTRACE开关

SET AUTOTRACE ON(SET AUTOT ON)：显示执行结果，执行计划和资源消耗。

SET AUTOTRACE OFF(SET AUTOT OFF)：只显示执行结果。

SET AUTOTRACE TRACEONLY：不显示执行结果，与ON区别只显示执行结果的数量，不显示执行结果的具体内容，适合于执行结果的具体内容特别长，刷屏的SQL，这时只关心执行计划和资源消耗量。

SET AUTOTRACE TRACEONLY EXPLAIN(SET AUTOT TRACE EXP)：与TRACEONLY区别不显示资源消耗量和执行计划，只显示执行计划。

SET AUTOTTRACE TRACEONLY STATISTICS(SET AUTOT TRACE STAT)：只显示资源消耗量，与TRACEONLY区别不显示执行计划，只显示执行结果的数量和资源消耗量。

(4)、10046事件

和explain plan、dbms_xplan和autotrace开关不同之处：所得到的的执行计划中明确显示了目标SQL实际执行计划中每一个执行步骤所消耗的逻辑读、物理读和花费的时间。

USER_DUMP_DEST生成trace文件。

激活10046事件：

alter session set events '10046 trace name context forever, level 12'

oradebug event 10046 trace name context forever, level 12，推荐这种方法，因为可以在激活10046事件后执行命令oradebug tracefile_name来得到当前session所对应的的trace文件的具体路径和名称。

通常值是12，表示产生的trace文件中除了有目标SQL的执行计划和资源消耗明细之外，还会包含目标SQL所使用的绑定变量的值及该session所经历的的等待事件。

alter session set events '10046 trace name context off'

oradebug event 10046 trace name context off

10046产生的原始trace文件习惯称为裸trace文件(raw trace)，不直观，Oracle提供了tkprof命令，翻译裸文件trace。

oradebug setmypid表示准备对当前session使用oradebug命令。

(5)、10053事件

(6)、AWR报告或Statspack报告。

(7)、一些现成的脚本(display_cursor_9i.sql等)。

2.3 如何得到真实的执行计划

除了10046事件：

explain plan命令

DBMS_XPLAN包

SQLPLUS中的AUTOTRACE开关

这几种方法得到的执行计划都有可能是不准确的。

Oracle中判断得到的执行计划是否准确，就是看目标SQL是否被真正执行，真正执行过的SQL所对应的的执行计划就是准的，反之则有可能不准。注意，这里的判断原则从严格意义上来说并不适用于AUTOTRACE开关，因为所有使用AUTOTRACE开关所显示的执行计划都有可能是不准的，即使对应的目标SQL实际上已经执行过。

(1)、explain plan命令

因为此时SQL并没有被实际执行，可能不准的，尤其SQL包含绑定变量时。默认开启绑定变量窥探的情况下，对含绑定变量的目标SQL使用explain plan得到的执行计划只是一个半成品，Oracle随后对该SQL的绑定变量进行窥探后就得到了这些绑定变量具体的值，此时Oracle很可能会对上述半成品的执行计划做调整，一旦做了调整，使用explain plan命令得到的执行计划就不准了。

(2)、DBMS_XPLAN包

select * from table(dbms_xplan.display);执行计划可能不准，因为它只适用于查看使用explain plan命令得到的目标SQL的执行计划，目标SQL此时还没有被真正执行。

(3)、AUTOTRACE开关

SET AUTOTRACE ON和SET AUTOTRACE TRACEONLY，目标SQL都已被实际执行，所以SET AUTOTRACE ON和SET AUTOTRACE TRACEONLY能看到SQL的实际资源消耗情况。当使用SET AUTOTRACE TRACEONLY EXPLAIN时，如果执行的是SELECT语句，则并没有被实际执行，如果执行的是DML语句，会被Oracle实际执行。

使用SET AUTOTRACE ON、SET AUTOTRACE TRACEONLY和SET AUTOTRACE TRACEONLY EXPLAIN来获得DML语句的执行计划时要小心，因为这些DML语句实际已经被执行过了。

但即使执行过了，但所有使用SET AUTOTRACE命令所得到的的执行计划都有可能是不准的，因为使用SET AUTOTRACE命令所显示的执行计划都是来源于调用explain plan命令。

执行计划还在共享池中：

脚本：display_cursor_9i.sql

存储过程：printsql

得到真实的执行计划和资源消耗情况。

如果执行计划已经被age out出shared pool了，可以执行DBMS_XPLAN.DISPLAY_AWR或者使用AWR SQL报告(awrsqrpt.sql)和Statspack SQL报告来得到其历史执行计划和资源消耗。(sprepsql)

display_cursor_9i.sql适用于Oracle 9i及以后，执行脚本时传入待查勘执行计划的目标SQL的SQL HASH VALUE和CHILD CURSOR NUMBER。

9i中没有DBMS_XPLAN包中的DISPLAY_CURSOR方法，无法使用select * from table(dbms_xplan.display_cursor('sql_id/hash_value', child_cursor_number, 'advanced'));，但执行这个脚本可以得到真实执行计划。

如果执行计划已经被Oracle age out出shared pool，能否得到执行计划取决于：
1、10g以上版本，SQL执行的计划被Oracle捕获并存储到了AWR Repository中，则可以用AWR SQL得到真实执行。

2、9i，除非额外部署Statspack报告，并且采集Statspack报告的level值大于或等于6。

和DBMS_XPLAN.DISPLAY_AWR一样，AWR SQL报告显示的执行计划中也看不执行步骤对应的谓词条件，因为Oracle将执行计划的采样数据从V$SQL_PLAN挪到AWR Repository的基表WRH$_SQL_PLAN中时，没有保留V$SQL_PLAN中记录谓词条件的列ACCESS_PREDICATES和FILTER_PREDICATES的值。
SQL> desc WRH$_SQL_PLAN
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SNAP_ID                                            NUMBER
DBID                                      NOT NULL NUMBER
SQL_ID                                    NOT NULL VARCHAR2(13)
PLAN_HASH_VALUE                           NOT NULL NUMBER
ID                                        NOT NULL NUMBER
OPERATION                                          VARCHAR2(30)
OPTIONS                                            VARCHAR2(30)
OBJECT_NODE                                        VARCHAR2(128)
OBJECT#                                            NUMBER
OBJECT_OWNER                                       VARCHAR2(30)
OBJECT_NAME                                        VARCHAR2(31)
OBJECT_ALIAS                                       VARCHAR2(65)
OBJECT_TYPE                                        VARCHAR2(20)
OPTIMIZER                                          VARCHAR2(20)
PARENT_ID                                          NUMBER
DEPTH                                              NUMBER
POSITION                                           NUMBER
SEARCH_COLUMNS                                     NUMBER
COST                                               NUMBER
CARDINALITY                                        NUMBER
BYTES                                              NUMBER
OTHER_TAG                                          VARCHAR2(35)
PARTITION_START                                    VARCHAR2(5)
PARTITION_STOP                                     VARCHAR2(5)
PARTITION_ID                                       NUMBER
OTHER                                              VARCHAR2(4000)
DISTRIBUTION                                       VARCHAR2(20)
CPU_COST                                           NUMBER
IO_COST                                            NUMBER
TEMP_SPACE                                         NUMBER
ACCESS_PREDICATES                                  VARCHAR2(4000)
FILTER_PREDICATES                                  VARCHAR2(4000)
PROJECTION                                         VARCHAR2(4000)
TIME                                               NUMBER
QBLOCK_NAME                                        VARCHAR2(31)
REMARKS                                            VARCHAR2(4000)
TIMESTAMP                                          DATE
OTHER_XML                                          CLOB

2.4 如何查看执行计划的执行顺序

先从最开头一直连续往右看，直到看到最右边的并列的地方；

对于不并列的，靠右的先执行；

如果见到并列的，就从上往下看，对于并列的部分，靠上的先执行。

select * from table(dbms_xplan.display_cursor);

XPLAN包其实是对DBMS_XPLAN包的封装，使用XPLAN包就可以很清晰地看到执行计划中每一步的执行顺序。执行顺序在XPLAN包的显示结果中以列Order来显示，Order的值从1开始递增，表示执行顺序的先后。

SQL> select /*+ xplan_example1 */ a from t;
         A
----------
         1

SQL> select sql_text, sql_id, child_number from v$sql where sql_text like 'select /*+ xplan_example1 */%';
SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID        CHILD_NUMBER
------------- ------------
select /*+ xplan_example1 */ a from t
1smx7psgknjbd            0

2.5 Oracle里常见的执行计划

2.5.1 与表访问相关的执行计划

1、全表扫描：TABLE ACCESS FULL

2、ROWID扫描：TABLE ACCESS BY USER ROWID或TABLE ACCESS BY INDEX ROWID。取决于访问表时的ROWID来源。ROWID是来源于用户手工指定，或来源于索引。

2.5.2 与B树索引相关的执行计划

包括索引唯一扫描(INDEX UNIQUE SCAN)、索引范围扫描(INDEX RANGE SCAN)、索引全扫描(INDEX FULL SCAN)、索引快速全扫描(INDEX FAST FULL SCAN)和索引跳跃式扫描(INDEX SKIP SCAN)。