概述

无意中在metalink上看到一个脚本（get_locked_objects_rpt.sql），测试后觉得非常不错，这里分享给大家，顺便介绍下测试的大致过程。

脚本内容

/*----------------------------------------------------------------------------+ | MODULE: get_locked_objects_rpt.sql | | DESCRIPTION: | | Script to retrieve a list of locked objects from the database and identify | the object being locked, and its rowid, and the SQL being blocked. | | INFORMATION: | | BRM Performance Tools ... +----------------------------------------------------------------------------*/ set serveroutput on size unlimited set feedback off DECLARE v_num_sessions INTEGER := 0; CURSOR cv IS SELECT dba_objects.object_name, locks_t.row#, locks_t.blocked_secs, locks_t.blocker_text, locks_t.blocked_text, locks_t.blocked_sql_text FROM (SELECT /*+ NO_MERGE */ blocking_lock_session.username||'@'||blocking_lock_session.machine||'(SID='||blocking_lock_session.sid||') ['|| blocking_lock_session.program||'/PID='||blocking_lock_session.process||']' as blocker_text, blocked_lock_session.username||'@'||blocked_lock_session.machine|| '(SID='||blocked_lock_session.sid||') ['|| blocked_lock_session.program||'/PID='||blocked_lock_session.process||']' as blocked_text, blocked_lock_session.row_wait_obj#, blocked_lock_session.row_wait_file#, blocked_lock_session.row_wait_block#, blocked_lock_session.row_wait_row#, DBMS_ROWID.ROWID_CREATE (1, blocked_lock_session.row_wait_obj#, blocked_lock_session.row_wait_file#, blocked_lock_session.row_wait_block#, blocked_lock_session.row_wait_row#) row#, blocked_lock_session.seconds_in_wait blocked_secs, blocked_sql.sql_text blocked_sql_text FROM v$lock blocking_lock, v$session blocking_lock_session, v$lock blocked_lock, v$session blocked_lock_session, v$sql blocked_sql WHERE blocking_lock.block = 1 AND blocking_lock.id1 = blocked_lock.id1 AND blocking_lock.id2 = blocked_lock.id2 AND blocked_lock.request > 0 AND blocking_lock.sid = blocking_lock_session.sid AND blocked_lock.sid = blocked_lock_session.sid AND blocked_lock_session.sql_id = blocked_sql.sql_id AND blocked_lock_session.sql_child_number = blocked_sql.child_number ) locks_t, dba_objects WHERE locks_t.row_wait_obj# = dba_objects.object_id AND locks_t.blocked_secs > &1 ORDER BY locks_t.blocked_secs; BEGIN FOR cv_rec IN cv LOOP dbms_output.put_line( '========= $Revision: 1.4 $ ($Date: 2013/09/16 13:15:22 $) ==========='); v_num_sessions := v_num_sessions + 1; dbms_output.put_line('Locked object : '|| cv_rec.object_name); dbms_output.put_line('Locked row# : '|| cv_rec.row#); dbms_output.put_line('Blocked for : '|| cv_rec.blocked_secs||' seconds'); dbms_output.put_line('Blocker info. : '|| cv_rec.blocker_text); dbms_output.put_line('Blocked info. : '|| cv_rec.blocked_text); dbms_output.put_line('Blocked SQL : '|| cv_rec.blocked_sql_text); END LOOP; dbms_output.new_line; dbms_output.put_line('Found '||TO_CHAR(v_num_sessions)|| ' blocked session(s).'); END; / exit; 

实验

新建3个会话来测试验证：

1、在会话ID为45的窗口执行下面SQL语句

SQL> select sid from v$mystat where rownum = 1; SQL> create table test(id number, name varchar2(12)); SQL> insert into test values(1001,'HWB'); SQL> commit; SQL> update test set name='xiaoming' where id=1001; 

2、在会话ID为171的窗口执行下面语句。

SQL> select sid from v$mystat where rownum = 1; SQL> update test set name='xiaohong' where id=1001; 

3、在会话窗口3执行下面语句查看阻塞或锁定对象情况,输入查询阻塞多少秒以上的SQL

SQL> @/home/oracle/scripts/get_locked_objects_rpt.sql Enter value for 1: 10 old 42: AND locks_t.blocked_secs > &1 new 42: AND locks_t.blocked_secs > 10 ========= $Revision: 1.4 $ ($Date: 2013/09/16 13:15:22 $) =========== Locked object : TEST Locked row# : AAAjo4AABAAAVcBAAA Blocked for : 243 seconds Blocker info. : SYS@WorkGroup\GT-DUKT(SID=45) [plsqldev.exe/PID=6324:8884] Blocked info. : SYS@iZmfgnjkehk13uZ(SID=171) [sqlplus@iZmfgnjkehk13uZ (TNS V1-V3)/PID=14209] Blocked SQL : update test set name='xiaohong' where id=1001 Found 1 blocked session(s). Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 

看完大家应该知道这个sql有什么用了吧~

这里有个思考的问题，如果A被B堵塞导致锁，B被C堵塞导致锁，C被D堵塞导致锁，这样我们怎么快速去找到源头呢，后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~