Shulou(Shulou.com)06/01 Report--

What is the solution of library cache pin/lock? I believe many inexperienced people are at a loss about it. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Oracle uses two data structures to control the concurrency of shared pool: lock and pin.

Lock has a higher level than pin.

Lock is obtained on handle, and before pin an object, you must first obtain the lock of the handle.

There are three main modes of locking: Null,share,Exclusive.

When reading access objects, you usually need to obtain locks in Null mode and share mode.

When you modify an object, you need to get an Exclusive lock.

After locking the Library Cache object, a process must pin the object before accessing it.

Similarly, pin has three modes, Null,shared and exclusive.

Get shared pin in read-only mode and exclusive pin in modified mode.

Usually we get the shared pin when we access, execute the process and Package. If the exclusive pin is held, then the database will wait at this time.

In many report of statspack, we may see the following wait events:

Top 5 Wait Events~ Wait% TotalEvent Waits Time (cs) Wt Time Library cache lock 75884 1409500 48.44latch free 34297906 1205636 41.43library cache pin 563 142491 4.90db file scattered read 146283 75871 2.61enqueue 2211 13003. 45-

We are concerned about library cache lock and library cache pin here. Next, let's study these waiting events.

(1). LIBRARY CACHE PIN wait event

The wait event is described in the Oracle document as follows:

"library cache pin" is used to manage the concurrent access of library cache. Pin an object will cause the corresponding heap to be

Loaded into memory (if not previously loaded), Pins can be obtained in three modes: NULL,SHARE,EXCLUSIVE, which can be thought of as a specific

A formal lock.

When a Library Cache Pin waits for an event, it usually indicates that the Pin is held by another user in an incompatible mode.

The waiting time of "library cache pin" is 3 seconds, of which 1 second is for the PMON background process, that is, wait up to 3 seconds before getting the pin, otherwise it will time out. The parameters of "library cache pin" are as follows, and the main useful ones are P1 and P2: P1-KGL Handle address. P2-Pin address P3-Encoded Mode & Namespace

"LIBRARY CACHE PIN" usually occurs when compiling or recompiling object such as PL/SQL,VIEW,TYPES. Compilation is usually explicit

Such as installing applications, upgrading, installing patches, etc., in addition, "ALTER", "GRANT", "REVOKE" and other operations will also invalidate object

These changes can be observed through object's "LAST_DDL".

When object becomes invalid, Oracle attempts to recompile the object the first time it is accessed, if another session has already set the object pin

Problems arise in library cache, especially when there are a large number of active session and more complex dependence. In some cases, re-

Compiling object may take several hours, blocking other processes trying to access the object.

Let's simulate and explain the wait through an example:

1. Create a stored procedure for testing

[oracle@jumper udump] $sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0-Production on Mon Sep 6 14:16:57 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to an idle instance.SQL > startupORACLE instance started.Total System Global Area 47256168 bytesFixed Size 451176 bytesVariable Size 29360128 bytesDatabase Buffers 16777216 bytesRedo Buffers 667648bytesDatabase mounted.Database opened.SQL > create or replace PROCEDURE pining 2 IS 3 BEGIN 4 NULL; 5 END; 6 / Procedure created.SQL > SQL > create or replace procedure calling 2 is 3 begin 4 pining; 5 dbms_lock.sleep 7 / Procedure created.SQL >

two。 Simulation

First execute the calling procedure and call the pining procedure in the calling procedure

At this point, a shared Pin is obtained on the pining process. If you try to license or recompile the pining at this time, there will be Library Cache Pin waiting.

Until the execution of calling is finished.

Session 1:

[oracle@jumper oracle] $sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.3.0-Production on Mon Sep 6 16:13:43 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.3.0-Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.3.0-Production

SQL > exec calling

At this point, calling begins to execute

Session 2:

[oracle@jumper udump] $sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.3.0-Production on Mon Sep 6 16:14:16 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.3.0-Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.3.0-Production

SQL > grant execute on pining to eygle

Session 2 hangs at this time

Ok, let's start our research:

Starting with v$session_wait, we can find out which session is going through library cache pin waiting.

