Oracle中使用DBMS_XPLAN處理執(zhí)行計(jì)劃詳解
DBMS_XPLAN是Oracle提供的一個(gè)用于查看SQL計(jì)劃，包括執(zhí)行計(jì)劃和解釋計(jì)劃的包；在以前查看SQL執(zhí)行計(jì)劃的時(shí)候，我都是使用set autotrace命令，不過(guò)現(xiàn)在看來(lái)，DBMS_XPLAN包給出了更加簡(jiǎn)化的獲取和顯示計(jì)劃的方式。
這5個(gè)函數(shù)分別對(duì)應(yīng)不同的顯示計(jì)劃的方式，DBMS_XPLAN包不僅可以獲取解釋計(jì)劃，它還可以用來(lái)輸出存儲(chǔ)在AWR，SQL調(diào)試集，緩存的SQL游標(biāo)，以及SQL基線(xiàn)中的語(yǔ)句計(jì)劃，實(shí)現(xiàn)如上的功能，通常會(huì)用到一下5個(gè)方法：
1.DISPLAY
2.DISPLAY_AWR
3.DISPLAY_CURSOR
4.DISPLAY_PLAN
5.DISPLAY_SQL_PLAN_BASELINE
6.DISPLAY_SQLSET
下面將重點(diǎn)討論關(guān)于DBMS_XPLAN包在解釋計(jì)劃和執(zhí)行計(jì)劃上的應(yīng)用。
來(lái)看一個(gè)經(jīng)常使用的查看某條語(yǔ)句的解釋計(jì)劃示例：
SQL> explain plan for select * from scott.emp;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 532 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
-- 對(duì)應(yīng)autotrace實(shí)現(xiàn)
SQL> set autotrace traceonly explain
上例中使用了dbms_xplan.display方法來(lái)顯示PLAN_TABLE中保存的解釋計(jì)劃，如果想要顯示執(zhí)行計(jì)劃，就需要使用到DMBS_XPLAN.DISPLAY_CURSOR方法了，DMBS_XPLAN.DISPLAY_CURSOR調(diào)用簽名：
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');
sql_id表示存儲(chǔ)在cursor cache中的SQL語(yǔ)句的id，child_number用于指示緩存sql語(yǔ)句計(jì)劃的子id，format參數(shù)用于控制包含在輸出中的信息類(lèi)型，官檔的參數(shù)如下：
1.BASIC: 顯示最少的信息，只包括操作類(lèi)型，ID名稱(chēng)和選項(xiàng)。
2.TYPICAL: 默認(rèn)值，顯示相關(guān)信息以及某些附加的顯示選項(xiàng)，如分區(qū)和并發(fā)使用等。
3.SERIAL: 與TYPICAL類(lèi)型相似，區(qū)別是它不包括并發(fā)的信息，即使是并行執(zhí)行的計(jì)劃。
4.ALL: 顯示最多的信息，包含了TYPICAL的全部以及更多的附加信息,如別名和遠(yuǎn)程調(diào)用等。
除了以上的基本的四種輸出格式外，format還有一些附加的選項(xiàng)可用于定制化輸出行為，使用中可以通過(guò)逗號(hào)和空格分隔來(lái)聲明多個(gè)關(guān)鍵字，同時(shí)可以使用”+”和”-”符號(hào)來(lái)包含或排除相應(yīng)的顯示元素，這些附加的選項(xiàng)在官檔中也有記載：
1.ROWS – 顯示被優(yōu)化器估算的記錄的行號(hào)
2.BYTES – 顯示優(yōu)化器估算的字節(jié)數(shù)
3.COST – 顯示優(yōu)化器計(jì)算的成本信息
4.PARTITION – 顯示分區(qū)的分割信息
5.PARALLEL – 顯示并行執(zhí)行信息
6.PREDICATE – 顯示謂語(yǔ)
7.PROJECTION – 顯示列投影部分(每一行的那些列被傳遞給其父列已經(jīng)這些列的大小)
8.ALIAS – 顯示查詢(xún)塊名稱(chēng)已經(jīng)對(duì)象別名
9.REMOTE – 顯示分布式查詢(xún)信息
10.NOTE – 顯示注釋
11.IOSTATS – 顯示游標(biāo)執(zhí)行的IO統(tǒng)計(jì)信息
12.MEMSTATS – 為內(nèi)存密集運(yùn)算如散列聯(lián)結(jié)，排序，或一些類(lèi)型的位圖運(yùn)算顯示內(nèi)存管理統(tǒng)計(jì)信息
13.ALLSTATS – 與'IOSTATS MEMSTATS'等價(jià)
14.LAST – 顯示最后執(zhí)行的執(zhí)行計(jì)劃統(tǒng)計(jì)信息，默認(rèn)顯示為ALL類(lèi)型，并且可以累積。
以上的參數(shù)同樣適用于解釋計(jì)劃的display方法。
示例部分：
一、使用display_cursor方法查看最近一條語(yǔ)句的執(zhí)行計(jì)劃
SQL> select /*+ gather_plan_statistics */ count(*) from scott.emp;
COUNT(*)
----------
14
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID f9qyz8s3c2c02, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from scott.emp
Plan hash value: 2937609675
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
| 2 | INDEX FULL SCAN| PK_EMP | 1 | 14 | 14 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------
14 rows selected.
使用dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')時(shí)，將sql_id和child_number設(shè)置成null，表示獲取上一條執(zhí)行語(yǔ)句的執(zhí)行計(jì)劃；注意上面的例子中一定要指定gather_plan_statistics提示或者手動(dòng)設(shè)置數(shù)據(jù)庫(kù)STATISTICS_LEVEL參數(shù)為ALL來(lái)使得其抓取行數(shù)據(jù)源的執(zhí)行統(tǒng)計(jì)信息，這些信息包括行數(shù)，一直讀取次數(shù)，物理讀次數(shù)，物理寫(xiě)次數(shù)以及運(yùn)算在一行數(shù)據(jù)上耗費(fèi)的運(yùn)行時(shí)間，如果沒(méi)有指定該提示，就不會(huì)有A-Rows,A-Time,Buffers這三列信息。
二、獲取某條指定語(yǔ)句的執(zhí)行計(jì)劃
SQL> select /*+ gather_plan_statistics */ count(*) from hr.employees where department_id in (90, 100);
COUNT(*)
----------
9
-- 通過(guò)v$sql視圖查詢(xún)到sql語(yǔ)句的SQL_ID和CHILD_NUMBER
SQL> select sql_id,child_number,sql_text from v$sql
2 where sql_text like '%select /*+ gather_plan_statistics */ count(*)%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ----------------------------------------------------------------------------------------------------
5qxmkvh40yw0p 0 select /*+ gather_plan_statistics */ count(*) from hr.employees where department_id in (90, 100)
bqjrnskvpv51n 0 select sql_id,child_number,sql_text from v$sql where sql_text like '%select /*+ gather_plan_statisti
cs */ count(*)%'
-- 獲取對(duì)應(yīng)的執(zhí)行計(jì)劃
SQL> select * from table(dbms_xplan.display_cursor('5qxmkvh40yw0p',0,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 5qxmkvh40yw0p, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from hr.employees where
department_id in (90, 100)
Plan hash value: 4167091351
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 |
| 2 | INLIST ITERATOR | | 1 | | 9 |00:00:00.01 | 2 |
|* 3 | INDEX RANGE SCAN| EMP_DEPARTMENT_IX | 2 | 9 | 9 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("DEPARTMENT_ID"=90 OR "DEPARTMENT_ID"=100))
21 rows selected.
三、通過(guò)format參數(shù)定制執(zhí)行計(jì)劃輸出信息
-- 使用ALL來(lái)顯示解釋計(jì)劃的全部信息
SQL> explain plan for
2 select * from emp e, dept d
3 where e.deptno = d.deptno
4 and e.ename = 'JONES' ;
Explained.
SQL> select * from table(dbms_xplan.display(format=>'ALL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3625962092
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 58 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 38 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / E@SEL$1
4 - SEL$1 / D@SEL$1
5 - SEL$1 / D@SEL$1
Predicate Information (identified by operation id):
3 - filter("E"."ENAME"='JONES')
4 - access("E"."DEPTNO"="D"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
"E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
"E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],
"D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
2 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
"E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
"E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],
"D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
3 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
"E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
"E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
5 - "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
43 rows selected.
-- 去除執(zhí)行計(jì)劃上的字節(jié)數(shù)和成本統(tǒng)計(jì)信息
SQL> select empno, ename from emp e, dept d
2 where e.deptno = d.deptno
3 and e.ename = 'JONES' ;
EMPNO ENAME
---------- ----------
7566 JONES
SQL> select * from table(dbms_xplan.display_cursor(null,null,format=>'ALLSTATS LAST -COST -BYTES'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 3mypf7d6npa97, child number 1
-------------------------------------
select empno, ename from emp e, dept d where e.deptno = d.deptno and
e.ename = 'JONES'
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 1 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("E"."ENAME"='JONES' AND "E"."DEPTNO" IS NOT NULL))
19 rows selected.
-- 另一種選項(xiàng)，窺視綁定變量的值，非常方便??！
SQL> variable v_empno number
SQL> exec :v_empno := 7566 ;
PL/SQL procedure successfully completed.
SQL> select * from emp where empno = :v_empno ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
SQL> select * from table(dbms_xplan.display_cursor(null,null,format=>'+PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 9q17w9umt58m7, child number 0
-------------------------------------
select * from emp where empno = :v_empno
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
--------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :V_EMPNO (NUMBER): 7566
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
2 - access("EMPNO"=:V_EMPNO)
24 rows selected.
-- 并行查詢(xún)信息篩選
SQL> select /*+ parallel(d, 4) parallel (e, 4) */
2 d.dname, avg(e.sal), max(e.sal)
3 from dept d, emp e
4 where d.deptno = e.deptno
5 group by d.dname
6 order by max(e.sal), avg(e.sal) desc;
DNAME AVG(E.SAL) MAX(E.SAL)
-------------- ---------- ----------
SALES 1566.66667 2850
RESEARCH 2175 3000
ACCOUNTING 2916.66667 5000
SQL> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL -BYTES -COST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID gahr597f78j0d, child number 0
-------------------------------------
select /*+ parallel(d, 4) parallel (e, 4) */ d.dname, avg(e.sal),
max(e.sal) from dept d, emp e where d.deptno = e.deptno group by
d.dname order by max(e.sal), avg(e.sal) desc
Plan hash value: 3078011448
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | | |
| 1 | PX COORDINATOR | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10004 | 4 | 00:00:01 | Q1,04 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 4 | 00:00:01 | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 4 | 00:00:01 | Q1,04 | PCWP | |
| 5 | PX SEND RANGE | :TQ10003 | 4 | 00:00:01 | Q1,03 | P->P | RANGE |
| 6 | HASH GROUP BY | | 4 | 00:00:01 | Q1,03 | PCWP | |
| 7 | PX RECEIVE | | 14 | 00:00:01 | Q1,03 | PCWP | |
| 8 | PX SEND HASH | :TQ10002 | 14 | 00:00:01 | Q1,02 | P->P | HASH |
|* 9 | HASH JOIN BUFFERED | | 14 | 00:00:01 | Q1,02 | PCWP | |
| 10 | PX RECEIVE | | 4 | 00:00:01 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10000 | 4 | 00:00:01 | Q1,00 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 4 | 00:00:01 | Q1,00 | PCWC | |
|* 13 | TABLE ACCESS FULL| DEPT | 4 | 00:00:01 | Q1,00 | PCWP | |
| 14 | PX RECEIVE | | 14 | 00:00:01 | Q1,02 | PCWP | |
| 15 | PX SEND HASH | :TQ10001 | 14 | 00:00:01 | Q1,01 | P->P | HASH |
| 16 | PX BLOCK ITERATOR | | 14 | 00:00:01 | Q1,01 | PCWC | |
|* 17 | TABLE ACCESS FULL| EMP | 14 | 00:00:01 | Q1,01 | PCWP | |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("D"."DEPTNO"="E"."DEPTNO")
13 - access(:Z>=:Z AND :Z<=:Z) 17 - access(:Z>=:Z AND :Z<=:Z)
38 rows selected.
相關(guān)文章
Oracle 高速批量數(shù)據(jù)加載工具sql*loader使用說(shuō)明
SQL*Loader（SQLLDR）是Oracle的高速批量數(shù)據(jù)加載工具,這是一個(gè)非常有用的工具，可用于多種平面文件格式向Oralce數(shù)據(jù)庫(kù)中加載數(shù)據(jù)2012-12-12
這篇文章介紹了Oracle在PL/SQL中嵌入SQL語(yǔ)句的方法，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2022-05-05
Oracle輕松取得建表和索引的DDL語(yǔ)句...2007-04-04
Oracle執(zhí)行計(jì)劃查看方法匯總及優(yōu)劣對(duì)比詳解
這篇文章主要介紹了Oracle執(zhí)行計(jì)劃查看方法匯總及優(yōu)劣對(duì)比,在?Oracle?數(shù)據(jù)庫(kù)中,查看執(zhí)行計(jì)劃是優(yōu)化?SQL?語(yǔ)句性能的重要工具,本文給大家介紹了幾種常用的查看執(zhí)行計(jì)劃的方法及其優(yōu)劣比較,需要的朋友可以參考下2024-11-11
oracle正則表達(dá)式多項(xiàng)匹配時(shí)相似項(xiàng)有優(yōu)先級(jí)詳解
這篇文章主要給大家介紹了關(guān)于oracle正則表達(dá)式多項(xiàng)匹配時(shí)相似項(xiàng)有優(yōu)先級(jí)的相關(guān)資料,Oracle中使用正則表達(dá)式需先使用REGEXP_LIKE函數(shù)來(lái)匹配字符串,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-06-06
在 Oracle 數(shù)據(jù)庫(kù)中,獲取表的大小可以通過(guò)查詢(xún)數(shù)據(jù)字典視圖 DBA_SEGMENTS、USER_SEGMENTS 或 ALL_SEGMENTS,具體取決于你的權(quán)限范圍和需求,本文通過(guò)代碼示例介紹的非常詳細(xì),需要的朋友可以參考下2025-04-04
Oracle數(shù)據(jù)導(dǎo)入導(dǎo)出imp/exp就相當(dāng)于oracle數(shù)據(jù)還原與備份。exp命令可以把數(shù)據(jù)從遠(yuǎn)程數(shù)據(jù)庫(kù)服務(wù)器導(dǎo)出到本地的dmp文件，imp命令可以把dmp文件從本地導(dǎo)入到遠(yuǎn)處的數(shù)據(jù)庫(kù)服務(wù)器中。2014-08-08
ORACLE 11g安裝中出現(xiàn)xhost: unable to open display問(wèn)題解決步驟
這篇文章主要給大家介紹了關(guān)于在ORACLE 11g安裝中出現(xiàn)xhost: unable to open display問(wèn)題的解決方法，文中介紹的非常詳細(xì)，對(duì)大家具有一定的參考價(jià)值，需要的朋友們下面來(lái)一起看看吧。2017-03-03
在Oracle數(shù)據(jù)庫(kù)中，Oracle系統(tǒng)變量函數(shù)是經(jīng)常會(huì)使用到的函數(shù)，分享下Oracle系統(tǒng)變量函數(shù)的用法。希望對(duì)大家有所幫助2014-08-08
Oracle8i和Microsoft SQL Server比較
Oracle8i和Microsoft SQL Server比較...2007-03-03