查看Oracle執行計畫的幾種常用方法-系列1，oracle執行計畫

查看Oracle執行計畫的幾種常用方法-系列1，oracle執行計畫

SQL的執行計畫實際代表了目標SQL在Oracle資料庫內部的具體執行步驟，作為調優，只有知道了最佳化器選擇的執行計畫是否為當前情形下最優的執行計畫，才能夠知道下一步往什麼方向。

執行計畫的定義：執行目標SQL的所有步驟的組合。

我們首先列出查看執行計畫的一些常用方法：

1. explain plan命令

PL/SQL Developer中通過快速鍵F5就可以查看目標SQL的執行計畫了。但其實按下F5後，實際後台調用的就是explain plan命令，相當於封裝了該命令。

explain plan使用方法：

(1) 執行explain plan for + SQL

(2) 執行select * from table(dbms_xplan.display);

實驗表準備：

SQL> desc test1;
 Name  Null           Type
 ----------------------------------------- -------- ----------------------------
 T1ID    NOT NULL NUMBER(38)
 T1V                       VARCHAR2(10)

SQL> desc test2;
 Name  Null           Type
 ----------------------------------------- -------- ----------------------------
 T2ID    NOT NULL NUMBER(38)
 T2V                       VARCHAR2(10)

實驗：

SQL> set linesize 100

SQL> explain plan for select t1id, t1v, t2id, t2v from test1, test2 where test1.t1id = test2.t2id;

Explained.

第一步使用explain plan對目標SQL進行了explain，第二步使用select * from table(dbms_xplan.display)語句展示出該SQL的執行計畫。

這裡test2作為驅動表，進行了全表掃描，test1作為被驅動表，由於其包含主鍵，所以用的是索引全掃描。左側ID帶*號的第四步操作，表示有謂詞條件，這裡可以看到既使用了主鍵索引(access)，又使用了過濾條件(filter)。

2.DBMS_XPLAN包

(1) select * from table(dbms_xplan.display);--上面以說明。

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

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

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

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

主要用於SQLPLUS中查看剛執行過SQL的執行計畫。首先第三個參數可以選擇'advanced'：

接下來，第三個參數使用'all'：

可以看出'advanced'記錄的資訊要比'all’多，主要就是多一個Outline Data。Outline Data主要是執行SQL時用於固定執行計畫的內部HINT組合，可以將這部分內容摘出來加到目標SQL中以固定其執行計畫。

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

其中第一個參數可以輸入SQL的sql_id或hash value，方法就是如果執行的SQL仍在庫緩衝中，則可以使用V$SQL查詢：

其中，使用@dbsnake大牛的SQL可以知道SQL_ID和HASH_VALUE的一一對應關係：

隱藏問題1：

這裡的可能有點問題，結果並不準確，問題就出在這個SQL中使用的演算法中，在另一篇博文中會仔細說明這個問題。

使用：

SQL> select * from table(dbms_xplan.display_cursor('1p2fk2v00c865', 0, 'advanced'));

或

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

就可以查出對應這條SQL的執行計畫，內容同(2)中的'advanced'，這就不展示了。

注意這還有第二個參數child_cursor_number，指的是子遊標編號，如果未產生新的子遊標，則此處寫的是0。

(2)和(3)的結論相近，區別就是(2)只是針對最近一次執行SQL查看執行計畫，(3)可以針對仍在庫緩衝中的任意一次SQL查看執行計畫。

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

(1)是使用explain plan for +SQL作為前提，(2)和(3)的前提則是SQL的執行計畫還在共用池中，具體講是在庫緩衝中。如果已經被age out交換出共用池，則不能用這兩種方法了。若該SQL的執行計畫被採集到AWR庫中，則可以用(4)來查詢曆史執行計畫。

隱藏問題2：

實驗這部分內容發現使用select * from table(dbms_xplan.display_awr('sql_id'));並沒有結果，@黃瑋老師說有可能是AWR收集的是top的SQL，有可能測試用的SQL不是most intensive SQL，但我是用alter system flush shared_pool後執行的手工採集快照，還是未被AWR抓到，比較奇怪的問題，這個也會在另一篇博文中仔細說明。

未完待續 。。。

To be continued ...

oracle的執行計畫中表的連結方式有幾種，分別適用在什情況下

