对造成数据库压力翻倍的bad sql的解决方案

来源:网络 责任编辑:栏目编辑 发表时间:2013-07-01 07:02 点击:

早上看到数据库一个节点的load比昨天翻倍,细查之下,发现主要是由新上线的一条sql引起的,查询语句如下
select b.sid,substr(b.username,1,10) username,decode(program, null, machine,replace(program,' (TNS V1-V3)','')||decode(machine,null,'@'||terminal)) machine,
substr(
(case
  when a.event like 'enq%' then a.event|| ':' || chr(bitand(a.p1,-16777216)/16777215)||chr(bitand(a.p1, 16711680)/65535)||'-'||bitand(a.p1, 65535)||':'||ROW_WAIT_FILE#||':
                              '||ROW_WAIT_BLOCK#||':'|| ROW_WAIT_ROW#
  else a.event
end),1,30) event,
a.p1||'/'||a.p2||'/'||a.p3 param,a.wait_time w,a.SECONDS_IN_WAIT WT,b.sql_id||'/'||b.PREV_SQL_ID SQL, decode(b.status,'ACTIVE','A','INACTIVE','I','KILLED','K',STATUS) status,last_call_et LT,
(sysdate -logon_time )*24*3600 LOGON_TIME
from v$session_wait a,v$session b
where ((a.event not like 'SQL*Net message%' and a.event not like 'rdbms%') or b.status='ACTIVE') and a.sid=b.sid
and b.type='USER'
and b.sid <>(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1)
order by sql_hash_value;

捕获的Sql语句结构如下
select a.col1,
       b.col2,
       ...
  from a
  left join b
    on a.b_id = b.id
WHERE sub_id in (:1, :2)
   and a.parent_id = 0
order by .id desc;
其中表a有800多万条记录,其上的字段sub_id和parent_id均有索引;而表b只有20条;

结果发现该sql执行计划的cost很低,只有6,但是buffer gets却很高,有6位数。
刚开始感觉是统计信息陈旧,查看后发现表A是昨天收集的,而表B也是最近收集的,因此排除这个可能。
仔细查看执行计划,发现其选择了parent_id上的索引,而非sub_id,这点比较可疑。
经查看parent_id=0的记录占了全表记录的80%,而且该列上没有直方图,导致CBO选择执行计划的时候误用了基于parent_id列的索引。

原因找到了,接下来是如何解决。
首先,重新收集表a的统计信息并收集parent_id上的直方图,考虑表a的访问十分繁忙,如果现在就让该游标失效会导致大量硬解析DB有可能因此而hang住,如果不让其失效load又一直居高不下,因此在调用
Dbms_stats.gather_table_stats的时候指定了no_invalidate为auto(也可以不标注,因为默认就是auto),决定让oracle自动决定何时去invalidate该游标。

之后观察了一段时间,发现该sql还是使用原来的错误执行计划,load还是居高不下。
我们的系统是10.2.0.5的,而oracle好像是10.2以后就可以调用dbms_shared_pool.purge了。
Support.oracle.com查了一下,发现调用该功能之前还必须要设定事件 alter session set events '5614566 trace name context forever';
由于不能判定其造成的影响,决定暂不使用该功能。

此刻,既不能对表做grant select on a to dba操作,也不能使用dbms_shared_pool.purge,而bad sql仍在运行。

忽然,我们组长想到了一个方案,对表b做grant select on b to dba操作(表b访问热度相对就少了很多)。
执行过后,sql立即选择了sub_id上的索引,buffer gets下降到了两位数,数据库压力不到1分钟就下降到了正常状态。

    相关新闻>>

      发表评论
      请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
      用户名: 验证码:点击我更换图片
      最新评论 更多>>

      推荐热点

      • sql常见面试题
      • SQL SERVER 2005性能之跟踪
      • SQL编程(一)
      • LINUX上RMAN自动备份脚本
      • sql server面试题
      • 如何将多个SQL查询统计结果一次显示出来
      • 浅谈SQL Server中的事务日志(三)----在简单恢复模式下日志的角色
      • sql server 列转行
      • SQL小技巧系列 --- 行转列合并
      网站首页 - 友情链接 - 网站地图 - TAG标签 - RSS订阅 - 内容搜索
      Copyright © 2008-2015 计算机技术学习交流网. 版权所有

      豫ICP备11007008号-1