Oracle中的父闩大致可以分成2类：有子闩的父闩或者独居的父闩，我们来看看这些父闩的属性:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
SQL> select count(distinct name) from v$latch_children;
COUNT(DISTINCTNAME)
-------------------
82
/* 10.2.0.4下共有82种不同子闩，同比11.2.0.1是75种，要比10g中少一些，因为一部分闩在11g中被mutex替代了 */
SQL> select count(distinct name) from v$latch_parent;
COUNT(DISTINCTNAME)
-------------------
394
/* 共有394种不同父闩 */
SQL> select lp.name
2 from v$latch_parent lp,(select distinct name from v$latch_children) lc
3 where lp.name=lc.name(+) and lc.name is NULL;
NAME
--------------------------------------------------
temp lob duration state obj allocation
alert memory latch
mapped buffers lru chain
resmgr:schema config
QMT
FAL subheap alocation
SGA kcrrssncpl latch
KJC global post event buffer
Reserved Space Latch
Request id generation latch
kpon sga structure
resmgr:runnable lists
hash table modification latch
xscalc freelist
gcs pcm hashed value bucket hash
parameter list
KMG MMAN ready and startup request latch
server alert latch
bq:time manger info latch
kwqbsn:qsga
job queue sob latch
KFCL Instance Latch
qm_init_sga
state object free list
KFCL BX Freelist
process
logical standby view
resmgr:queued list
multiblock read objects
hint flashback FBA barrier
i/o slave adaptor
ksxp tid allocation
KJC receiver ctx free list
In memory undo latch
OS process: request allocation
krbmrosl
resmgr:running actses count
active service list
parallel recoverable recovery
xssinfo freelist
cache table scan latch
XDB unused session pool
queue sender's info. latch
flashback hint SCN barrier
RSM process latch
first spare latch
qmn task queue latch
JS broadcast autostart latch
constraint object allocation
AW SGA latch
KSFQ
cached attr list
loader state object freelist
JS queue state obj latch
shared server info
dummy allocation
peplm
policy information
Testing
shrink stat allocation latch
FOB s.o list latch
media recovery process out of buffers
KJC receiver queue access list
flashback mapping
shared server configuration
ASM map operation freelist
statistics aggregation
resmgr:free threads list
block media rcv so alloc latch
ges statistic table
query server freelists
mostly latch-free SCN
RSM SQL latch
name-service entry
name-service request
threshold alerts latch
name-service request queue
ges process table freelist
virtual circuits
kupp process latch
archive process latch
Token Manager
error message lists
Memory Queue Message Subscriber #1
direct msg latch
Memory Queue
ges deadlock list
gcs remastering latch
logical standby cache
resmgr group change latch
KFK SGA context latch
pass worker exception to master
process group creation
NSV creation/termination latch
ksfv subheap
Media rcv so alloc latch
job_queue_processes parameter latch
segmented array pool
ASM map operation hash table
name-service pending queue
message enqueue sync latch
KJC snd proxy ctx free list
image handles of buffered messages latch
KJCT receiver queue access
XDB used session pool
ASM file locked extent latch
change tracking consistent SCN
trace latch
flashback sync request
Policy Refresh Latch
KFC SGA latch
AQ Propagation Scheduling System Load
DMON Work Queues Latch
Streams Generic
session state list latch
OS process allocation
gcs opaque info freelist
shared server spare latch 2
KJC snd proxy queue access list
KFC Hash Latch
Bloom filter list latch
resumable state object
JS event notify broadcast latch
Change Notification Hash table latch
global ctx hash table latch
alert log latch
AQ Propagation Scheduling Proc Table
reservation so alloc latch
ksir sga latch
KFM allocation
ASM allocation
dispatcher info
Transportable DB Context Latch
kwqbcco:cco
SGA mapping latch
kwqi:kchunk latch
datapump attach fixed tables latch
process allocation
KJC destination ctx free list
JS broadcast drop buf latch
SQL memory manager latch
resmgr:gang list
compile environment latch
slave class create
JOX SGA heap latch
MQL Tracking Latch
shared server spare latch 1
ASM file allocation latch
instance enqueue
redo writing
name-service memory objects
bufq statistics
session allocation
global hanganlyze operation
session timer
gcs domain validate latch
qmn state object latch
NSV command ID generation latch
ping redo on-disk SCN
SGA kcrrlatmscnl latch
JS broadcast load blnc latch
KFMD SGA
internal temp table object number allocation latc
bug fix control action latch
job_queue_processes free list latch
Memory Queue Message Subscriber #4
kwqbsgn:msghdr
enqueues
cost function
flashback marker cache
AWR Alerted Metric Element list
KFR redo allocation latch
FIB s.o chain latch
Memory Queue Message Subscriber #3
spilled messages latch
gcs remaster request queue
sequence cache
managed standby latch
KTF sga latch
resmgr:vc list latch
kmcpvec latch
Memory Management Latch
KJC global resend message queue
name-service namespace objects
shared server spare latch 3
resmgr:active threads
ASM network background latch
global KZLD latch for mem in SGA
presentation list
multiple dbwriter suspend
ASM map headers
query server process
KFCL LE Freelist
Managed Standby Recovery State
lgwr LWN SCN
rm cas latch
file number translation table
X$KSFQP
archive control
WCR: kecu cas mem
JS broadcast add buf latch
pebof_rrv
KWQMN job cache list latch
file cache latch
resmgr:incr/decr stats
Consistent RBA
ges timeout list
user lock
kwqbsn:qxl
kokc descriptor allocation latch
hash table column usage latch
dml lock allocation
redo on-disk SCN
LGWR NS Write
Bloom Filter SGA latch
KFA SGA latch
ASM map load waiting list
recovery domain freelist
virtual circuit buffers
object stats modification
TXN SGA
hot latch diags
PL/SQL warning settings
KPON ksr channel latch
dynamic channels
ges caches resource lists
cache protection latch
end-point list
second spare latch
ges s-lock bitvec freelist
Mutex
kmcptab latch
rules engine rule statistics
ASM rollback operations
rules engine rule set statistics
library cache load lock
event group latch
list of block allocation
gcs drop object freelist
STREAMS LCR
KWQMN to-be-Stopped Buffer list Latch
address list
instance information
ktm global data
XDB Config
SGA IO buffer pool latch
active checkpoint queue latch
JS broadcast kill buf latch
DMON Process Context Latch
Undo Hint Latch
rules engine evaluation context statistics
dictionary lookup
event range base latch
begin backup scn array
fixed table rows for x$hs_session
flashback FBA barrier
ASM db client latch
KSXR large replies
buffer pin latch
ges synchronous data
FAL request queue
gcs resource validate list
SGA kcrrpinfo latch
reg$ timeout service time
transaction branch allocation
Change Notification Latch
Mutex Stats
SGA kcrrgap latch
KFC FX Hash Latch
flashback allocation
sort extent pool
logminer work area
KFC LRU latch
OS file lock latch
ksupkttest latch
database property service latch
NLS data objects
Policy Hash Table Latch
Memory Queue Message Subscriber #2
cache buffer handles
queued dump request
JS mem alloc latch
device information
ncodef allocation latch
change tracking state change latch
flashback SCN barrier
temporary table state object allocation
parallel txn reco latch
ksuosstats global area
rules engine aggregate statistics
change tracking optimization SCN
channel anchor
parameter table allocation management
OLS label cache
vecio buf des
STREAMS Pool Advisor
cas latch
Real time apply boundary
Memory Queue Subscriber
generalized trace enabling latch
Fast-Start Failover State Latch
DMON Network Error List Latch
numer of job queues for server notfn
messages
ksv instance
resmgr:method mem alloc latch
QOL Name Generation Latch
enqueue sob latch
KMG resize request state object freelist
Memory Management Parameter Latch
KWQP Prop Status
datapump job fixed tables latch
312 rows selected.
/* 10.2.0.4中共有312个独居的父闩 */
SQL> select lp.name,lp.gets,lp.immediate_gets
2 from v$latch_parent lp, (select distinct name from v$latch_children) lc
3 where lp.name = lc.name(+)
4 and lc.name is NULL
5 and (lp.gets != 0 or lp.immediate_gets != 0) order by gets asc;
NAME GETS IMMEDIATE_GETS
-------------------------------------------------- ---------- --------------
RSM SQL latch 0 1
Undo Hint Latch 0 122
Memory Management Latch 0 4189799
MQL Tracking Latch 0 250886
recovery domain freelist 1 0
instance enqueue 1 0
QMT 1 0
gcs drop object freelist 1 0
channel anchor 1 0
reg$ timeout service time 1 0
address list 1 0
generalized trace enabling latch 1 0
global hanganlyze operation 1 0
rules engine evaluation context statistics 1 2
qm_init_sga 1 0
rules engine rule statistics 2 0
resmgr:vc list latch 2 0
ksv instance 2 0
Mutex Stats 2 0
managed standby latch 2 0
global ctx hash table latch 2 0
shared server configuration 3 2
resmgr:method mem alloc latch 3 0
alert log latch 5 2
qmn state object latch 6 0
NLS data objects 7 0
rules engine aggregate statistics 8 0
KJC receiver ctx free list 9 2
KJCT receiver queue access 9 0
KJC snd proxy ctx free list 9 0
KJC snd proxy queue access list 9 0
KJC receiver queue access list 9 0
name-service entry 11 0
job_queue_processes free list latch 13 2
KJC destination ctx free list 18 2
name-service namespace objects 22 2
cache table scan latch 24 12187349
event range base latch 94 0
JS broadcast kill buf latch 103 0
file number translation table 135 0
trace latch 526 0
temp lob duration state obj allocation 1831 0
ges synchronous data 3369 244198
KWQP Prop Status 3491 0
KSXR large replies 3796 0
JOX SGA heap latch 4006 1147
query server process 4458 4452
resmgr:schema config 6987 0
SQL memory manager latch 6998 4183835
state object free list 7012 0
temporary table state object allocation 7346 0
X$KSFQP 12112 0
internal temp table object number allocation latc 24327 0
name-service request 26760 0
slave class create 29562 0
JS mem alloc latch 42492 2
KTF sga latch 52471 2860365
FIB s.o chain latch 189472 0
ncodef allocation latch 207281 0
global KZLD latch for mem in SGA 213870 0
event group latch 250324 0
FAL subheap alocation 270455 0
FAL request queue 270455 0
dictionary lookup 293787 0
ktm global data 294500 0
rules engine rule set statistics 349119 0
hash table modification latch 410073 2
kwqbsn:qsga 447245 0
library cache load lock 451132 0
ges process table freelist 500374 0
OS process: request allocation 500376 0
process group creation 500376 0
object stats modification 504747 5
krbmrosl 654559 0
archive control 671384 0
gcs remaster request queue 720951 0
ksuosstats global area 851936 0
sort extent pool 931800 0
KWQMN job cache list latch 984125 0
loader state object freelist 1181736 0
error message lists 1276908 0
threshold alerts latch 1314860 0
hash table column usage latch 1665060 358586939
qmn task queue latch 1747101 0
statistics aggregation 1955529 0
parameter list 2321759 0
query server freelists 2425792 0
JS broadcast load blnc latch 2505489 0
job_queue_processes parameter latch 2681109 0
JS broadcast drop buf latch 2882525 0
JS broadcast add buf latch 2882597 0
ges timeout list 3193262 3404126
KMG MMAN ready and startup request latch 4189807 0
FOB s.o list latch 4211788 0
session timer 4341863 0
archive process latch 4745101 0
OS process allocation 4781454 0
begin backup scn array 5372508 0
parallel txn reco latch 7060020 0
ges deadlock list 8225144 3734
kokc descriptor allocation latch 10218320 0
ASM db client latch 10368227 0
compile environment latch 10408232 0
SGA IO buffer pool latch 10518957 10524060
parameter table allocation management 10580949 0
resmgr group change latch 10928225 0
file cache latch 12909240 0
cache buffer handles 20398694 0
user lock 20713291 0
gcs opaque info freelist 20714278 0
resmgr:free threads list 20855255 0
resmgr:active threads 20862246 0
dummy allocation 20870065 0
session state list latch 21249620 0
name-service pending queue 22618030 0
name-service memory objects 23624996 2
list of block allocation 23630931 0
active checkpoint queue latch 27637189 5
multiblock read objects 32939240 2
sequence cache 33287559 0
Consistent RBA 35761759 0
lgwr LWN SCN 36790265 0
mostly latch-free SCN 36976437 0
PL/SQL warning settings 41507388 0
active service list 58157997 4341784
queued dump request 62853955 0
JS queue state obj latch 90975988 0
ges caches resource lists 97073996 145681370
gcs remastering latch 107632668 0
process allocation 112440225 249864
AWR Alerted Metric Element list 129684638 0
redo writing 142555594 0
name-service request queue 148086376 0
dml lock allocation 156109225 0
transaction branch allocation 449472105 0
messages 670565488 0
session allocation 1648369839 0
enqueues 2505157425 0
138 rows selected.
/* 以上列出了312个独居父闩中有138个常被使用，其中最常用的是"enqueues"，"session allocation"，"messages"等父闩，
若他们被某个dead process长期持有则可能导致整个实例hang住
*/
/* 注意一般RAC环境中才会用到ges caches resource lists等全局队列闩 */
SQL> select lp.name, lp.gets, lp.immediate_gets, lc.name
2 from v$latch_parent lp, (select distinct name from v$latch_children) lc
3 where lp.name = lc.name(+)
4 and lc.name is not NULL
5 and (lp.gets != 0 or lp.immediate_gets != 0)
6 order by gets asc;
NAME GETS IMMEDIATE_GETS NAME
-------------------------------------------------- ---------- -------------- --------------------------------------------------
resmgr:resource group CPU method 1 0 resmgr:resource group CPU method
simulator lru latch 2 0 simulator lru latch
resmgr:plan CPU method 2 0 resmgr:plan CPU method
recovery domain hash list 4 0 recovery domain hash list
process queue 23 0 process queue
process queue reference 48 0 process queue reference
shared pool 57 0 shared pool
enqueue hash chains 3613 0 enqueue hash chains
library cache 27905 0 library cache
redo allocation 28695 0 redo allocation
JS slv state obj latch 155438 0 JS slv state obj latch
undo global data 945519 0 undo global data
transaction allocation 3327272 0 transaction allocation
13 rows selected.
/* 82个有子闩的父闩中只有13个是常使用的，
其中最常用的是"transaction allocation"，"undo global data","JS slv state obj latch"
*/
有用的父闩被持有往往会酿成灾难，因为他们在数据库堪称是真正one and only的。
How to Identify Which Latch is Associated with a “latch free” wait
Applies to:
Oracle Server – Enterprise Edition – Version: 8.1.7.4 to 10.2.0.4
Information in this document applies to any platform.
Oracle Server Enterprise Edition – Version: 8.1.7.4 to 10.2.0.4
Goal
It is important to identify which latch is associated with latch free waits when tuning a database for latch waits.
For versions prior to 10g, there is an umbrella wait event called latch free that covers all latch waits. The specific latch or latches involved must be determined from either a 10046 trace (and TKProf) or from a statspack report. This note will show you how to determine which latch or latches are associated with the latch free event.
In Oracle 10g or later, finding which latches are causing waits is easy because most wait events have been introduced for specific latch waits (e.g., latch: shared pool). However, some latch waits are still rolled up in the old latch free wait event and you will need to follow the procedure here to obtain more information.
Solution
TKProf
This technique will help you identify the latch as well as the top SQL statements associated with the latch free event.
* In the “Overall Totals” section,look for wait events with high elapsed times for “latch:” or “latch free” events (Overall Totals, recursive and non-recursive)
For example, the listing below is from the overall summary for recursive statements (in this case, the application was PL/SQL and non-recursive statements were negligible):
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
latch: library cache 623 0.45 29.48
latch: shared pool 494 0.25 10.31
latch free 77 0.33 4.92
latch: row cache objects 1 0.01 0.01
* In the “Overall Totals” section, determine which call type is associated with the highest elapsed time: parse, execute, or fetch
For example, here you see that parse calls have the highest elapsed time.
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 3337 86.97 371.50 0 0 0 0
Execute 3338 0.97 3.06 0 0 0 0
Fetch 3338 0.75 2.67 0 3338 0 3338
——- —— ——– ———- ———- ———- ———- ———-
total 10013 88.69 377.25 0 3338 0 3338
* Generate a new TKProf report sorted by the call type found for the highest elapsed times. For example:
Parse calls:
tkprof trace_file_name output_file sort=prsela
Fetch calls:
tkprof trace_file_name output_file sort=fchela
* Choose a few of the top cursors in this new TKProf report and find them in the original trace file.
* Oracle 10g+: Examine the waits for the statement and see which “latch:” wait it is
* When most waits are for latch free rather than a specific latch:
o Look at the lines with “WAIT #” for the latch free event corresponding to the cursor and find the value of the “P2” field. This field corresponds to the latch number in V$LATCHNAME.
o Query V$LATCHNAME to find the name of the latch
o Now, you can get an idea of which latches are causing most of the waits.
For example, here is what we would look for in the trace file:
From the trace file:
=====================
PARSING IN CURSOR #1 len=98 dep=1 uid=54 oct=3 lid=54 tim=5351590246329 hv=2697127572 ad=’88f5bf60′
SELECT COUNT(*) FROM (SELECT 1949,3898,5847,7796,9745,11694,13643,15592 FROM EMP WHERE rownum = 1)
END OF STMT
PARSE #1:c=10000,e=7527,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=5351590246298
EXEC #1:c=0,e=329,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=5351590247140
FETCH #1:c=0,e=221,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=5351590247568
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op=’SORT AGGREGATE (cr=1 pr=0 pw=0 time=263 us)’
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=0 op=’VIEW (cr=1 pr=0 pw=0 time=163 us)’
STAT #1 id=3 cnt=1 pid=2 pos=1 obj=0 op=’COUNT STOPKEY (cr=1 pr=0 pw=0 time=148 us)’
STAT #1 id=4 cnt=1 pid=3 pos=1 obj=51152 op=’INDEX FULL SCAN PK_EMP (cr=1 pr=0 pw=0 time=111 us)’
WAIT #1: nam=’latch free’ ela= 176876 address=15232212216 number=202 tries=1 obj#=-1 tim=5351590477313
=====================
PARSING IN CURSOR #1 len=70 dep=1 uid=54 oct=3 lid=54 tim=5351590478048 hv=3616361148 ad=’85b72468′
SELECT COUNT(*) FROM (SELECT 1950,3900,5850 FROM EMP WHERE rownum = 1)
END OF STMT
PARSE #1:c=40000,e=229111,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=5351590478021
EXEC #1:c=10000,e=572,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=5351590479029
In SQLPlus, find out which latch corresponds to the latch free waits:
SQL> select latch#, name from v$latchname where latch# = 202;
LATCH# NAME
———- ———–
202 kks stats
AWR or statspack report
* 10g or higher; most latch waits will include which latch is causing the wait. E.g., latch: library cache
* 9.0.x – 9.2.x, the wait will be latch free. You’ll have to visit the Latch Activities section and find the latches with the highest wait times.
For example, here is what we would look for in the AWR or Statspack regarding the Top Timed Events:
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
—————————— ———— ———– —— —— ———-
CPU time 571 23.8
latch: library cache 3,894 195 50 8.1 Concurrenc
latch: shared pool 2,439 45 18 1.9 Concurrenc
latch free 448 31 69 1.3 Other
control file parallel write 706 7 10 0.3 System I/O
————————————————————-
Then, we would examine the Latch Activities section of the report:
Latch Activity DB/Inst: DB10GR2/DB10gR2 Snaps: 5162-5163
-> “Get Requests”, “Pct Get Miss” and “Avg Slps/Miss” are statistics for
willing-to-wait latch get requests
-> “NoWait Requests”, “Pct NoWait Miss” are for no-wait latch get requests
-> “Pct Misses” for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Name Requests Miss /Miss (s) Requests Miss
———————— ————– —— —— —— ———— ——
…
job_queue_processes para 37 0.0 N/A 0 0 N/A
kks stats 106,603 0.4 1.0 31 0 N/A
ksuosstats global area 144 0.0 N/A 0 0 N/A
…
library cache 645,359 0.5 1.3 195 9,728 0.1
…
shared pool 646,234 0.3 1.1 45 0 N/A
…
In the above report sample, one can see that the time spent waiting for the library cache and shared pool latches are easily accounted for (195 and 45 seconds) and match the wait events for those latches. However, the kks stats latch is not accounted in its own wait event but instead its time is rolled into the latch free wait event (31 seconds).
* Prior to 9.0.x, the wait will be latch free. You’ll have to visit the Latch Sleep breakdown section and find the latches with the highest sleeps.
For example, assuming latch free waits are significant, we’ll look at the Latch Sleep section of the report (this is from an 8.1.7 statspack):
Latch Sleep breakdown for DB: P013 Instance: P013 Snaps: 6 -11
-> ordered by misses desc
Get
Latch Name Requests Misses Sleeps
————————– ————– ———– ———–
cache buffer handles 95,036,786 21,350,918 364,796
cache buffers chains 231,148,059 10,683,933 4,136,206
shared pool 6,296,880 502,802 166,198
row cache objects 5,752,233 40,837 8,470
library cache 6,138,041 40,031 30,015
In this example, the cache buffers chains latch has the highest sleeps and is responsible for most of the latch wait time. Get Requests and Misses is not very reliable for finding the latch causing most of the wait time – use sleeps since it has a more direct correlation to the time spent waiting.