bulk.keep moving

latch中的cache buffers chains

最近系统压力一直很大，先是在STATSPACK的TOP EVENT 中第一，第二位出现ENQUEN和LATCHE。分析应用程序，调整了某些SQL和要求开发部去掉一些不必要而且又耗资源的日志事务操作，现在ENQUEN下去了，但是LATCH一直还在TOP5中徘徊，一般都排在三和四。其实在SQL方面还有调整的空间，但是似乎系统压力缓解后，上面也就没有再过问，不着急了(通常系统负载越高，程序越慢，我的工作进展就越快)。除了调整SQL可以缓解这方面的压力外，我就思考能否调整一些参数来辅助下，让效果更好。由于某些原因，以下的调整没有在生产库上实施过，权当练兵吧。

参考文档：http://www.itpub.net/showthread.php?threadid=177484&pagenumber=
http://asktom.oracle.com/pls/ask/f?p=4950:8:11052001865409674410::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1229436447262

v$latch表的每一行包括了对不同类型latch的统计，每一列反映了不同类型的latch请求的活动情况。不同类型的latch请求之间的区别在于，当latch不可立即获得时，请求进程是否继续进行。按此分类，latch请求的类型可分为两类：willing-to-wait和immediate。

Willing-to-wait : 是指如果所请求的latch不能立即得到，请求进程将等待一很短的时间后再次发出请求。进程一直重复此过程直到得到latch。

Immediate：是指如果所请求的latch不能立即得到，请求进程就不再等待，而是继续执行下去。

在v$latch中的以下字段反映了Willing-to-wait请求：

GETS---成功地以Willing-to-wait请求类型请求一个latch的次数。

MISSES---初始以Willing-to-wait请求类型请求一个latch不成功的次数。

SLEEPS---初始以Willing-to-wait请求类型请求一个latch不成功后，进程等待获取latch的次数。

在v$latch中的以下字段反映了Immediate类请求：

IMMEDIATE_GETS---以Immediate请求类型成功地获得一个latch的次数。

IMMEDIATE_MISSES---以Immediate请求类型请求一个latch不成功的次数。

latch有40余种，但作为DBA关心的主要应有以下几种：

Cache buffers chains latch: 当用户进程搜索SGA寻找database cache buffers时需要使用此latch。

Cache buffers LRU chain latch: 当用户进程要搜索buffer cache中包括所有 dirty blocks的LRU (least recently used) 链时使用该种latch。

Redo log buffer latch: 这种latch控制redo log buffer中每条redo entries的空间分配。

Row cache objects latch: 当用户进程访问缓存的数据字典数值时，将使用Row cache objects latch。

TOM关于LATCH产生得解释：

Blocks in the buffer cache are placed on linked lists(cache buffer chains) which hang off a hash table.
The hash chain that a block is placed on is based on the DBA and CLASS of the block. Each hash chain is
protected by a single child latch. Processes need to get the relevant latch to allow them the scan a hash chain for a buffer so that the linked list does not change underneath them.

Contention for these latches can be caused by:

- Very long buffer chains.
- very very heavy access to the same blocks.

现在来看下手下得生产数据库

SQL> select count(*) from v$latch_children where misses>0 and name='cache buffers chains';

COUNT(*)
----------
2048

SQL>select addr,name,misses from v$latch_children where misses>0 and name='cache buffers chains' order by misses desc;
ADDR NAME MISSES
-------- ---------------------------------------------------------------- ----------
..........
69CC28BC cache buffers chains 1591
69A3CF1C cache buffers chains 1591
69CBDDFC cache buffers chains 1589
69B92DFC cache buffers chains 1586
69C5DEBC cache buffers chains 1585
69AB0354 cache buffers chains 1585
69A70F9C cache buffers chains 1585
69A81F54 cache buffers chains 1585
...........

SQL> select bh.addr,obj.name obj_name,bh.tch touch from x$bh bh,sys.file$ f,v$datafile fl,sys.obj$ obj,sys.ts$ ts
2 where fl.file#=f.file# and bh.file#=fl.file# and obj.dataobj#=bh.obj and bh.ts#=ts.ts#
3 and bh.HLADDR='69CC28BC' and bh.tch>0 order by bh.tch desc;

