概述
今天主要简单记录一下最近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_WAIT
WHERE event NOT
IN ('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,state
ORDER 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$pgastat8、问题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 ASC9、最终建议
1、改写sql，走索引
这个sql存在的问题：
1）NOT IN和OR导致这个表没走索引
2）引用了两次这个表，是不是可以考虑只查一次？例如with xxx
2、这个是日志表，可以删除不必要的数据，只保留1周数据（考虑定时任务解决）
后面会分享更多devops和DBA方面内容，感兴趣的朋友可以关注下！
这里引申一个问题，oracle要去找历史的一些问题还是有很多办法的，那么mysql呢？当你重启mysql数据库后怎么去定位历史的一些问题源头？