Classification and query of latch in SGA: combined with v$latch view

Classification and query of latch in SGA

Latch in the SGA are divided into two categories:

(1) willing to wait for latch (willing-to_wait)

(2) Unwilling to wait for immediate

CBC multiple BH Components Cache Buffer Chain (CBC)

For example: CBC latch's generation of queries:

sys@ Bys3>col name for A20

sys@ Bys3>select Name,gets, Misses, sleeps, immediate_gets, immediate_misses from V$latch where NAME like '%cache Buffe RS chains% ';

NAME GETS misses sleeps immediate_gets immediate_misses

-------------------- ---------- ---------- ---------- -------------- ----------------

Cache buffers Chains 2006932 0 0 78095 0

V$latch each field Description:--From official documents

GETS number of times the latch is requested in willing-to-wait mode is willing to wait for a pattern to acquire a lock

Misses number of times the latch is requested in willing-to-wait mode and the requestor had to wait for mode, requestor request latch not Number of Waits after success

Sleeps number of times a willing-to-wait latch request resulted in a session sleeping while waiting for the latch Number of times you are willing to wait for mode to cause a session sleep

Immediate_gets number of times a latch is requested in no-wait mode immediately gets latch success

Immediate_misses number of times a no-wait latch request did not succeed (which is, missed) immediately get latch unsuccessful

###################################

Queries for V$latch views

sys@ bys3>select Count (*) from V$latch; The--oracle version is 11.2.0.4, the lock has 582 kinds

COUNT (*)

----------

582

sys@ bys3>select Count (*) from V$latch where immediate_gets+immediate_misses>0; --Willing to wait for Willing-to-wait's latch

COUNT (*)

----------

35

sys@ bys3>select Count (*) from V$latch where immediate_gets+immediate_misses=0; ---don't want to wait for immediate latch

COUNT (*)

----------

547

################

The number of times the requester requests latch to wait after unsuccessful willing-to-wait mode involves the _spin_count parameter: The default value of 2000, that is, the SPIN2000 time is still unable to get the latch to sleep.

sys@ Bys3>@?/rdbms/admin/show_para-Script specific content see: http://blog.csdn.net/haibusuanyun/article/details/17140197 second section.

Enter value for P:spin

Old 3:where i.inst_id = USERENV (' Instance ') and cv.inst_id = USERENV (' Instance ') and i.indx = Cv.indx and Upper (I.KSP PINM) like upper ('%&p% ') Order by REPLACE (I.KSPPINM, ' _ ', ')

New 3:where i.inst_id = USERENV (' Instance ') and cv.inst_id = USERENV (' Instance ') and i.indx = Cv.indx and Upper (I.KSP PINM) like upper ('%spin% ') Order by REPLACE (I.KSPPINM, ' _ ', ')

P_name p_description p_value IsDefault ismodified Isadj

------------------ ------------------------ ---------- --------- ---------- -----

_lm_lms_spin make LMS is false TRUE false

_mutex_spin_count Mutex spin count 255 TRUE False

_spin_count Amount to spin waiting for a latch 1 TRUE false

Here because it is the single core that is used on the virtual machine, so the _spin_count is 1, the dual core is the default value: 2000

Show parameter CPU to view current CPU condition.

#############