ADDR OBJ_NAME TOUCH
-------- ------------------------------ ----------
B6FD3078 IDX_GCTID_IUID_GM634 24
B6FD3078 REG_LOG 8
B6FD2F9C AGENT_CARD_TYPE 7
B6FD3078 RESELLER_AGENTCARD_PRICE 6
B6FD3078 RESELLER_LOG 6
B6FD3078 IDX_ACL_AGENTID_LOGTIME 6
B6FD3078 RESELLER_LOG 6
.............

就是上面涉及到的这些对象，造成LATCH

SQL> select COUNT(*) from x$bh bh,sys.file$ f,v$datafile fl,sys.obj$ obj,sys.ts$ ts
2 where fl.file#=f.file# and bh.file#=fl.file# and obj.dataobj#=bh.obj and bh.ts#=ts.ts#
3 and bh.HLADDR='69CC28BC' and bh.tch>0;

COUNT(*)
----------
51

一段文档资料：

Under 8.0, the default was next_prime(db_block_buffers/4), and the
number of _db_block_hash_latches was 1:1 with the number of buckets.
Under 8i, the world changed a lot. The default number of hash buckets
is 2 * db_block_buffers, but the latches work differently. It's
really not necessary to have one latch per hash chain, so, Oracle made
them a pooled resource. When you need to interrogate a hash chain,
you grab a latch from the pool and assign it to a hash chain. That
prevents anyone else from modifying the chain or it's contents while
your process is using it. So, in 8i, the size of the latch pool is
dynamic but is set to 1024 for most cases. It's smaller for very
small buffer caches and larger for very large buffer caches. The
formula is:
if (db_block_buffers < 2052) then
db_block_hash_latches = 2^trunc(log(2,db_block_buffers - 4) - 1)
else if(2052 =< db_block_buffers <= 131075) then
db_block_hash_latches = 1024
else if(db_block_buffers > 131075)
db_block_hash_latches = 2^trunc(log(2,db_block_buffers - 4) - 6)
end if

So, under 8i, you probably don't need to touch _db_block_hash_buckets,
as 2 * db_block_buffers is almost certainly more than adequate. And
unless you're dealing huge numbers of concurrent users and a
relatively small buffer cache, you probably don't need to mess with
_db_block_hash_latches, either.

增大 _db_buffer_hash_latches 可以更快速的查找到 blocks 并且降低 cache buffer chains 等待
我的操作系统是9I,db_block_buffers 为DB_CACHE_SIZE，如果我调整此参数，那么我应该是采取
db_block_hash_latches = 2^trunc(log(2,DB_CACHE_SIZE - 4) - 6)

SQL> show parameter db_cache_size;

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
db_cache_size big integer 1073741824

