江苏龙网

  1. 首页 > 人文 > >

测试环境|分享测试环境中一条sql拖垮整个数据库的解决思路


今天主要简单记录一下最近UAT环境中一条sql拖垮数据库的案例 ， 仅供参考 。
1、告警 一大早起来 ， 就冒出这么个告警 ， 看起来又要干活了~
测试环境|分享测试环境中一条sql拖垮整个数据库的解决思路
本文插图

2、检查服务器状态 可以发现缓存了13G ， 内存已经不够用了 ， 这台服务器上同时部署了Oracle和mysql服务器 ， 初步判断问题在Oracle数据库上 。
测试环境|分享测试环境中一条sql拖垮整个数据库的解决思路
本文插图

3、尝试清缓存 无效
测试环境|分享测试环境中一条sql拖垮整个数据库的解决思路
本文插图

4、查看当前等待事件 这台数据库是没有部署DG的 ， 暂时不知道为什么有DG相关进程

--V$sessiong_wait提供了任何情况下session在数据库中当前正在等待什么(如果session当前什么也没在做 ， 则显示它最后的等待事件),注意这里排除了一些常见的IDLE等待事件SELECT inst_id,wait_class,EVENT, SUM(DECODE(WAIT_TIME, 0, 0, 1)) "Prev", SUM(DECODE(WAIT_TIME, 0, 1, 0)) "Curr", COUNT(*) "Tot" , state, sum(SECONDS_IN_WAIT) SECONDS_IN_WAIT FROM GV$SESSION_WAITWHERE event NOTIN ("smon timer","pmon timer","rdbms ipc message","SQL*Net message from client","gcs remote message") AND event NOT LIKE "%idle%" AND event NOT LIKE "%Idle%" AND event NOT LIKE "%Streams AQ%"GROUP BY inst_id,wait_class,EVENT,stateORDER BY 1,6 desc说明：1）当state值为Waiting ， Second_in_wait值才是实际的等待时间(单位：秒) ， 当state值为Waiting known time ， 那么wait_time值就是实际等待时间 。 Prev代表上一次等待次数 ， Curr代表当前等待次数
测试环境|分享测试环境中一条sql拖垮整个数据库的解决思路
本文插图

测试环境|分享测试环境中一条sql拖垮整个数据库的解决思路
本文插图

5、查看当前会话基本信息 其实在这里就可以看到第二个会话是问题sql了

--可以获得会话基本信息、执行时间、执行sql、使用的临时表空间大小、undo大小和表空间等SELECT /* use_hash(sess,proc,undo,tmp) use_nl(s)*/distinct sess.inst_id, sess.sid, sess.serial#, sess.username, substr(osuser, 1, 10) osuser, status, sess.process, proc.spid, sess.machine, sess.program, regexp_substr(NUMTODSINTERVAL(nvl((SYSDATE - SQL_EXEC_START) * 24 * 60 * 60, last_call_et), "SECOND"), "+d{2} d{2}:d{2}:d{2}") running_sec, TEMP_MB, UNDO_MB, s.sql_id, TSPS.NAME TSPS, decode(sess.action, null, "", sess.action || ", ") || replace(s.sql_text, chr(13), " ") sql FROM gv$session sess, gv$process proc, gv$sql s, (select ses_addr as saddr, sum(used_ublk / 128) UNDO_MB from v$transaction group by ses_addr) undo, (select session_addr as saddr, SESSION_NUM serial#, sum((blocks / 128)) TEMP_MB from gv$sort_usage group by session_addr, SESSION_NUM) tmp, (select inst_id, sid, serial#, event, t.name from gv$session ls, sys.file$ f, sys.ts$ t where status = "ACTIVE" and ls.p1text in ("file number", "file#") and ls.p1 = f.file# and f.ts# = t.ts#) tsps WHERE sess.inst_id = proc.inst_id(+) and sess.saddr = tmp.saddr(+) and sess.serial# = tmp.serial#(+) AND sess.status = "ACTIVE" and sess.username is not null and sess.sid = tsps.sid(+) and sess.inst_id = tsps.inst_id(+) and sess.serial# = tsps.serial#(+) AND sess.paddr = proc.addr(+) and sess.sql_id = s.sql_id(+) and sess.saddr = undo.saddr(+) ORDER BY running_sec desc, 4, 1, 2, 3
6、观察awr报告 最快的办法就是看等待事件--》找对应sql
等待事件很明显是CPU占用过高 ， 对应看sql order by CPU time实际上就有结果了 。
测试环境|分享测试环境中一条sql拖垮整个数据库的解决思路
本文插图