SQL > select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state 2 from v$session_wait where event like 'library%' SID SEQ# EVENT P1 P1RAW P2 P2RAW P3 WAIT_TIME SECONDS_IN_WAIT STATE -8 268 library cache pin 1389785868 52D6730C 1387439312 52B2A4D0 301 02 WAITING wait 3 seconds before timeout Seq# will change SQL > SID SEQ# EVENT P1 P1RAW P2 P2RAW P3 WAIT_TIME SECONDS_IN_WAIT STATE -8 269 library cache pin 1389785868 52D6730C 1387439312 52B2A4D0 301 02 WAITINGSQL > SID SEQ# EVENT P1 P1RAW P2 P2RAW P3 WAIT_TIME SECONDS _ IN_WAIT STATE -8 270 library cache pin 1389785868 52D6730C 1387439312 52B2A4D0 301 00 WAITING

In this output, the P1 column is the Library Cache Handle Address,Pn field is represented by decimal, and the PnRaw field is represented by hexadecimal

We see that the handle address of the object that library cache pin is waiting for is: 52D6730C

Through this address, we can query the X$KGLOB view to get the specific information of the object:

Note: Xuan KGLOBLLI-[K] ernel [G] eneric [L] ibrary Cache Manager [OB] ject

Col KGLNAOWN for a10col KGLNAOBJ for a20select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH KGLHDOBJfrom X$KGLOBwhere KGLHDADR = '52D6730C'/ADDR KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ- 404F9FF0 52D6730C 52D6730C SYS PINING 2300250318 52D65BA4

Where KGLNAHSH represents the Hash Value of the object

From this we know that we are going through library cache pin waiting on the PINING object.

Then we introduce another internal view, X$KGLPN:

Note:X$KGLPN-- [K] ernel [G] eneric [L] ibrary Cache Manager object [P] i[N] s

Select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL,b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ from v$session a X$kglpn b where a.saddr=b.kglpnuse and b.kglpnhdl = '52D6730C' and b.KGLPNMOD0/ SID USERNAME PROGRAM ADDR KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGLPNMOD KGLPNREQ -13 SYS sqlplus@jumper.hurray.com.cn (TNS V1-V3) 404FA034 52B2A518 51E2013C 51E2013C 52D6730C 52B294C8 20

Through the federation of v$session, you can obtain the information of the user who currently holds the handle.

For our test sid=13, users are holding the handle

So what is this user waiting for?

SQL > select * from v$session_wait where sid=13 SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE-- -13 25 PL/SQL lock timer duration 120000 0001D4C0 0 00 0 00 0 1200 WAITING

Ok, this user is waiting for a PL/SQL lock timer timing.

When we get the sid, we can get the current operation of session by correlating the view of vaccounsession.SQLroomADDRESS and other fields, such as vaccounsession.SQLroomHASHANGVALUE, etc.

SQL > select sql_text from v$sqlarea where vaulsqlarea.hashworthy valueholders 3045375777 sqlarea.hashcards valueholders 3045375777 sqlarea.hashcards valueholders 3045375777

Here we know that the user is executing the calling stored procedure, and the next job is to check what calling is doing.

The work done by our calling is dbms_lock.sleep (3000)

That's why PL/SQL lock timer is waiting.

At this point, we have found the reason for Library Cache Pin.

Simplify the above query:

1. Get the object that Library Cache Pin is waiting for

SELECT addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh Kglhdobj FROM x$kglob WHERE kglhdadr IN (SELECT p1raw FROM v$session_wait WHERE event LIKE 'library%') / ADDR KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ-- -- 404F2178 52D6730C 52D6730C SYS PINING 2300250318 52D65BA4

two。 Get the session information that holds the waiting object

SELECT a.SID, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse, b.kglpnses, b.kglpnhdl, b.kglpnlck, b.kglpnmod, b.kglpnreq FROM v$session a X$kglpn b WHERE a.saddr = b.kglpnuse AND b.kglpnmod 0 AND b.kglpnhdl IN (SELECT p1raw FROM v$session_wait WHERE event LIKE 'library%') / SQL > SID USERNAME PROGRAM ADDR KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGLPNMOD KGLPNREQ-- -13 SYS sqlplus@jumper.hurray.com.cn (TNS V1-V3) 404F6CA4 52B2A518 51E2013C 51E2013C 52D6730C 52B294C8 20

3. Get the code executed by the user who holds the object

SELECT sql_text FROM v$sqlarea WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN (SELECT sql_address, sql_hash_value FROM v$session WHERE SID IN (SELECT SID FROM v$session a) X$kglpn b WHERE a.saddr = b.kglpnuse AND b.kglpnmod 0 AND b.kglpnhdl IN (SELECT p1raw FROM v$session_wait WHERE event LIKE 'library%')) / SQL_TEXT- -BEGIN calling END

Before and after grant, we can dump the contents of shared pool to observe and compare:

SQL > ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 32'

Session altered.

Before grant:

