latch:library cache

针对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查询
/* ** Display System-wide latch statistics. */ 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#;
/* ** Given a latch address, find out the latch name. */ 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#;
/* ** Display latch statistics by latch name. */ 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#;

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/48010/viewspace-1030604/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/48010/viewspace-1030604/