ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 转library cache pin&lock

转library cache pin&lock

原创 Linux操作系统 作者:yyp2009 时间:2011-05-24 23:26:13 0 删除 编辑

library cache pin
=================
原理
~~~~~
An Oracle instance has a library cache that contains the description of  
different types of objects e.g. cursors, indexes, tables, views, procedures,  
... Those objects cannot be changed when they are used. They are locked by a  
mechanism based on library locks and pins. A session that need to use an object  
will first acquire a library lock in a certain mode (null, shared or exclusive)  
on the object, in order to prevent other sessions from accessing the same  
object (e.g. exclusive lock when recompiling a package or view) or to maintain  
the object definition for a long time. Locking an object is sometimes referred
as the job to locate it in the library cache and lock it in a certain mode.
If the session wants to modify or examine the object, it must acquire after  
the lock also a pin in a certain mode (again null, shared or exclusive).  

Each SQL statement that want to use/modify objects that are locked or pinned  
and whose lock/pin mode is incompatible with the requested mode, will wait  
on events like 'library cache pin' or 'library cache lock' until a timeout  
occurs. The timeout normally occurs after 5 minutes and the SQL statement  
then ends with an ORA-4021. If a deadlock is detected, an ORA-4020 is given  
back.


Dealing with slow downs related to "mysterious" library cache pins  
and load locks we should look for the reason of the database object  
invalidations. They are likely to be triggered by actions causing  
changes to "LAST_DDL" attribute of database objects that have other
dependent ones. Typically they are the object maintenance operations -  
ALTER, GRANT, REVOKE, replacing views, etc. This behavior. is described
in Oracle Server Application Developer's Guide as object dependency
maintenance.

After object invalidation, Oracle tries to recompile the object at the
time of the first access to it. It may be a problem in case when other
sessions have pinned the object to the library cache. It is obvious that
it is more likely to occur with more active users and with more complex
dependencies (eg. many cross-dependent packages or package bodies).  
In some cases waiting for object recompilation may even take hours  
blocking all the sessions trying to access it.


ORA-04021 timeout occurred while waiting to lock object %s%s%s%s%s".
Cause:  While trying to lock a library object, a time-out occurred.
Action: Retry the operation later.

ORA-04020 deadlock detected while trying to lock object %s%s%s%s%s
Cause:  While trying to lock a library object, a deadlock is detected.
Action: Retry the operation later.
(see )

2.   Which views can be used to detect library locking problems?
----------------------------------------------------------------

Different views can be used to detect pin/locks:

DBA_KGLLOCK : one row for each lock or pin of the instance
-KGLLKUSE  session address
-KGLLKHDL  Pin/Lock handle
-KGLLKMOD/KGLLKREQ  Holding/requested mode
0           no lock/pin held
1           null mode
2           share mode
3           exclusive mode
-KGLLKTYPE Pin/Lock
(created via the $ORACLE_HOME/rdbms/admin/catblock.sql)

V$ACCESS : one row for each object locked by any user
-SID       session sid
-OWNER     username
-OBJECT    object name
-TYPE      object type

V$DB_OBJECT_CACHE : one row for each object in the library cache
-OWNER         object owner
-NAME          object name or cursor text
-TYPE          object type
-LOCKS         number of locks on this object
-PINS          number of pins on this object

DBA_DDL_LOCKS  : one row for each object that is locked (exception made of the cursors)
-SESSION_ID
-OWNER
-NAME
-TYPE
-MODE_HELD
-MODE_REQUESTED

V$SESSION_WAIT : each session waiting on a library cache pin or lock is blocked by some other session
-p1 = object address
-p2 = lock/pin address


3.   How to find out why an ORA-4021 occurs?
--------------------------------------------

When you execute the statement that generates the ORA-4021, it is possible  
during the delay of 5 minutes to detect the reason for the blocking situation.  
Following query can be used to find the blocking and waiting sessions:

FYI: You need to run the script. called "catblock.sql" first.  
===  This script. can be found in:  $ORACLE_HOME/rdbms/admin/catblock.sql  
  

