Re: Help: large "cache buffers lru chain", large performace degradation

Thanks for the suggestions.. i can try them out only today night when there is no traffic.
I have done some analysis on buffer contention, as per this it tells that there are no contentions. If this is true than it means that i am barking up the wrong tree..

Thanks,
Nagarajan

Information taken from
http://database.sarang.net/database/oracle/concept/oracle-latch/latch.html

Internal structures in oracle are protected by

• latches
• enqueues
• distributed locks
• global locks

latches

• control access to internal data structures
• if process does not get a latch immediately, it spins leading to additional cpu and slowing of system

There were 1054126 waits and 975035 total wait time with avg 3.38 wait time

Query to give name of latch using address         

	Select name from v$latchname ln, v$latch l
	where l.addr = 'give_address_here'
	and l.latch# = ln.latch#

>>>where does the address come from

query to provide system-wide latch statistics

	select ln.name, l.addr, l.gets, l.misses, l.sleeps,
		l.immediate_gets, lh.pid
	from v$latch l, v$latchholder lh, v$latchname ln
        where l.latch# = ln.latch#
	order by l.latch#;


latch wait list                                                
0000000000800F30172707              28                  47            
     0                   9
process allocation                                             
000000000080101893529               3                   3             
     93529               9
session allocation                                             
00000000008010B8854028              89                  119           
     0                   9
cached attr list                                               
0000000000801F300                   0                   0             
     0                   9
modify parameter values                                        
00000000008020D093529               31                  81            
     0                   9
messages                                                       
00000000008022001077038             153                 149           
     0                   9
cache protection latch                                         
00000000008038380                   0                   0             
     0                   9
cache buffers lru chain                                        
0000000000803BF8149090851           975786              729074        
     159948831           9
redo allocation                                                
00000000008042003050922             3430                4134          
     0                   9
KCL name table latch                                           
00000000008049D80                   0                   0             
     0                   9
instance latch                                                 
0000000000804F700                   0                   0             
     0                   9
lock element parent latch                                      
00000000008050100                   0                   0             
     0                   9
loader state object freelist                                   
00000000008051F094                  0                   0             
     0                   9
sequence cache                                                 
00000000008061B0292166              0                   0             
     0                   9
row cache objects                                              
00000000008062D88441263             763                 786           
     3660                9
user lock                                                      
00000000008086D0374056              47                  49            
     0                   9
shared pool                                                    
0000000000808A802833988             1386                1557          
     0                   9
library cache load lock                                        
0000000000808BC02180                0                   0             
     0                   9
virtual circuit buffers                                        
0000000000808D400                   0                   0             
     0                   9
query server process                                           
000000000080A5C80                   0                   0             
     0                   9
process queue reference                                        
000000000080A8480                   0                   0             
     0                   9

• check contention for latch

Ratio of gets to misses is less than 1%

Ratio of immediate_misses to sum of immediate_misses and immediate_gets

select ln.name, l.addr, l.gets, l.misses, l.sleeps, l.immediate_gets, l.immediate_misses, lh.pid

from v$latch l, v$latchholder lh, v$latchname ln where l.addr = lh.laddr(+)

and l.latch# = ln.latch# and ln.name like '%buffer%' order by l.latch#

cache buffers chains                                           
00000000008036581720824308          136981              288049        
     291490122           280087
cache buffer handles                                           
00000000008036F85470                0                   0             
     0                   0
cache buffers lru chain                                        
0000000000803BF8150597009           988927              738402        
     161565972           2108125
virtual circuit buffers                                        
0000000000808D400                   0                   0             
     0                   0
parallel query alloc buffer                                    
000000000080AA780                   0                   0             
     0                   0

checking for cache buffer chains

280087 to (291490122 + 280087)
280087 to 291770209, less than one percent

2108125 to (161565972 + 2108125)
2108125 to 163674097, less than one percent

no contention for buffers? Received on Mon Jan 13 2003 - 07:39:37 CST