Re: Latch contention in 8.1.5

Marin,

The latch that you are monitoring namely "cache buffer chain" does not related to the setting of db_block_lru_latches. The "cache buffer chain latch" is directly related to the length of "a chain of blocks" that a server process has to "walk" before arriving at the block that it is attempting to read.

To fix "cache buffer chain" latch contention you need to set _db_block_hash_buckets to the value of db_block_buffers. On making that change and further monitoring you will find that the contention for that latch is greatly reduced. By setting the _db_block_hash_buckets to the same value of db_block_buffers, you have pretty much ensured that each block is in its own chain.

With regards to db_block_lru_latches, in my experience I have seen (2 * # CPUs) to be adequate. I was unaware that (6 * # of CPUs) is even supported. I just checked out my 8i documentation and it says the range of values is (1 - twice the number of CPUs). I am curious to know which documentation that you are referring to that states (6 * # of CPUs). At any rate, 2048 for db_block_lru_latches is too high is probably not even relevant.

So, here is the scoop: set db_block_lru_latches = (2 * # of CPUs) and _db_block_hash_buckets = (db_block_buffers), monitor the latch hit ratios and get back to us.

Hope that helps,

Gaja.

• Marin Dimitrov <marin_at_sirma.bg> wrote:
  > > I am running Oracle 8.1.5 on Solaris 2.6 with a 2 GB SGA
  > (1.4 GB buffer
  > > cache). At intermittent times, my misses and waits for my
  > cache buffer
  > > chains latch goes from 10% to 90% and stays there for 30-40
  > minutes and
  > then
  > > drops again. During this time, my site performance goes way
  > down as
  > overall
  > > database waits skyrocket. There doesn't seem to be any
  > coorelation
  > between
  > > the spike and extra users, different SQL, etc. I have set
  > > db_block_lru_latches=2048. Is this too high or low?
  >
  > it's too high. The docs recommend setting
  > db_block_lru_latches from #CPU x
  > 2 to #CPU x 6
  > Anywise, I think Oracle would just ignore the value u have
  > already set
  > because it's too high.
  >
  >
  > marin
  >
  >
  > ------------
  >
  > "The important thing for all of us is never look *at* the
  > bars, look
  > *through* them. Because if you keep looking *at* bars, you'll
  > never get
  > anything done, and you'll never have fun in life, any joy."
  >
  > William
  > Wharton, "Pride"
  >
  >
  > --
  > Author: Marin Dimitrov
  > INET: marin_at_sirma.bg
  >
  > Fat City Network Services -- (858) 538-5051 FAX: (858)
  > 538-5051
  > San Diego, California -- Public Internet access /
  > Mailing Lists
  >

  ---

  
  > To REMOVE yourself from this mailing list, send an E-Mail
  > message
  > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
  > and in
  > the message BODY, include a line containing: UNSUB ORACLE-L
  > (or the name of mailing list you want to be removed from).
  > You may
  > also send the HELP command for other information (like
  subscribing).

  ---

Gaja Krishna Vaidyanatha   | 3460 West Bayshore Road,
Manager - Integration      | Palo Alto, CA 94303
& Consulting Services      | gaja_at_brio.com
Global Alliances           | (650)-565-4442
Brio Technology            | www.brio.com 

"Opinions and views expressed are my own and not of Brio Technology"