oracle 等待原因查找,查询引起锁等待的SQL语句

本帖最后由 bfc99 于 2014-9-22 17:34 编辑

主要通过四个性能视图：

1、v$locked_object  查看当前哪些对象上有锁，及其所属的会话。

2、v$session_blocker  查看相关会话的阻止关系，即谁阻止了谁，谁是锁之源。

3、v$session  查看指定会话上运行的SQL_ID

4、v$sql  查看指定SQL_ID的具体内容。

举例如下：

新建会话a

SQL>  select * from a for update;

ID COL1

---------- --------------------------------------------------------------------------------

1                      1

0                      2

SQL>

再新建一个会话b，在这个会话中，查看目前被锁定的对象。

SQL> SELECT object_id,session_id,locked_mode FROM v$locked_object;

OBJECT_ID SESSION_ID LOCKED_MODE

---------- ---------- -----------

19824          125                3

可以看到，目前被锁定的对象ID是19824，会话的SID是125.(在我的环境下，经查询，可以确定19824就是表a)

再新建一个会话C，发出另一个语句

SQL> select * from a where id=1 for update;

回到会话b,查看当前的对象锁定情况。

SQL> SELECT object_id,session_id,locked_mode FROM v$locked_object;

OBJECT_ID SESSION_ID LOCKED_MODE

---------- ---------- -----------

19824           16                3

19824          125                3

可以发现，多了一行，其对象ID是一样的，会话ID为16.显然，在这种情况下，我们可以很清楚地知道是SID为125的会话，阻止了SID为16的会话对表a的锁定。但如果我们不是这样一步一步的监控，就不知道到底是谁阻止了谁。这时，可以借助v$session_blocker视图来判断。

还是在会话b中，执行以下语句：

SQL> SELECT SID,blocker_sid FROM v$session_blockers;

SID BLOCKER_SID

---------- -----------

16           125

从这里就可以看出，阻止者是SID为125的会话，而被阻止的是SID为16的会话。

接下来，我们就要去v$session中，找出SID125上到底运行的是哪个SQL语句。

SQL> SELECT SID,sql_id,prev_sql_id FROM v$session WHERE SID IN (16,125);

SID SQL_ID         PREV_SQL_ID

---------- ------------- -------------

16 8w0ct9utt04pf 8w0ct9utt04pf

125                 14w0qs44p3w6u

可以看到，SID125上当前(SQL_ID列)为空，说明当前该会话上没有正在跑的SQL(select * from a for update已经执行完毕，只是还没有提交或回滚)，而上一条运行过的SQL_ID(prev_sql_id列)是有同内容的，用它去v$sql中去查。

SQL> SELECT sql_text FROM v$sql WHERE sql_id='14w0qs44p3w6u';

SQL_TEXT

--------------------------------------------------------------------------------

select * from a for update

SQL>

可见，已经可以找到引起这个锁等待最初的语句了。