在日常运维Oracle过程中经常会遇到一些锁及等待事件方面的问题需要我们快速去发现和解决如何快速定位锁和等待事件是关系我们能否快速解决问题的关键这非常考验每个DBA的运维水平。从事数据库运维这么多年我习惯将一些好的SQL语句整理出来随着越积越多我发现我整理的内容已经有400多页我平时主要就是靠着这份文档来日常运维我的数据库现在我希望能将这部分内容发出来供更多人用到。1)Oracle 查看当前所有会话SQL和等待事件select t2.sid, t2.SERIAL#, -- 库级唯一定位一个session t1.SPID OS_PID, -- 操作系统的PID t3.SQL_ID, t2.EVENT,-- SQLID和等待事件 t2.P1TEXT, t2.P1, -- 等待事件的P1信息 t2.p2TEXT, t2.P2, -- 等待事件的P2信息 t2.p3TEXT, t2.P3, -- 等待事件的P3信息 t3.SQL_FULLTEXT -- 被执行的SQL完整内容 from v$process t1, v$session t2, v$sql t3 where t1.ADDR t2.PADDR and t2.STATUS ACTIVE -- 表示当前正在执行SQL的会话 -- and t2.STATUS INACTIVE -- 表示当前等待执行SQL的会话 -- and t2.STATUS KILLED -- 表示当前会话正在被杀掉未提交事务强制回滚 and t2.SQL_ID t3.SQL_ID; -- t2.sid, t2.SERIAL# 可以用作库级杀死会话alter system kill session t2.sid, t2.SERIAL#; -- t1.SPID OS_PID 可以用作系统级杀死会话kill -9 t1.SPID -- t2.EVENT 和 P1、P2、P3 不同的等待事件的P1、P2和P3的信息是不尽相同的具体要查官档 -- t3.SQL_FULLTEXT 是一个CLOB类型的字段 去掉注释较为清爽的ctrlc版SQL select t2.sid, t2.SERIAL#, t1.SPID OS_PID, t3.SQL_ID, t2.EVENT, t2.P1TEXT, t2.P1, t2.p2TEXT, t2.P2, t2.p3TEXT, t2.P3, t3.SQL_FULLTEXT from v$process t1, v$session t2, v$sql t3 where t1.ADDR t2.PADDR and t2.STATUS ACTIVE -- INACTIVE and t2.SQL_ID t3.SQL_ID;2)Oracle 查看当前等待事件SELECT * FROM (SELECT Current Process: || OSID W_PROC, SID || I.INSTANCE_NAME INSTANCE, INST #: || INSTANCE INST, Blocking Process: || DECODE(BLOCKER_OSID, NULL, none, BLOCKER_OSID) || from Instance || BLOCKER_INSTANCE BLOCKER_PROC, Number of waiters: || NUM_WAITERS WAITERS, Final Blocking Process: || DECODE(P.SPID, NULL, none, P.SPID) || from Instance || S.FINAL_BLOCKING_INSTANCE FBLOCKER_PROC, Program: || P.PROGRAM IMAGE, Wait Event: || WAIT_EVENT_TEXT WAIT_EVENT, P1: || WC.P1 P1, P2: || WC.P2 P2, P3: || WC.P3 P3, Seconds in Wait: || IN_WAIT_SECS SECONDS, Seconds Since Last Wait: || TIME_SINCE_LAST_WAIT_SECS SINCELW, Wait Chain: || CHAIN_ID || : || CHAIN_SIGNATURE CHAIN_SIGNATURE, Blocking Wait Chain: || DECODE(BLOCKER_CHAIN_ID, NULL, none, BLOCKER_CHAIN_ID) BLOCKER_CHAIN FROM V$WAIT_CHAINS WC, GV$SESSION S, GV$SESSION BS, GV$INSTANCE I, GV$PROCESS P WHERE WC.INSTANCE I.INSTANCE_NUMBER() AND (WC.INSTANCE S.INST_ID() AND WC.SID S.SID() AND WC.SESS_SERIAL# S.SERIAL#()) AND (S.FINAL_BLOCKING_INSTANCE BS.INST_ID() AND S.FINAL_BLOCKING_SESSION BS.SID()) AND (BS.INST_ID P.INST_ID() AND BS.PADDR P.ADDR()) AND (NUM_WAITERS 0 OR (BLOCKER_OSID IS NOT NULL AND IN_WAIT_SECS 10)) ORDER BY CHAIN_ID, NUM_WAITERS DESC) WHERE ROWNUM 101; 注 如果state值为Waiting那么wait_time值无用。Second_in_wait值是实际的等待时间(单位秒)。 如果state值为Wait unknow time那么wait_time值和Second_in_wait值都无用。 如果state值为Wait short time那么wait_time值和Second_in_wait值都无用。 如果state值为Waiting known time那么wait_time值就是实际等待时间(单位秒)Second_in_wait值无用。3) ​​​​​​​Oracle 查看会话等待事件及其对应SQLSELECT P.PID, S.SID, S.SERIAL#, S.USERNAME, Q.SQL_ID, Q.SQL_TEXT, Q.SQL_FULLTEXT, W.EVENT, W.WAIT_TIME, W.STATE, CASE WHEN W.STATE WAITING THEN W.SECONDS_IN_WAIT WHEN W.STATE WAITING KNOWN TIME THEN W.WAIT_TIME END AS SEC_IN_WAIT FROM V$SESSION S, V$SESSION_WAIT W, V$SQLAREA Q, V$PROCESS P WHERE S.SID W.SID AND S.SQL_ID Q.SQL_ID AND P.ADDR S.PADDR AND W.EVENT NOT LIKE SQL*Net% AND S.USERNAME IS NOT NULL AND W.WAIT_TIME 0 ORDER BY W.SECONDS_IN_WAIT DESC; 查找阻塞和等待 SELECT holding_session.sid AS holding_sid, holding_session.username AS holding_user, waiting_session.sid AS waiting_sid, waiting_session.username AS waiting_user, waiting_session.event AS waiting_event FROM v$lock holding, v$lock waiting, v$session holding_session, v$session waiting_session WHERE holding.sid holding_session.sid AND waiting.sid waiting_session.sid AND holding.block 1 AND waiting.request 0 AND holding.id1 waiting.id1 AND holding.id2 waiting.id2;4) ​​​​​​​Oracle 查看某个时段历史等待事件SELECT sample_id, sample_time, CAST (sample_time AS DATE), TO_CHAR (sample_time, yyyymmdd hh24:mi:ss), session_id, session_serial#, user_id, sql_id, SQL_EXEC_START, sql_child_number, sql_opname, force_matching_signature, top_level_sql_id, sql_plan_hash_value, event, p1, p1text, p2, p2text, p3, p3text, wait_class, wait_time, session_state, blocking_session_status, blocking_session, blocking_session_serial#, blocking_inst_id, program, module, machine, CURRENT_OBJ#, CURRENT_FILE#, CURRENT_BLOCK#, CURRENT_ROW# FROM dba_hist_active_sess_history WHERE CAST (sample_time AS DATE) BETWEEN TO_DATE (2020-06-01 01:40:00, yyyy-mm-dd hh24:mi:ss) AND TO_DATE (2020-06-01 02:30:00, yyyy-mm-dd hh24:mi:ss);5) ​​​​​​​Oracle 查看锁定的表进程SELECT substr(v$lock.sid, 1, 4) SID, serial#, V$SESSION.sid, substr(username, 1, 12) UserName, substr(object_name, 1, 25) ObjectName, v$lock.type LockType, decode(rtrim(substr(lmode, 1, 4)), 2, Row-S (SS), 3, Row-X (SX), 4, Share, 5, S/Row-X (SSX), 6, Exclusive, Other) LockMode, substr(v$session.program, 1, 25) ProgramName FROM V$LOCK, SYS.DBA_OBJECTS, V$SESSION WHERE (OBJECT_ID v$lock.id1 AND v$lock.sid v$session.sid AND username IS NOT NULL AND username NOT IN (SYS, SYSTEM) AND SERIAL# ! 1);6) ​​​​​​​Oracle 查杀锁会话select s.sid, s.serial#, program, l.sql_text, /*l.sql_fulltext,*/ s.event, s.last_call_et, s.state, s.wait_time, s.BLOCKING_SESSION, s.machine, s.sql_id, s.USERNAME from v$session s, v$sqlstats l where s.status ACTIVE AND s.TYPE USER and s.username VISAANAQUERY and s.sql_id l.sql_id order by last_call_et; 查杀语句如下 SELECT alter system kill session || || s.SID || , || s.SERIAL# || || ;, s.SID, s.BLOCKING_SESSION, s.MACHINE, s.OSUSER, s.PROGRAM, s.USERNAME, s.LAST_CALL_ET, a.SQL_ID, s.LOGON_TIME, a.SQL_TEXT, a.SQL_FULLTEXT, w.EVENT, a.DISK_READS, a.BUFFER_GETS FROM v$PROCESS p, v$SESSION s, v$SQLAREA a, v$SESSION_WAIT w WHERE p.ADDR s.PADDR AND s.SQL_ID a.SQL_ID AND s.SID w.SID AND s.STATUS ACTIVE -- AND s.PROGRAM ! plsqldev.exe AND s.OSUSER ! oracle --and a.SQL_TEXT like select% ORDER BY s.LAST_CALL_ET DESC; select alter system kill session || s.sid || , || s.serial# || ;, program, l.sql_text, /*l.sql_fulltext,*/ s.event, s.last_call_et, s.state, s.wait_time, s.BLOCKING_SESSION, s.machine, s.sql_id, s.USERNAME from v$session s, v$sqlstats l where s.status ACTIVE AND s.TYPE USER and s.username VISAANAQUERY and s.sql_id l.sql_id order by last_call_et; 或者 select kill -9 || p.spid, s.username, s.sid, s.serial#, s.event, l.sql_text, l.SQL_FULLTEXT, s.program, s.last_call_et, s.state, s.wait_time, s.BLOCKING_SESSION, s.BLOCKING_SESSION_STATUS, s.machine, s.sql_id from v$session s, v$sqlstats l, v$process p where s.status ACTIVE AND s.TYPE USER and s.sql_id l.sql_id and s.paddr p.addr order by last_call_et desc;7​​​​​​​Oracle 查询当前被锁对象SELECT l.session_id sid, s.serial#, l.locked_mode 锁模式, l.oracle_username 登录用户, l.os_user_name 登录机器用户名, s.machine 机器名, s.terminal 终端用户名, o.object_name 被锁对象名, s.logon_time 登录数据库时间 FROM v$locked_object l, all_objects o, v$session s WHERE l.object_id o.object_id AND l.session_id s.sid ORDER BY sid, s.serial#;8​​​​​​​Oracle 分析锁阻塞源头一、查询异常时刻数据库异常等待 select event,count(*) from gv$session where wait_class ! Idle group by event order by 2; 二、分析行锁等待的阻塞者 select sid,sql_id,status,event,seconds_in_wait,state,blocking_session,machine from gv$session where event like %enq%; 三、查看阻塞者blocking_session SID 在做什么 select sid,last_call_et,sql_id,status,event,seconds_in_wait,state,machine,blocking_session from gv$session where sidXXX; ## XX 四、根据SID查找对应SQL信息 根据sid查询已经执行过的sql select a.sql_fulltext,b.machine,b.status from gv$sqlarea a,gv$session b where a.SQL_IDb.PREV_SQL_ID and b.SIDsid; 根据sid查询当前执行的sql select sql_text from v$sqlarea where (hash_value,address)(select sql_hash_value,sql_address from v$session where sidsid); SELECT t.SQL_ID, t.USERNAME, t.EVENT, t.MACHINE, t.SID, t.BLOCKING_SESSION bs, (SELECT sql_text FROM v$sql s WHERE s.sql_id t.sql_id AND ROWNUM 1) sql_text FROM v$session t WHERE t.STATUS ACTIVE AND t.TYPE USER --and t.service_name not in (SYS$USERS,SYS$BACKGROUND) AND t.sql_id IS NOT NULL --and t.module JDBC Thin client;