select /*+ ordered */ w1.sid  waiting_session,
        h1.sid  holding_session,
        w.kgllktype lock_or_pin,
        w.kgllkhdl address,
        decode(h.kgllkmod,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
           'Unknown') mode_held,  
        decode(w.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
           'Unknown') mode_requested
  from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where
  (((h.kgllkmod != 0) and (h.kgllkmod != 1)
     and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
   and
     (((w.kgllkmod = 0) or (w.kgllkmod= 1))
     and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
  and  w.kgllktype         =  h.kgllktype
  and  w.kgllkhdl         =  h.kgllkhdl
  and  w.kgllkuse     =   w1.saddr
  and  h.kgllkuse     =   h1.saddr
/

The result looks like:

WAITING_SESSION HOLDING_SESSION LOCK ADDRESS  MODE_HELD MODE_REQU
--------------- --------------- ---- -------- --------- ---------
             16              12 Pin  03FA2270 Share     Exclusive

The object that is locked can be found with v$object_dependency and  
should be the same as the one mentioned in the ORA-4021 error message.
e.g.
select to_name from v$object_dependency where to_address = '03FA2270';
should give:

TO_NAME
-------------
DBMS_PIPE

You can find which library objects are used by each session via following  
queries, e.g.  
a. for the blocked session:

select distinct kglnaobj from x$kgllk  where  
kgllkuse in (select saddr from v$session where sid = 16);

b. for the blocking session

select distinct kglnaobj from x$kgllk  where  
kgllkuse in (select saddr from v$session where sid = 12);

One of those objects can be the cursor or statement that each session is  
executing/trying to execute.

You can also use the $ORACLE_HOME/rdbms/admin/utldtree.sql utility to find out  
how the dependency tree looks like and which objects are dependent on e.g.  
DBMS_PIPE. One of those objects will be the sql statement of the holding  
session. A variant script. on utldtree.sql stands in [NOTE:139594.1] and  
gives which objects an object depends on.




        Library cache pins are used to manage library cache concurrency.
        Pinning an object causes the heaps to be loaded into memory (if not already loaded).
        PINS can be acquired in NULL, SHARE or EXCLUSIVE modes and can be considered like a special form. of lock.
        A wait for a "library cache pin" implies some other session holds that PIN in an incompatible mode.
        
        P1 = Handle address
        P2 = Pin address
        P3 = Encoded Mode & Namespace
        
        ·Handle address
        ~~~~~~~~~~~~~~~~                                                                                          
        Use P1RAW rather than P1                                                                                         
        This is the handle of the library cache object which the waiting session wants to acquire a pin on.   

        
查找library cache对象
~~~~~~~~~~~~~~  
        The actual object being waited on can be found using                                                            
          SELECT kglnaown "Owner", kglnaobj "Object"                                                                     
            FROM x$kglob                                                                                                
           WHERE kglhdadr='&1RAW'                                                                                       
          ;                                                                                                              
        ·Pin address
        ~~~~~~~~~~~~~
        Use P2RAW rather than P2                                                                                         
        This is the address of the PIN itself.                                                                           
        ·Encoded Mode & Namespace                                                                                 
        ~~~~~~~~~~~~~~~~~~~~~~~~~
        In Oracle 7.0 - 8.1.7 inclusive the value is 10 * Mode + Namespace.                                             
        In Oracle 9.0 - 9.2 inclusive the value is 100 * Mode + Namespace.                                               
                                                                                                                        
        Where:                                                                                                           
                                                                                                                        
        Mode is the mode in which the pin is wanted. This is a number thus:                                             
        o        2 - Share mode                                                                                          
        o        3 - Exclusive mode                                                                                       
                                                                                                                        
        Namespace is just the namespace number of the namespace in the library cache in which the required object lives:
        o        0 SQL Area                                                                                               
        o        1 Table / Procedure / Function / Package Header                                                         
        o        2 Package Body                                                                                          
        o        3 Trigger                                                                                                
        o        4 Index                                                                                                  
        o        5 Cluster                                                                                                
        o        6 Object                                                                                                
        o        7 Pipe                                                                                                   
        o        13 Java Source                                                                                          
        o        14 Java Resource                                                                                         
        o        32 Java Data         



 


====================
library cache lock的成因和解决方法
====================
两种原因
~~~~~~~~~
* a DML operation that is hanging because the table which is accessed is currently  
   undergoing changes (ALTER TABLE). This may take quite a long time depending on  
   the size of the table and the type of the modification  
   (e.g. ALTER TABLE x MODIFY (col1 CHAR(200) on thousands of records).  

* The compilation of package will hang on Library Cache Lock and Library Cache Pin  
  if some users are executing any Procedure/Function defined in the same package.  

两种解决办法
~~~~~~~~~~~~~~~
METHOD 1: SYSTEMSTATE ANALYSIS
------------------------------

One way of finding the session blocking you is to analyze the system state dump.
Using the systemstate event one can create a tracefile containing detailed  
information on every Oracle process. This information includes all the resources
held & requested by a specific process.

Whilst an operation is hanging, open a new session and launch the following
statement:

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 8';

Oracle will now create a systemstate tracefile in your USER_DUMP_DEST directory.
Get the PID (ProcessID) of the 'hanging' session from the V$PROCESS by matching  
PADDR from V$SESSION with ADDR from V$PROCESS:

SELECT PID FROM V$PROCESS WHERE ADDR=
        (SELECT PADDR FROM V$SESSION WHERE SID=sid_of_hanging_session);

The systemstate dump contains a separate section with information for each  
process. Open the tracefile and do a search for 'PROCESS pid_from_select_stmt'.
In the process section look up the wait event by doing a search on 'waiting for'.

Example output:

PROCESS 8:
   ----------------------------------------
   SO: 50050b08, type: 1, owner: 0, flag: INIT/-/-/0x00
   (process) Oracle pid=8, calls cur/top: 5007bf6c/5007bf6c, flag: (0) -
             int error: 0, call error: 0, sess error: 0, txn error 0
   (post info) last post received: 82 0 4
               last post received-location: kslpsr
               last process to post me: 5004ff08 1 2
               last post sent: 0 0 13
               last post sent-location: ksasnd
               last process posted by me: 5004ff08 1 2
     (latch info) wait_event=0 bits=0
     Process Group: DEFAULT, pseudo proc: 50058ac4
     O/S info: user: daemon, term: pts/1, ospid: 15161
     OSD pid info: 15161
     ----------------------------------------
     SO: 5005f294, type: 3, owner: 50050b08, flag: INIT/-/-/0x00
     (session) trans: 0, creator: 50050b08, flag: (41) USR/- BSY/-/-/-/-/-
               DID: 0001-0008-00000002, short-term DID: 0000-0000-00000000
               txn branch: 0
               oct: 6, prv: 0, user: 41/LC
     O/S info: user: daemon, term: pts/1, ospid: 15160, machine: goblin.forgotten.realms
               program: sqlplus@goblin.forgotten.realms (TNS V1-V3)
     application name: SQL*Plus, hash value=3669949024
     waiting for 'library cache lock' blocking sess=0x0 seq=253 wait_time=0  --等待事件,seq 次数,
!>>              handle address=5023ef9c, lock address=5019cad4, 10*mode+namespace=15

Using the 'handle address' you can look up the process that is keeping a lock
on your resource by doing a search on the address within the same tracefile.

Example output:  

PROCESS 9:
   ----------------------------------------
   SO: 50050e08, type: 1, owner: 0, flag: INIT/-/-/0x00
   (process) Oracle pid=9, calls cur/top: 5007bbac/5007bbfc, flag: (0) -
             int error: 0, call error: 0, sess error: 0, txn error 0
  
    ....
   
        ----------------------------------------
         SO: 5019d5e4, type: 34, owner: 5015f65c, flag: INIT/-/-/0x00
!>>      LIBRARY OBJECT PIN: pin=5019d5e4 handle=5023ef9c mode=X lock=0  --handle address
         user=5005fad4 session=5005fad4 count=1 mask=0511 savepoint=118218 flags=[00]
   
From the output we can see that the Oracle process with PID 9 has an exclusive
lock on the object we are trying to access. Using V$PROCESS and V$SESSION we can
retrieve the sid,user,terminal,program,... for this process. The actual statement
that was launched by this session is also listed in the tracefile (statements and
other library cache objects are preceded by 'name=').


METHOD 2: EXAMINE THE X$KGLLK TABLE
-----------------------------------

The X$KGLLK table (accessible only as SYS/INTERNAL) contains all the  
library object locks (both held & requested) for all sessions and
is more complete than the V$LOCK view although the column names don't
always reveal their meaning.

You can examine the locks requested (and held) by the waiting session  
by looking up the session address (SADDR) in V$SESSION and doing the  
following select:

select * from x$kgllk where KGLLKSES = 'saddr_from_v$session'

This will show you all the library locks held by this session where
KGLNAOBJ contains the first 80 characters of the name of the object.
The value in KGLLKHDL corresponds with the 'handle address' of the
object in METHOD 1.

You will see that at least one lock for the session has KGLLKREQ > 0  
which means this is a REQUEST for a lock (thus, the session is waiting).  
If we now match the KGLLKHDL with the handles of other sessions in  
X$KGLLK that should give us the address of the blocking session since
KGLLKREQ=0 for this session, meaning it HAS the lock.
  
SELECT * FROM X$KGLLK LOCK_A  
WHERE KGLLKREQ = 0
   AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
               WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
               AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
               AND KGLLKREQ > 0);

If we look a bit further we can then again match KGLLKSES with SADDR  
in v$session to find further information on the blocking session:

SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in  
  (SELECT KGLLKSES FROM X$KGLLK LOCK_A  
   WHERE KGLLKREQ = 0
     AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
                 WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
                 AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
                 AND KGLLKREQ > 0)
  );

In the same way we can also find all the blocked sessions:

SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in  
  (SELECT KGLLKSES FROM X$KGLLK LOCK_A  
   WHERE KGLLKREQ > 0
     AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
                 WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKING SESSION */
                 AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
                 AND KGLLKREQ = 0)
  );

 

 

What's the difference between pins and gets?
       

When an object has been located in (or loaded into) the library cache, the session "remembers" its location by maintaining a null mode lock on the object. When a session needs to use an object, if it already (or still) has the library cache lock, then it can proceed with the pin request. If it does not yet have a lock then a get operation is needed to get a lock before the pin can be requested.

This probably makes little sense unless you understand the distinction between the handle of a library cache object and its heaps. Locks protect the handle. Pins protect the heaps. The heaps are the "body" parts of the object that get loaded as required, except for heap 0 which contains the identifying information about the object. When we speak of an object being aged out and then reloaded, it is actually the heaps other than heap 0 of which we are speaking. Heap 0 is never aged out while any session is holding a lock on its handle.

The fundamental reason for the distinction between locks and pins is that it in theory allows an object's identity to be checked using a shared lock on the handle while another session has an exclusive pin on one or more of its heaps. The distinction is also important for maintaining cache coherence.

Hope this is not going to trigger a flood of follow-up questions ....

       

how does Oracle determine that a needed object was previously located in the library cache before it would be pinned ? Does Oracle use some information in the library cache or it firstly tries to get the object ?

       

A get is an attempt to locate an object in the library cache. If it is not found, it is loaded. Database object definitions are loaded from the data dictionary via the dictionary cache. Cursors are loaded by parsing the statement text.

A pin is an attempt to use a previously located object in the library cache. If it has been aged out, then it must be reloaded.

       

Could anybody please explain about what is the difference between gets and pins and correspondingly between gethits and pinhits in the v$librarycache view?

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13750068/viewspace-696194/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Oracle 高水位(HWM)
请登录后发表评论 登录
全部评论

注册时间:2008-10-17

  • 博文量
    330
  • 访问量
    1037200