通常,我们在分析锁导致的会话被阻塞的问题的时候,都会查询视图DBA_WAITERS或者DBA_HOLDERS,找到阻塞进程,提交或者回滚它,就能为被阻塞的会话“疏通”道路了。但是,在RAC环境中,这两个视图并不完全可靠。当阻塞和被阻塞会话都在同一个instance上时,这两个视图对我们还是有帮助的。但是,当阻塞和被阻塞会话在不同instance上时,从这两个视图中就不能得出结果。看下面的测试:
SQL代码
- Inst_1:
- SQL> select * from ttt for update;
- …
- 23 rows selected
- SQL>
- Inst_2:
- SQL> select * from ttt for update;
实例2上的会话被阻塞了。这时,无论在实例1上还是实例2上都无法从上面两个视图中查到有价值的内容:
SQL代码
- SQL> select * from dba_waiters;
- no rows selected
- SQL>
通过查看DBA_WAITERS的创建语句,看出它的锁的信息是从v$_lock的中获取的,而从v$_lock中只能查询到当前实例中的锁的信息。实际上,从视图gv$lock中则可以查询到RAC中全部实例中的锁的信息。利用gv$lock,我们可以建立一个新的视图DBA_GLOBAL_WAITERS,获取到RAC环境中所有的阻塞队列:
SQL代码
- create or replace view DBA_GLOBAL_WAITERS
- as
- select w.INST_ID waiting_instance,
- w.sid waiting_session,
- h.inst_id holding_instance,
- h.sid holding_session,
- decode(w.type,
- 'MR', 'Media Recovery',
- 'RT', 'Redo Thread',
- 'UN', 'User Name',
- 'TX', 'Transaction',
- 'TM', 'DML',
- 'UL', 'PL/SQL User Lock',
- 'DX', 'Distributed Xaction',
- 'CF', 'Control File',
- 'IS', 'Instance State',
- 'FS', 'File Set',
- 'IR', 'Instance Recovery',
- 'ST', 'Disk Space Transaction',
- 'TS', 'Temp Segment',
- 'IV', 'Library Cache Invalidation',
- 'LS', 'Log Start or Switch',
- 'RW', 'Row Wait',
- 'SQ', 'Sequence Number',
- 'TE', 'Extend Table',
- 'TT', 'Temp Table',
- w.type) lock_type,
- decode(h.lmode,
- 0, 'None', /* Mon Lock equivalent */
- 1, 'Null', /* N */
- 2, 'Row-S (SS)', /* L */
- 3, 'Row-X (SX)', /* R */
- 4, 'Share', /* S */
- 5, 'S/Row-X (SSX)', /* C */
- 6, 'Exclusive', /* X */
- to_char(h.lmode)) mode_held,
- decode(w.request,
- 0, 'None', /* Mon Lock equivalent */
- 1, 'Null', /* N */
- 2, 'Row-S (SS)', /* L */
- 3, 'Row-X (SX)', /* R */
- 4, 'Share', /* S */
- 5, 'S/Row-X (SSX)', /* C */
- 6, 'Exclusive', /* X */
- to_char(w.request)) mode_requested,
- to_char(w.id1) lock_id1,
- to_char(w.id2) lock_id2
- from gv$lock w, gv$lock h
- where h.block != 0
- and h.lmode != 0
- and h.lmode != 1
- and w.request != 0
- and w.type = h.type
- and w.id1 = h.id1
- and w.id2 = h.id2
- and w.addr != h.addr ;
- create or replace public synonym DBA_GLOBAL_WAITERS for DBA_GLOBAL_WAITERS;
- grant select on DBA_GLOBAL_WAITERS to select_catalog_role;
- grant select on DBA_GLOBAL_WAITERS to dba;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24867586/viewspace-713171/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24867586/viewspace-713171/