一.擷取執行計劃的6種方法(詳細步驟已經在每個例子的開頭注釋部分說明了）：
1. explain plan for擷取；
2. set autotrace on ；
3. statistics_level=all;
4. 通過dbms_xplan.display_cursor輸入sql_id參數直接擷取
5. 10046 trace跟蹤
6. awrsqrpt.sql
--環境構造
--研究Nested Loops Join通路次數前準備工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS PURGE;
CREATE TABLE t1 (
id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000)
)
;
CREATE TABLE t2 (
id NUMBER NOT NULL,
t1_id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000)
)
;
execute dbms_random.seed(0);
INSERT INTO t1
SELECT rownum, rownum, dbms_random.string('a', 50)
FROM dual
CONNECT BY level <= 1000
ORDER BY dbms_random.random;
INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000
ORDER BY dbms_random.random;
COMMIT;
CREATE INDEX t1_n ON t1 (n);
CREATE INDEX t2_t1_id ON t2(t1_id);
下面我們針對每一個執行計劃來做試驗
方法1(explain plan for 的方式。類似PLSQL DEVELOPE裡的F5)
SQL> set linesize 1000
SQL> set pagesize 2000
SQL> explain plan for
2 SELECT *
3 FROM t1, t2
4 WHERE t1.id = t2.t1_id
5 AND t1.n in(18,19);
已解釋。
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan hash value: 3532430033
-------------------------------------------------------------------------------- -----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------- -----------
| 0 | SELECT STATEMENT | | 2 | 8138 | 11 (0)| 00:37:20 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2 | 8138 | 11 (0)| 00:37:20 |
| 3 | INLIST ITERATOR | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 4056 | 5 (0) | 00:16:59 |
|*5 | INDEX RANGE SCAN | T1_N | 4 | | 2 (0) | 00:06:48 |
|*6 | INDEX RANGE SCAN | T2_T1_ID | 1 | | 1 (0) | 00:03:24 |
| 7 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 2041 | 3 (0) | 00:10:11 |
-------------------------------------------------------------------------------- -----------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."N"=18 OR "T1"."N"=19)
6 - access("T1"."ID"="T2"."T1_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
已選擇24行。
優點： 1.無需真正執行，快捷友善
缺陷： 1.沒有輸出運作時的相關統計資訊（産生多少邏輯讀，多少次遞歸調用，多少次實體讀的情況）；
2.無法判斷是處理了多少行；
3.無法判斷表被通路了多少次
方法2(set autotrace on 方式)
步驟1：set autotrace on
步驟2：在此處執行你的SQL即可，後續自然會有結果輸出
其它的輸出方式，我們是為了得到執行計劃，
是以使用 set autotrace traceonly
set autotrace on (得到執行計劃，輸出運作結果）
set autotrace traceonly (得到執行計劃，不輸出運作結果）
set autotrace traceonly explain (得到執行計劃，不輸出運作結果和統計資訊部分，僅展現執行計劃部分）
set autotrace traceonl statistics(不輸出運作結果和執行計劃部分，僅展現統計資訊部分）
SQL> set autotrace traceonly
SQL> SELECT *
2 FROM t1, t2
3 WHERE t1.id = t2.t1_id
4 AND t1.n in(18,19);
執行計劃
----------------------------------------------------------
Plan hash value: 3532430033
--------------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | 2 | 8138 | 11 (0)|
00:37:20 |
| 1 | NESTED LOOPS | | | | |
|
| 2 | NESTED LOOPS | | 2 | 8138 | 11 (0)|
00:37:20 |
| 3 | INLIST ITERATOR | | | | |
|
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 4056 | 5 (0)|
00:16:59 |
|* 5 | INDEX RANGE SCAN | T1_N | 4 | | 2 (0)|
00:06:48 |
|* 6 | INDEX RANGE SCAN | T2_T1_ID | 1 | | 1 (0)|
00:03:24 |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 2041 | 3 (0)|
00:10:11 |
--------------------------------------------------------------------------------
-----------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."N"=18 OR "T1"."N"=19)
6 - access("T1"."ID"="T2"."T1_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
7 recursive calls
0 db block gets
95 consistent gets
8 physical reads
0 redo size
1200 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> set autotrace off
優點：1.可以輸出運作時的相關統計資訊（産生多少邏輯讀，多少次遞歸調用，多少次實體讀的情況）；
2.雖然必須要等語句執行完畢後才可以輸出執行計劃，但是可以有traceonly開關來控制傳回結果不打屏輸出。
缺陷：1.必須要等到語句真正執行完畢後，才可以出結果；
2.無法看到表被通路了多少次。
方法3（statistics level=all的方式)
步驟1：alter session set statistics_level=all ;
步驟2：在此處執行你的SQL
步驟3：select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
注意：
1. 如果你用 /*+ gather_plan_statistics */的方法，可以省略步驟1，直接步驟2,3。
2. 關鍵字解讀（其中OMem、1Mem和User-Mem在後續的課程中會陸續見到）：
Starts為該sql執行的次數。
E-Rows為執行計劃預計的行數。
A-Rows為實際傳回的行數。A-Rows跟E-Rows做比較，就可以确定哪一步執行計劃出了問題。
A-Time為每一步實際執行的時間（HH：MM：SS.FF），根據這一行可以知道該sql耗時在了哪個地方。
Buffers為每一步實際執行的邏輯讀或一緻性讀。
Reads為實體讀。
OMem:目前操作完成所有記憶體工作區(Work Aera)操作所總共使用私有記憶體(PGA)中工作區的大小，
這個資料是由優化器統計資料以及前一次執行的性能資料估算得出的
1Mem:當工作區大小無法滿足操作所需的大小時，需要将部分資料寫入臨時磁盤空間中(如果僅需要寫入一次就可以完成操作，
就稱一次通過，One-Pass;否則為多次通過，Multi_Pass).該列資料為語句最後一次執行中，單次寫磁盤所需要的記憶體
大小，這個由優化器統計資料以及前一次執行的性能資料估算得出的
User-Mem:語句最後一次執行中，目前操作所使用的記憶體工作區大小，括号裡面為(發生磁盤交換的次數,1次即為One-Pass,
大于1次則為Multi_Pass,如果沒有使用磁盤，則顯示OPTIMAL)
OMem、1Mem為執行所需的記憶體評估值，0Mem為最優執行模式所需記憶體的評估值，1Mem為one-pass模式所需記憶體的評估值。
0/1/M 為最優/one-pass/multipass執行的次數。Used-Mem耗的記憶體
SQL> alter session set statistics_level=all ;
會話已更改。
SQL> SELECT *
2 FROM t1, t2
3 WHERE t1.id = t2.t1_id
4 AND t1.n in(18,19);
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID
1a914ws3ggfsn
, child number 1
-------------------------------------
SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19)
Plan hash value: 3532430033
--------------------------------------------------------------------------------
---------------------
| Id | Operation | Name |
Starts
| E-Rows | A-Rows |
A-Time | Buffers |
--------------------------------------------------------------------------------
---------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00
:00:00.01 | 14 |
| 1 | NESTED LOOPS | | 1 | | 2 |00
:00:00.01 | 14 |
| 2 | NESTED LOOPS | | 1 | 2 | 2 |00
:00:00.01 | 12 |
| 3 | INLIST ITERATOR | | 1 | | 2 |00
:00:00.01 | 7 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 2 | 2 |00
:00:00.01 | 7 |
|* 5 | INDEX RANGE SCAN | T1_N | 2 | 4 | 2 |00
:00:00.01 | 5 |
|* 6 | INDEX RANGE SCAN | T2_T1_ID | 2 | 1 | 2 |00
:00:00.01 | 5 |
| 7 | TABLE ACCESS BY INDEX ROWID| T2 | 2 | 1 | 2 |00
:00:00.01 | 2 |
--------------------------------------------------------------------------------
---------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(("T1"."N"=18 OR "T1"."N"=19))
6 - access("T1"."ID"="T2"."T1_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
優點：1.可以清晰的從STARTS得出表被通路多少。
2.可以清晰的從E-ROWS和A-ROWS中得到預測的行數和真實的行數，進而可以準确判斷Oracle評估是否準确。
3.雖然沒有專門的輸出運作時的相關統計資訊，但是執行計劃中的BUFFERS就是真實的邏輯讀的多少
缺陷：1.必須要等到語句真正執行完畢後，才可以出結果。
2.無法控制記錄輸屏打出，不像autotrace有 traceonly 可以控制不将結果打屏輸出。
3.看不出遞歸調用的次數，看不出實體讀的多少（不過邏輯讀才是重點）
方法4(知道sql_id後，直接帶入的方式）
步驟1： select * from table(dbms_xplan.display_cursor('&sq_id')); （該方法是從共享池裡得到）
注：
1. 還有一個方法，select * from table(dbms_xplan.display_awr('&sq_id'));（這是awr性能視圖裡擷取到的）
2. 如果有多執行計劃，可以用類似方法查出
select * from table(dbms_xplan.display_cursor('cyzznbykb509s',0));
select * from table(dbms_xplan.display_cursor('cyzznbykb509s',1));
SQL> select * from table(dbms_xplan.display_cursor('1a914ws3ggfsn'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID 1a914ws3ggfsn, child number 0
-------------------------------------
SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19)
Plan hash value: 3532430033
--------------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | | | 11 (100)|
|
| 1 | NESTED LOOPS | | | | |
|
| 2 | NESTED LOOPS | | 2 | 8138 | 11 (0)|
00:37:20 |
| 3 | INLIST ITERATOR | | | | |
|
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 4056 | 5 (0)|
00:16:59 |
|* 5 | INDEX RANGE SCAN | T1_N | 4 | | 2 (0)|
00:06:48 |
|* 6 | INDEX RANGE SCAN | T2_T1_ID | 1 | | 1 (0)|
00:03:24 |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 2041 | 3 (0)|
00:10:11 |
--------------------------------------------------------------------------------
-----------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(("T1"."N"=18 OR "T1"."N"=19))
6 - access("T1"."ID"="T2"."T1_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
優點：1.知道sql_id立即可得到執行計劃，和explain plan for 一樣無需執行；
2.可以得到真實的執行計劃。（
和explain plan for不同的是，此時的執行計劃為oracle實際執行過的，并非預估值
）
缺陷 1.沒有輸出運作時的相關統計資訊（産生多少邏輯讀，多少次遞歸調用，多少次實體讀的情況）；
2.無法判斷是處理了多少行；
3.無法判斷表被通路了多少次。
方法5(10046TRACE）
步驟1：alter session set events '10046 trace name context forever,level 12'; （開啟跟蹤）
步驟2：執行你的語句
步驟3：alter session set events '10046 trace name context off'; （關閉跟蹤）
步驟4：找到跟蹤後産生的檔案
步驟5：tkprof trc檔案 目标檔案 sys=no sort=prsela,exeela,fchela （格式化指令）
select d.value
|| '/'
|| LOWER (RTRIM(i.INSTANCE, CHR(0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
from (select p.spid
from v$mystat m,v$session s, v$process p
where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,
(select t.INSTANCE
FROM v$thread t,v$parameter v
WHERE v.name='thread'
AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,
(select value
from v$parameter
where name='user_dump_dest') d; --
找到相關的trace語句 10g的方法
SQL> alter session set statistics_level=typical;
會話已更改。
SQL> alter session set events '10046 trace name context forever,level 12';
會話已更改。
SQL> SELECT *
2 FROM t1, t2
3 WHERE t1.id = t2.t1_id
4 AND t1.n in(18,19);
SQL> select d.value
2 || '/'
3 || LOWER (RTRIM(i.INSTANCE, CHR(0)))
4 || '_ora_'
5 || p.spid
6 || '.trc' trace_file_name
7 from (select p.spid
8 from v$mystat m,v$session s, v$process p
9 where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,
10 (select t.INSTANCE
11 FROM v$thread t,v$parameter v
12 WHERE v.name='thread'
13 AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,
14 (select value
15 from v$parameter
16 where name='user_dump_dest') d;
TRACE_FILE_NAME
--------------------------------------------------------------------------------
d:\app\zhoukai\diag\rdbms\orcl\orcl\trace/orcl_ora_9336.trc
C:\Users\zhoukai>tkprof d:\app\zhoukai\diag\rdbms\orcl\orcl\trace/orcl_ora_9336.
trc d:\trace.txt sys=no sort=prsela,exeela,fchela
TKPROF: Release 11.2.0.1.0 - Development on 星期日 4月 27 21:41:09 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.04 0 81 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 14 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.04 0 95 0 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
2 NESTED LOOPS (cr=14 pr=0 pw=0 time=0 us)
2 NESTED LOOPS (cr=12 pr=0 pw=0 time=25 us cost=11 size=8138 card=2)
2 INLIST ITERATOR (cr=7 pr=0 pw=0 time=17 us)
2 TABLE ACCESS BY INDEX ROWID T1 (cr=7 pr=0 pw=0 time=0 us cost=5 size=4056 card=2)
2 INDEX RANGE SCAN T1_N (cr=5 pr=0 pw=0 time=0 us cost=2 size=0 card=4)(object id 85681)
2 INDEX RANGE SCAN T2_T1_ID (cr=5 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 85682)
2 TABLE ACCESS BY INDEX ROWID T2 (cr=2 pr=0 pw=0 time=0 us cost=3 size=2041 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 10.74 10.74
優點：1.可以看出SQL語句對應的等待事件
2.如果SQL語句中有函數調用，SQL中有SQL，将會都被列出，無處遁形。
3.可以友善的看出處理的行數，産生的實體邏輯讀。
4.可以友善的看出解析時間和執行時間。
5.可以跟蹤整個程式包
缺陷: 1.步驟繁瑣，比較麻煩
2.無法判斷表被通路了多少次。
3.執行計劃中的謂詞部分不能清晰的展現出來。
awr就不做測試了
![Oracle編輯SQL語句（多次重複語句輸入時簡化）[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)