Orace SQL調(diào)優(yōu)系列之執(zhí)行計(jì)劃學(xué)習(xí)筆記

文章目錄

一、獲取執(zhí)行計(jì)劃的方法

(1) explain plan for

(2) set autotrace on

(3) statistics_level=all

(4) dbms_xplan.display_cursor獲取

(5) 事件10046 trace跟蹤

(6) awrsqrpt.sql

二、解釋經(jīng)典執(zhí)行計(jì)劃的方法

【單獨(dú)型】

【聯(lián)合型關(guān)聯(lián)型】

(1) 聯(lián)合型的關(guān)聯(lián)型(NL)

(2) 聯(lián)合型的關(guān)聯(lián)型(FILTER)

(3) 聯(lián)合型的關(guān)聯(lián)型(UPDATE)

(4) 聯(lián)合型的關(guān)聯(lián)型(CONNECT BY WITH FILTERING)

【聯(lián)合型非關(guān)聯(lián)型】

一、獲取執(zhí)行計(jì)劃的方法

(1) explain plan for

步驟：

1：explain plan for 你的SQL;

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

優(yōu)點(diǎn)：不需要真的執(zhí)行，快捷方便

缺點(diǎn)：沒(méi)有輸出運(yùn)行時(shí)的統(tǒng)計(jì)信息(邏輯讀、遞歸調(diào)用，物理讀)，因?yàn)闆](méi)有真正執(zhí)行，所以不能看到執(zhí)行了多少行、表被訪問(wèn)了多少次等等

(2) set autotrace on

sqlplus登錄：

用戶名/密碼@主機(jī)名稱:1521/數(shù)據(jù)庫(kù)名

1

步驟：

1：set sutoatrace on

2：在此次執(zhí)行你的sql;

優(yōu)點(diǎn)：可以看到運(yùn)行時(shí)的統(tǒng)計(jì)信息(邏輯讀、遞歸調(diào)用，物理讀)

缺點(diǎn)：不能看到表被訪問(wèn)了多少次，也需要等sql執(zhí)行完成才能看

(3) statistics_level=all

步驟：

1：alter session set statistics_level=all;

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

3：select * from table(dbms_xplan.display_cursor(null , null,‘a(chǎn)llstats last’));

假如使用了Hint語(yǔ)法: /*+ gather_plan_statistics */，就可以省略步驟1，直接執(zhí)行步驟2和3,獲取執(zhí)行計(jì)劃

關(guān)鍵字解讀：

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í)行的邏輯讀或一致性讀

Reads：物理讀

OMem：當(dāng)前操作完成所有內(nèi)存工作區(qū)操作總使用私有內(nèi)存工作區(qū)(PGA)的大小

lMem：當(dāng)工作區(qū)大小無(wú)法符滿足操作需求的大小時(shí)，需要將部分?jǐn)?shù)據(jù)寫入臨時(shí)磁盤空間中(如果僅需要寫入一次就可以完成操作，就稱一次通過(guò)，One-Pass；否則為多次通過(guò)，Multi-Pass)。改數(shù)據(jù)為語(yǔ)句最后一次執(zhí)行中，單次寫磁盤所需要的內(nèi)存大小，這個(gè)是由優(yōu)化器統(tǒng)計(jì)數(shù)據(jù)以及前一次執(zhí)行的性能數(shù)據(jù)估算得出的

Used-Mem：語(yǔ)句最后一次執(zhí)行中，當(dāng)前操作所使用的內(nèi)存工作區(qū)大小，括號(hào)里面為(發(fā)生磁盤交換的次數(shù)，1次即為One-Pass,大于一次則為Mullti-Pass，如果沒(méi)有使用磁盤，則顯示為OPTI1MAL)

OMem、lMem為執(zhí)行所需要的內(nèi)存評(píng)估值，OMem為最優(yōu)執(zhí)行模式所需要內(nèi)存的評(píng)估值，Used-Mem為消耗的內(nèi)存

優(yōu)點(diǎn)：

可以從STARTS得出表被訪問(wèn)多少次；

可以清晰地從E-ROWS和A-ROWS中分別得出預(yù)測(cè)的行數(shù)和真實(shí)的行數(shù)

缺點(diǎn)：

必須等到語(yǔ)句真正執(zhí)行完成后，才可以得出結(jié)果

無(wú)法控制記錄打屏輸出，不想aututrace有traceonly命令

沒(méi)有專門的輸出統(tǒng)計(jì)信息，看不到遞歸調(diào)用的次數(shù)，看不出物理讀具體數(shù)值，不過(guò)有邏輯讀，邏輯讀才是重點(diǎn)

(4) dbms_xplan.display_cursor獲取

步驟

從共享池獲取

//${SQL_ID}參數(shù)可以從共享池拿 select * from table(dbms_xplan.display_cursor(${SQL_ID}));

1

2

還可以從AWR性能視圖里獲取

select * from table(dbms_xplan.display_awr(${SQL_ID}));

1

多個(gè)執(zhí)行計(jì)劃的情況,可以用類似方法查出

select * from table(dbms_xplan.display_cursor(${SQL_ID},0)); select * from table(dbms_xplan.display_cursor(${SQL_ID},1));

1

2

3

4

優(yōu)點(diǎn)：

和explain一樣不需要真正執(zhí)行，知道sql_id就好

缺點(diǎn)：

不能判斷處理了多少行

無(wú)法判斷表被訪問(wèn)了多少次

沒(méi)有輸出運(yùn)行時(shí)的相關(guān)統(tǒng)計(jì)信息(邏輯讀、遞歸調(diào)用、物理讀)

