Re: Latch contention in 8.1.5

Chris,

I sent out a response to Marin's note, but then later found out that it did not have the entire original posting. Anyhow, at the outset, given all the symptoms that you have mentioned, I tend to believe that your SGA is probably too large.

If your cache hit ratios are very high you might want to think about shrinking the size of your SGA. When calculating the hit ratios for your shared pool, please keep in mind that you are purging your shared pool periodically.

If you can help us with the math of your SGA, that will also be great. You said that you have a 2 GB SGA, and also mentioned that (1.4 Gb Buffer Cache, 450 Mb. Shared Pool). We are still 150 Mb short in the math, and I sincerly hope you do not have a redo log buffer which is sized that large. For most environments redo log buffers of 512 K to 1 Mb. is more than adequate.

The fact that you are experiencing shared pool latch contention and the fact that it gets alleviate when the purge is done, makes me want to arrive at the conclusion that your shared pool is probably larger than what it should be. The large size of the shared pool may be having an adverse effect on the "hashing algorithm" that does the mapping of the SQL in the shared pool, while perfoming a "soft parse". And when the purge is done, every SQL statment has to be "hard parsed" for obvious reasons. Something to think about...

I sincerely feel you may just be experiencing the effects of allocating too much memory for the SGA. I have already written in my previous posting the required latch tuning for the buffer cache.

Best Regards,

Gaja.

• Chris Call <Chris.Call_at_rm.panurgy.com> 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?
  >
  > Also, and maybe related, if I don't purge my 450 MB shared
  > pool every half
  > hour, the shared pool latch waits go from 10% to 90% also. To
  > prevent this,
  > I have written a script which does a purge automatically, but
  > this is
  > obviously less than ideal. What could be causing this? It is
  > as if the
  > latch can't keep up with the SQL in the shared_pool. I am
  > just confused as
  > to what is happening here. Any help would be greatly
  > appreciated.
  >
  > Chris
  > --
  > Author: Chris Call
  > INET: Chris.Call_at_rm.panurgy.com
  >
  > 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"