Discussion on Shared Pool (5)

xiaoxiao2021-03-06 64

Original link:

http://www.eygle.com/internal/shared_pool-5.htm

Oracle uses two data structures to make Shared Pool concurrent control: Lock and Pin.lock have a higher level than PIN.

Lock is available on Handle, you must first get the Handle's lock. There are three modes: null, Share, Exclusive. When reading access objects, you usually need to get null (empty) mode and Share ( Share) Mode lock. When you modify the object, you need to get Exclusive lock.

After locking the library cache object, a process must PIN before access. There are three modes, null, shared, and exclusive. Read-only mode, the shared PIN is shared, and the modification mode gets his PIN.

Usually we have access, executive, and package is shared, if you hold him PIN is held, then the database will be waiting at this time. In many statspack reports, we may see the following wait events:

Top 5 Wait Events ~~~~~~~~~~~~~~~~~ Wait% Totalevent Waits Time (CS) WT Time ------------------- ------------------------ ------------ ------ library cache lock 75,884 1,409,500 48.44latch free 34,297,906 1,205,636 41.43library cache pin 563 142,491 4.90db file scattered read 146,283 75,871 2.61enqueue 2,211 13,003 .45 --------------- ----------------------------------------------

The library cache lock and library cache pin here are our concern. Let's take a look at these waiting events.

(1) .library Cache PIN Waiting for the event

This is an overview of the Oracle documentation: "library cache pin" is used to manage the concurrent access to library cache, and an Object will cause the corresponding HEAP to be loaded (if it is not loaded), Pins can be in three NULL, Share, Exclusive, can be considered to be a specific form of lock. When the library cache pin is waiting to appear, the PIN is usually held by other users.

"library cache pin" is 3 seconds, one second for the PMON background process, that is, waiting for 3 seconds before getting the PIN, otherwise it will time out. "Library Cache Pin" parameters are as follows, useful Mainly P1 and P2: P1 - KGL HANDLE ADDRESS. P2 - PIN Address P3 - Encoded Mode & Namespace "library cache pin" is usually occurs when compiling or recompile PL / SQL, View, Types and other Object. Compile usually Obvious, such as installing applications, upgrades, installation patches, etc. When Object becomes invalid, Oracle will try to recompile it in the first time, if other session has already issued this Object Pin to library cache, there is a lot of activities when there is a lot of activities. Session and more complicated dependence. In some cases, recompile Object may take a few hours to block other tries to try to access this object. Let's simulate and explain this in an example:

1. Create a test stored procedure

[Oracle @ Jumper Udump] $ SQLPLUS "/ as sysdba"

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

Connected to an iDLE Instance.

SQL> StartupoPoracle Instance Started.

Total System Global Area 47256168 bytesFixed Size 451176 bytesVariable Size 29360128 bytesDatabase Buffers 16777216 bytesRedo Buffers 667648 bytesDatabase mounted.Database opened.SQL> create or replace PROCEDURE pining 2 IS 3 BEGIN 4 NULL; 5 END; 6 /

Procedure create.

SQL> SQL> Create or Replace Procedure Calling 2 IS 3 Begin 4 Pining; 5 dbms_lock.sleep (3000); 6 End; 7 /

Procedure create.

SQL>

2. Simulation first execute the calling process, call the Pining process during the calling process At this time, you will get a shared PIN on the PINING.

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 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.3.0 - Production

SQL> EXEC CALLING

At this time, Calling started

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

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 - Production

SQL> Grant Execute on Pining to Eygle;

Sensions 2 hangs at this time

OK, we start our research:

From V $ session_wait, we can get what sessions are experiencing 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%'

Waiting for 3 seconds, timeout, SEQ # will change

SQL>

SID SEQ # Event P1 P1RAW P2 P2RAW P3 WAIT_TIME Seconds_in_Wait State ---- -------------------------------- ------------ ----------------------- - - ---------------------------------- 8 270 Library Cache Pin 1389785868 52D6730C 1387439312 52B2A4D0 301 0 0 waiting in this In the output, the P1 column is the library cache handle address, and the PN field is 10 credit, and the PNRAW field is 16 credit representation.

