OLTP系统中,经常碰到的一种场景,就是某个操作卡住了,再从日志定位,很可能就看到是在执行某个增删改的数据库操作的时候出现hang的问题。
对于数据库中的这个场景,卡住的会话,通常是在等什么,在Oracle中,就会出现所谓的等待事件,如何快速定位会话的等待事件以及等待链?今天介绍的SQL就是为解决这个问题。
示例如下,会话1,执行update,更新id=1的记录,未提交/回滚,
SQL> update a set id=1 where id=1;1 row updated.SQL> select * from dual;D-X
会话2,执行update,更新相同的id=1记录,此时的会话,hang住了,
SQL> update a set id=1 where id=1;
会话3,执行update,继续要更新id=1,还是hang了,
SQL> update a set id=1 where id=1;
SQL来了,
WITH blockers_and_blockees AS (SELECT ROWNUM rn, a.* FROM gv$session a WHERE blocking_session_status = 'VALID' OR (inst_id, sid) IN (SELECT blocking_instance, blocking_session FROM gv$session WHERE blocking_session_status = 'VALID')) SELECT LPAD(' ', 3 * (LEVEL - 1)) || sid || DECODE(LEVEL, 1, ' root blocker') blocked_session, inst_id, event, TO_CHAR(FLOOR(seconds_in_wait / 3600), 'fm9900') || ':' || TO_CHAR(FLOOR(MOD(seconds_in_wait, 3600) / 60), 'fm00') || ':' || TO_CHAR(MOD(seconds_in_wait, 60), 'fm00') time_in_wait, username, osuser, machine, (SELECT owner || '.' || object_name FROM dba_objects WHERE object_id = b.row_wait_obj#) waiting_on_object, CASE WHEN row_wait_obj# > 0 THEN DBMS_ROWID.rowid_create(1, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#) END waiting_on_rowid, (SELECT sql_text FROM gv$sql s WHERE s.sql_id = b.sql_id AND s.inst_id = b.inst_id AND s.child_number = b.sql_child_number) current_sql, status, serial#, (SELECT spid FROM gv$process p WHERE p.addr = b.paddr AND p.inst_id = b.inst_id) os_process_id FROM blockers_and_blockees bCONNECT BY PRIOR sid = blocking_session AND PRIOR inst_id = blocking_instanceSTART WITH blocking_session IS NULL;
其中,
BLOCKED_SESSION:阻塞的会话,并且支持缩进,第一行是root blocker,自上而下,逐层等待。
INST_ID:实例ID,对单实例数据库,就是1。
EVENT:该会话等待的等待事件。
TIME_IN_WAIT:该会话的等待时间。
USERNAME:会话使用的数据库账号。
OSUER:会话使用的操作系统账号。
MACHINE:会话所在主机名。
WAITING_ON_OBJECT:会话等待的数据库对象。
WAITING_ON_ROWID:会话等待的记录rowid。
CURRENT_SQL:会话当前执行的SQL。
STATUS:会话当前的状态。
SERIAL#:会话的SERIAL#。
OS_PROCESS_ID:会话的操作系统进程ID。
SQL的执行结果,通过PLSQL Developer,看得清晰一些,会话2和会话3的等待事件都是"enq: TX - row lock contention",行锁争用,而当前行的行锁持有者是会话1,等待链是"会话3等待会话2,会话2等待会话1",从WAITING_ON_OBJECT和WAITING_ONROWID可以知道,这几个会话都在对BISAL_A表的这个rowid对应的行在进行操作,
另外,可以看到,SQL中用的视图都是gv,因此这条SQL不仅支持单实例,而且支持RAC。因此,这条SQL,功能还是很强大,能告诉我们很多关于会话等待的信息,推荐指数5颗星。