--累计每天物理读消耗最多的表对象

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

ITPub博客

效率低的语句

最新文章