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;