导航：首页 > 服务器 >

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

发表于：2025-01-23 作者：千家信息网编辑

千家信息网最后更新 2025年01月23日，这期内容当中小编将会给大家带来有关oracle中怎么利用dbms_xplan获取执行计划，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。dbms_xplan包disp

千家信息网最后更新 2025年01月23日oracle中怎么利用dbms_xplan获取执行计划

这期内容当中小编将会给大家带来有关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获取执行计划了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注行业资讯频道。

很赞哦！