一、什么是執(zhí)行計劃？

執(zhí)行計劃是一條查詢語句在Oracle中的執(zhí)行過程或訪問路徑的描述。

執(zhí)行計劃描述了SQL引擎為執(zhí)行SQL語句進行的操作，分析SQL語句相關的性能問題或僅僅質(zhì)疑查詢優(yōu)化器的決定時，必須知道執(zhí)行計劃；所以執(zhí)行計劃常用語SQL調(diào)優(yōu)。

二、怎么獲取執(zhí)行計劃？（6種方法）

方法1：explain plan for explain [?k?sple?n]解釋，說明

（1）獲取步驟

步驟1：explain plan for + 跟上你要執(zhí)行的SQL;

步驟2：select * from table(dbms_xplan.display());

（2）優(yōu)點

無須真正執(zhí)行，快捷方便

（3）缺點

1、沒有輸出運行時的相關統(tǒng)計信息（產(chǎn)生多少邏輯讀；多少次遞歸調(diào)用；多少次物理讀情況）；

2、無法判斷處理了多少行；

3、無法判斷表被訪問了多少次；

（4）應用場景

如果某SQL執(zhí)行很長時間才出結果或返回不了結果

--1、explain plan for + 跟上你要執(zhí)行的SQL 
EXPLAIN PLAN FOR
SELECT A.*, B.*
  FROM EMP A
  LEFT JOIN DEPT B
    ON A.DEPTNO = B.DEPTNO
 WHERE A.EMPNO IN ('7369', '7499');
--2、dbms_xplan包括一系列函數(shù)，主要用于顯示SQL語句的執(zhí)行計劃，且不用的情形下使用不同的函數(shù)來顯示，
--如預估的執(zhí)行計劃則使用display函數(shù)，而實際的執(zhí)行計劃則是用display_cursor函數(shù)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

方法2：set autotrace on 【自動跟蹤】 trace [tre?s] 追蹤

（1）獲取步驟

步驟1：:set autotrace on/traceonly

步驟2：在此處執(zhí)行你的SQL

（2）優(yōu)點

1、可以輸出運行時的相關統(tǒng)計信息（產(chǎn)生多少邏輯讀，多少遞歸調(diào)用，多少次物理讀的情況）；

2、雖然必須要等語句執(zhí)行完畢后才可以輸出執(zhí)行計劃，但是可以有traceonly開關來控制返回結果不大屏輸出。

（3）缺點

1、必須要等語句真正執(zhí)行完畢后，才可以出結果；

2、無法看到表被訪問了多少次。

（4）應用場景

只能粗略知道recursive calls遞歸調(diào)用次數(shù)，詳細用10046trace事件方法

recursive [r??k??rs?v] 遞歸

SET AUTOTRACE TRACEONLY
SELECT A.*, B.*
  FROM SCOTT.EMP A
  LEFT JOIN SCOTT.DEPT B
    ON A.DEPTNO = B.DEPTNO
 WHERE A.EMPNO IN ('7369', '7499');

方法3：statistics_level=all statistics [st??t?st?ks] 統(tǒng)計，level [?levl] 層次，數(shù)量

（1）獲取步驟

步驟1：alter session set statistics_level=all;

步驟2：在此處執(zhí)行你的SQL

步驟3：select * from table(dbms_xplan.display_cursor(null,null,‘allstats last’));

（2）優(yōu)點

1、可以清晰地從STATS得出表被訪問多少次？

2、可以清晰地從E-ROWS和A-ROWS中得到預測的行數(shù)和真實的行數(shù)，從而可以準確判斷oracle評估是否準確？

e-rows即為estimate-rows,是根據(jù)表的統(tǒng)計信息得來的預估行數(shù)；

a-rows即為actual-rows,是sql在執(zhí)行過程中實際取到的行數(shù)。

3、雖然沒有專門的輸出運行時的相關統(tǒng)計信息，但是執(zhí)行計劃中的BUFFERS就是真實的邏輯讀的數(shù)值。

（3）缺點

1、必須要等到語句真正執(zhí)行完畢后，才可以出結果；

2、無法控制輸出記錄展現(xiàn)與否，而autotrace有traceonly可以控制不將輸出記錄打屏；

3、看不出遞歸調(diào)用的次數(shù)，看不出物理讀的數(shù)值。

（4）應用場景

想要獲取表被訪問的次數(shù)，只能用方法3

• starts:該SQL執(zhí)行的次數(shù)
• E-Rows:為執(zhí)行計劃預計的行數(shù)
• A-Rows：實際返回的行數(shù)，E-Rows和A-Rows作比較，就可以看出具體哪一步執(zhí)行計劃出問題了。
• A-Time:每一步實際執(zhí)行的時間，可以看出耗時的SQL
• Buffers：每一步實際執(zhí)行的邏輯讀或一致性讀

方法4：dbms_xplan.display_cursor

（1）獲取步驟

select * from table(dbms_xplan.display_cursor(‘&sq_id’));(該方法是從共享池里得到)