SQL>select name,
value,
decode(isdefault, 'TRUE', 'Y', 'N') as "Default",
decode(ISEM, 'TRUE', 'Y', 'N') as SesMod,
decode(ISYM, 'IMMEDIATE', 'I', 'DEFERRED', 'D', 'FALSE', 'N') as SysMod,
decode(IMOD, 'MODIFIED', 'U', 'SYS_MODIFIED', 'S', 'N') as Modified,
decode(IADJ, 'TRUE', 'Y', 'N') as Adjusted,
description
from (--GV$SYSTEM_PARAMETER
select x.inst_id as instance,
x.indx + 1,
ksppinm as name,
ksppity,
ksppstvl as value,
ksppstdf as isdefault,
decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE') as ISEM,
decode(bitand(ksppiflg / 65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 'FALSE') as ISYM,
decode(bitand(ksppstvf, 7), 1, 'MODIFIED', 'FALSE') as IMOD,
decode(bitand(ksppstvf, 2), 2, 'TRUE', 'FALSE') as IADJ,
ksppdesc as description
from x$ksppi x,
x$ksppsv y
where x.indx = y.indx
and substr(ksppinm, 1, 1) = '_'
and x.inst_id = USERENV('Instance'))
where name = '_db_block_hash_latches'
order by name;

NAME VALUE
------------------------------ ---------------
_db_block_hash_latches 2048

SQL> select power(2,trunc(log(2,1073741824 - 4) - 6)) from dual;

POWER(2,TRUNC(LOG(2,1073741824-4)-6))
-------------------------------------
8388608

如果是按照文档得做法，这个参数得修改如此大，觉得有点汗颜，当然修改不一定按照这个参数。9I之后关于这个参数得调整，我在网上没有见到有过类似得例子。总得来说，LATCH产生得原因还是从应用入手，不要期望通过调整某些参数达到立杆见影的效果，热块，大的逻辑读和物理读，全表扫描都是会导致产生LATCH得原因。

作者 bulkaunt ( 性能调优 ) :: 最新回复 (9) :: 静态链接网址 :: 引用 (0)

comment4, natuzzi italian leather furniture, 183114, pocono vacation deals, 265, downloader virus removal, mjl, [回复]

comment4, natuzzi italian leather furniture, 183114, pocono vacation deals, 265, downloader virus removal, mjl,

Comment by adventure park uk (03/12/2010 05:22)

comment1, Ambien Skin Care, 630658, Ranbaxy Receives Approval To Manufacture Zolpidem, 8-((, Tbi Treatment With Ambien Guide Lines, 697653, Buy Zolpidem, obkn, Purchase Zolpidem, >:-))), Ambien Federal Express Overnight Delivery, >:-))), Adiccion Al Zolpidem, cch, Ambien And Hair Loss, 40471, Zolpidem Veterinary Dosage, >:(((, Ambien Recreational Use, =))), [回复]

comment1, Ambien Skin Care, 630658, Ranbaxy Receives Approval To Manufacture Zolpidem, 8-((, Tbi Treatment With Ambien Guide Lines, 697653, Buy Zolpidem, obkn, Purchase Zolpidem, >:-))), Ambien Federal Express Overnight Delivery, >:-))), Adiccion Al Zolpidem, cch, Ambien And Hair Loss, 40471, Zolpidem Veterinary Dosage, >:(((, Ambien Recreational Use, =))),

Comment by jonn3 (03/12/2010 05:22)

IXjAQV pcruxapyidcy, [url=http://cozyveeipqbw.com/]cozyveeipqbw[/url], [link=http://mjsvpinktnvq.com/]mjsvpinktnvq[/link], http://wgajfhbhphik.com/ [回复]

IXjAQV pcruxapyidcy, [url=http://cozyveeipqbw.com/]cozyveeipqbw[/url], [link=http://mjsvpinktnvq.com/]mjsvpinktnvq[/link], http://wgajfhbhphik.com/

Comment by rnockdrm (03/12/2010 05:20)

obtain free credit report [回复]

6 obtain free credit report or check credit score free or texas freeze credit report 2007 or casos de intoxicacion con tramadol or the cheapest car insurance the uk or 7 tramadol online pharmacy or phentermine and script or what makes phentermine work or lowest car insurance quotes toronto or xanax 10mg or free maryland credit report or overnight us phentermine or indian pharmacy cialis or cancel free credit score or ligit pharmacy sells phentermine or nascar cialis or seconds free credit report or cialis g7 or ritalin tramadol or original phentermine 37.5 or xanax generic form or amancio phentermine or instant auto insurance quote california or buy generic tramadol by or fake phentermine pills or request to remove items from credit report or tramadol dose cats or 37.5 diet mg phentermine pill or is tramadol stronger than darvocet or viagra super active sildenafil or cialis errection or car insurance quotes au or dispute credit report errors online or phentermine diet pills phentermine or free credit report ok or free credit report by transunion or fatal tramadol dosage or examples of car insurance quote or overnight no prescription phentermine pharmacies or paxil and xanax overdose or tramadol order tramadol or cialis incredible power or official credit score site or phentermine is it dangerous or dr perez pa phentermine or modified car insurance quotes online or viagra no prescreption or tramadol hcl 50 mg tablet amn or cheapest uk car insurance or cheapest credit report online or