ITPub博客

首页 > 数据库 > Oracle > 效率低的语句

效率低的语句

原创 Oracle 作者:jelephant 时间:2015-12-05 10:44:23 0 删除 编辑
--累计每天物理读消耗最多的表对象
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博客 ” ,链接:http://blog.itpub.net/29337971/viewspace-1853858/,如需转载,请注明出处,否则将追究法律责任。

上一篇: B*树索引
请登录后发表评论 登录
全部评论

注册时间:2013-12-07

  • 博文量
    143
  • 访问量
    626946