链接：http://blog.itpub.net/28602568/viewspace-1771776/

估算---> plan_table[$]中读取

1）Toad、PL/SQL Developer工具

2）explain plan for select 1 from dual; --> SELECT * FROM plan_table/*或plan_table$表*/; /*plan_table和plan_table$区别*/

3）SET AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

4) 10046、10053....事件

真实---> 从内存v$..视图得到的执行计划是真实的

1)select * from table(dbms_xplan.display_cursor('a3s3279q7xaxs',1,'advanced')); -->默认child_number=0,advanced选项带有高级执行计划hint...,

select * from table(dbms_xplan.display_awr('sql_id')); --> AWR

2)如下sql可实现通过查询v$sql_plan真实执行计划来获取类似plsql F5快捷键格式的信息。

select lpad(' ',
(case
when p.DEPTH = 0 or DEPTH is null then
1
else
DEPTH
end),
' ') || p.OPERATION || ' ' || options || ' ' || p.OPTIMIZER description,
p.OBJECT_OWNER,
object_name,
object_type,
cost,
p.CARDINALITY,
p.BYTES,
DEPTH
from v$sql_plan p
where p.SQL_ID = 'a3s3279q7xaxs'
and child_number = 1
order by p.id;

显示如下：

description	OBJECT_OWNER	OBJECT_NAME	object_type	cost	CARDINALITY	BYTES	DEPTH
MERGE STATEMENT ALL_ROWS				528			0
MERGE	FWSB	CS_ORDER_BATCH_CHG_ACCE					1
VIEW							2
NESTED LOOPS							3
NESTED LOOPS				528	1	297	4
VIEW				526	1	226	5
SORT ORDER BY				526	1	107	6
WINDOW SORT PUSHED RANK				526	1	107	7
NESTED LOOPS							8
NESTED LOOPS				524	1	107	9
HASH JOIN				276	124	11532	10
NESTED LOOPS							11
NESTED LOOPS				223	78	4992	12
SORT UNIQUE				52	19	323	13
TABLE ACCESS BY INDEX ROWID	FWSB	CS_ORDER_BATCH_CHG_ACCE	TABLE	52	19	323	14
INDEX RANGE SCAN	FWSB	IDX_COBCA_OPERATOR	INDEX	11	986		15
INDEX RANGE SCAN	FWSB	INDEX_ORDER_ID	INDEX	2	23		13
TABLE ACCESS BY INDEX ROWID	FWSB	CS_ORDER_ITEM	TABLE	17	4	188	12
TABLE ACCESS BY INDEX ROWID	FWSB	CS_ORDER_BATCH_CHG_ACCE	TABLE	52	158	4582	11
INDEX RANGE SCAN	FWSB	IDX_COBCA_OPERATOR	INDEX	11	986		12
INDEX UNIQUE SCAN	FWSB	PK_CS_ORDER_BATCH_CHG_ACCE	INDEX (UNIQUE)	1	1		10
TABLE ACCESS BY INDEX ROWID	FWSB	CS_ORDER_BATCH_CHG_ACCE	TABLE	2	1	14	9
INDEX UNIQUE SCAN	FWSB	PK_CS_ORDER_BATCH_CHG_ACCE	INDEX (UNIQUE)	1	1		5
TABLE ACCESS BY INDEX ROWID	FWSB	CS_ORDER_BATCH_CHG_ACCE	TABLE	2	1	71	4

具体资料介绍可参考

[Oracle] 获取执行计划的几种方法

【源于本人笔记】若有书写错误，表达错误，请指正...

此条目发表在 SQL、SQL优化篇分类目录。将固定连接加入收藏夹。

ITPub博客

Oracle 获取执行计划的几种方法

[Oracle] 获取执行计划的几种方法

最新文章