中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

溫馨提示×

溫馨提示×

您好,登錄后才能下訂單哦!

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

Oracle如何查看執行計劃

發布時間:2020-06-29 22:55:05 來源:網絡 閱讀:4888 作者:hbxztc 欄目:關系型數據庫

一、如何查看執行計劃

Oracle數據庫里,我們通常可以使用如下方法(包括但不限于)得到目標SQL的執行計劃:

  1. explain plan 命令

  2. DBMS_XPLAN

  3. SQLPLUS中的AUTOTRACE開關

  4. 10046事件

  5. 10053事件

  6. AWR報告或Statspack報告

  7. 一些現成的腳本(     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命令對目標SQLexplain,再使用“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 ROWSGLOBALTEMPORARY TABLE,所以這里Oracle可以做到各個的Session只能看到自己執行的SQL所產生的執行計劃,并且各個SessionPLAN_TABLE$寫入執行計劃的過程互不干擾。

1.2 DBMS_XPLAN

使用DBMS_XPLAN包中的方法是在Oracle數據庫中得到目標SQL的執行計劃的第二種方法。針對不同的應用場景,你可以選擇如下四種方法中的一種:

  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'));

方法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所傳入的第一個參數的值是指定SQLSQL 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_id3yfu3wh250aqtSQL HASH_VALUE38808281,對應的ChildCursor Number0

本質上SQL IDSQL 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

從上述結果可以看到,目標SQLSQL ID(3yfu3wh250aqt)經過運算后得到的值就是該SQLSQL HASH VALUE(38808281)

只要目標SQL的執行計劃所在的Child Cursor還沒有被age outShard Pool,就可以使用方法3查看該SQL的執行計劃

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

方法4 用于查看指定SQL的所有歷史執行計劃。

      使用方法23能夠顯示目標SQL執行計劃的前提條件是該SQL的執行計劃還在Shared Pool中,而如果該SQL的執行計劃已經被age outSharedPool,那么只要該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_PREDICATESFILTER_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]

  1. SQLPLUS的當前Session中執行命令SET AUTOTRACE ON,可以在當前Session中將AUTOTRACE開關完全打開。這樣,在這個Session隨后執行的所有SQL除了顯示SQL執行結果外,還會額外顯示這些SQL所對應的執行計劃和資源消耗情況。

  2. SQLPLUS的當前Session中執行命令SET AUTOTRACE OFF,可以在當前Session中將AUTOTRACE開關關閉,這樣,在這個Session中隨后執行的所有SQL都只會顯示SQL執行結果,AUTOTRACE開關的默認值就是OFF

  3. SQLPLUS的當前Session中執行命令SET AUTOTRACE     TRACEONLY,可以在當前Session中將AUTOTRACE開關以不顯示SQL執行結果的具體內容的方式完全打開。這種方式與SET AUTOTRACE ON的唯一區別在于TRACEONLY只顯示SQL執行結果的數量,而不會顯示執行結果的具體內容。適用于SQL執行結果的具體內容特別長,會連續刷屏的SQL,這種情況下我們往往并不關心這些SQL的執行結果的具體內容,而只是關心它們的執行計劃和資源消耗量。

  4. SQLPLUS的當前Session中執行命令SET AUTOTRACE     TRACEONLY EXPLAIN,可以在當前Session中將AUTOTRACE開關以只顯示SQL執行計劃的方式打開。這種方式與TRACEONLY的區別在于TRACEONLY EXPLAIN不會顯示目標SQL的資源消耗量和執行結果,而只會顯示目標SQL的執行計劃。

  5. 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_當前Sessionspid.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優化》

向AI問一下細節

免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。

AI

饶阳县| 海盐县| 慈利县| 石嘴山市| 婺源县| 呈贡县| 赤水市| 普兰县| 九龙县| 莱芜市| 深泽县| 双峰县| 辽宁省| 米脂县| 阳西县| 寿阳县| 衡南县| 本溪| 盐城市| 大新县| 织金县| 岳普湖县| 镇远县| 洪雅县| 高邑县| 固原市| 泰兴市| 淅川县| 平果县| 内江市| 肇庆市| 柳江县| 武宁县| 宝山区| 静乐县| 兰西县| 托克逊县| 陆良县| 平度市| 莫力| 内黄县|