讀書筆記-《基於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)。