January 13, 2011

How to find out the SQL which caused another session's blocking (PART A)

We can find out that which session (B) is blocked and which session (A) is blocking session B.
We also can easily find out the SQL keep waiting in session B.

But, in some case, we want to find out what SQL in session A caused such blocking, how?

There don't exist a method that can definitely find out target SQL since session A may still be active and execute any other SQLs.
But we can try as following, if you are lucky enough, you can find it out :

▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼
duck_11 > select SID,TYPE,LMODE,REQUEST,CTIME,BLOCK from v$lock where block=1 or request != 0;
SID TY LMODE REQUEST CTIME BLOCK
---   --- ---------  ----------- -------- ---------
27   TX 0             6                90         0
146 TX 6             0                172       1

duck_11 > select SID,SERIAL#,STATUS,SQL_ID,PREV_SQL_ID,EVENT from v$session where sid=146;
SID   SERIAL# STATUS      SQL_ID PREV_SQL_ID  EVENT
-----  ----------  ------------ --------- -----------------  -------------------------------
146   7606         INACTIVE                1tn2c9qm0r65q   SQL*Net message from client

duck_11 > select sql_text from v$sql where sql_id='1tn2c9qm0r65q';
SQL_TEXT
----------------------------------------------------------------------------------
update lockt set l2=567 where l1=333
▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲
Notice values of STATUS and Event column, this session maybe don't execute any other SQLs. So the
result maybe the target SQL we want. You can check it with the blocked SQL.
But what if the worse matter happen, Session A have executed many other SQL after the target SQL ?
What should we do next?
:-) It's time for home. I will continue tomorrow.

0 Comments:

Post a Comment