oracle中怎么利用dbms_xplan獲取執(zhí)行計(jì)劃

發(fā)布時間：2021-07-29 14:31:49 來源：億速云閱讀：149 作者：Leah 欄目：建站服務(wù)器

這期內(nèi)容當(dāng)中小編將會給大家?guī)碛嘘P(guān)oracle中怎么利用dbms_xplan獲取執(zhí)行計(jì)劃，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

dbms_xplan包

display:返回存儲在plan_table中的執(zhí)行計(jì)劃
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
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

table_name :默認(rèn)為plan_table
statement_id:默認(rèn)null將顯示最近插入plan_table中的執(zhí)行計(jì)劃，指定sql語句的名字( plan_table.STATEMENT_ID ),explain plan時此參數(shù)可選
format:提供那些輸出，基本的
basic:顯示最少的信息（操作+操作對象）
typical:顯示大部分（除去別名，outline和字段投影外所有信息），defualt typical
serial:也typical一樣，不顯示并行操作
all:除去outline外都顯示
advanced:都顯示
修飾符+，-，bytes,cost,alias(查詢塊和別名),note(note部分顯示)，outline顯示,parallel（是否顯示并行信息）
partition(控制分區(qū)),peeked_binds(窺視bind,explain plan for不會窺視，而plan_table 一般是通過explain for獲取，也可以insert 所以這個修飾符沒對于display沒什么意義)，predicate(謂詞顯示 access,filter部分),projection(投影信息),rows,remote(遠(yuǎn)程執(zhí)行sql語句顯示)

filter_preds：基于plan_table的一個where條件,默認(rèn)null 表示輸出最近的plan_table,10gR2 顯示

基本使用

SQL> select count(*) from t1;

COUNT(*)
----------
3000

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Error: cannot fetch last explain plan from PLAN_TABLE

需要explain plan 存儲plan_table(也可以建立成別的名字)

SQL> explain plan for select count(*) from t1;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    10 |     6   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |      |     1 |    10 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 3000 | 30000 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
- SQL plan baseline "SYS_SQL_PLAN_b98b6a04616acf47" used for this statement

SQL> explain plan set statement_id='xh_test' for select * from t1;

Explained.

