Blocking locks history
In my previous blogs I have already wrote script for monitoring blocking locks in real time.
http://oraclue.com/2009/05/18/procedure-to-kill-blocking-session-in-rac-11g/
But what if I am accessing database next day or week later and trying to find out who caused blocking lock? I know there are trace files etc but this time I will use v$views.
Let me start with familiar scenario to produce blocking lock ( I have use same code for deadlock scenario in previous blogs):
Session 1:
create table test(a int primary key);
insert into test select rownum from dba_tables where rownum <9;
commit;
update test set a=9 where a=1;
update test set a=10 where a=2;
Table created.
8 rows created.
Commit complete.
1 row updated.
1 row updated.
Session 2:
update test set a=11 where a=3;
update test set a=12 where a=4;
1 row updated.
1 row updated.
again session 1:
update test set a=25 where a=8;
update test set a=13 where a=3;
| So to detect blocking lock that occurred in the past I use active session history or to be precise column blocking_session from view V$ACTIVE_SESSION_HISTORY. Note that blocking_session column is populated only when the session was waiting for enqueues or a “buffer busy” wait. |
For mutex related waits, v$session.blocking_session is not populated in 10.2.
Use Metalink Note 786507.1
v$session.blocking_session is populated in 11g .
My simple script looks like:
SELECT distinct a.sql_id ,a.inst_id,a.blocking_session,a.blocking_session_serial#,a.user_id,s.sql_text,a.module
FROM GV$ACTIVE_SESSION_HISTORY a ,gv$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0 —- exclude SYS user
and a.sample_time > sysdate – 7
It will list all blocking locks for last 7 days.Note GV$ views .So this scripts works for RAC.
Also I can use AWR view WRH$_ACTIVE_SESSION_HISTORY .
select * from (
SELECT a.sql_id ,
COUNT(*) OVER (PARTITION BY a.blocking_session,a.user_id ,a.program) cpt,
ROW_NUMBER() OVER (PARTITION BY a.blocking_session,a.user_id ,a.program
order by blocking_session,a.user_id ,a.program ) rn,
a.blocking_session,a.user_id ,a.program, s.sql_text
FROM sys.WRH$_ACTIVE_SESSION_HISTORY a ,sys.wrh$_sqltext s
where a.sql_id=s.sql_id
and blocking_session_serial# <> 0
and a.user_id <> 0
and a.sample_time > sysdate – 10
) where rn = 1
To test I will run this script to list blocking sessions for my case above ( reduced query for clarity):
SQL_ID SQL_TEXT
————- ——————–
4wf9hgcd0hqvp update test set a=13 where a=3
and fair enough sql Ids involved into blocking locks are reported.
and event better script:
/* Formatted on 5/24/2011 4:55:57 PM (QP5 v5.163.1008.3004) */
SELECT *
FROM ( SELECT a.sql_id,
a.sample_time,
COUNT (*)
OVER (PARTITION BY a.blocking_session, a.user_id, a.program)
cpt,
ROW_NUMBER ()
OVER (PARTITION BY a.blocking_session, a.user_id, a.program
ORDER BY blocking_session, a.user_id, a.program)
rn,
a.blocking_session,
a.user_id,
a.program,
s.sql_text
FROM sys.WRH$_ACTIVE_SESSION_HISTORY a, sys.wrh$_sqltext s
WHERE a.sql_id = s.sql_id
AND blocking_session_serial# <> 0
AND a.user_id <> 0
AND a.sample_time > SYSDATE – 1
ORDER BY a.sample_time)
WHERE rn = 1
Comments
10 Responses to “Blocking locks history”Trackbacks
Check out what others are saying...-
[...] Miladin Modrakovic-Blocking locks history [...]
-
[...] To Rebuild And Make An Index Bigger, Not Smaller (Carry That Weight), Miladin Modrakovic’s Blocking locks history, Marco Gralike’s HOWTO: Partition Binary XML, XMLType Storage and Slavik Markovich’s [...]
You are mixing history and present so that these sql only report lcoks only if the query is currently in memory. Is it intentional? If yes then your process is not a “report lock history”.
If not then WRH$_SQLTEXT should replace v$sql and the distinct with an analytic to report counters:
set long 200
col blocking_session for 9999999 head ‘Blocking|Session’
col program for a20 truncate
set feed on
col sql_text for a90
col rn noprint
select * from (
SELECT a.sql_id ,
COUNT(*) OVER (PARTITION BY a.blocking_session,a.user_id ,a.program) cpt,
ROW_NUMBER() OVER (PARTITION BY a.blocking_session,a.user_id ,a.program
order by blocking_session,a.user_id ,a.program ) rn,
a.blocking_session,a.user_id ,a.program, s.sql_text
FROM sys.WRH$_ACTIVE_SESSION_HISTORY a ,wrh$_sqltext s
where a.sql_id=s.sql_id
and blocking_session_serial# 0
and a.user_id 0
and a.sample_time > sysdate – $NN
) where rn = 1
;
Hi Bernard,
First query report queries in memory.I guess my memory hold data forever
because I got correct result for few days back.
Now, you are correct for second query.This was more copy/paste scenario and not paying attention to it.Updated with your query.
Thanks,
Miladin
Well, in the 1st query , with GV$ACTIVE_SESSION_HISTORY , the column a.machine is not valid and has to be removed because produces an error.
My feeling is that for blocking sessions you are also interested in what exactly EVENT caused that, so with proper reference from CURRENT_OBJ# you might get the name of the table, for example, that has a lock and then decide what to do.
If happens many times you may consider increased PCTFREE to spread it more or increase INITRANS.
This is not addressed by those scripts.
Hi Dimitrios,
You can customize this query any way you like.Whatever fit your needs.:)
What is actual fix is another problem and big topic.Apparently there is big myth out there on internet that altering table storage parameters will fix this issues.In many cases ( most cases I saw ) problem is application itself and bad design ( locking logics).
Thanks,
Miladin
Ok. I can customize it , of course…
But my point is , that when the EVENT is ‘log file sync’ , as it happened when I run the customized query, what can be done with the sql code or application ?
You have to check other matters first…
Whatever you have to do to resolve blocking with locks depends on specific situation and experience…
For me and specific situations, changeing INITRANS actually helped.
(No myth !)
I guess that the point of view is different for various professionals, and I think that you have more programming backgroud .
I wish I have
This query will give you basic information and how are you going to use it is up to you or any other individuals.This just list locks that happened in the past.
And do not forget fixing application’s issues on database side ( what I do sometimes) is not best solution.Everything starts with proper application’s design.
Cheers,
Miladin
Hi All,
I think agree with Miladin opinion. Based my experiences, locking mainly caused by bad design of application logic. Miladin query help us for monitoring and find where parts of the application make it locked.
Regards,
Sigcle
Hi
I tried your query however it only shows the session who was blocked. It doenst show the blocker.