Home > Troubleshooting > Blocking locks history

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

About these ads
Categories: Troubleshooting
  1. Bernard Polarski
    October 20, 2009 at 11:49 am | #1

    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
    ;

  2. October 20, 2009 at 1:51 pm | #2

    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

  3. Dimitrios Spanos
    October 29, 2009 at 8:41 am | #3

    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.

  4. October 29, 2009 at 1:41 pm | #4

    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

  5. Dimitrios Spanos
    October 29, 2009 at 2:55 pm | #5

    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 .

  6. October 29, 2009 at 4:06 pm | #6

    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

  7. LinXianHan
    February 29, 2012 at 1:02 pm | #7

    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

  8. john
    January 8, 2013 at 4:21 pm | #8

    Hi

    I tried your query however it only shows the session who was blocked. It doenst show the blocker.

  1. October 21, 2009 at 11:30 pm | #1
  2. October 23, 2009 at 6:22 pm | #2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 41 other followers

%d bloggers like this: