Tuesday, April 18, 2006

Getting a Handle on Logical I/O

The other day a colleague brought to my attention an interesting situation related to one of the databases he supports. The database was, rather consistently, experiencing heavy cache buffers chains (CBC) latch wait events while processing against a set of “related” tables. The solution devised to mitigate the CBC latch contention involved range partitioning said tables. I believe proper partitioning can be a very reasonable approach to minimize the probability of CBC latch collisions. Of course, you must know the manner in which your data is accessed and partition accordingly, as you don’t want to sacrifice existing solid execution plans among other considerations.

As it turned out, the partitioning approach did indeed reduce the CBC collisions; albeit another form of contention surfaced as a corollary, cache buffer handles latch collisions. I must admit I had a very limited knowledge of buffer handles prior to being made aware of this situation. My colleague pointed me to a very interesting article on Jonathan Lewis' site. This article gives a pithy description of buffer handles. I highly recommend you carve out a few minutes to read it. Not only might you learn something about buffer handles, you might be surprised that the more traditional notions of logical I/O do not really suffice. I was first suitably introduced to the buffer is pinned count statistic during a Hotsos training course. Essentially, this statistic indicates the presence of latch-reduced logical I/O.

While, generally speaking, Oracle recommends that hidden parameters not be changed, sometimes they need to be modified to accommodate very specific issues your database is encountering. In this particular case, increasing the value of the _db_handles_cached parameter got rid of the newly surfaced collisions on the cache buffer handles latch. I love learning from others’ experiences. It is amazing how many interesting little tales such as this exist. Also, this type of unforeseen contention shifting reinforces the need to properly test production changes - or maybe better said, the ability to properly test production changes.

6 Comments:

Anonymous Anonymous said...

Corresponding article does not exist. Would you direct me to the link ?

12/26/2007 9:03 PM  
Anonymous Anonymous said...

it is cool.

6/22/2008 10:50 PM  
Anonymous Anonymous said...

Hi

how did you determine what to set the _db_handle_cached parameter to ?

thanks

Stephen

10/19/2009 1:02 PM  
Blogger daspeac said...

I believe you may be also interested in the outlook express inbox repair tool

12/13/2010 1:51 PM  
Blogger Unknown said...

http://www.securityalarmchina.com
http://www.gsmsecurityalarm.com/
http://www.chinawolfguard.com
http://www.dreamboxs.com
http://www.dreamboxok.com
http://www.sunraysat.com
http://www.dreambox-store.com
http://www.dreamboxabc.com

7/14/2015 11:15 PM  
Blogger Unknown said...

dreambox dm7080
dreambox dm7080
dreambox dm820
dreambox dm7020 hd v2
cheap wholesale dreambox dm800 hd v2

7/14/2015 11:16 PM  

Post a Comment

<< Home