Shared pool Latch/library Cache Latch/lock Pin Introduction

Latch:library Cache--desc V$librarycache;

Latch:library cache is used to protect the hash bucket.

Library cache Lock protects handle.

Library cache pin protects library cache Object--lco.

From 10G, the library cache lock and library cache pin are replaced by the mutex part. The mutex is not discussed for the time being.

Number of Latch:library cache:

sys@ bys3>select name from V$latch_children where name like '%librarycache% ';

Implied parameters: _kgl_bucket_count, the default value is greater than the minimum number of CPUs in the system-no more than 67. The query is displayed as 0--bug.

A latch:library cache manages multiple Librarycache buckets.

Latch:library cache is mostly because the local latch:library cache access more frequent, increase the number and can not be solved.

If the shared pool is too small, it can also cause librarycache latch competition, which in turn causes the shared pool latch to compete---reference awr--shared Pool advisory

SQL with high Version_count can also easily lead to latch:library cache, because latch:library cache is always held until the child lco is searched.

#########

Library cache Lock protects the handle of handle--parent cursors and child cursors

In hard parsing, the Librarycache lock and library cache pin are required to be held in exclusive mode (EXCLUSIVE).

Process access to Lco, first need to latch:librarycache under the protection of the library cache lock to access and modify the handle, and then obtain the library cache pin to access and Lco.

The handle and Lco of the child cursors are accessed as above.

Mode has three categories: null 1;shared 2;exclusive 3;

NULL 1; NULL Lock: Empty lock and exclusive lock are not blocking each other, mainly "mark" purpose. The tag object is in use, or it will be used after the object is marked. Ensure that object memory is not overwritten or released. --can be performed three times to view

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/storage/

Select Kglhdadr,kglhdpar,kglhdlmd,kglobhs0,kglobhd0,kglobhd6 from X$kglob wherekglnaobj as "select * from AAA";

View whether the cursor is closed. Execution is not greater than 3 times, does not cache, if there are other statements, then empty the cache.

SELECT * FROM Bys.dept executed three times,

sys@ bys3>select kglhdadr,kglhdpar,kglhdlmd,kglhdpmd,kglobhs0,kglobhd0,kglobhd6from X$kglob where kglnaobj like ' SELECT * from Bys.dept ';

Kglhdadr kglhdpar kglhdlmd KGLHDPMD KGLOBHS0 KGLOBHD0

-------- -------- ---------- ---------- ---------- -------- --------

2499B1C0 24965DB4 1 0 4372 246c5ce0 252f0dd0----Cached child cursors.

24965DB4 24965DB4 1 0 4500 23cc848c 00

Cached cursors: Child cursor heap 6 can be overwritten when memory is low, and other hadnle cannot be overwritten. The reason: rebuilding the execution plan--the parent heap 0, the child heap 0, and so on--can quickly rebuild the execution plan--and hard parsing, but consumes less resources than normal hard parsing.

The P1 P2 P3 waiting for the event are:

P1=handle Address

P2=lock/pin Address

Ps=mode*100+namespace

Namespace are divided into the following types:

1.SQL Area

2.table/procedure/function/package HEADER

3.PACKAGE Bady

4.TRIGGER

5.INDEX

6.CLUSETER

7.PIPE

13.JAVA SOURCE

14.JAVE RESOURCE

32.JAVA DATA

Common library Cache lock hold mode situation:

The statements held in exclusive possession are:

ALTER TABLE ...,

CREATE OR REPLACE PROCEDURE;

Shared mode hold: SQL parsing phase

In the SQL execution phase, the shared schema is converted to null.

Locate the statement that causes the library cache lock to wait for the event:

Select B.sid from x$kgllk a,v$session b where a.kgllkhdl in (select P1raw fromv$session_wait where wait_time=0 and event= ' Library cache Lock ') and A.kgllkmod<>0and b.saddr=a.kgllkuse;

Common library Cache Pin Holding mode situation:

Held in exclusive mode are:

ALTER PROCEDURE. comple;

Hard parsing results in the execution planning process required

Held in shared mode are: SQL execution phase, procedure execution phase.

Locate the session that caused the library cache pin to wait for the event:

Select A.sid from X$KGLPN b,v$session a where b.kglpnhdl in (select C.p1rawfrom v$session_wait c where c.wait_time=0 and C . event like ' Library cachepin% ') and b.kglpnmod<>0 and A.saddr=b.kglpnuse;