[转载]library cache pin原理(页 1) - 常用数据库类{ Database Security } - 巨人脚下[ Oracle ] - 邪恶八进制信息安全团队技术讨论组努力为祖国的信息安全撑起一片蓝天

pub!1c 2006-2-22 19:03

[转载]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 <NOTE.166924.1>) 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 -KGLLKUSEsession address -KGLLKHDLPin/lock handle -KGLLKMOD/KGLLKREQHolding/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.sidwaiting_session, h1.sidholding_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)))) andw.kgllktype =h.kgllktype andw.kgllkhdl =h.kgllkhdl andw.kgllkuse = w1.saddr andh.kgllkuse = h1.saddr / The result looks like: WAITING_SESSION HOLDING_SESSION lock ADDRESSMODE_HELD MODE_REQU --------------- --------------- ---- -------- --------- --------- 16 12 Pin03FA2270 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$kgllkwhere kgllkuse in (select saddr from v$session where sid = 16); b. for the blocking session select distinct kglnaobj from x$kgllkwhere 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='&P1RAW' ; ·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

页: [1]

邪恶八进制信息安全团队技术讨论组's Archiver

[转载]library cache pin原理