The Handle for pining obtained from the previous query is 52D6730C:

* BUCKET 67790: LIBRARY OBJECT HANDLE: handle=52d6730c name=SYS.PINING hash=891b08ce timestamp=09-06-2004 16:43:51 namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/ [02000000] kkkk-dddd-llll=0000-0011-0011 lock=N pin=S latch#=1-- exists shared pin-- on Object, Null mode lock on handle This mode allows other users to continue locking the object in Null/shared mode: lwt=0x52d67324 [0x52d67324re0x52d67324] ltm=0x52d6732c [0x52d6732cre0x52d6732c] pwt=0x52d6733c [0x52b2a4e8] ptm=0x52d67394 [0x52d67394] ref=0x52d67314 [0x52d67314 0x52d67314] lnd=0x52d673a0 [0x52d67040 lnd=0x52d673a0 0x52d6afcc] LIBRARY OBJECT: bject=52d65ba4 type=PRCD flags=EXS/LOC [0005] pflags=NST [01] status=VALD load=0 DATA BLOCKS: data# heap pointer status pins change alloc (K) size (K)-0 52d65dac 52d65c90 I/P/A 0 NONE 0.30 0.55 4 52d65c40 52d67c08 I/P/A 1 NONE 0.44 0.48

After issuing the grant command:

* BUCKET 67790: LIBRARY OBJECT HANDLE: handle=52d6730c name=SYS.PINING hash=891b08ce timestamp=09-06-2004 16:43:51 namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/ [02000000] kkkk-dddd-llll=0000-0011-0011 lock=X pin=S latch#=1-- due to incomplete calling execution Keep sharing pin-- on object because grant causes the object to be recompiled, the exclusive lock on handle is already held-further need to get Exclusive pin on object, and since shared pin is held by calling, library cache pin is waiting to appear. Lwt=0x52d67324 [0x52d67324,0x52d67324] ltm=0x52d6732c [0x52d6732c,0x52d6732c] pwt=0x52d6733c [0x52b2a4e8,0x52b2a4e8] ptm=0x52d67394 [0x52d67394,0x52d67394] ref=0x52d67314 [0x52d67314 0x52d67314] lnd=0x52d673a0 [0x52d67040 lnd=0x52d673a0 0x52d6afcc] LIBRARY OBJECT: bject=52d65ba4 type=PRCD flags=EXS/LOC [0005] pflags=NST [01] status=VALD load=0 DATA BLOCKS: data# heap pointer status pins change alloc (K) size (K)-0 52d65dac 52d65c90 I/P/A 0 NONE 0.30 0.55 4 52d65c40 52d67c08 I/P/A 1 NONE 0.44 0.48

In fact, the recompile process consists of the following steps, so let's take a look at how lock and pin work alternately:

1. The library cache object of the stored procedure is locked in exclusive mode, which is obtained on the handle

Exclusive locking prevents other users from doing the same, while preventing other users from creating new objects that reference this process.

two。 Pin the object in shared mode to perform security and error checking.

3. The shared pin is released, re-pin the object in exclusive mode, and perform a recompilation.

4. Invalidate all objects that depend on this process

5. Release exclusive lock and exclusive pin

(2). LIBRARY CACHE LOCK wait event

If we issue another grant or compile command at this point, the library cache lock wait event will appear:

Session 3:

[oracle@jumper oracle] $sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0-Production on Tue Sep 7 17:05:25 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.3.0-ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.3.0-ProductionSQL > alter procedure pining compile

This process is suspended, and we can query the v$session_wait view for the following information:

SQL > select * from v$session_wait SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS STATE- -11 143 library cache pin handle address 1390239716 52DD5FE4 pin address 1387617456 52B55CB0 100*mode+namespace 301 0000012D 0 6 WAITING 13 18 library cache lock handle Address 1390239716 52DD5FE4 lock address 1387433984 52B29000 100*mode+namespace 301 0000012D 0 3 WAITING 8 415 PL/SQL lock timer duration 120000 0001D4C0 0000 000 63 WAITING....13 rows selected

Because the lock on handle is already held by session 2 in exclusive mode, session 3 waits.

We can see that granting permissions and recompiling objects in the production database may lead to library cache pin waiting.

Therefore, the above operations should be avoided during peak hours as far as possible.

In addition, the case we tested shows that if there are complex and interactive dependencies in the Package or process, it can easily lead to the emergence of library cache pin.

Therefore, in the process of application development, we should also pay attention to this aspect.

After reading the above, have you mastered what the solution of library cache pin/lock is? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.