Oracle Latches:Tough one

[Manpreet]

Does anyone know a good source to study about LATCHES.
I am just witnessing a performance problem on our production server.Its an oracle parallel server and is running 3 instances on it.However we r getting problems on only one of them.This is the output of latch script

SQL> select
2 l.name,
3 l.sleeps * l.sleeps / (l.misses - l.spin_gets) impact,
4 to_char(100 * l.sleeps / l.gets, '99990.00') || '%' sleep_rate,
5 l.waits_holding_latch holding,
6 l.level#
7 from
8 sys.v_$latch l
9 where
10 l.sleeps > 0
11 order by
12 2 desc
13 /

LATCH TYPE IMPACT SLEEP RATE WAITS HOLDING LEVEL

dlm resource hash list 1860158780 24.28% 422769 1
dlm resource table freelist 12806644 0.01% 37 3
dlm shared communication latch 1195359 0.04% 388 5
dlm lock table freelist 525145 0.01% 79 5
library cache 2433 0.00% 271 5
messages 1267 0.00% 390 8
session allocation 148 0.00% 34 5
cache buffers chains 92 0.00% 2 1
process parent latch 76 0.00% 22 2
KCL lock element parent latch 68 0.00% 20 4
session idle bit 62 0.00% 0 1
enqueue hash chains 32 0.00% 0 4
cache buffers lru chain 27 0.00% 22 3
shared pool 21 0.00% 3 7
dlm timeout list 20 0.00% 13 3
row cache objects 5 0.00% 3 4
dlm group lock table latch 4 0.00% 1 5
enqueues 2 0.00% 0 5
redo writing 2 0.00% 2 5
Checkpoint queue latch 2 0.00% 2 7



The other 2 instances do not have such high values of impact and waits holding.I just want to know what does all this means?Should these values be brought down for better performance and also how to bring them down??

[marist89]

http://www.oreilly.com/catalog/orinternals/

[marist89]

http://www.ixora.com.au/home.htm

[Sureshy]

If I remember correctly, the only latches a DBA can tune are :

Redo Allocation Latch
Redo Copy Latch
DBBuffer LRU Latch.

Concentrate on these areas.

[sambavan]

Latch tuning is an multipart operation. So, you next have to see v$waits, v$session, and v$session_waits to see what are the operations waiting for the latches. Then you twigue that to see whether it would improve.

In your case check the table that is suffering from the freelist contention and increase that table's free lists count.


Sam