This discussion is archived
3 Replies Latest reply: Dec 7, 2012 6:40 PM by Victor Armbrust RSS

How to find holder of cross node library cache lock

Bobby Durrett Explorer
Currently Being Moderated
We are on Oracle 11.2.0.2, BP 16. It is Exadata but this is really a generic RAC question so I'm posting this on the RAC forum. OS is Oracle Linux.

We are having high library cache locks that are across the nodes and can't find the original holder of the lock and the SQL it is running. I've read Oracle support documents and internet posts and it appears that you can't use GV$LOCK or x$kgllk for cross node library cache locks in RAC. I have found a reference to gv$ges_blocking_enqueue and have built a possibly useful query using it. But, I wanted to know if anyone out there had experience diagnosing cross node library cache locks and could you tell me if gv$ges_blocking_enqueue is the way to go.

- Bobby

p.s. Here is my as yet unproven query:
select 
gbe.INST_ID,
gbe.HANDLE,
gbe.OWNER_NODE,
gbe.BLOCKED,
gbe.BLOCKER,
s.SID,
s.SERIAL#,
s.USERNAME,
s.MACHINE,
s.TERMINAL,
s.PROGRAM,
s.SQL_ID,
s.MODULE,
s.LAST_CALL_ET,
s.EVENT,
a.sql_text
from 
gv$ges_blocking_enqueue gbe, 
gv$session s,  
gv$sqlarea a
where gbe.inst_id=s.inst_id and
to_char(gbe.pid)=s.process and
s.sql_id=a.sql_id(+) and
s.inst_id=a.inst_id(+)
order by gbe.INST_ID,gbe.HANDLE;
Thanks for any help.

Edited by: Bobby Durrett on Dec 7, 2012 9:50 AM
  • 1. Re: How to find holder of cross node library cache lock
    Victor Armbrust Oracle ACE
    Currently Being Moderated
    Hi Bobby

    I tipically use the script below which shows up the wait events on Database. you can only adpat to show library cache lock.
    I suggest you to double check your memory configuration. I always try to use ASMM or AMM, since the most part of SQL instructions happen on Storage Server basically you'll not need a big amount of it. Try to look for hot blocks as well

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




    set lines           250
    set term           on
    set feed           on
    set verify           off
    set head          on
    set pages          30

    col sid           form 999999
    col event           form a30 trunc
    col wait_time           form 999,999,999
    col seconds_in_wait      form 999,999,999
    col state           form a20 trunc
    col username           form a15 trunc
    col osuser           form a10 trunc
    col status           form a12 trunc
    col machine           form a22 trunc
    col p1               new_value pp1 noprint
    col p2               new_value pp2 noprint
    col sa                new_value vsa noprint
    col shv           new_value vshv noprint

    col segment_name     form a30
    col segment_type      form a12 trunc
    col tablespace_name      form a23
    col owner           form a30
    col sql_text          form a65
    col IO               form 999,999,999,999
    col logon_time          form a21


    undef sid
    undef pp1
    undef pp2
    undef vshv
    undef vsa
    set pagesize 400
    ttitle left 'USUARIOS EM WAIT ' skip 2

    select sw.inst_id,
         sw.sid,
         s.serial#,
         s.username,
         sw.event,
    --     sw.wait_time,
         sw.seconds_in_wait,
    --     sw.state,
         s.status,
         s.osuser,
         substr(s.machine,1,20) machine,
         s.sql_address sa,
         s.sql_hash_value shv,
         sw.p1,
         sw.p2,
         to_char(logon_time, 'dd/mm/yyyy hh24:mi:ss') logon_time
    from      gv$session_wait sw, gv$session s
    where      sw.sid = s.sid
    and      sw.event not like 'SQL%'
    and      sw.event not like 'rdbms%'
    and sw.event not like '%timer'
    and s.username is not null
    order by 1, sw.seconds_in_wait
    /

    undef Sid

    accept Sid prompt 'Qual o SID que deseja verificar ? '

    set term off
    select
         sw.sid,
         s.serial#,
         s.username,
         sw.event,
    --     sw.wait_time,
    --     sw.seconds_in_wait,
    --     sw.state,
         s.status,
         s.osuser,
         substr(s.machine,1,20) machine,
         s.sql_address sa,
         s.sql_hash_value shv,
         sw.p1,
         sw.p2,
         to_char(logon_time, 'dd/mm/yyyy hh24:mi:ss') logon_time
    from      gv$session_wait sw, gv$session s
    where      sw.sid = s.sid
    and      sw.event not like 'SQL%'
    and      sw.event not like 'rdbms%'
    and sw.event not like '%timer'
    and sw.sid=&&Sid
    /
    set term on

    select      
         owner,      
         segment_name,
         segment_type,
         tablespace_name
    from      dba_extents
    where      file_id = &&pp1
    and      &&pp2 between block_id and block_id + blocks-1
    /

    select
         sql_text,
         executions,
         rows_processed,
         disk_reads + buffer_gets IO
    from      gv$sqlarea
    where     hash_value= &&vshv
    and     address='&&vsa'
    /

    ttitle off
    --------------


    I also use this one to identify locks

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

    select inst_id, blocking_session, sid, serial#, wait_class, seconds_in_wait from gv$session
    where blocking_session is not NULL order by blocking_session,seconds_in_wait;


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


    Victor
  • 2. Re: How to find holder of cross node library cache lock
    Bobby Durrett Explorer
    Currently Being Moderated
    Thanks for your post. I'll compare your scripts to the ones I've tried.

    I updated the one in my post to join to gv$sqlarea but still, it isn't working. At least, it may work in some cases, but it isn't showing me what I really need which is which session is the root blocker and what SQL it is running.

    Thanks again,
    Bobby
  • 3. Re: How to find holder of cross node library cache lock
    Victor Armbrust Oracle ACE
    Currently Being Moderated
    Ok Bobby

    Please let us know if you need help

    Victor

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points