January 17, 2011

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

As i wrote in last post, if session A executed some other SQL after the target SQL, how can we find out the target SQL?
The answer is: v$active_session_history
Be familiar with this table right? It is for ASH feature.

This table sample all active sessions every one second. If you query v$session and find the target session status is active, it is very possible that the target SQL is recorded by v$active_session_history.

duck_11 > select /*+first_rows */ SID,TYPE,LMODE,REQUEST,CTIME,BLOCK from v$lock where block=1 or request !=0;  
---- ---- ---------  ----------   -------- ---------
9     TX  6             0                 1999     1 

10   TX  0             6                 1986     0 

duck_11 > select sid,serial# from v$session where sid=9;
SID          SERIAL# 

---------- ----------
9              29187 

duck_11 >  select max(sample_time),sql_text from v$active_session_history a,v$sql b where a.sql_id=b.sql_id and SESSION_ID=9 and SESSION_SERIAL#=29187 group by sql_text order by max(sample_time) desc;

MAX(SAMPLE_TIME)                     SQL_TEXT
-------------------------------            -----------------

16-JAN-11 PM            delete from t1
16-JAN-11 PM            insert into t1 select * from t2
16-JAN-11 PM            insert into t2 select * from t1
16-JAN-11 PM            insert into lockt select * from lockt

The red part is the target SQL. You can find that after the target SQL, several other no-related SQL is executed. So we can't use the PART A method to find out the target SQL.
v$active_session_history is very useful. If v$active_session_history can't meet your needs, you can query other AWR-related table, these tables name looks like dba_hist_%.


Post a Comment