(5) 事件10046 trace跟蹤

步驟：

1：alter session set events '10046 trace name context forever,level 12';//開啟跟蹤 2：執(zhí)行你的語(yǔ)句 3：alter session set events '10046 trace name context off';//關(guān)閉跟蹤 4：找到跟蹤產(chǎn)生的文件 5：tkprof trc文件 目標(biāo)文件 sys=no sort=prsela,exeela,fchela(格式化命令)

1

2

3

4

5

6

優(yōu)點(diǎn)：

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

可以列出sql語(yǔ)句中的函數(shù)調(diào)用的

可以看出解析事件和執(zhí)行事件

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

可以看出處理的行數(shù)，產(chǎn)生的邏輯讀

缺點(diǎn)：

步驟比較繁瑣

無(wú)法判斷表被訪問(wèn)了多少次

執(zhí)行計(jì)劃中的謂詞部分不能清晰地顯示出來(lái)

(6) awrsqrpt.sql

步驟：

1：@?/rdbms/admin/awrsqrpt.sql 具體可以參考我之前的博客：https://smilenicky.blog.csdn.net/article/details/89429989

1

2

二、解釋經(jīng)典執(zhí)行計(jì)劃的方法

可以分為兩種類型：?jiǎn)为?dú)型和聯(lián)合型

聯(lián)合型分為：關(guān)聯(lián)的聯(lián)合型和非關(guān)聯(lián)的聯(lián)合型

【單獨(dú)型】

單獨(dú)型比較好理解，執(zhí)行順序是按照id=1,id=2,id=3執(zhí)行，由遠(yuǎn)及近

先scott登錄，然后執(zhí)行sql，例子來(lái)自《收獲，不止SQL優(yōu)化》一書

select deptno, count(*) from emp where job = 'CLERK' and sal < 3000 group by deptno

1

2

3

4

5

所以可以給出單獨(dú)型的圖例：

【聯(lián)合型關(guān)聯(lián)型】

這里使用Hint的nl

select /*+ ordered use_nl(dept) index(dept) */ * from emp, dept where emp.deptno = dept.deptno and emp.comm is null and dept.dname != 'SALES'

1

2

3

4

5

這圖來(lái)自《收獲，不止SQL優(yōu)化》，可以看出id為2的A-Rows實(shí)踐返回行數(shù)為10，id為3的Starts為10，說(shuō)明驅(qū)動(dòng)表emp訪問(wèn)的結(jié)果集返回多少條記錄，被驅(qū)動(dòng)表就被訪問(wèn)多少次，這是關(guān)聯(lián)型的顯著特征

關(guān)聯(lián)型不一定是驅(qū)動(dòng)表返回多少條，被驅(qū)動(dòng)表就被訪問(wèn)多少次的，注意FILTER模式也是關(guān)聯(lián)型的

前面已經(jīng)介紹了聯(lián)合型關(guān)聯(lián)型(nl)這種方法的，這種方法是驅(qū)動(dòng)表返回多少條記錄，被驅(qū)動(dòng)表就被訪問(wèn)了多少次，不過(guò)這種情況對(duì)于FILTER模式下并不適用

執(zhí)行SQL，這里使用Hint /*+ no_unnset */

select * from emp where not exists (select /*+ no_unnset */ 0 from dept where dept.dname='SALES' and dept.deptno = emp.deptno) and not exists(select /*+ no_unnset */ 0 from bonus where bonus.ename = emp.ename)

1

2

3

ps:圖來(lái)自《收獲，不止SQL優(yōu)化》一書，這里可以看出id為2的地方，A-Rows實(shí)際返回行數(shù)為8，而id為3的地方，Starts為3，說(shuō)明對(duì)應(yīng)SQL執(zhí)行3次，也即dept被驅(qū)動(dòng)表被訪問(wèn)了3次，這和剛才介紹的nl方式不同，為什么不同?

查詢一下SQL，可以看出實(shí)際返回3條，其它的都是重復(fù)多的，

select dname, count(*) from emp, dept where emp.deptno = dept.deptno group by dname;

1

所以，就很明顯了，被過(guò)濾了重復(fù)數(shù)據(jù)，也就是說(shuō)FILTER模式的對(duì)數(shù)據(jù)進(jìn)行過(guò)濾，驅(qū)動(dòng)表執(zhí)行結(jié)果集返回多少行不重復(fù)數(shù)據(jù)，被驅(qū)動(dòng)表就被訪問(wèn)多少次，F(xiàn)ILTER模式可以說(shuō)是對(duì)nl模式的改善

update emp e1 set sal = (select avg(sal) from emp e2 where e2.deptno = e1.deptno),comm = (select avg(comm) from emp e3)

1

聯(lián)合型的關(guān)聯(lián)型(UPDATE)和FILTER模式類似，所以就不重復(fù)介紹

select /*+ connect_by_filtering */ level, ename ,prior ename as manager from emp start with mgr is null connect by prior empno = mgr

1

2

給出聯(lián)合型關(guān)聯(lián)型圖例：

【聯(lián)合型非關(guān)聯(lián)型】

可以執(zhí)行SQL

select ename from emp union all select dname from dept union all select '%' from dual

1

對(duì)于plsql可以使用工具查看執(zhí)行計(jì)劃，sqlplus客戶端的可以使用statistics_level=all的方法獲取執(zhí)行計(jì)劃，具體步驟

1：alter session set statistics_level=all;

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

3：select * from table(dbms_xplan.display_cursor(null , null,‘a(chǎn)llstats last’));

可以給出聯(lián)合型非關(guān)聯(lián)型的圖例：

SQL

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。