Oracle 查詢(xún)真實(shí)執(zhí)行計(jì)劃

什么是真實(shí)執(zhí)行計(jì)劃

獲取Oracle的執(zhí)行計(jì)劃，有幾種方式。（本文使用Oracle 11g XE版本，以及普通用戶(hù)scott登錄）

* explain plan for
有兩個(gè)步驟：
* explain plan for ${SQL}
* select * from table(dbms_xplan.display);
這一個(gè)方法可以在PLSQLDev的cmd窗口和sql窗口執(zhí)行，同時(shí)不需要給用戶(hù)授權(quán)。
示例：


* autotrace
有兩個(gè)步驟：
* set autot on
* 執(zhí)行${SQL}
但普通用戶(hù)需要授權(quán)，才能執(zhí)行。不了解授權(quán)過(guò)程，知道的同學(xué)可以留言。
并且在我的PLSQLDev里無(wú)法執(zhí)行，必須得到SqlPlus才能執(zhí)行。
示例：


可以看到多了一些統(tǒng)計(jì)信息，不過(guò)不是十分直觀(guān)。

但是，上面兩種方法

使用AUTOTRACE或者EXPLAIN PLAN FOR
獲取的執(zhí)行計(jì)劃來(lái)自于PLAN_TABLE。PLAN_TABLE是一個(gè)會(huì)話(huà)級(jí)的臨時(shí)表，里面的執(zhí)行計(jì)劃并不是SQL真實(shí)的執(zhí)行計(jì)劃，它只是優(yōu)化器估算出來(lái)的。真實(shí)的執(zhí)行計(jì)劃不應(yīng)該是估算的，應(yīng)該是真正執(zhí)行過(guò)的。SQL執(zhí)行過(guò)的執(zhí)行計(jì)劃存在于共享池中，具體存在于數(shù)據(jù)字典V$SQL_PLAN中，帶有A-Time的執(zhí)行計(jì)劃來(lái)自于V$SQL_PLAN，是真實(shí)的執(zhí)行計(jì)劃，而通過(guò)AUTOTRACE、通過(guò)EXPLAIN
PLAN FOR獲取的執(zhí)行計(jì)劃只是優(yōu)化器估算獲得的執(zhí)行計(jì)劃。（注1）

這里說(shuō)的帶有A-Time的執(zhí)行計(jì)劃，即是本文所說(shuō)的真實(shí)執(zhí)行計(jì)劃。

* 真實(shí)執(zhí)行計(jì)劃
這種方法需要對(duì)普通用戶(hù)授權(quán)，可用以下語(yǔ)句一次性授權(quán)。 grant select any dictionary to scott;
這種執(zhí)行計(jì)劃結(jié)果如下：


可以看到多了A-Rows、A-Time等字段。

Starts 表示這個(gè)操作執(zhí)行的次數(shù)
E-Rows表示優(yōu)化器估算的行數(shù)，就是普通執(zhí)行計(jì)劃中的Rows
A-Rows表示真實(shí)的行數(shù)
A-Time表示累加的總時(shí)間。與普通執(zhí)行計(jì)劃不同的是，普通執(zhí)行計(jì)劃中的Time是假的，而A-Time是真實(shí)的。
Buffers表示累加的邏輯讀
Reads表示累加的物理讀
（注2）

Starts、A-Rows、A-Time這幾個(gè)字段很直觀(guān)，對(duì)于非數(shù)據(jù)庫(kù)開(kāi)發(fā)人員來(lái)說(shuō)，容易理解。
真實(shí)執(zhí)行計(jì)劃的獲取方式 ，下面會(huì)介紹。

需要注意的是，普通執(zhí)行計(jì)劃估算出來(lái)的行數(shù)，受直方圖統(tǒng)計(jì)信息的影響，可能會(huì)使優(yōu)化器對(duì)執(zhí)行計(jì)劃的選擇產(chǎn)生誤判（例如本該走HASH JOIN，結(jié)果變成NESTED
LOOPS）。因此，直方圖統(tǒng)計(jì)信息應(yīng)該定期更新。這項(xiàng)工作在我司是DBA的日常工作。

如何獲取真實(shí)的執(zhí)行計(jì)劃

首先要有訪(fǎng)問(wèn)動(dòng)態(tài)性能視圖的權(quán)限，可用以下語(yǔ)句授權(quán)
grant select any dictionary to scott;
有了權(quán)限之后，分以下幾步走

1.alter session set statistics_level = all;
（這一步對(duì)當(dāng)前會(huì)話(huà)窗口有效，可以不做，下面解釋?zhuān)?br>
2.執(zhí)行語(yǔ)句;
（如果上一步不做，則需要在語(yǔ)句中添加 /+ gather_plan_statistics /。 例如：select /+
gather_plan_statistics/ * from dual; 但這樣做比較麻煩 ，每條語(yǔ)句都要加上，不推薦）

3.找出執(zhí)行語(yǔ)句的SQL ID，例如：
select v.last_active_time, v.* from v$sql v where v.last_active_time >
to_date('2019/10/02 17:00:00', 'yyyy/mm/dd hh24:mi:ss') and v.sql_text like
'select * from %' and v.parsing_schema_name = 'SCOTT' order by
v.last_active_time desc;

參數(shù)根據(jù)實(shí)際情況修改，越精確越好。
可以得到如下計(jì)劃，把SQL ID取出

4.根據(jù)SQL ID查出執(zhí)行計(jì)劃
select * from table(dbms_xplan.display_cursor('b8x994z12hax9',null,'allstats
last'));
其中第一個(gè)參數(shù)就是第3步獲得的SQL ID。可得到執(zhí)行計(jì)劃


復(fù)制出來(lái)貼到notepad++


可以看到已經(jīng)有了實(shí)際執(zhí)行的信息。
這個(gè)例子還比較簡(jiǎn)單，可以試試下面這個(gè)例子。
select d.dname, d.loc, e.empno, e.ename ? from emp e, dept d ?where e.deptno =
d.deptno ?order by d.dname, e.empno;
執(zhí)行計(jì)劃如下：


下面還有更多的信息，可以知道每一步操作的具體信息，比如兩個(gè)表之間通過(guò)什么關(guān)聯(lián)等。

總結(jié)


真實(shí)執(zhí)行計(jì)劃提供了SQL執(zhí)行的真實(shí)信息，包括A-Time（真實(shí)時(shí)間）、A-Rows（真實(shí)行數(shù)）、Starts（步驟執(zhí)行次數(shù)）等，對(duì)于非數(shù)據(jù)庫(kù)開(kāi)發(fā)人員來(lái)說(shuō)，十分直觀(guān)方便。我也借此在工作優(yōu)化了10+
SQL，收獲滿(mǎn)滿(mǎn)~
在這里要推薦一本書(shū)《SQL 優(yōu)化核心思想》，羅炳森 黃超 鐘僥 著。本文的所有文字引用，均摘自此書(shū)。我沒(méi)有完全看懂這本書(shū)，但目前在工作中，已經(jīng)夠用了。

• ? 上一篇： 教程 | 一文讀懂自學(xué)機(jī)器學(xué)習(xí)的誤區(qū)和陷阱（附學(xué)習(xí)資料）
• ? 下一篇： Linux高性能服務(wù)器設(shè)計(jì)