oracle中快速找到引起数据库问题的sql

一、背景

业务发生卡、慢的场景经常出现，排除网络、硬件原因，单纯从数据库来讲，作为dba的我们都知道一个道理，用的人越多，数据库出现这种问题的概率就越大。用得越少，当然问题就越可能出现的少。

但是当业务发生问题后，很多人习惯性的去看awr、ash等报告，分析半天却找不到问题。实际上我个人第一时间就是看sql有没有异常，有没有异常的等待事件这些，以下就是工作中我整理的部分sql。

二、sql分享

2、1 看锁

select * from (select a.inst_id, a.sid, a.serial#,
a.sql_id,
a.event,
a.status,
connect_by_isleaf as isleaf,
sys_connect_by_path(a.SID||'@'||a.inst_id, ' <- ') tree,
level as tree_level
from gv$session a
start with a.blocking_session is not null
connect by (a.sid||'@'||a.inst_id) = prior (a.blocking_session||'@'||a.blocking_instance)) where isleaf = 1 order by tree_level asc;

2、2 看对应的应用程序及是否有锁表对象

select max(t1.sql_id),max(sql_text),
t1.client_info,t1.last_call_et "会话status持续时间",t1.seconds_in_wait "会话锁住时间",
t1.INST_ID,t1.logon_time,--blocking_instance,blocking_session "死锁直接源",final_blocking_instance,final_blocking_session "死锁最终源",
t1.event,wait_class, t1.username,
t1.status,t1.program,t1.module,t1.sid,t1.serial#,t1.paddr,t1.username,t1.process,t1.machine,
t1.terminal,t1.type,'orakill orcl '||spid,'kill -9 '||spid,
'ALTER SYSTEM KILL SESSION '||''''||t1.SID||','||t1.SERIAL#||',@'||t1.inst_id||''''||' IMMEDIATE;',listagg (c.OBJECT_NAME, ',') WITHIN GROUP (ORDER BY c.OBJECT_NAME) lck

from sys.gV_$PROCESS t,gv$session t1,gv$sql s,gv$locked_object a,all_objects c
where t.addr=t1.paddr
and t.INST_ID=t1.INST_ID
and s.Sql_id=t1.sql_id
and s.inst_id=t1.inst_id
and t1.INST_ID=a.INST_ID(+)
and t1.sid =a.session_id(+)
and a.object_id =c.object_id(+)
and wait_class# <> 6
and t1.status='ACTIVE' and t1.Type='USER'
--and spid in (23097,15860,22496,14957) --看linux的spid
group by t1.client_info,t1.last_call_et,t1.seconds_in_wait ,
t1.INST_ID,t1.logon_time,blocking_instance,blocking_session,final_blocking_instance,final_blocking_session,
t1.event,wait_class, t1.username,
t1.status,t1.program,t1.module,t1.sid,t1.serial#,t1.paddr,t1.username,t1.process,t1.machine,
t1.terminal,t1.type,spid;

2、3 等待事件

select inst_id,event,count(1) from gv$session where wait_class#<>6 group by inst_id,event order by 1,3;

2、4 事务查询

select /*+rule*/b.sid,b.serial#,b.machine,b.status,b.event,b.program,a.start_date, a.start_scn, a.status, c.sql_id,c.sql_text
from v$transaction a, v$session b, v$sqlarea c
where b.saddr=a.ses_addr and c.address=b.sql_address
and b.sql_hash_value=c.hash_value;

select /*+ rule */
lpad(nvl(s.username,' '),8)||'('||s.sid||','||s.serial#||')' as sess,
s.status,
p.spid as svr_ospid,
nvl(osuser,' ') as clnt_user,
s.process as clnt_pid,
substr((case instr(s.PROGRAM, '@')
when 0 then
s.program
else
case instr(s.PROGRAM, '(TNS V1-V3)')
when 0 then
substr(s.program, 1, instr(s.PROGRAM, '@') - 1) || substr(s.program, instr(s.PROGRAM, '(') - 1)
else
substr(s.program, 1, instr(s.PROGRAM, '@') - 1)
end
end),
1, 18) as program,
(case
when length(s.MACHINE) > 8 then substr(s.machine,1,8)||'~'
else s.machine
end
) || '('||nvl(s.client_info, 'Unknown IP')||')' as machine, s.sql_id,
substr(s.event, 1, 32) as event,
s.seconds_in_wait as waitsec
from v$transaction t,v$session s,v$process p
where t.ses_addr=s.saddr and s.paddr=p.addr
order by s.seconds_in_wait, s.program, s.machine;