小編給大家分享一下如何使用dbms_xplan查看執(zhí)行計(jì)劃，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

10余年的嫩江網(wǎng)站建設(shè)經(jīng)驗(yàn)，針對(duì)設(shè)計(jì)、前端、開發(fā)、售后、文案、推廣等六對(duì)一服務(wù)，響應(yīng)快，48小時(shí)及時(shí)工作處理。成都全網(wǎng)營(yíng)銷推廣的優(yōu)勢(shì)是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同，自動(dòng)調(diào)整嫩江建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無(wú)論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計(jì)，從而大程度地提升瀏覽體驗(yàn)。成都創(chuàng)新互聯(lián)從事“嫩江網(wǎng)站設(shè)計(jì)”,“嫩江網(wǎng)站推廣”以來(lái)，每個(gè)客戶項(xiàng)目都認(rèn)真落實(shí)執(zhí)行。

概述

dbms_xplan包提供了多樣的，預(yù)定義的方式去解釋執(zhí)行計(jì)劃。sql執(zhí)行計(jì)劃，執(zhí)行時(shí)間等信息都存儲(chǔ)與v$sql_plan,v$sql_plan_statistics_all視圖中。此包運(yùn)行的權(quán)限是執(zhí)行用戶，不是包的擁有者sys。

當(dāng)執(zhí)行diplay_awr時(shí)，需要DBA_HIST_SQL_PLAN, DBA_HIST_SQLTEXT, and V$DATABASE的查詢權(quán)限

當(dāng)執(zhí)行display_cursor時(shí)需要V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL的查詢權(quán)限

當(dāng)執(zhí)行display_sql_plan_baseline時(shí)，需要DBA_SQL_PLAN_BASELINES的查詢權(quán)限

當(dāng)執(zhí)行display_sqlset時(shí)，需要ALL_SQLSET_STATEMENTS and ALL_SQLSET_PLANS的查詢權(quán)限

以上的所有權(quán)限都在SELECT_CATALOG角色中，所以直接賦權(quán)SELECT_CATALOG就可以了。

dbms_xplan中有許多功能，我這里著重研究其中幾個(gè)常用的

SQL> desc dbms_xplan;

…

FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE

 Argument Name Type In/Out Default?

 ------------------------------ ----------------------- ------ --------

 SQL_ID VARCHAR2 IN

 PLAN_HASH_VALUE NUMBER(38) IN     DEFAULT

 DB_ID NUMBER(38) IN     DEFAULT

 FORMAT VARCHAR2 IN     DEFAULT

FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE

 Argument Name Type In/Out Default?

 ------------------------------ ----------------------- ------ --------

 SQL_ID VARCHAR2 IN     DEFAULT

 CURSOR_CHILD_NO NUMBER(38) IN     DEFAULT

 FORMAT VARCHAR2 IN     DEFAULT

FUNCTION DISPLAY_PLAN RETURNS CLOB

 Argument Name Type In/Out Default?

 ------------------------------ ----------------------- ------ --------

 TABLE_NAME VARCHAR2 IN     DEFAULT

 STATEMENT_ID VARCHAR2 IN     DEFAULT

 FORMAT VARCHAR2 IN     DEFAULT

 FILTER_PREDS VARCHAR2 IN     DEFAULT

 TYPE VARCHAR2 IN     DEFAULT

FUNCTION DISPLAY_SQLSET RETURNS DBMS_XPLAN_TYPE_TABLE

 Argument Name Type In/Out Default?

 ------------------------------ ----------------------- ------ --------

 SQLSET_NAME VARCHAR2 IN

 SQL_ID VARCHAR2 IN

 PLAN_HASH_VALUE NUMBER(38) IN     DEFAULT

 FORMAT VARCHAR2 IN     DEFAULT

 SQLSET_OWNER VARCHAR2 IN     DEFAULT

FUNCTION DISPLAY_SQL_PLAN_BASELINE RETURNS DBMS_XPLAN_TYPE_TABLE

 Argument Name Type In/Out Default?

 ------------------------------ ----------------------- ------ --------

 SQL_HANDLE VARCHAR2 IN     DEFAULT

 PLAN_NAME VARCHAR2 IN     DEFAULT

 FORMAT VARCHAR2 IN     DEFAULT

…

