3 Replies Latest reply: Dec 7, 2012 8:40 PM by Victor Armbrust RSS

    How to find holder of cross node library cache lock

    Bobby Durrett
      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
          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
            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
              Ok Bobby

              Please let us know if you need help

              Victor