oracle中怎么利用dbms_xplan获取执行计划

发布时间：2021-07-29 14:31:49 作者：Leah
来源：亿速云阅读：150

这期内容当中小编将会给大家带来有关oracle中怎么利用dbms_xplan获取执行计划，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

dbms_xplan包

display:返回存储在plan_table中的执行计划
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 :默认为plan_table
statement_id:默认null将显示最近插入plan_table中的执行计划，指定sql语句的名字( plan_table.STATEMENT_ID ),explain plan时此参数可选
format:提供那些输出，基本的
basic:显示最少的信息（操作+操作对象）
typical:显示大部分（除去别名，outline和字段投影外所有信息），defualt typical
serial:也typical一样，不显示并行操作
all:除去outline外都显示
advanced:都显示
修饰符+，-，bytes,cost,alias(查询块和别名),note(note部分显示)，outline显示,parallel（是否显示并行信息）
partition(控制分区),peeked_binds(窥视bind,explain plan for不会窥视，而plan_table 一般是通过explain for获取，也可以insert 所以这个修饰符没对于display没什么意义)，predicate(谓词显示 access,filter部分),projection(投影信息),rows,remote(远程执行sql语句显示)

filter_preds：基于plan_table的一个where条件,默认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信息，实际就是一堆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参数使用

SQL> explain plan for select count(*) from t1;刚才plan_table中已经存储了一个plan，现在再生成一个

Explained.

SQL> select * from table(dbms_xplan.display);实际就是查询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'));实际就是查询plan_table，现在加了一个where plan_id=233

8 rows selected.

display_cursor：从libary cache中获取执行计划,(10g才可用)
sql_id;返回sql语句的父cursor，默认null,返回当前session最后一条sql语句执行计划
cursor_chilid_no：子游标number，默认为null,表示父游下所有子游标都会返回
format:包含display中的所有（加修饰符）,另外多了一个运行时统计信息（parameter statistics_level=all,or use hint gahter_plan_statistics），默认typical

使用display_cursor需要v$session,v$sql,v$sql_plan,v$sql_plan_statistics_all 的select 权限，role=select_catalog_role 或拥有select any dictionary系统权限就可以了

format针对运行时统计信息多的修饰符
allstats=iostats+memstats
iostats=i/o信息显示
last=默认显示所有执行计算过的统计，要是指定了last只显示最后一次执行的统计信息
memstats:pga信息
runstats_last=iostats last 10gr1
runstats_tot=iostats 10gr1

运行时统
starts:操作执行次数,a-rows:操作返回真实记录数，a-time:操作执行真实时间（HH：MM：SS.FF）
i/o统计:
buffers:逻辑读数量
reads:物理读数量
writes:物理写数量
从这里有些有用的信息,a-rows,与buffers，a-rows/buffers表示返回每行的逻辑读次数

a-rows/buffers<5个logical reads表示访问路径不错
a-rows/buffers between 10 and 15 logical reads，表示访问路径可以接受
a-rows/buffers>15or20 logical reads，表示路径不好，可以优化
另外a-rows与e-rows可以简单的识别错误的评估（统计信息）e-rows评估基数（card）,a-rows 真实基数（card）,但有时候要考虑相关类型操作（执行计划用一个child控制其他child，会次执行，而非相关型只执行一次）比如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'));加这个是最后一次执行的统计信息

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执行几次（且共享了这个子游标），里面信息就是这几次的

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执行了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都需要运行时统计信息
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中提取信息执行计划
sql_id:父游标id
plan_hash_value:执行计划hash value，默认null（表示sql_id下所有执行计划都会输出）
db_id:返回执行sql语句所在db,默认null（返回当恰db）
format:与display的全兼。默认typical
与display_cursor区别display_cursor来自shared pool libirary cache，而display_awr来自awr特性，最后保存在 dba_hist类的view里（ASH特性），所以flush shared pool后
display cursor无法获得执行计划了，而display_awr还可以（存在固定view中）

SQL> @ F:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\awrrpt.sql生成awr报告

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也可以

系统已更改。

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 这种方式不太准确尤其有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

经过trace select * from table(dbms_xplan.display) 可以发现关键一步 (select max(timestamp)表示默认从plan_table取时间最近的执行计划信息
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中将执行计划 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;

因为 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获取执行计划了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注亿速云行业资讯频道。

oracle中怎么利用dbms_xplan获取执行计划

相关阅读