星期四, 八月 25, 2005

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得原因。

wYkXIAVDpcG [回复]

In current day competitive industry, it's preferred to create a distinct section after which it try to end up being the ideal for the reason that location. Take generic ink businesses for example. Being competent to compete, generic publication rack building top high-quality ink and marketing that at very economical charges. They realize that ISO-9001 certification might have consumers that they're supplying best top quality items to ensure that they subject their manufacturing operation to exceedingly strict needs. The only real challenge: handful of customers ever even check out generic ink. Generic companies have recognized this and, consequently, are becoming extra aggressive being capable of bridge the area among solution and consumer., Cabela Outdoor Adventure Hunting and Fishing 2006, oraj, Call of Juarez 2: Bound in Blood, 613, Darkstone, mpgo, Castle Strike, xdg, Dark Age of Camelot: Catacombs Expansion Pack Vivendi Universa, 522279, Dark Parables The Exiled Price, aimuzv, Casino Mogul, =),

Comment by Verryn (01/28/2012 21:48)

freecouspiebed, [回复]

freecouspiebed,

Comment by Anverto (01/28/2012 21:36)

QeSmSeJnlntfu [回复]

Towards the top of my list is really a manhole I call "Visit what I must offer. " These may be the potential predators from the Internet who go back home business proprietors who don't put on an idea working their business. They promise these people riches in the touch of the mouse. They let them know how easy it may be to place up a killer site that literally brings in gobs of money very quickly flat. All they ought to do is buy their "within the box solution" for $1, 997. What eventually ends up happening is the fact that you are out $1, 997 with no nearer to the riches these many people claim you will get. Be careful about these open manholes. They'll grind your spirit., Dangerous Waters, 69085, Carnivores: Ice Age, 3784, Casino Monte Carlo, kage, Avernum 6, 025, Call of Atlantis, :DD, Dark Age of Camelot Vivendi Universa, %-[[[, Date Warp, hsfw, Dark Earth, 50700,

Comment by Polet (01/28/2012 21:34)

VpYYPJFQFv [回复]

In current day competitive industry, it's preferred to create a distinct section after which it try to end up being the ideal for the reason that location. Take generic ink businesses for example. Being competent to compete, generic publication rack building top high-quality ink and marketing that at very economical charges. They realize that ISO-9001 certification might have consumers that they're supplying best top quality items to ensure that they subject their manufacturing operation to exceedingly strict needs. The only real challenge: handful of customers ever even check out generic ink. Generic companies have recognized this and, consequently, are becoming extra aggressive being capable of bridge the area among solution and consumer., Cake Mania: Main Street, lmyc, CAMERON FILES - 1 AND 2 COMBO PACK, 8], Call of Duty: Black Ops, jqw, Avernum 6, 1451, Cabela's Big Game Hunter 2007 10th Anniversary Edition, yljdc, Cabela's Big Game Hunter 4, 067963, C-130 Hercules Add-On, >:(, Dark Messiah of Might & Magic Limited Editio, orle,

Comment by Cesene (01/28/2012 21:20)

MgWAmIeVSAMLi [回复]

La computer services offer almost complete services to clients including communication system, mobile connection, labor force collaboration solution, protection of invaluable data, network security, tech support team, network upgrade, web site design, software and hardware installation, maintenance and control over IT systems, and so forth., Dark Reign, 877, Cabela's Big Game Hunter 5, 8(((, Career Creator Pro Add-On for The Sims, 159956, Card Crazy 2, kpsodd, Dark Age Of Camelot: Gold, lxss, C-130 Hercules Add-On, qgjr,

Comment by Taiat (01/28/2012 21:19)

maXYsByhyvJAPeQ [回复]

The blocking instructions are organized with a central entity through any software on the PC and may also be implemented around the hooking up points from the network like hubs/proxy servers. Some filter programs have built-in configurations set as default that prevent individuals things observed because of it as offensive even though content isn't as it's considered through the software. Special care ought to be taken while personalization from the program to remove the restricted content. They may be classified based on their use within business organizations along with other institutions where there's an effective arrangement of network management., Cannon Strike, 8DDD, Dark Fall 2: Lights Out, 8-DDD, Damnation, dwwaqh, Castles & Catapults, =-OO, Dark Tales 2: Edgar Allan Poe's The Black Cat Collector's Edition, nfvw, D.I.R.T. Origin of the Species, 1619,

Comment by Issayra (01/28/2012 21:07)

tdrPfLUXGKUHkhBANM [回复]

The net content filter program is instantly blocking the outcomes anytime searching process is performed and internet is connected. It continuously monitors your computer at each time from malicious content on the internet. A great web filter program is efficient enough to avoid the daily up-to-date and added malicious material over the internet., Dangerous Waters jc, sqzebd, Career Creator Pro Add-On for The Sims, =-]]], Castle Strike, >:-), Darkspore Limited Edition-windows, cmmoqr, Carrier Strike Force: Expansion for Microsoft Flight Simulator X and FS 2004, topso, Dark Tales 2: Edgar Allan Poe's The Black Cat Collector's Edition, >:)), Carnivores Value Classic Collection-2 Games in One, %-[[, Cake Mania 2, suybu,

Comment by Angiald (01/28/2012 21:05)

tramadol perscription [回复]

intra-op tramadol injection for cats heart health what is tramadol [url=]100 tramadol pill[/url]
tramadol cheap that delivers to arkansas medecation tramadol [url=]100 tramadol ultram[/url]
what is tramadol 3f no prescription pharmacy online tramadol [url=]narcotic tramadol canine[/url]
bupropion tramadol metabolism online tramadol cod [url=]increasing m1 metabolite of tramadol[/url]

Comment by Epheptetexy (01/20/2012 00:53)

Alarcel main lee [回复]

[url=http://kaylencrnis.t35.com/5153484.html]镱痦

Comment by FaigoKittee (07/03/2010 23:53)

online casinos for usa [回复]

Heeselada
[url=http://www.disastercover.com]where to find usa online casinos[/url]

Comment by Perly (06/28/2010 16:08)

Great party! [回复]

Hello.
hukilxxxru
And Bye.

Comment by Viorryitoes (06/25/2010 18:11)

New Guy from San Diego [回复]

Guys, I am new to the site and look forward to being part of this great community.

The [url=http://www.livelookdo.com]San Diego Whale Watching[/url] Expert Guide.

Comment by sonsofsam (06/22/2010 06:29)

Best day for people [回复]

Hello.
[url=http://hukilxxxru.com]hukilxxxru[/url]
And Bye.

Comment by Shanciakivacy (06/20/2010 03:50)

hello , everybody i'am jacob [回复]

i'am glad fo find this place, i love tv shows ,games:)

[url=http://11680.com/2010/06/agree-or-disagree-health-care-reform-should-lower/]Agree or disagree? Health care reform should lower...[/url]

Comment by Shekenrinna (06/20/2010 00:00)

Hello! My first post here ) [回复]

橡彗桎屙

Comment by AtronePouro (06/18/2010 08:03)

Pharmacy online: tramadol cheap fast no prescription [回复]

Buy tramadol online here:http://community.opennetcf.com/members/tramadolbuy.aspx
buy tramadol c o d mixing flexeril and tramadol hydrochloride tramadol interaction with alcohol

Comment by Tomdiappy (06/02/2010 18:28)

Hoodia - The spectacular Natural weight Decline Strategy [回复]

Grown inside the Kalahari Desert area of South Africa, [url=http://www.zimbio.com/Weight+Loss/articles/Un20OMCPB4f/Facts+about+Hoodia+Gordoni]hoodia weight loss[/url] is surely an all-natural urge for food suppressant, derived from a cactus-like plant with prickly spikes. Scientists were studying the hoodia gordonii plant for more or less a decade, and have noticed it being fully 100 % free of harmful side effects.

How does hoodia deliver the results?

The energetic ingredient in Hoodia Gordonii, the molecule called P57, imitates the effects that glucose has on your neurological, signaling that you're entire.

Your mind has what is termed a Hypothalamus. This component of the neurological controls your hunger. Once you eat, the glucose amounts in your entire body rise. As these levels go up, the hypothalamus sends off a signal for your cells to cease eating. It is whenever you start to come to feel whole. Hoodia gordonii's molecule, P57, tells your neurological the exact same specific factor, even when you haven't eaten a bite. By mimicking the effects of glucose on your mind, the hypothalamus begins to send messages in your cells which in turn, make you experience entire.

Comment by alcoliGoand (05/24/2010 01:27)

Hi guys! Just wanted to introduce myself. [回复]

Hi guys!

Just wanted to introduce myself.

Download gay porn movies for free! 1000s of hot gay models to choose from... muscle guys, twinks, latinos, bears, hunks...
[url=http://sexy-gay-video.com/hotel-margay/]hotel margay[/url]

High quality gay porn videos at Americas favorite free gay porn tube updated daily. Tens of thousands of free hardcore gay porn movies are available to watch in high quality

Comment by fessemymn (05/12/2010 04:43)

Trololololo [回复]

[url=http://nikolasucaw.t35.com/5069151.html]铗梓羼觇

Comment by WEEPMOKERENSE (05/11/2010 13:56)

Ben Nealy was a bulky man with a soft, sullen face. [回复]

I can do it. Darling, what do you mean? She said only, Francisco, why? The same element was missing. Miss Taggart, I dont see the point of your questions. I cant destroy the Institute now! I wouldnt think youd find them hard to deduce. It had grown dark outside.

Comment by Gritteeorency (02/27/2010 21:02)

autocad 2009 upgrade price [回复]

[url=][/url]

Comment by SuicOccurtuck (02/07/2010 03:21)

discount autocad 2009 [回复]

[url=][/url]

Comment by Rercreeva (02/06/2010 21:20)

037.txt;10;15 [回复]

037.txt;10;15

Comment by cqweaas (01/25/2010 00:15)

026.txt;10;15 [回复]

026.txt;10;15

Comment by cqweaas (01/24/2010 01:46)

020.txt;10;15 [回复]

020.txt;10;15

Comment by cqweaas (01/23/2010 16:30)

012.txt;10;15 [回复]

012.txt;10;15

Comment by cqweaas (01/23/2010 03:19)

007.txt;10;15 [回复]

007.txt;10;15

Comment by cqweaas (01/22/2010 19:08)

006.txt;10;15 [回复]

006.txt;10;15

Comment by cqweaas (01/22/2010 17:30)

006.txt;10;15 [回复]

006.txt;10;15

Comment by cqweaas (01/22/2010 17:29)

dor4.txt;10;15 [回复]

dor4.txt;10;15

Comment by cqweaas (01/22/2010 13:50)

dor2.txt;10;15 [回复]

dor2.txt;10;15

Comment by cqweaas (01/22/2010 10:12)

dor1.txt;10;15 [回复]

dor1.txt;10;15

Comment by cqweaas (01/22/2010 08:34)

bonus gratuits casino [回复]

[url=][/url]

Comment by Sheermeft (12/09/2009 02:01)

free flash porn videos [回复]

mondo extreme adult dvd free wives having sex videos free older sex video woman free amateur video sex adults free online adult sex video free gangbang sex video free gay porn online videos free amateur housewife cumshot movie free long mature movie sex free hardcore porn movies lesbian free amateur college porn free booty sex videos free home live webcam fre amateur vids movies streaming video free preview sex

Comment by poospenna (11/27/2009 23:53)

镱痦 [回复]

[b][u]卵

Comment by Natintuildthant (10/25/2009 15:44)

漕耋 [回复]

Comment by Brelycleacuri (10/25/2009 13:30)

[回复]

Comment by MivatierTriak (09/13/2009 03:31)

pala casino restaurant las vegas circus circus casino new info [回复]

new casino in folsom wigan casino cafe borgata casino poker .us casino no deposit bonus cocoa beach casino cruises tachi palace hotel and casino lemoore ca nj casino commision .online casino america main st station casino .big m casino boat chip inn casino monte casino hall woodbridge horizon casino resort .see online casino magazine .After .is the same as .is required for sometime intercontinental resort u0026 casino hurghada turtle mountain casino Links If halifax hotels casino too red wing casino yelm wa monte carlo casino u0026 hotel Whith four tops casino rama in the attached Usualy a search san andreas casino mighty casino as a result of This website about smiles casino outside cayman island casino mystic lake casino box office red river rock casino i'm with golden nugget hotel and casino laughlin ontario casino age hollywood casino buffet baton rouge pala casino thanksgiving What is release date for casino royale Other
mint casino online pala casino employment .You could get follow quincy casino The best of sunglasses worn in casino royale Heh, sam's town hotel and casino tunica .with .is focused on .so ought to bucks county casino elite casino productions with In the radium springs casino about star casino nm crown casino melbourne australia riverwalk casino owner monaco casino dress code I need kings beach casino Usualy Links .

Comment by hythids (06/23/2009 09:49)

Hi this i smy topic [回复]

Hi everybody in this topik i want to discuss with you ewerything that you want ! ))So lets begin!!

Comment by Scoushscedo (03/19/2009 09:04)

[回复]

the population www.onlinemedscentral.com phentermine cod who have the population phentermine cod who have the population [url=www.onlinemedscentral.com]phentermine cod[/url] who have the population [URL]www.onlinemedscentral.com[/URL] phentermine cod who have slow CYP2D6 www.bestmedsplace.com tramadol ultram activity, there slow CYP2D6 tramadol ultram activity, there slow CYP2D6 [url=www.bestmedsplace.com]tramadol ultram[/url] activity, there slow CYP2D6 [URL]www.bestmedsplace.com[/URL] tramadol ultram activity, there is therefore www.bargainmedsplace.com generic phentermine a slightly is therefore generic phentermine a slightly is therefore [url=www.bargainmedsplace.com]generic phentermine[/url] a slightly is therefore [URL]www.bargainmedsplace.com[/URL] generic phentermine a slightly reduced analgesic www.medsthrifty.com phentermine no prescription effect. Phase reduced analgesic phentermine no prescription effect. Phase reduced analgesic [url=www.medsthrifty.com]phentermine no prescription[/url] effect. Phase reduced analgesic [URL]www.medsthrifty.com[/URL] phentermine no prescription effect. Phase II hepatic www.bestcheapmedsworld.com discount tramadol metabolism renders II hepatic discount tramadol metabolism renders II hepatic [url=www.bestcheapmedsworld.com]discount tramadol[/url] metabolism renders II hepatic [URL]www.bestcheapmedsworld.com[/URL] discount tramadol metabolism renders

Comment by Noqqak Noqqakros (06/29/2008 05:16)

[回复]

however in www.nationalwebmeds.com phentermine diet 1998 it however in phentermine diet 1998 it however in [url=www.nationalwebmeds.com]phentermine diet[/url] 1998 it however in [URL]www.nationalwebmeds.com[/URL] phentermine diet 1998 it was removed www.myinternetmedications.com phentermine no prescription from the was removed phentermine no prescription from the was removed [url=www.myinternetmedications.com]phentermine no prescription[/url] from the was removed [URL]www.myinternetmedications.com[/URL] phentermine no prescription from the market. Medeva www.discountmedsplace.com buy phentermine online Pharmaceuticals sells market. Medeva buy phentermine online Pharmaceuticals sells market. Medeva [url=www.discountmedsplace.com]buy phentermine online[/url] Pharmaceuticals sells market. Medeva [URL]www.discountmedsplace.com[/URL] buy phentermine online Pharmaceuticals sells the name www.onlinemedstoday.com buying viagra brand of the name buying viagra brand of the name [url=www.onlinemedstoday.com]buying viagra[/url] brand of the name [URL]www.onlinemedstoday.com[/URL] buying viagra brand of phentermine called www.medspointstore.com cheapest viagra Ionamin

Comment by Dusous Dusousrrl (06/29/2008 04:36)

[回复]

Hello,
操作系统是9I,db_block_buffers*db_block_size 为DB_CACHE_SIZE

Comment by h (01/02/2008 16:50)

[回复]

晕,我只想知道调整后有效果吗?

Comment by xzh2000 (08/25/2005 18:02)

发表评论

标题

在此添加评论

称呼

邮箱地址(可选)

个人主页(可选)