在日常基於資料庫應用的開發過程中，我們經常需要對多個表或者資料來源進行關聯查詢而得出我們需要的結果集。那麼Oracle到底存在著哪幾種串連方式？最佳化器內部又是怎樣處理這些串連的？哪種串連方式又是適合哪種查詢需求的？只有對這些問題有了清晰的理解後，我們才能針對特定的查詢需求選擇合適的串連方式，開發出健壯的資料庫應用程式。選擇合適的表串連方法對SQL語句啟動並執行效能有著至關重要的影響。下面我們就Oracle常用的一些串連方法及適用情景做一個簡單的介紹。
3.1 嵌套迴圈串連(nested loop)
嵌套迴圈串連的工作方式是這樣的：
1、 Oracle首先選擇一張表作為串連的驅動表，這張表也稱為外部表格（Outer Table）。由驅動表進行驅動串連的表或資料來源稱為內部表（Inner Table）。
2、 提取驅動表中合格記錄，與被驅動表的串連列進行關聯查詢合格記錄。在這個過程中，Oracle首先提取驅動表中合格第一條記錄，再與內部表的串連列進行關聯查詢相應的記錄行。在關聯查詢的過程中，Oracle會持續提取驅動表中其他合格記錄與內部表關聯查詢。這兩個過程是並行進行的，因此嵌套迴圈串連返回前幾條記錄的速度是非常快的。在這裡需要說明的是，由於Oracle最小的IO單位為單個資料區塊，因此在這個過程中Oracle會首先提取驅動表中合格單個資料區塊中的所有行，再與內部表進行關聯串連查詢的，然後提取下一個資料區塊中的記錄持續地迴圈串連下去。當然，如果單行記錄跨越多個資料區塊的話，就是一次單條記錄進行關聯查詢的。
3、 嵌套迴圈串連的過程如下所示：
Nested loop
Outer loop
Inner loop
我們可以看出這裡面存在著兩個迴圈，一個是外部迴圈，提取驅動表中合格每條記錄。另外一個是內部迴圈，根據外迴圈中提取的每條記錄對內部表進行串連查詢相應的記錄。由於這兩個迴圈是嵌套進行的，故此種串連方法稱為嵌套迴圈串連。
嵌套迴圈串連適用於查詢的選擇性強、約束性高並且僅返回小部分記錄的結果集。通常要求驅動表的記錄（合格記錄，通常通過高效的索引訪問）較少，且被驅動表串連列有唯一索引或者選擇性強的非唯一索引時，嵌套迴圈串連的效率是比較高的。
嵌套迴圈串連驅動表的選擇也是串連中需要著重注意的一點，有一個常見的誤區是驅動表要選擇小表，其實這是不對的。假如有兩張表A、B關聯查詢，A表有1000000條記錄，B表有10000條記錄，但是A表過濾出來的記錄只有10條，這時候顯然用A表當做驅動表是比較合適的。因此驅動表是由過濾條件限制返回記錄最少的那張表，而不是根據表的大小來選擇的。
在外串連查詢中，如果走嵌套迴圈串連的話，那麼驅動表必然是沒有符合條件關聯的那張表，也就是後面不加(+)的那張表。這是由於外串連需要提取可能另一張表沒合格記錄，因此驅動表需要是那張我們要返回所有符合條件記錄的表。比如下面這個查詢，
嵌套迴圈串連返回前幾行的記錄是非常快的，這是因為使用了嵌套迴圈後，不需要等到全部迴圈結束再返回結果集，而是不斷地將查詢出來的結果集返回。在這種情況下，終端使用者將會快速地得到返回的首批記錄，且同時等待Oracle內部處理其他記錄並返回。如果查詢的驅動表的記錄數非常多，或者被驅動表的串連列上無索引或索引不是高度可選的情況，嵌套迴圈串連的效率是非常低的
-- 刪除原表
drop table t1;

-- 建立測試表
create table t1(
f1 varchar2(10),
f2 varc......餘下全文>>
 
講解ORACLE的執行計畫的幾種方法

　　Oracle RDBMS執行每一條SQL語句，都必須經過Oracle最佳化器的評估。所以，瞭解最佳化器是如何選擇(搜尋)路徑以及索引是如何被使用的，對最佳化SQL語句有很大的協助。Explain可以用來迅速方便地查出對於給定SQL語句中的查詢資料是如何得到的即搜尋路徑(我們通常稱為Access Path)。從而使我們選擇最優的查詢方式達到最大的最佳化效果。
　　1.1 、安 裝
　　要使用EXPLAIN首先要執行相應的指令碼，建立出Explain_plan表。
　　具體指令碼執行如下：
　　$ORACLE_HOME/rdbms/admin/utlxplan.sql (UNIX) 該指令碼後會產生一個表這個程式會建立一個名為plan_table的表。
　　1.2 、使用
　　常規使用文法：
　　explainPLAN[SETSTATEMENT_ID[=]<stringliteral][INTO<table_name]
　　FOR<sql_statement　　其中：　　STATEMENT_ID：是一個唯一的字串，把當前執行計畫與儲存在同一PLAN中的其它執行計畫區別開來。
　　TABLE_NAME：是plan表名，它結構如前所示，你可以任意設定這個名稱。
　　SQL_STATEMENT：是真正的SQL語句。
　　比如：　　SQLexplainplansetstatement_id='T_TEST'forselect*fromt_test;　　SQL　　Explained
　　執行下面語句可以查詢到執行計畫