We see that the Handle address of the object of the Library Cache PIN is: 52D6730C passes this address, we query the X $ KGLOB view to get the specific information of the object:

Note: x $ kglob - [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

Here KGlnahsh represents the Hash Value of the object

Thus we know, you are experiencing the Waiting Cache Pin 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.kglpnmod <> 0 /

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 2 0 You can get user information currently holding the handle of the Handle through the Joint V $ SESSION. For our test SID = 13, the Handle is held

So what is this user is waiting?

SQL> SELECT * from V $ session_wait where sid = 13;

SID SEQ # Event P1text P1 Graw 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.

Get the SID, we can associate v $ SQLText, v $ SQLTEXT, V $ SQLAREA, etc. vs, v $ session.sql_address, and V $ SQLText, V $ SQLAREA, etc. to get the current session being executed.

SQL> SELECT SQL_TEXT FROM V $ SQLAREA WHERE V $ SQLAREA.hash_Value = '3045375777';

SQL_Text ------------------------------------------------- ------------------------------ Begin Calling;

Here we get this user is performing the stored procedure, and the next job should check what Calling is doing.

Our work is dbms_lock.sleep (3000) is why PL / SQL LOCK TIMER is waiting for

At this point, I found the reason for library cache pin.

Simplify the above query:

1. Get objects waiting for library cache pin

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

2. Get session information holding a 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 KGLPNS KGLPNHDL KGLPNLCK KGLPNMOD KGLPNREQ --------------------------------------- ----------------------------------- ---------- ------------------------ ---------- 13 Sys Sqlplus @ Jumper.hurray.com.cn (TNS V1-V3) 404F6CA4 52B2A518 51E2013C 51E2013C 52D6730C 52B294C8 2 0

3. Get the code that holds an object user

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;

Before the GRANT and after we can dump the content of Shared Pool's content:

SQL> Alter Session Set Events 'Immediate Trace Name Library_Cache Level 32'

Session altered.

Before GRANT:

Getting a Pining 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 = x Pin = s Latch # = 1-- Since the execution is not completed, keep the shared share in Object, because GRANT can cause the object to be recompiled, So in Handle, the lock has been held - further needs to get Exclusive PIN on Object, because Shared Pin is held by Calling, so 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,0x52d6afcc] LIBRARY OBJECT: object = 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 52D65C4052D67C08 I / P / A 1 None 0.44 0.48 In fact, the Recompile process contains the following steps, let's take a look at how Lock and PIN alternately play the role: 1. The library cache object of the stored procedure is locked by the row of his mode. This lock is in Handle. The EXCLUSIL locks can prevent other users from performing the same operation while preventing other users from creating new references to this process. 2. In the Shared mode PIN this object to perform security and error checking .3. Sharing PIN is released, Re-arranged his mode PIN this object, perform recompilation. 4. Make all objects that depend on the process fail 5. Release Exclusive Lock and Exclusive Pin

(2) .Library Cache Lock Waiting for the event

If we issue a grant or compile command, then the library cache lock 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 2004

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 - Productions QL> ALTER Procedure Pining Compile;

This process hangs, we query V $ session_wait view to get the following information:

SQL> SELECT * from V $ session_wait;

SID SEQ # Event P1text P1 Graw 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 WAITI 8 415 PL / SQL LOCK TIMER DURATION 120000 0001D4C0 0 00 0 00 0 63 Waiting ....

13 rows selected

Since the Lock on Handle has been held by Session 2 in Exclusive mode, SESSION 3 generates waiting.

We can see that the grant of permissions in the production database can cause the Library Cache PIN waiting to appear. So you should try to avoid the above operations during the peak period.

In addition, the case we test itself will indicate that if there is complex, interactive dependence, the relationship between the panel or the process is extremely easy to cause the Library Cache PIN. So we should also pay attention to this in the process of application development.

转载请注明原文地址:https://www.9cbs.com/read-119441.html

9cbs

New Post(0)