SQL> select * from table(dbms_xplan.display(null,'xh_test'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 3000 |   111K|     6   (0)| 00:00:01 |
|   1 | TABLE ACCESS FULL| T1   | 3000 |   111K|     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL>

SQL> select id,operation ,object_name from plan_table where statement_id='xh_test';

        ID OPERATION                      OBJECT_NAME
---------- ------------------------------ ------------------------------
         0 SELECT STATEMENT
         1 TABLE ACCESS                   T1

SQL> explain plan for select * from t1;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'basic'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

----------------------------------
| Id | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT |      |
|   1 | TABLE ACCESS FULL| T1   | 只顯示最基本的信息
----------------------------------

8 rows selected.

SQL> select * from table(dbms_xplan.display(null,null,'TYPICAl'));

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 3000 |   111K|     6   (0)| 00:00:01 |        平常看的信息cost(%cpu) 6(2)表示cpu占了cost的百分比
|   1 | TABLE ACCESS FULL| T1   | 3000 |   111K|     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL> select * from table(dbms_xplan.display(null,null,'all'));

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / T1@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "COUNTRY"[VARCHAR2,16], "T1"."CONTINENT"[VARCHAR2,9], ~~~~~~~~~~~~~~~~~~~~顯示了字段（可以看到字段定義）
"T1"."PLANET"[CHARACTER,5], "T1"."ONE"[NUMBER,22]

19 rows selected.

SQL> select * from table(dbms_xplan.display(null,null,'advanced')); ~~~顯示了所有信息

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / T1@SEL$1

Outline Data
-------------

/*+
      BEGIN_OUTLINE_DATA                              ~~~顯示了outline信息，實(shí)際就是一堆hints，并不是表示使用了outline，只是顯示如果做成outline的話包含哪些hints
      FULL(@"SEL$1" "T1"@"SEL$1")                     可以理解為這些hint可以固定這個plan（使用outline的話note中會顯示use outline xx）
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "COUNTRY"[VARCHAR2,16], "T1"."CONTINENT"[VARCHAR2,9],
"T1"."PLANET"[CHARACTER,5], "T1"."ONE"[NUMBER,22]

33 rows selected.

組合一些修飾符使用

SQL> select * from table(dbms_xplan.display(null,null,'typical -rows'));

------------------------------------------------------------------
| Id | Operation         | Name | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |   111K|     6   (0)| 00:00:01 |
|   1 | TABLE ACCESS FULL| T1   |   111K|     6   (0)| 00:00:01 |
------------------------------------------------------------------

8 rows selected.

SQL> select * from table(dbms_xplan.display(null,null,'typical +outline'));

Outline Data
-------------

/*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "T1"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.1.0.6')
      OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
      IGNORE_OPTIM_××DED_HINTS
      END_OUTLINE_DATA
*/

22 rows selected.

filter_preds參數(shù)使用

SQL> explain plan for select count(*) from t1;剛才plan_table中已經(jīng)存儲了一個plan，現(xiàn)在再生成一個

Explained.

SQL> select * from table(dbms_xplan.display);實(shí)際就是查詢plan_table

Note

13 rows selected.

SQL> select count(*) from plan_table;

COUNT(*)
----------
5

SQL> select distinct plan_id from plan_table;

   PLAN_ID
----------
       233
       234

SQL> select * from table(dbms_xplan.display(null,null,null,'plan_id=233'));實(shí)際就是查詢plan_table，現(xiàn)在加了一個where plan_id=233

8 rows selected.

display_cursor：從libary cache中獲取執(zhí)行計(jì)劃,(10g才可用)
sql_id;返回sql語句的父cursor，默認(rèn)null,返回當(dāng)前session最后一條sql語句執(zhí)行計(jì)劃
cursor_chilid_no：子游標(biāo)number，默認(rèn)為null,表示父游下所有子游標(biāo)都會返回
format:包含display中的所有（加修飾符）,另外多了一個運(yùn)行時統(tǒng)計(jì)信息（parameter statistics_level=all,or use hint gahter_plan_statistics），默認(rèn)typical

使用display_cursor需要v$session,v$sql,v$sql_plan,v$sql_plan_statistics_all 的select 權(quán)限，role=select_catalog_role 或擁有select any dictionary系統(tǒng)權(quán)限就可以了

format針對運(yùn)行時統(tǒng)計(jì)信息多的修飾符
allstats=iostats+memstats
iostats=i/o信息顯示
last=默認(rèn)顯示所有執(zhí)行計(jì)算過的統(tǒng)計(jì)，要是指定了last只顯示最后一次執(zhí)行的統(tǒng)計(jì)信息
memstats:pga信息
runstats_last=iostats last 10gr1
runstats_tot=iostats 10gr1

運(yùn)行時統(tǒng)
starts:操作執(zhí)行次數(shù),a-rows:操作返回真實(shí)記錄數(shù)，a-time:操作執(zhí)行真實(shí)時間（HH：MM：SS.FF）
i/o統(tǒng)計(jì):
buffers:邏輯讀數(shù)量
reads:物理讀數(shù)量
writes:物理寫數(shù)量
從這里有些有用的信息,a-rows,與buffers，a-rows/buffers表示返回每行的邏輯讀次數(shù)

a-rows/buffers<5個logical reads表示訪問路徑不錯
a-rows/buffers between 10 and 15 logical reads，表示訪問路徑可以接受
a-rows/buffers>15or20 logical reads，表示路徑不好，可以優(yōu)化
另外a-rows與e-rows可以簡單的識別錯誤的評估（統(tǒng)計(jì)信息）e-rows評估基數(shù)（card）,a-rows 真實(shí)基數(shù)（card）,但有時候要考慮相關(guān)類型操作（執(zhí)行計(jì)劃用一個child控制其他child，會次執(zhí)行，而非相關(guān)型只執(zhí)行一次）比如nl

SQL> select /*+gather_plan_statistics*/count(*) from t1;

COUNT(*)
----------
1

SQL> select sql_id,child_number from v$sql where sql_text='select /*+gather_plan_statistics*/count(*) from t1';

SQL_ID CHILD_NUMBER
------------- ------------
87da3j7f1yd4a 0

SQL> select * from table(dbms_xplan.display_cursor('87da3j7f1yd4a',0,'allstats last'));加這個是最后一次執(zhí)行的統(tǒng)計(jì)信息

Plan hash value: 3724264953

-------------------------------------------------------------------------------------
| Id | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 | SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       7 |
-------------------------------------------------------------------------------------

13 rows selected.

SQL> select * from table(dbms_xplan.display_cursor('87da3j7f1yd4a',0,'allstats')); 這個是輸出總的這個sql執(zhí)行幾次（且共享了這個子游標(biāo)），里面信息就是這幾次的

Plan hash value: 3724264953

-------------------------------------------------------------------------------------
| Id | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 | SORT AGGREGATE    |      |      2 |      1 |      2 |00:00:00.01 |      14 |        可以看出這個sql執(zhí)行了2次
|   2 |   TABLE ACCESS FULL| T1   |      2 |      1 |      2 |00:00:00.01 |      14 |
-------------------------------------------------------------------------------------

13 rows selected.

SQL> select /*+gather_plan_statistics*/* from t1 order by 1;

A
----------
1

SQL> select sql_id,child_number from v$sql where sql_text='select /*+gather_plan_statistics*/* from t1 order by 1';

SQL_ID CHILD_NUMBER
------------- ------------
1x49dbnz86s5f 0

SQL> select * from table(dbms_xplan.display_cursor('1x49dbnz86s5f',0,'memstats last'));

Plan hash value: 2148421099

------------------------------------------------------------------------------------------------------
| Id | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------
|   1 | SORT ORDER BY     |      |      1 |      1 |      1 |00:00:00.01 | 2048 | 2048 | 2048 (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       |       |          |
------------------------------------------------------------------------------------------------------

13 rows selected.

SQL>

可以看到iostats,memesats都需要運(yùn)行時統(tǒng)計(jì)信息
SQL> select * from table(dbms_xplan.display_cursor('4hrkxzxp0uj5q',0,'iostats last'));

Plan hash value: 2148421099

--------------------------------------------
| Id | Operation          | Name | E-Rows |
--------------------------------------------
|   1 | SORT ORDER BY     |      |      1 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |
--------------------------------------------

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system l

SQL> select * from table(dbms_xplan.display_cursor('4hrkxzxp0uj5q',0,'memstats last'));

Plan hash value: 2148421099

-----------------------------------------------------------------------
| Id | Operation          | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
|   1 | SORT ORDER BY     |      |      1 | 2048 | 2048 | 2048 (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |       |       |          |
-----------------------------------------------------------------------

19 rows selected.

由于explain plan for 不會peeking（按5%算選擇率）,而display_cursor從library cache中提取，所以 peeked_binds這個修飾符有些用處，我們可以看到query opitimzer peeking的值是多少（11g會多次peeking）
SQL> variable a number
SQL> execute :a:=1

PL/SQL procedure successfully completed.

SQL> select * from t1 where a=:a;

A
----------
1

SQL> select sql_id,child_number from v$sql where sql_text='select * from t1 where a=:a';

SQL_ID CHILD_NUMBER
------------- ------------
b980nwgpprj6w 0

SQL> select * from table(dbms_xplan.display_cursor('b980nwgpprj6w',0,'typical +peeked_binds'));

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |     3 (100)|          |
|* 1 | TABLE ACCESS FULL| T1   |     1 |     3 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :A (NUMBER): 1

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=:A)

display_awr：可以從awr中提取信息執(zhí)行計(jì)劃
sql_id:父游標(biāo)id
plan_hash_value:執(zhí)行計(jì)劃hash value，默認(rèn)null（表示sql_id下所有執(zhí)行計(jì)劃都會輸出）
db_id:返回執(zhí)行sql語句所在db,默認(rèn)null（返回當(dāng)恰db）
format:與display的全兼。默認(rèn)typical
與display_cursor區(qū)別display_cursor來自shared pool libirary cache，而display_awr來自awr特性，最后保存在 dba_hist類的view里（ASH特性），所以flush shared pool后
display cursor無法獲得執(zhí)行計(jì)劃了，而display_awr還可以（存在固定view中）

SQL> @ F:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\awrrpt.sql生成awr報(bào)告

6aq34nj2zb2n7 select col#, grantee#, privilege#, max(mod(nvl(option$, 0), 2)) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
grantee# order by col#, grantee#

用 SQL_ID 6aq34nj2zb2n7

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID 6aq34nj2zb2n7
--------------------
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from
objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
grantee# order by col#, grantee#

Plan hash value: 2874733959

---------------------------------------------------
| Id | Operation                    | Name       |
---------------------------------------------------
|   0 | SELECT STATEMENT             |            |
|   1 | SORT GROUP BY               |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| OBJAUTH$   |
|   3 |    INDEX RANGE SCAN          | I_OBJAUTH1 |
---------------------------------------------------

Note
-----
- rule based optimizer used (consider using cbo)

已選擇21行。

SQL> alter system flush shared_pool;～～～刷新shared pool也可以

系統(tǒng)已更改。

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

Plan hash value: 2874733959

Note
-----
- rule based optimizer used (consider using cbo)

已選擇21行。

9i 獲取plan，從library cache中,9i中沒有display_cursor，而用explain plan for 這種方式不太準(zhǔn)確尤其有bind的時候，此時用下面的方法

[oracle@smxdmk1 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@smxdmk1 admin]$ sqlplus '/ as sysdba'

SQL> @utlxplan.sql

Table created.

SQL> create table t (a int);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> commit;

Commit complete.
SQL> select count(*) from t;

COUNT(*)
----------
1

SQL> select hash_value,child_number,address from v$sql where sql_text='select count(*) from t';

HASH_VALUE CHILD_NUMBER ADDRESS
---------- ------------ ----------------
2816538551 0 00000001311BC490

SQL> INSERT INTO plan_table ( operation, options,
2                              object_node, object_owner, object_name, optimizer,
3                          search_columns, id, parent_id, position, cost,
4                                  cardinality, bytes, other_tag, partition_start,
5                              partition_stop, partition_id, other, distribution,
6                              cpu_cost, io_cost, temp_space, access_predicates,
7                              filter_predicates)
8      SELECT
9             operation, options, object_node, object_owner, object_name,
10            optimizer, search_columns, id, parent_id, position, cost,
11            cardinality, bytes, other_tag, partition_start, partition_stop,
12            partition_id, other, distribution, cpu_cost, io_cost, temp_space,
13            access_predicates, filter_predicates
14     FROM v$sql_plan
15     WHERE address = '00000001311BC490'
16     AND hash_value = 2816538551
17     AND child_number = 0;

3 rows created.

SQL> commit;

Commit complete.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Error: cannot fetch last explain plan

SQL> delete plan_table;

3 rows deleted.

SQL> commit;

Commit complete.

SQL> INSERT INTO plan_table ( timestamp,operation, options,
2                              object_node, object_owner, object_name, optimizer,
3                          search_columns, id, parent_id, position, cost,
4                                  cardinality, bytes, other_tag, partition_start,
5                              partition_stop, partition_id, other, distribution,
6                              cpu_cost, io_cost, temp_space, access_predicates,
7                              filter_predicates)
8      SELECT
9             to_timestamp(sysdate),operation, options, object_node, object_owner, object_name,
10            optimizer, search_columns, id, parent_id, position, cost,
11            cardinality, bytes, other_tag, partition_start, partition_stop,
12            partition_id, other, distribution, cpu_cost, io_cost, temp_space,
13            access_predicates, filter_predicates
14     FROM v$sql_plan
15     WHERE address = '00000001311BC490'
   AND hash_value = 2816538551
   AND child_number = 0;

16 17
3 rows created.

SQL> SQL> commit;

Commit complete.

SQL> select * from table(dbms_xplan.display(null,'xh'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Error: cannot fetch plan for statement_id 'xh'SQL> alter session set events '10046 trace name context forever ,level 12';

SQL> alter session set events '10046 trace name context forever ,level 12';

Session altered.

SQL> select * from table(dbms_xplan.display(null,'xh'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Error: cannot fetch plan for statement_id 'xh'

SQL> alter session set events '10046 trace name context off';

Session altered.

PARSING IN CURSOR #2 len=2152 dep=1 uid=0 ct=3 lid=0 tim=1240937193104553 hv=1438696113 ad='33f697b0'
SELECT /* EXEC_FROM_DBMS_XPLAN */ id, position, level , operation, options, object_name , cardinality, bytes, temp_space, cost, io_cost, cpu_cost ,decode(partition_sta
rt, 'ROW LOCATION',
              'ROWID', decode(partition_start, 'KEY', 'KEY',
              decode(partition_start, 'KEY(INLIST)', 'KEY(I)',
              decode(substr(partition_start, 1, 6),
              'NUMBER', substr(substr(partition_start, 8, 10), 1,
              length(substr(partition_start, 8, 10))-1),
              decode(partition_start,null,' ',partition_start))))),
              decode(partition_stop, 'ROW LOCATION', 'ROW L',
              decode(partition_stop, 'KEY', 'KEY', decode(partition_stop,
              'KEY(INLIST)', 'KEY(I)', decode(substr(partition_stop, 1, 6),
              'NUMBER', substr(substr(partition_stop, 8, 10), 1,
              length(substr(partition_stop, 8, 10))-1),
              decode(partition_stop,null,' ',partition_stop))))), object_node, other_tag, decode(distribution, null,' ',
              decode(distribution, 'PARTITION (ROWID)', 'PART (RID)',
              decode(distribution, 'PARTITION (KEY)', 'PART (KEY)',
              decode(distribution, 'ROUND-ROBIN', 'RND-ROBIN',
              decode(distribution, 'BROADCAST', 'BROADCAST',
              substr(distribution, 1, 12)))))) , access_predicates, filter_predicates , null from PLAN_TABLE start with id = 0
            and timestamp >= (select max(timestamp)
                               from PLAN_TABLE where id=0 and statement_id = 'xh' and nvl(statement_id, ' ') not like 'SYS_LE%')
            and nvl(statement_id, ' ') not like 'SYS_LE%' and statement_id = 'xh' connect by (prior id = parent_id
                      and prior nvl(statement_id, ' ') =
                                nvl(statement_id, ' ')
                      and prior timestamp <= timestamp)
                  or (prior nvl(object_name, ' ') like 'SYS_LE%'
                      and prior nvl(object_name, ' ') =
                                 nvl(statement_id, ' ')
                      and id = 0 and prior timestamp <= timestamp)
          order siblings by id

經(jīng)過trace select * from table(dbms_xplan.display) 可以發(fā)現(xiàn)關(guān)鍵一步 (select max(timestamp)表示默認(rèn)從plan_table取時間最近的執(zhí)行計(jì)劃信息
SQL> explain plan for select * from t;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id | Operation            | Name       | Rows | Bytes | Cost |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 | TABLE ACCESS FULL   | T           |       |       |       |
--------------------------------------------------------------------

Note: rule based optimization

9 rows selected.

SQL> select timestamp from plan_table;

TIMESTAMP
---------
08-APR-10

08-APR-10

SQL> select to_char(timestamp,'YYYY-MM-DD HH24:mi:ss') from plan_table;

TO_CHAR(TIMESTAMP,'
-------------------
2010-04-08 17:44:31

2010-04-08 17:44:31

SQL> SQL>   INSERT INTO plan_table ( timestamp,operation, options,
2                                  object_node, object_owner, object_name, optimizer,
3                              search_columns, id, parent_id, position, cost,
4                                      cardinality, bytes, other_tag, partition_start,
                                partition_stop, partition_id, other, distribution,
5    6                                  cpu_cost, io_cost, temp_space, access_predicates,
7                                  filter_predicates)
8          SELECT
9                 to_timestamp(sysdate),operation, options, object_node, object_owner, object_name,
10               optimizer, search_columns, id, parent_id, position, cost,
11               cardinality, bytes, other_tag, partition_start, partition_stop,
12               partition_id, other, distribution, cpu_cost, io_cost, temp_space,
13               access_predicates, filter_predicates
14        FROM v$sql_plan
15        WHERE address = '00000001311BC490'
16     AND hash_value = 2816538551
17     AND child_number = 0;

3 rows created.

SQL>
SQL> commit;

Commit complete.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id | Operation            | Name       | Rows | Bytes | Cost |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 | SORT AGGREGATE      |             |       |       |       |
|   2 |   TABLE ACCESS FULL | T           |       |       |       |
--------------------------------------------------------------------

Note: rule based optimization

10 rows selected.

SQL> select * from t;

A
----------
1

SQL> select hash_value,child_number,address from v$sql where sql_text='select * from t';

HASH_VALUE CHILD_NUMBER ADDRESS
---------- ------------ ----------------
520543201 0 0000000133F1E708

INSERT INTO plan_table ( timestamp,statement_id,operation, options,
                            object_node, object_owner, object_name, optimizer,
                        search_columns, id, parent_id, position, cost,
                                cardinality, bytes, other_tag, partition_start,
                            partition_stop, partition_id, other, distribution,
                            cpu_cost, io_cost, temp_space, access_predicates,
                            filter_predicates)
    SELECT
           to_timestamp(sysdate),'xh',operation, options, object_node, object_owner, object_name,
          optimizer, search_columns, id, parent_id, position, cost,
          cardinality, bytes, other_tag, partition_start, partition_stop,
          partition_id, other, distribution, cpu_cost, io_cost, temp_space,
          access_predicates, filter_predicates
   FROM v$sql_plan
   WHERE address = '0000000133F1E708'
   AND hash_value = 520543201
   AND child_number = 0;

SQL>
SQL> INSERT INTO plan_table ( timestamp,statement_id,operation, options,
2                              object_node, object_owner, object_name, optimizer,
3                          search_columns, id, parent_id, position, cost,
4                                  cardinality, bytes, other_tag, partition_start,
5                              partition_stop, partition_id, other, distribution,
6                              cpu_cost, io_cost, temp_space, access_predicates,
7                              filter_predicates)
8      SELECT
9             to_timestamp(sysdate),'xh',operation, options, object_node, object_owner, object_name,
10            optimizer, search_columns, id, parent_id, position, cost,
11            cardinality, bytes, other_tag, partition_start, partition_stop,
12            partition_id, other, distribution, cpu_cost, io_cost, temp_space,
13            access_predicates, filter_predicates
14     FROM v$sql_plan
15     WHERE address = '0000000133F1E708'
16     AND hash_value = 520543201
17     AND child_number = 0;

2 rows created.

SQL>
SQL> commit;

Commit complete.

SQL> select * from table(dbms_xplan.display(null,'xh'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Note: rule based optimization

9 rows selected.

SQL>

10g ，11g 要從libary cache中將執(zhí)行計(jì)劃 insert到plan_table ，要加上plan_id

INSERT INTO plan_table ( plan_id,statement_id,operation, options,
                            object_node, object_owner, object_name, optimizer,
                        search_columns, id, parent_id, position, cost,
                                cardinality, bytes, other_tag, partition_start,
                            partition_stop, partition_id, other, distribution,
                            cpu_cost, io_cost, temp_space, access_predicates,
                            filter_predicates)
    SELECT
           to_timestamp(sysdate),'xh',operation, options, object_node, object_owner, object_name,
          optimizer, search_columns, id, parent_id, position, cost,
          cardinality, bytes, other_tag, partition_start, partition_stop,
          partition_id, other, distribution, cpu_cost, io_cost, temp_space,
          access_predicates, filter_predicates
   FROM v$sql_plan
   WHERE address = '0000000133F1E708'
   AND hash_value = 520543201
   AND child_number = 0;

因?yàn)?trace select * from table(dbms_xplan.display)
SQL> SELECT /*+ opt_param('parallel_execution_enabled','false') */
2                           /* EXEC_FROM_DBMS_XPLAN */ id, position, depth, operation, options, object_name, cardinality, bytes, temp_space, cost, io_cost, cpu_cost , time, partition_start, partition_stop, object_node, other_tag, distribution, projection, access_predicates, filter_predicates , other, qblock_name, object_alias, nvl(other_xml, remarks) other_xml, null sql_profile, null sql_plan_baseline, null, null, null, null, null, null, null,
3                              null, null, null, null, null,
4                              null, null, null, null from PLAN_TABLE where plan_id = (select max(plan_id)
5                                          from PLAN_TABLE where id=0 and statement_id = 'xh')
6                         order by id
7 /

上述就是小編為大家分享的oracle中怎么利用dbms_xplan獲取執(zhí)行計(jì)劃了，如果剛好有類似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道。

向AI問一下細(xì)節(jié)

oracle中怎么利用dbms_xplan獲取執(zhí)行計(jì)劃

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