--累计每天物理读消耗最多的表对象
select trunc(ss.end_interval_time),sso.owner,sso.object_name,sso.object_type,sum(sgs.physical_reads_delta/128)
from sys.wrh$_seg_stat sgs,sys.wrh$_seg_stat_obj sso,sys.wrm$_snapshot ss
where sgs.dbid=sso.dbid
--and ss.snap_id<sso.dbid
and sgs.ts#=sso.ts#
and sgs.dataobj#=sso.dataobj#
and sgs.obj#=sso.obj#
and sgs.snap_id=ss.snap_id
and sgs.dbid=ss.dbid
and sgs.instance_number=ss.instance_number
and sgs.dbid=1994895652
and sgs.snap_id>=45743
and sgs.snap_id<=46127
and sgs.instance_number=1
and sso.object_type='TABLE'
group by trunc(ss.end_interval_time),sso.owner,sso.object_name,sso.object_type
order by sum(sgs.physical_reads_delta/128) desc
--根据找到的表对象,查找对象上存在全表扫的sql
select sqp.snap_id,
sqp.dbid,
sqp.sql_id,
sqp.plan_hash_value,
sqp.operation,
sqp.options,
sqp.object_name,sqt.sql_text
--dbms_lob.substr(sqt.sql_text, 2000)
from sys.wrh$_sql_plan sqp, sys.wrh$_sqltext sqt
where sqp.dbid = sqt.dbid
and sqp.sql_id = sqt.sql_id
and sqp.dbid = 1994895652
and sqp.object_name = upper('STAGEDBAGTAB')
and sqp.options='FULL'
and sqp.snap_id>=45743
and sqp.snap_id<=46127
select sqp.snap_id,
sqp.dbid,
sqp.sql_id,
sqp.plan_hash_value,
sqp.operation,
sqp.options,
sqp.object_name,sqt.sql_text
--dbms_lob.substr(sqt.sql_text, 2000)
from sys.wrh$_sql_plan sqp, sys.wrh$_sqltext sqt
where sqp.dbid = sqt.dbid
and sqp.sql_id = sqt.sql_id
and sqp.dbid = 1994895652
and sqp.object_name = upper('STAGEDBAGTAB')
and sqp.options='FULL'
and sqp.snap_id>=45743
and sqp.snap_id<=46127
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29337971/viewspace-1853858/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29337971/viewspace-1853858/