[Oracle]-性能优化工具（5）-AWRSQL

db name	db id	instance	inst num	startup time	release	rac
test11g	977587123	test11g	1	23-2月 -14 07:02	11.2.0.1.0	no

	snap id	snap time	sessions	cursors/session
begin snap:	2039	23-2月 -14 15:56:23	28	2.0
end snap:	2040	23-2月 -14 15:56:38	30	1.9
elapsed:		0.24 (mins)
db time:		0.25 (mins)

sql id	elapsed time (ms)	module	action	sql text
1rrtf60fmhxkj	13,564	sql*plus		select count(*) from t1, t2 where t1.object_id=t2.object_id...

在awr中定位到问题sql语句后想要了解该sql statement的具体执行计划，于是就用awr报告中得到的sql id去v$sql等几个动态性能视图中查询，但发现v$sql或v$sql_plan视图都已经找不到对应sql id的记录，一般来说这些语句已经从shared pool共享池中被替换出去了。
这个时候我们可以尝试使用dbms_xplan.display_awr存储过程来将oracle automatic workload repository自动负载仓库中记录的sql语句相关信息抽取出来，如:

@?/rdbms/admin/awrsqrpt.sql

下面是上诉语句生成的awrsql：

workload repository sql report

snapshot period summary

sql summary

back to top

sql id: 1rrtf60fmhxkj

•  

1st capture and last capture snap ids refer to snapshot ids witin the snapshot range

select count(*) from t1,t2 where t1.object_id=t2.object_id

plan statistics

execution plan back to top

% total db time is the elapsed time of the sql statement pided into the total database time multiplied by 100

dynamic sampling used for this statement (level=2)