Oracle常用的几个父栓

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的。

Comments

  1. 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.

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号