latch: library cache 模拟

latch: library cache

引自：http://blueocean926.itpub.net/post/38439/495977

針對latch:library cache,首先我們來看看具體這種等待事件的概念

針對工作過程中最常見的library cache,針對其他的等待，在以後可以做實驗模拟出來

主要有

library cache

library cache pin

library cache lock

我們這裡主要讨論library cache

LIBRARY CACHE

Library cache latch:

The library cache latches protect the cached SQL statements and objects definitions held in the library cache within the shared pool. The library cache latch must be acquired in order to add a new statement to the library cache. During a parse, Oracle searches the library cache for a matching statement. If one is not found, then Oracle will parse the SQL statement, obtain the library cache latch and insert the new SQL.

The first resource to reduce contention on this latch is to ensure that the application is reusing as much as possible SQL statement representation. Use bind variables whenever possible in the application. Misses on this latch may also be a sign that the application is parsing SQL at a high rate and may be suffering from too much parse CPU overhead.If the application is already tuned the SHARED_POOL_SIZE can be increased. Be aware that if the application is not using the library cache appropriately, the contention might be worse with a larger structure to be handled.

The _KGL_LATCH_COUNT parameter controls the number of library cache latches. The default value should be adequate, but if contention for the library cache latch cant be resolved, it may be advisable to increase this value. The default value for _KGL_LATCH_COUNT is the next prime number after CPU_COUNT

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2 to 10.2

Information in this document applies to any platform.

Goal

What is the default value and behavior of the hidden parameter "_KGL_LATCH_COUNT" ?

Solution

"_KGL_LATCH_COUNT" Defaults to 0.

Hidden parameters should not be set to a certain value unless advised by oracle support.

The value of 0 actually means we will use the default behavior, So whenever cpu_count changes(statically or dynamically) the amount of associated latches will depend on it. If you specify a value then this is static.

SQL> show parameter cpu

NAME TYPE VALUE

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

cpu_count integer 1

parallel_threads_per_cpu integer 2

那麼讓我們來做做實驗看下吧，這裡的思路是建立20個表，編寫一些SHELL不使用綁定變量來操作資料庫

conn scott/tiger

create table testlib1 (id number) tablespace users;

create table testlib2 (id number) tablespace users;

create table testlib3 (id number) tablespace users;

create table testlib4 (id number) tablespace users;

create table testlib5 (id number) tablespace users;

create table testlib6 (id number) tablespace users;

create table testlib7 (id number) tablespace users;

create table testlib8 (id number) tablespace users;

create table testlib9 (id number) tablespace users;

create table testlib10 (id number) tablespace users;

create table testlib11 (id number) tablespace users;

create table testlib12 (id number) tablespace users;

create table testlib13 (id number) tablespace users;

create table testlib14 (id number) tablespace users;

create table testlib15 (id number) tablespace users;

create table testlib16 (id number) tablespace users;

create table testlib17 (id number) tablespace users;

create table testlib18 (id number) tablespace users;

create table testlib19 (id number) tablespace users;

create table testlib20 (id number) tablespace users;

vi test4.sh

#!/bin/ksh

i="$1"

while true

do

echo $i

sqlplus scott/tiger << EOF

select * from testlib$i where id = $RANDOM;

exit

EOF

done

alter system flush shared_pool;

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

nohup sh test4.sh 1 &

nohup sh test4.sh 2 &

nohup sh test4.sh 3 &

nohup sh test4.sh 4 &

nohup sh test4.sh 5 &

nohup sh test4.sh 6 &

nohup sh test4.sh 7 &

nohup sh test4.sh 8 &

nohup sh test4.sh 9 &

nohup sh test4.sh 10 &

nohup sh test4.sh 11 &

nohup sh test4.sh 12 &

nohup sh test4.sh 13 &

nohup sh test4.sh 14 &

nohup sh test4.sh 15 &

nohup sh test4.sh 16 &

nohup sh test4.sh 17 &

nohup sh test4.sh 18 &

nohup sh test4.sh 19 &

nohup sh test4.sh 20 &

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

可以看到在測試環境下，出現了大量的library cache等待事件

檢視awr報告

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time Wait Class

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

latch: library cache 988 356 361 23.1 Concurrenc

enq: SQ - contention 119 108 912 7.0 Configurat

latch: row cache objects 141 101 713 6.5 Concurrenc

CPU time 92 5.9

log file sync 210 85 406 5.5 Commit

而我們的hard parse

Load Profile

~~~~~~~~~~~~ Per Second Per Transaction

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

Redo size: 2,663.45 90,173.20

Logical reads: 557.01 18,857.90

Block changes: 25.91 877.10

Physical reads: 0.36 12.25

Physical writes: 1.20 40.60

User calls: 193.37 6,546.80

Parses: 106.87 3,618.15

Hard parses: 7.66 259.35

每秒才7次而已就已經出現了大量的library cache，那麼在生産系統上，可見如果某個時間點出現大量硬解析的情況下，肯定會出現大量的library cache

考慮

1）針對日常不使用綁定變量的消耗資源的SQL，考慮使用綁定變量

2）如果暫時真的修改不了應用，可以嘗試修改_KGL_LATCH_COUNT

3）針對常用的一些library cache查詢

column name format A32 truncate heading "LATCH NAME"

column pid heading "HOLDER PID"

select c.name,a.addr,a.gets,a.misses,a.sleeps, a.immediate_gets,a.immediate_misses,b.pid from v$latch a, v$latchholder b, v$latchname c where a.addr = b.laddr(+) and a.latch# = c.latch# order by a.latch#;

column name format a64 heading 'Name'

select a.name from v$latchname a, v$latch b where b.addr = '&addr' and b.latch#=a.latch#;

column name format a32 heading 'LATCH NAME'

column pid heading 'HOLDER PID'

select c.name,a.addr,a.gets,a.misses,a.sleeps, a.immediate_gets,a.immediate_misses,b.pid from v$latch a, v$latchholder b, v$latchname c where a.addr = b.laddr(+) and a.latch# = c.latch# and c.name like '&latch_name%' order by a.latch#;

--The end --