Re: latch/lock , buffer - questions.

Sadagopan, Sathish wrote: > > Hi All, > 1) When a thread needs a buffer to read in data , does it acquire any > latches/locks.How is the allocation done ? This is all explained in the Administrator's Guide. Basically the thread hashes into the LRU queues selecting a queue at random. It tries to latch the LRU. If it cannot latch the LRU it spins for a while. If it still cannot get the latch it will rehash. Once an LRU is latched the least recently used buffer on the clean half of the selected LRU is latched and moved to the most recently used end of the queue and the LRU latch is released. Then the thread makes an I/O request for the desired page to be read into the selected page. Once the I/O completes the latch on the buffer is released and the page scanned. > 2) What is the difference between locks and latches. Locks are on data pages or rows, latches are on memory and other engine resources. > 3) If i want to track , the number of times a PARTICULAR user's operations > caused his threads to wait for buffers , where to look? This is reported by onstat -g wst which depends on the collection of thread level statistics which must be enabled. Thread wait statistics collection is off by default because it add significant overhead and will slow the engine. > onstat -p 's "ovbuff" is across all users , isn't it ? Ovbuff is for the entire instance and tracks the number of times the engine needed a buffer and there were none available. This should be a very small number typically if not you need to add buffers. > 4) Related to Ques.1 , for every increment in "ovbuff" of onstat -p is > there a corresponding increment in "wait" of onstat -u ? Probably since the fact that there are no buffers aged sufficiently to be reused will neccessitate a thread waiting for one. If you have this problem you are probably seeing large numbers of foreground writes also (onstat -F) and need to increase the number of buffers and the number of LRUS also. Art S. Kagel