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

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

續上篇：http://blog.csdn.net/bisal/article/details/38919181

3. AUTOTRACE開關

SQLPLUS中開啟AUTOTRACE開關可以得到SQL的執行計畫。

從提示可以看到AUTOTRACE有幾個選項：

OFF/ON/TRACEONLY/EXPLAIN/STATISTICS。

實驗：

1. 執行SET AUTOTRACE ON：

2. 執行SET AUTOTRACE TRACEONLY：

3. 執行SET AUTOTRACE TRACEONLY EXPLAIN：

4. 執行SET AUTOTRACE TRACEONLY STATISTICS：

AUTOTRACE開關小結：

OFF：預設選項，當前session執行SQL只會顯示結果。

SET AUTOTRACE OFF(SET AUTOT OFF)

ON：除顯示執行SQL結果外，還會顯示對應的執行計畫和資源消耗。

SET AUTOTRACE ON(SET AUTOT ON)

TRACEONLY：只會顯示SQL執行結果的數量，不顯示執行結果的內容，適用於刷屏的SQL，還會顯示執行計畫和資源消耗。

SET AUTOTRACE TRACEONLY(SET AUTOT TRACE)

EXPLAIN：只顯示SQL執行計畫，不顯示SQL的資源消耗和執行結果。

SET AUTOTRACE TRACEONLY EXPLAIN(SET AUTOT TRACE EXP)

STATISTICS：只顯示SQL的執行結果數量和資源消耗，不顯示執行計畫。

SET AUTOTRACE TRACEONLY STATISTICS(SET AUTOT TRACE STAT)

未完待續。。。

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 sql的執行計畫怎查看

一、通過PL/SQL Dev工具
1、直接File->New->Explain Plan Window，在視窗中執行sql可以查看計劃結果。其中，Cost表示cpu的消耗，單位為n%，Cardinality表示執行的行數，等價Rows。
2、先執行 EXPLAIN PLAN FOR select * from tableA where paraA=1，再 select * from table(DBMS_XPLAN.DISPLAY)便可以看到oracle的執行計畫了，看到的結果和1中的一樣，所以使用工具的時候推薦使用1方法。
注意：PL/SQL Dev工具的Command window中不支援set autotrance on的命令。還有使用工具方法查看計劃看到的資訊不全，有些時候我們需要sqlplus的支援。

二、通過sqlplus
1．最簡單的辦法
Sql> set autotrace on
Sql> select * from dual;
　　執行完語句後，會顯示explain plan 與 統計資訊。
　　這個語句的優點就是它的缺點，這樣在用該方法查看執行時間較長的sql語句時，需要等待該語句執行成功後，才返回執行計畫，使最佳化的周期大大增長。如果不想執行語句而只是想得到執行計畫可以採用：
Sql> set autotrace traceonly
這樣，就只會列出執行計畫，而不會真正的執行語句，大大減少了最佳化時間。雖然也列出了統計資訊，但是因為沒有執行語句，所以該統計資訊沒有用處，如果執行該語句時遇到錯誤，解決方案為：
(1）在要分析的使用者下：
Sqlplus > @ ?
dbmsadminutlxplan.sql
(2) 用sys使用者登陸
Sqlplus > @ ?sqlplusadminplustrce.sql
Sqlplus > grant plustrace to user_name;
- - user_name是上面所說的分析使用者

　2．用explain plan命令
(1) sqlplus > explain plan for select * from testdb.myuser
(2) sqlplus > select * from table(dbms_xplan.display);
　　上面這2種方法只能為在本會話中正在啟動並執行語句產生執行計畫，即我們需要已經知道了哪條語句啟動並執行效率很差，我們是有目的只對這條SQL語句去最佳化。其實，在很多情況下，我們只會聽一個客戶抱怨說現在系統運行很慢，而我們不知道是哪個SQL引起的。此時有許多現成的語句可以找出耗費資源比較多的語句，如：
SELECT ADDRESS, substr(SQL_TEXT,1,20) Text, buffer_gets, executions,
buffer_gets/executions AVG FROM v$sqlarea
WHERE executions>0 AND buffer_gets > 100000 ORDER BY 5;
ADDRESS TEXT BUFFER_GETS EXECUTIONS AVG
-------- ---------------------------------------- ----------- ---------- -......餘下全文>>