dbms_xplan.display

展示執(zhí)行計(jì)劃內(nèi)容

簡(jiǎn)單的執(zhí)行一次display

SQL> explain plan for insert into scott.emp values(1111,'xplan','','','','','','');

Explained.

SQL> select * from table(dbms_xplan.display);

7 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2137789089

---------------------------------------------------------------------------------------------

| Id  | Operation   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |     |  8168 | 16336 | 29   (0)| 00:00:01 |

|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |  8168 | 16336 | 29   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

       1890  recursive calls

40  db block gets

       3635  consistent gets

  0  physical reads

  0  redo size

       1102  bytes sent via SQL*Net to client

523  bytes received via SQL*Net from client

  2  SQL*Net roundtrips to/from client

38  sorts (memory)

  0  sorts (disk)

  7  rows processed

SQL> select * from scott.emp where empno=1111;

no rows selected

—explain plan for ，dbms_xplan.display都沒有執(zhí)行sql。explain plan for是產(chǎn)生sql的執(zhí)行計(jì)劃并保存在PLAN_TABLE表中，dbms_xplan.display從PLAN_TABLE中提取計(jì)劃并展示出來(lái)。

定義

DBMS_XPLAN.DISPLAY(

   table_name    IN  VARCHAR2  DEFAULT 'PLAN_TABLE',

   statement_id  IN  VARCHAR2  DEFAULT  NULL, 

   format            IN  VARCHAR2  DEFAULT  'TYPICAL',

   filter_preds     IN  VARCHAR2 DEFAULT NULL);

table_name：指定的是計(jì)劃的存儲(chǔ)表表名（不是執(zhí)行表的表名）。默認(rèn)是PLAN_TABLE。

statement_id：指定計(jì)劃的statement_id，如果沒有指定，則該值為explain plan的statement_id , 如果沒有expain plan，則該值為最近一次解釋的執(zhí)行計(jì)劃。

format：解釋計(jì)劃的level

? BASIC: Displays the minimum information in the plan—the operation ID, the operation name and its option.展示最少的信息

? TYPICAL: This is the default. Displays the most relevant information in the plan (operation id, name and option, #rows, #bytes and optimizer cost). Pruning, parallel and predicate information are only displayed when applicable. Excludes only PROJECTION, ALIAS and REMOTE SQL information (see below).默認(rèn)值

? SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.沒有并發(fā)信息

? ALL: Maximum user level. Includes information displayed with the TYPICAL level with additional information (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).展示最多的信息，包含了分布式操作的遠(yuǎn)程sql執(zhí)行信息

filter_preds：sql過濾，限制從計(jì)劃表中返回的行。

dbms_xplan.display_awr  

展示在awr中存儲(chǔ)的sql的執(zhí)行計(jì)劃

定義

DBMS_XPLAN.DISPLAY_AWR( 

   sql_id            IN      VARCHAR2,

   plan_hash_value   IN      NUMBER DEFAULT NULL,

   db_id             IN      NUMBER DEFAULT NULL,

   format            IN      VARCHAR2 DEFAULT TYPICAL);

sql_id:在dba_hist_sqltext中可以找到sql_id

plan_hash_value:sql執(zhí)行計(jì)劃的hash值。如果該值被忽略，則函數(shù)返回sql_id中的所有執(zhí)行計(jì)劃

db_id：database_id。如果不知道則該值為V$DATABASE視圖中的database_id，也就是本地?cái)?shù)據(jù)庫(kù)。

format：與display類似。總共有4個(gè)level：BASIC，TYPICAL,SERIAL,ALL。

SQL> select * from scott.emp where rownum<=1;

     EMPNO ENAME      JOB       MGR HIREDATE     SAL       COMM

---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO

----------

      7369 SMITH      CLERK       7902 17-DEC-80     800

20

—手動(dòng)生成快照

SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

PL/SQL procedure successfully completed.

SQL> select sql_id,sql_text from dba_hist_sqltext where sql_text like '%rownum%';

SQL_ID

-------------

SQL_TEXT

--------------------------------------------------------------------------------

6yzbcy3x0yr1j