（2）優(yōu)點

1、知道sql_id立即可得到執(zhí)行計劃，和explain plan for一樣無須執(zhí)行；

2、可以得到真實的執(zhí)行計劃。

（3）缺點

1、沒有輸出運行時的相關統(tǒng)計信息（產(chǎn)生多少邏輯讀；多少次遞歸調(diào)用；多少次物理讀情況）；

2、無法判斷處理了多少行；

3、無法判斷表被訪問了多少次；

（4）應用場景

觀察某條SQL有多條執(zhí)行計劃的情況

方法5：事件10046trace跟蹤

（1）獲取步驟

步驟1：alter session set events’10046 trace name context forever,level 12’;（開啟跟蹤)

步驟2：執(zhí)行你的語句

步驟3：alter session set events ‘10046 trace name context off’;(關閉跟蹤)

步驟4：exit(退出當前窗口)

步驟5：找到跟蹤后產(chǎn)生的文件 路徑：此電腦/D/app/Administrator/diag/rdbms/prcl/trace

步驟6：tkprof trc 文件目標文件 【tkprof 是oracle自帶的一個命令行工具，主要作用是將原始的跟蹤文件轉換為格式化的文本文件】
“ Tkprof全稱:tool kit profiler trace kernel profiler” 工具包探查器跟蹤內(nèi)核探查器

trace文件(*.trc格式)對開發(fā)者來說是不可讀的格式，需要把跟蹤文件轉換為為可讀的格式，tkprof命令用是把跟蹤文件格式的工具。

tkprof D:\app\Administrator\diag\rdbms\orcl\orcl\TRACE/orcl_ora_4308.trc d:\10046.txt SYS=NO SORT=prsela,exeela,fchela

（2）優(yōu)點

1、可以看出SQL語句對應的等到事件；

2、如果SQL語句中有函數(shù)調(diào)用，SQL中有SQL，都將會被列出，無處循形；

3、可以方便地看出處理的行數(shù)，產(chǎn)生的物理邏輯讀；

4、可以方便地看出解析時間和執(zhí)行時間；

5、可以跟蹤整個程序包

（3）缺點

1、步驟煩瑣，比較麻煩；

2、無法判斷表被訪問了多少次；

3、執(zhí)行計劃中的謂詞部分不能清晰地展現(xiàn)出來

（4）應用場景

如果SQL中含有函數(shù)，函數(shù)中又嵌套SQL等，即存在多層調(diào)用，想準確分析只能用該方法

方法6：awrsqrpt.sql

AWR全稱叫Automatic Workload Repository-自動負載信息庫,AWR 是通過對比兩次快照(snapshot)收集到的統(tǒng)計信息。

AWRSQRPT可以生成指定快照區(qū)間目標SQL語句的統(tǒng)計報表，可以查看多個執(zhí)行計劃。

這個腳本可以很方便地取出某個sql在某兩個快照間隔內(nèi)，消耗cpu時間，執(zhí)行次數(shù)，邏輯讀，物理讀，sql的執(zhí)行計劃以及sql的full sql text，對調(diào)優(yōu)非常方便。

報告關注點：SQL ID部分的執(zhí)行計劃個數(shù)、Plan statistics 計劃統(tǒng)計、Execution Plan 執(zhí)行計劃

Automatic [???t??mæt?k] 自動的；Workload 工作量；Repository 知識寶典

（1）獲取步驟

步驟1：以管理員用戶的身份登錄

sqlplus / as sysdba

步驟2：執(zhí)行@?/rdbms/admin/awrsqrpt.sql 生產(chǎn)AWR報告

步驟3：填寫要生成的報告格式，支持html和text，html是默認值可直接回車。

步驟4：要求輸入要列出snap id的天數(shù)，一般最大保存了一個月的快照。依據(jù)自己的需要的時間段輸入要列出最近幾天的快照。

步驟5：要輸入AWR報告啟和止的snap_id，依據(jù)自己要的時間段輸入snap id即可

步驟6：sql的id:0k8522rmdzg4k 默認值

查詢SQL_ID，sql_text可以從AWR報告拿

select sql_text, last_load_time, t.SQL_ID
      from v$sql t
     where last_load_time is not null
       and sql_text like 'SELECT count(*) from%'
     order by t.LAST_LOAD_TIME desc

步驟7：最后要求輸入報告名稱

填寫AWRSQRPT報告的名稱，我可以填寫awrsqrpt_20190421.html，然后在打印的日志里有文件保存的路徑：，比如：

D:\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\awrsqrpt.html

（2）優(yōu)點

可以方便地看到多個執(zhí)行計劃

（3）缺點

獲取的過程比較麻煩

（4）應用場景

想觀察某條SQL的多個執(zhí)行計劃用該方法

總結

到此這篇關于Oracle獲取執(zhí)行計劃的六種方法的文章就介紹到這了,更多相關Oracle獲取執(zhí)行計劃內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論