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

執(zhí)行計(jì)劃是一條查詢(xún)語(yǔ)句在Oracle中的執(zhí)行過(guò)程或訪(fǎng)問(wèn)路徑的描述。

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

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

方法1：explain plan for explain [?k?sple?n]解釋?zhuān)f(shuō)明

（1）獲取步驟

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

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

（2）優(yōu)點(diǎn)

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

（3）缺點(diǎn)

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

2、無(wú)法判斷處理了多少行；

3、無(wú)法判斷表被訪(fǎng)問(wèn)了多少次；

（4）應(yīng)用場(chǎng)景

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

--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語(yǔ)句的執(zhí)行計(jì)劃，且不用的情形下使用不同的函數(shù)來(lái)顯示，
--如預(yù)估的執(zhí)行計(jì)劃則使用display函數(shù)，而實(shí)際的執(zhí)行計(jì)劃則是用display_cursor函數(shù)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

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

（1）獲取步驟

步驟1：:set autotrace on/traceonly

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

（2）優(yōu)點(diǎn)

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

2、雖然必須要等語(yǔ)句執(zhí)行完畢后才可以輸出執(zhí)行計(jì)劃，但是可以有traceonly開(kāi)關(guān)來(lái)控制返回結(jié)果不大屏輸出。

（3）缺點(diǎn)

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

2、無(wú)法看到表被訪(fǎng)問(wèn)了多少次。

（4）應(yīng)用場(chǎng)景

只能粗略知道recursive calls遞歸調(diào)用次數(shù)，詳細(xì)用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)計(jì)，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)點(diǎn)

1、可以清晰地從STATS得出表被訪(fǎng)問(wèn)多少次？

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

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

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

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

（3）缺點(diǎn)

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

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

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

（4）應(yīng)用場(chǎng)景

想要獲取表被訪(fǎng)問(wèn)的次數(shù)，只能用方法3

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

方法4：dbms_xplan.display_cursor

（1）獲取步驟

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

（2）優(yōu)點(diǎn)

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

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

（3）缺點(diǎn)

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

2、無(wú)法判斷處理了多少行；

3、無(wú)法判斷表被訪(fǎng)問(wèn)了多少次；

（4）應(yīng)用場(chǎng)景

觀(guān)察某條SQL有多條執(zhí)行計(jì)劃的情況

方法5：事件10046trace跟蹤

（1）獲取步驟

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

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

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

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

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

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

trace文件(*.trc格式)對(duì)開(kāi)發(fā)者來(lái)說(shuō)是不可讀的格式，需要把跟蹤文件轉(zhuǎn)換為為可讀的格式，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)點(diǎn)

1、可以看出SQL語(yǔ)句對(duì)應(yīng)的等到事件；

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

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

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

5、可以跟蹤整個(gè)程序包

（3）缺點(diǎn)

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

2、無(wú)法判斷表被訪(fǎng)問(wèn)了多少次；

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

（4）應(yīng)用場(chǎng)景

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

方法6：awrsqrpt.sql

AWR全稱(chēng)叫Automatic Workload Repository-自動(dòng)負(fù)載信息庫(kù),AWR 是通過(guò)對(duì)比兩次快照(snapshot)收集到的統(tǒng)計(jì)信息。

AWRSQRPT可以生成指定快照區(qū)間目標(biāo)SQL語(yǔ)句的統(tǒng)計(jì)報(bào)表，可以查看多個(gè)執(zhí)行計(jì)劃。

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

報(bào)告關(guān)注點(diǎn)：SQL ID部分的執(zhí)行計(jì)劃個(gè)數(shù)、Plan statistics 計(jì)劃統(tǒng)計(jì)、Execution Plan 執(zhí)行計(jì)劃

Automatic [???t??mæt?k] 自動(dòng)的；Workload 工作量；Repository 知識(shí)寶典

（1）獲取步驟

步驟1：以管理員用戶(hù)的身份登錄

sqlplus / as sysdba

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

步驟3：填寫(xiě)要生成的報(bào)告格式，支持html和text，html是默認(rèn)值可直接回車(chē)。

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

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

步驟6：sql的id:0k8522rmdzg4k 默認(rèn)值

查詢(xún)SQL_ID，sql_text可以從AWR報(bào)告拿

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：最后要求輸入報(bào)告名稱(chēng)

填寫(xiě)AWRSQRPT報(bào)告的名稱(chēng)，我可以填寫(xiě)awrsqrpt_20190421.html，然后在打印的日志里有文件保存的路徑：，比如：

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

（2）優(yōu)點(diǎn)

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

（3）缺點(diǎn)

獲取的過(guò)程比較麻煩

（4）應(yīng)用場(chǎng)景

想觀(guān)察某條SQL的多個(gè)執(zhí)行計(jì)劃用該方法

總結(jié)

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

最新評(píng)論