insert into wrh$_dispatcher   (snap_id, dbid, instance_number,   name, serial#,

fsbqktj5vw6n9

select next_run_date, obj#, run_job, sch_job from (select decode(bitand(a.flags,

cv959u044n88s

select 1 from sys.aq$_subscriber_table where rownum < 2 and subscriber_id <> 0 a

…

SQL_ID

-------------

SQL_TEXT

--------------------------------------------------------------------------------

bd3tcy3ar02px

select * from scott.emp where rownum<=1

7 rows selected.

SQL> 

—指定awr中存在sql

SQL> select * from table(dbms_xplan.display_awr('bd3tcy3ar02px'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID bd3tcy3ar02px

--------------------

select * from scott.emp where rownum<=1

Plan hash value: 1973284518

---------------------------------------------------------------------------

| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time   |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |   |   |   | 2 (100)|   |

|   1 |  COUNT STOPKEY   |   |   |   |       |   |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|   2 |   TABLE ACCESS FULL| EMP  |    15 |  1305 | 2   (0)| 00:00:01 |

---------------------------------------------------------------------------

Note

-----

   - dynamic sampling used for this statement (level=2)

18 rows selected.

SQL> 

dbms_xplan.display_cursor

展示cursor中的執(zhí)行計(jì)劃

定義

DBMS_XPLAN.DISPLAY_CURSOR(

   sql_id           IN  VARCHAR2  DEFAULT  NULL,

   cursor_child_no  IN  NUMBER    DEFAULT  0, 

   format           IN  VARCHAR2  DEFAULT  ‘TYPICAL');

sql_id

cursor_child_no:子游標(biāo)標(biāo)志。如果沒有指定，則展示sql_id下的所有執(zhí)行計(jì)劃

format

默認(rèn)值執(zhí)行

SQL> select * from scott.emp where rownum<=2;

     EMPNO ENAME      JOB       MGR HIREDATE     SAL       COMM

---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO

----------

      7369 SMITH      CLERK       7902 17-DEC-80     800

20

      7499 ALLEN      SALESMAN       7698 20-FEB-81   1600        300

30

—默認(rèn)查詢了當(dāng)前session最后一次執(zhí)行sql

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID 90ud69jbjz75c, child number 0

-------------------------------------

select * from scott.emp where rownum<=2

Plan hash value: 1973284518

---------------------------------------------------------------------------

| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time   |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |   |   |   | 2 (100)|   |

|*  1 |  COUNT STOPKEY   |   |   |   |       |   |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|   2 |   TABLE ACCESS FULL| EMP  |    15 |  1305 | 2   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<=2)

Note

-----

   - dynamic sampling used for this statement (level=2)

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

23 rows selected.

SQL> 

指定sql_id查找plan，首先要找到sql_id

SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%rownum<=2%';

SQL_ID

-------------

SQL_TEXT

--------------------------------------------------------------------------------

75gpskbx0uk8w

select sql_id,sql_text from v$sqlarea where sql_text like '%rownum<=2%'

90ud69jbjz75c

select * from scott.emp where rownum<=2

SQL> select * from table(dbms_xplan.display_cursor('90ud69jbjz75c'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID 90ud69jbjz75c, child number 0

-------------------------------------

select * from scott.emp where rownum<=2

Plan hash value: 1973284518

---------------------------------------------------------------------------

| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time   |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |   |   |   | 2 (100)|   |

|*  1 |  COUNT STOPKEY   |   |   |   |       |   |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|   2 |   TABLE ACCESS FULL| EMP  |    15 |  1305 | 2   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<=2)

Note

-----

   - dynamic sampling used for this statement (level=2)

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

23 rows selected.

SQL> 

如果sql不在內(nèi)存中，則不能通過cursor方式查詢

SQL> select * from table(dbms_xplan.display_cursor('90ud69jbjz75c'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID: 90ud69jbjz75c, child number: 0 cannot be found

這時(shí)就可以通過生成快照的方式從awr中查詢plan

以上是“如何使用dbms_xplan查看執(zhí)行計(jì)劃”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

名稱欄目：如何使用dbms_xplan查看執(zhí)行計(jì)劃
文章轉(zhuǎn)載：http://jinyejixie.com/article26/psijcg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計(jì)公司、域名注冊(cè)、移動(dòng)網(wǎng)站建設(shè)、網(wǎng)站改版、營(yíng)銷型網(wǎng)站建設(shè)、網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)