您好,登錄后才能下訂單哦!
一、如何查看執行計劃
在Oracle數據庫里,我們通常可以使用如下方法(包括但不限于)得到目標SQL的執行計劃:
explain plan 命令
DBMS_XPLAN包
SQLPLUS中的AUTOTRACE開關
10046事件
10053事件
AWR報告或Statspack報告
一些現成的腳本(如 display_cursor_9i.sql等)
前四種方法使用得比較普遍
1.1 explain plan 命令
習慣使用PL/SQL Developer的人都知道,按下快捷鍵F5后就可以顯示目標SQL的執行計劃,實際上,PL/SQL Developer就調用了explain plan命令,快捷鍵F5只不過是在explain plan命令上的一層封裝而已。
explain plan命令的語法是依次執行如下兩條命令:
explain plan for + 目標SQL
select * from table(dbms_xplan.display)
先使用explain plan命令對目標SQL做explain,再使用“select * from table(dbms_xplan.display)”查看上述使用explain plan命令后得到的執行計劃。
explain plan for select empno,ename,dname fromscott.emp,scott.dept where emp.deptno=dept.deptno;
select * from table(dbms_xplan.display);
explain plan 命令到底做了什么事情呢?在Oracle 10g 及其以上的版本里,如果我們對目標SQL執行explain plan 命令,則Oracle就將解析目標SQL所產生的執行計劃的具體執行步驟寫入PLAN_TABLE$,隨后執行“select * from table(dbms_xplan.display)”只是從PLAN_TABLE$中將這些具體執行步驟以格式化的方式顯示出來。PLAN_TABLES$是一個ON COMMIT PRESERVE ROWS的GLOBALTEMPORARY TABLE,所以這里Oracle可以做到各個的Session只能看到自己執行的SQL所產生的執行計劃,并且各個Session往PLAN_TABLE$寫入執行計劃的過程互不干擾。
1.2 DBMS_XPLAN包
使用DBMS_XPLAN包中的方法是在Oracle數據庫中得到目標SQL的執行計劃的第二種方法。針對不同的應用場景,你可以選擇如下四種方法中的一種:
select * from table(dbms_xplan.display);
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced'));
select * from table(dbms_xplan.display_awr('sql_id'));
方法1是需要與explain plan命令配合使用,上節已經介紹過。
方法2用于在SQLPLUS中查看剛剛執行過的SQL的執行計劃。這里針對方法DBMS_XPLAN.DISPLAY_CURSOR所傳入的第一個和第二個參數的值均為null,第三個參數值是“advanced”,第三個輸入參數的值也可以是“all”,只不過用“advanced”后的顯示結果會比“all”顯示的結果更詳細一些。
set linesize 800 pagesize 900
col plan_table_output for a200
select empno,ename,dname from scott.emp,scott.deptwhere emp.deptno=dept.deptno;
select * fromtable(dbms_xplan.display_cursor(null,null,'advanced'));
select empno,ename,dname from scott.emp,scott.deptwhere emp.deptno=dept.deptno;
select * fromtable(dbms_xplan.display_cursor(null,null,'all'));
“all”得到的結果與“advanced”的顯示結果相比,少了“Outline Data”部分的內容。
方法3用于查看指定SQL的執行計劃。這里針對方法DBMS_XPLAN.DISPLAY_CURSOR所傳入的第一個參數的值是指定SQL的SQL ID或者SQL HASH VALUE,第二個參數的值是要查看的執行計劃所在的Chile Cursor Number,第三個參數已經在介紹方法2時已經提到過,這個參數值一般都用“advanced”。
selectsql_text,sql_id,hash_value,child_number from v$sql where sql_text like 'selectempno,ename%';
SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER
------------------------------------------------------------------------------- ------------- ---------- -------------
selectempno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptno 3yfu3wh250aqt 38808281 0
sql_id為3yfu3wh250aqt,SQL HASH_VALUE為38808281,對應的ChildCursor Number為0。
本質上SQL ID和SQL HASH_VALUE是一回事,它們是可以互相轉換的,這也是方法DBMS_XPLAN.DISPLAY_CURSOR所傳入的第一個參數的值可以是SQL ID,也可以是SQLHASH_VALUE的原因。
selectlower(trim('3yfu3wh250aqt')) sql_id,
trunc(mod(sum((instr('0123456789abcdfghjkmnpqrstuvwxyz',
substr(lower(trim('3yfu3wh250aqt')),level,1))-1)*power(32,length(trim('3yfu3wh250aqt'))-level)),
power(2,32)))hash_value
fromdual
connectby level<=length(trim('3yfu3wh250aqt'));
SQL_ID HASH_VALUE
-------------------------
3yfu3wh250aqt 38808281
從上述結果可以看到,目標SQL的SQL ID(3yfu3wh250aqt)經過運算后得到的值就是該SQL的SQL HASH VALUE(38808281)。
只要目標SQL的執行計劃所在的Child Cursor還沒有被age out出Shard Pool,就可以使用方法3查看該SQL的執行計劃
select* from table(dbms_xplan.display_cursor('3yfu3wh250aqt',0,'advanced'));
方法4 用于查看指定SQL的所有歷史執行計劃。
使用方法2、3能夠顯示目標SQL執行計劃的前提條件是該SQL的執行計劃還在Shared Pool中,而如果該SQL的執行計劃已經被age out出SharedPool,那么只要該SQL的執行計劃被Oracle采集到AWR Repository中,就可以使用方法4來查看該SQL的所有歷史執行計劃。
用DBMS_XPLAN.DISPLAY_AWR和之前用DBMS_XPLAN.DISPLAY_CURSOR顯示的執行計劃相比,有一個非常不好的地方——就是用DISPLAY_AWR顯示的執行計劃中看不到執行步驟對應的謂詞條件。根本的原因是Oracle在把執行計劃的采樣數據從V$SQL_PLAN挪到AWR Repository的基表WRH$_SQL_PLAN中時沒有保留V$SQL_PLAN中記錄謂詞條件(包括驅動查詢條件和過濾查詢條件)的列ACCESS_PREDICATES和FILTER_PREDICATES的值,所以不是DBMS_XPLAN.DISPLAY_CURSOR不想顯示謂詞條件,而是根本就同有謂詞條件可供顯示。
1.3 AUTOTRACE開關
在SQLPLUS中將AUTOTRACE開關打開也能得到目標SQL的執行計劃,而且,除此之外還能得到目標SQL在執行時的資源消耗量,即通過設置AUTOTRACE開關我們可以額外觀察到目標SQL執行時所耗費的物理讀、邏輯讀、產生redo的數量以及排序的數量等。
在SQLPLUS中設置AUTOTRACE開關的語法如下:
SET AUTOTRACE{OFF|ON|TRACE[ONLY]}
[EXPLAIN][STATISTICS]
在SQLPLUS的當前Session中執行命令SET AUTOTRACE ON,可以在當前Session中將AUTOTRACE開關完全打開。這樣,在這個Session隨后執行的所有SQL除了顯示SQL執行結果外,還會額外顯示這些SQL所對應的執行計劃和資源消耗情況。
在SQLPLUS的當前Session中執行命令SET AUTOTRACE OFF,可以在當前Session中將AUTOTRACE開關關閉,這樣,在這個Session中隨后執行的所有SQL都只會顯示SQL執行結果,AUTOTRACE開關的默認值就是OFF。
在SQLPLUS的當前Session中執行命令SET AUTOTRACE TRACEONLY,可以在當前Session中將AUTOTRACE開關以不顯示SQL執行結果的具體內容的方式完全打開。這種方式與SET AUTOTRACE ON的唯一區別在于TRACEONLY只顯示SQL執行結果的數量,而不會顯示執行結果的具體內容。適用于SQL執行結果的具體內容特別長,會連續刷屏的SQL,這種情況下我們往往并不關心這些SQL的執行結果的具體內容,而只是關心它們的執行計劃和資源消耗量。
在SQLPLUS的當前Session中執行命令SET AUTOTRACE TRACEONLY EXPLAIN,可以在當前Session中將AUTOTRACE開關以只顯示SQL執行計劃的方式打開。這種方式與TRACEONLY的區別在于TRACEONLY EXPLAIN不會顯示目標SQL的資源消耗量和執行結果,而只會顯示目標SQL的執行計劃。
在SQLPLUS的當前Session中執行命令SET AUTOTRACE TRACEONLY STATISTICS,可以在當前Session中將AUTOTRACE開關以只顯示SQL的資源消耗量的方式打開,與TRACEONLY的唯一區別在于TRACEONLY STATISTICS不顯示目標SQL的執行計劃,而只會顯示目標SQL執行結果的數據和資源消耗量。
設置AUTOTRACE開關的相關命令也沒用了Oracle一貫的可以使用簡寫的慣例:
關鍵字AUTOTRACE可以用簡寫AUTOT來代替
關鍵字TRACEONLY可以用簡寫TRACE來代替
關鍵字EXPLAIN可以用簡寫EXP來代替
關鍵字STATISTICS可以用簡寫STAT來代替
SET AUTOTRACE ON
select empno,ename,dname from scott.emp,scott.deptwhere emp.deptno=dept.deptno;
SET AUTOTRACE TRACEONLY
select empno,ename,dname from scott.emp,scott.deptwhere emp.deptno=dept.deptno;
SET AUTOTRACE OFF
select empno,ename,dname from scott.emp,scott.deptwhere emp.deptno=dept.deptno;
1.4 10046事件與tkprof命令
使用10046事件是在Oracle數據庫中查看目標SQL的執行計劃的另一種方法。這種方法與使用explain plan命令、DBMS_XPLAN包和AUTOTRACE開關的不同之處在于,所得到的執行計劃中明確顯示了目標SQL實際執行計劃中每一個執行步驟所消耗的邏輯讀、物理讀和花費的時間。這種細粒度的明細顯示在我們診斷復雜SQL的性能問題時尤為有用,而且這也是其他三種方法所不能提供的(實際上,用GATHER_PLAN_STATISTICS Hint配合DBMS_XPLN包一起使用可以達到類似10046事件這種細粒度的明細顯示效果)。
用10046事件得到目標SQL的執行計劃是很容易的,只需要依次執行如下三個步驟:
首先在當前Session中激活10046事件;
接著在此Session中執行目標SQL;
最后在此Session中關閉10046事件。
當執行完上述步驟后,Oracle就會將目標SQL的執行計劃和明細資源消耗寫入此Session所對應的trace文件中,查看這個trace文件就能知道目標SQL的執行計劃和資源消耗明細了。Oracle會在參數USER_DUMP_DEST所代表的目標下生成這個trace文件,其命名格式為“實例名_ora_當前Session的spid.trc”,例如orcl_ora_86541.trc。
通常可以使用如下兩種方法在當前Session中激活10046事件:
在當前Session中執行alter session set events '10046 trace name context forever ,level 12'
在當前Session中執行oradebug event 10046 trace name context forever,level 12
上述命令中的關鍵字“level”后的數字是表示設置的10046事件的level值。這個值是可以修改的,我們通常使用的值為12,表示產生的trace文件中除了目標SQL的執行計劃和資源消耗明細之外,還會包含目標SQL所使用的綁定變量的值以及該Session所經歷的等待事件。除了上述level值之外,其他部分是固定語法,我們無法修改。使用第2種方法,在激活10046事件后執行命令oradebugtracefile_name來得到當前Session所對應的trace文件的具體路徑和名稱。
對應的,在當前Session中關閉10046事件的兩種方法:
在當前Session中執行alter session set events '10046 trace name context off'
在當前Session中執行oradebug event 10046 trace name context off
需要注意的是10046事件所產生的原始trace文件習慣稱之為裸trace文件(raw trace),Oracle記錄在裸trace文件中的內容一眼看上去并不是那么觀,也不是那么容易看懂。為了祼trace文件能夠以一種更直觀、更容易懂的方式展現出來,Oracle提供了tkprof命令,這個命令是Oracle自帶的,可以用它來翻譯祼trace文件。
關于10046事件可以參考之前的博客:
http://hbxztc.blog.51cto.com/1587495/1898624
http://hbxztc.blog.51cto.com/1587495/1898753
參考:《基于Oracle的SQL優化》
免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。