contention on 'session allocation' latch

[Strife]

Hi all,

have noticed the session allocaion latch is running at 97.7% on a good day! the system is running very slow.

Any ideas on how to reduce contention for this type of latch?

System is:
Oracle81630
Sun OS 4cpus 3Gb Ram.
SGA: buffers 320M shared pool 160M

OAS 4081.

Most connection are from OAS's wrks.

Thanks
Strife

 

[Strife]

more analysis on this is showing 'library cache' and 'cache buffers chains' latches to have the highest waits..

could this possibly mean reducing the block buffers as the buffer cache hit-ratio is at 100%..

anyone have experiance with this type of contention?

Thanks

 

[rcurva]

For library cache, make sure that the application is well tuned. For the buffer cache, make sure that there is no "hot blocks" existing before you increase DB_BLOCK_BUFFERS bec you'll just waste your memory. Hot blocks are highly accessed blocks.

To check for hot blocks, high wait value for 'buffer busy waits' in UTLESTAT report.

 

[Strife]

nope.. thats not it.. buffer cache is already at 100%.

if anyone is interested this is my conclusion:

Recommend setting _db_block_hash_latches = 4096 # in 8.1.x default for this with 40000 block buffers is 1024

I have found a high number of waits for the latch 'cache buffers chains' and increasing the number of latches should reduce the contention

 

[rcurva]

For high cache buffer chains, the undocumented parameter is
_db_block_hash_buckets. But, based on the documents that I read, increasing this will only help a little. The best course of action is to tune the SQL.

http://www.ixora.com.au/q+a/cache.htm

 

[Strife]

nope that's not it..

the hash_buckets by default are set to 2x the number of buffers and that is the case in this db. we are working on finding the hashing algorithm now to understand why a db would need even one more bucket than number of buffers.

the problem is with the number of latches NOT buckets.

 

[sem]

Try to increase your shared pool and pin your large sql statements.
Increase spin_count and/or _latch_spin_count, this will not reduce contention but may improve performance.
Check your sqlarea for similar statements, using substituting instead of bind variables.
Try to set CURSOR_SHARING = FORCE to prevent Oracle from reparsing similar statements (you may suffer from rule-based optimization but this will decrease sqlarea)

 

[Strife]

Hi,

thanks for that.. will be looking at the library cache problem next (it is only a small issue)..

currently the main problem is with block buffer latches.

 

[sem]

Do you have table(s) everybody inserts/updates, try to "parallelize" them by adding free lists/free list groups and setting PCTFREE to a larger value.