主頁(yè) > 知識(shí)庫(kù) > Oracle中使用DBMS_XPLAN處理執(zhí)行計(jì)劃詳解

Oracle中使用DBMS_XPLAN處理執(zhí)行計(jì)劃詳解

熱門標(biāo)簽:騰訊地圖標(biāo)注要費(fèi)用嗎 車瑪仕極限運(yùn)動(dòng)場(chǎng)所地圖標(biāo)注 外呼電話系統(tǒng)用卡嗎 電渠外呼系統(tǒng) 高德地圖標(biāo)注公司名字大全 七日殺a19.5全地圖標(biāo)注 N個(gè)你智能電銷機(jī)器人 地圖標(biāo)注怎么保存 廣東營(yíng)銷智能外呼系統(tǒng)商家

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基線中的語(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ì)劃示例:

復(fù)制代碼 代碼如下:

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)用簽名:

復(fù)制代碼 代碼如下:

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ù)用于控制包含在輸出中的信息類型,官檔的參數(shù)如下:

1.BASIC: 顯示最少的信息,只包括操作類型,ID名稱和選項(xiàng)。
2.TYPICAL: 默認(rèn)值,顯示相關(guān)信息以及某些附加的顯示選項(xiàng),如分區(qū)和并發(fā)使用等。
3.SERIAL: 與TYPICAL類型相似,區(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 – 顯示查詢塊名稱已經(jīng)對(duì)象別名
9.REMOTE – 顯示分布式查詢信息
10.NOTE – 顯示注釋
11.IOSTATS – 顯示游標(biāo)執(zhí)行的IO統(tǒng)計(jì)信息
12.MEMSTATS – 為內(nèi)存密集運(yùn)算如散列聯(lián)結(jié),排序,或一些類型的位圖運(yùn)算顯示內(nèi)存管理統(tǒng)計(jì)信息
13.ALLSTATS – 與'IOSTATS MEMSTATS'等價(jià)
14.LAST – 顯示最后執(zhí)行的執(zhí)行計(jì)劃統(tǒng)計(jì)信息,默認(rèn)顯示為ALL類型,并且可以累積。

以上的參數(shù)同樣適用于解釋計(jì)劃的display方法。

示例部分:

一、使用display_cursor方法查看最近一條語(yǔ)句的執(zhí)行計(jì)劃

復(fù)制代碼 代碼如下:

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ù),物理寫次數(shù)以及運(yùn)算在一行數(shù)據(jù)上耗費(fèi)的運(yùn)行時(shí)間,如果沒有指定該提示,就不會(huì)有A-Rows,A-Time,Buffers這三列信息。

二、獲取某條指定語(yǔ)句的執(zhí)行計(jì)劃

復(fù)制代碼 代碼如下:

SQL> select /*+ gather_plan_statistics */ count(*) from hr.employees where department_id in (90, 100);

  COUNT(*)
----------
         9

-- 通過(guò)v$sql視圖查詢到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ì)劃輸出信息

復(fù)制代碼 代碼如下:

-- 使用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.

-- 并行查詢信息篩選
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.


您可能感興趣的文章:
  • ORACLE數(shù)據(jù)庫(kù)查看執(zhí)行計(jì)劃的方法
  • Oracle中獲取執(zhí)行計(jì)劃的幾種方法分析
  • 查看Oracle的執(zhí)行計(jì)劃一句話命令
  • Oracle中基于hint的3種執(zhí)行計(jì)劃控制方法詳細(xì)介紹
  • Oracle中直方圖對(duì)執(zhí)行計(jì)劃的影響詳解

標(biāo)簽:來(lái)賓 長(zhǎng)沙 大興安嶺 玉樹 棗莊 贛州 遼寧 蘇州

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《Oracle中使用DBMS_XPLAN處理執(zhí)行計(jì)劃詳解》,本文關(guān)鍵詞  Oracle,中,使用,DBMS,XPLAN,處理,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《Oracle中使用DBMS_XPLAN處理執(zhí)行計(jì)劃詳解》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于Oracle中使用DBMS_XPLAN處理執(zhí)行計(jì)劃詳解的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章