sql 一对多获得一条数据_分享测试环境中一条sql拖垮整个数据库的解决思路

概述

今天主要简单记录一下最近UAT环境中一条sql拖垮数据库的案例，仅供参考。

---

1、告警

一大早起来，就冒出这么个告警，看起来又要干活了~

---

2、检查服务器状态

可以发现缓存了13G，内存已经不够用了，这台服务器上同时部署了Oracle和mysql服务器，初步判断问题在Oracle数据库上。

---

3、尝试清缓存

无效

---

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代表当前等待次数

---

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产生了多少的逻辑读..

---

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=BOTH;2. 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= &new_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$process                                          where 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 > 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

---

9、最终建议

1、改写sql，走索引

这个sql存在的问题：

1)NOT IN和OR导致这个表没走索引

2)引用了两次这个表，是不是可以考虑只查一次？例如with xxx

2、这个是日志表，可以删除不必要的数据，只保留1周数据(考虑定时任务解决)

---

后面会分享更多devops和DBA方面内容，感兴趣的朋友可以关注下！

这里引申一个问题，oracle要去找历史的一些问题还是有很多办法的，那么mysql呢？当你重启mysql数据库后怎么去定位历史的一些问题源头？