测试环境|分享测试环境中一条sql拖垮整个数据库的解决思路
本文插图

有兴趣的朋友可以算一下这条sql产生了多少的逻辑读..

测试环境|分享测试环境中一条sql拖垮整个数据库的解决思路
本文插图

7、关于"acknowledge over PGA limit" Wait Event 细心的朋友从AWR应该可以观察到"acknowledge over PGA limit" Wait Event ， 查询mos文档 ， 发现此类问题是由于PGA大小达到了PGA_AGGREGATE_LIMIT的值 ， 防止ORA-4036错误 ， 后面进程分配pga时需要等待其他进程释放pga 。
临时解决方案：
1. Set PGA_AGGREGATE_LIMIT=0 (as SYS user) to revert to 11g behavior of PGA memory management using PGA_AGGREGATE_TARGET parameter and the wait event will be alleviated. ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0 SID="*" SCOPE=BOTH2. Set PGA_AGGREGATE_LIMIT to higher than the default value (as SYS user), which was calculated based on the PGA_AGGREGATE_TARGET, PROCESSES or "_pga_limit_target_perc" setting and that will also help to reduce this wait event.ALTER SYSTEM SET PGA_AGGREGATE_LIMIT= &ampnew_value SID="*" SCOPE=BOTH oracle建议设置PGA_AGGREGATE_LIMIT=0或者增大这个参数的值解决ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0 SCOPE=BOTH
上面这个方法只是暂时解决 ， 究竟是什么导致的呢 ， 需要继续检查pga使用情况

首先判断该应用pga设置是否合理 ， 即连接数数量和pga大小 ， 可以通过查询process数量和pga使用 ， 如果明显出现process较小而pga使用很大 ， 则有可能是某些应用进程或者数据库进程异常导致 ， 其实就是下面sql导致了 ， 也就是上面AWR看到的sql 。
进一步探究可用以下sql:
--列出占用pga最大的进程select pid, spid, substr(username, 1, 20) "USER", program, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM from v$process where pga_alloc_mem = (select max(pga_alloc_mem) from v$processwhere program not like "%LGWR%")--检查应用进程分配的pga,查询出大于50m的select a.sid,a.status,a.sql_id,a.last_call_et,b.pga_alloc_mem/1024/1024,a.event,a.state from v$session a,v$process b where a.paddr=b.addr and a.status="INACTIVE" and b.pga_alloc_mem/1024/1024 &gt 50--查看当前pga使用情况select * from v$pgastat
8、问题sql分析 两个800万的表全扫后做hash连接 ， 连接是没问题的 ， 问题在于两点：
1）为什么800万的表要全扫
2）为什么同样的表要查两次
ps：看不懂执行计划不要问我 ， www.baidu.com或者看之前文章..

SELECT id FROM xxl_job_log WHERE ID NOT IN (SELECT id FROM xxl_job_log WHERE (trigger_code in (0, 200) and handle_code = 0) OR (handle_code = 200) ) AND alarm_status = 0 ORDER BY id ASC
测试环境|分享测试环境中一条sql拖垮整个数据库的解决思路
本文插图
【测试环境|分享测试环境中一条sql拖垮整个数据库的解决思路】

测试环境|分享测试环境中一条sql拖垮整个数据库的解决思路
本文插图

9、最终建议 1、改写sql ， 走索引
这个sql存在的问题：
1）NOT IN和OR导致这个表没走索引
2）引用了两次这个表 ， 是不是可以考虑只查一次？例如with xxx
2、这个是日志表 ， 可以删除不必要的数据 ， 只保留1周数据（考虑定时任务解决）
后面会分享更多devops和DBA方面内容 ， 感兴趣的朋友可以关注下！
这里引申一个问题 ， oracle要去找历史的一些问题还是有很多办法的 ， 那么mysql呢？当你重启mysql数据库后怎么去定位历史的一些问题源头？
测试环境|分享测试环境中一条sql拖垮整个数据库的解决思路
本文插图

声明：转载此文是出于传递更多信息之目的 。 若有来源标注错误或侵犯了您的合法权益 ， 请作者持权属证明与本网联系 ， 我们将及时更正、删除 ， 谢谢 。邮箱地址：newmedia@xxcb.cn


    推荐阅读


    上一篇：吴桥县|万般天籁，莫过一语

    下一篇：庆祝|夫妻中444元彩票还要喝酒庆祝，半夜妻子发病，丈夫抱尸睡觉浑然不知