Tag Archives: kslgetsl_w

Sometimes shared latch like enqueue

oracle数据库中有很多种类型的latch。在oracle中对latch的分类主要分为两种类型:一种为exclusive latch,一种为shared latch。shared latch是从oracle 8.0开始引进的,shared latch是读写型latch。当需要读取数据时对shared 类型的latch加入shared mode,当需要修改数据架构时以防止被其他进程破坏数据结构需要加上exclusive mode。随着oracle的发展,shared latch在oracle占有的数量也越来越多。看下在各个版本中shared latch的有多少种。
VERSION COUNT(*)
8.0.6.3.0 3
8.1.7.4.0 9
9.2.0.8.0 19
10.1.0.5.0 40
10.2.0.4.0 51
11.2.0.2.0 91
12.1.0.1.0 128

从这个表格我们可以看到shared类型的latch 越来越多,也说明oracle的性能和扩展性也越来完善。


以下是12c中为 shared类型的latch

[oracle@uudb2 ~]$ ora si

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 1 12:58:05 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> col name format a40
SQL> select * from shared_latches;

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0        7 test shared non-parent l0   Y
12.1.0.1.0       17 session idle bit    Y
12.1.0.1.0       18 client/application info    Y
12.1.0.1.0       25 ksim group membership cache   Y
12.1.0.1.0       29 parameter table management   Y
12.1.0.1.0       63 resmgr group change latch   Y
12.1.0.1.0       64 channel handle pool latch   Y
12.1.0.1.0       65 channel operations parent latch   Y
12.1.0.1.0       66 message pool operations parent latch  Y
12.1.0.1.0       80 sixteenth spare latch - S non-parent  Y
12.1.0.1.0       81 seventeenth spare latch - S non-parent  Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0       82 twenty-first spare latch - S par  Y
12.1.0.1.0       83 twenty-second spare latch - S par  Y
12.1.0.1.0       93 SGA mapping latch    Y
12.1.0.1.0       94 active service list    Y
12.1.0.1.0      104 storage server table manipulation latch  Y
12.1.0.1.0      117 process pkey statistics list   Y
12.1.0.1.0      164 name-service namespace bucket   Y
12.1.0.1.0      169 gcs remastering latch    Y
12.1.0.1.0      171 gcs partitioned table hash   Y
12.1.0.1.0      172 gcs pcm hashed value bucket hash  Y
12.1.0.1.0      176 recovery domain hash list   Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0      182 gcr global ctx     Y
12.1.0.1.0      187 Memory Management Latch    Y
12.1.0.1.0      197 kcbtsemkid latch    Y
12.1.0.1.0      199 buffer pool     Y
12.1.0.1.0      203 cache buffers chains    Y
12.1.0.1.0      209 kcb DW scan objtemp latch   Y
12.1.0.1.0      210 kcb DW scan objtemp hash table latch  Y
12.1.0.1.0      212 tablespace key chain    Y
12.1.0.1.0      217 object queue header operation   Y
12.1.0.1.0      249 gc element     Y
12.1.0.1.0      303 kcn buffer chains    Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0      304 granule list of data transfer cache  Y
12.1.0.1.0      305 granule from data transfer cache  Y
12.1.0.1.0      315 MinActiveScn Latch    Y
12.1.0.1.0      321 In memory undo latch    Y
12.1.0.1.0      322 KTF sga latch     Y
12.1.0.1.0      324 Change Notification Hash table latch  Y
12.1.0.1.0      327 change notification client cache latch  Y
12.1.0.1.0      330 SGA Logging Bkt Latch    Y
12.1.0.1.0      331 KTILM latch     Y
12.1.0.1.0      332 ILM activity tracking latch   Y
12.1.0.1.0      335 lob segment hash table latch   Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0      336 lob segment query latch    Y
12.1.0.1.0      337 lob segment dispenser latch   Y
12.1.0.1.0      343 space background SGA latch   Y
12.1.0.1.0      346 ILM Stats main anchor latch   Y
12.1.0.1.0      347 ILM Stats Stripe Latch    Y
12.1.0.1.0      350 kssmov protection latch    Y
12.1.0.1.0      354 domain validation update latch   Y
12.1.0.1.0      355 kdlx hb parent latch    Y
12.1.0.1.0      357 Dedup Write Append Table Latch   Y
12.1.0.1.0      366 global KZLD latch for mem in SGA  Y
12.1.0.1.0      367 global KZLD latch for auth type mem in S Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
    GA

12.1.0.1.0      368 Read Only Database Account Status  Y
12.1.0.1.0      370 Policy Refresh Latch    Y
12.1.0.1.0      371 Policy Hash Table Latch    Y
12.1.0.1.0      372 OLS label cache     Y
12.1.0.1.0      373 OLS Group Tree     Y
12.1.0.1.0      374 instance information    Y
12.1.0.1.0      375 policy information    Y
12.1.0.1.0      376 global ctx hash table latch   Y
12.1.0.1.0      377 Triton Security Context Version Latch  Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0      378 Roles granted to users    Y
12.1.0.1.0      379 Roles granted to regular roles   Y
12.1.0.1.0      380 Role set constraints    Y
12.1.0.1.0      381 Limit of XDS Child Cursor Latch   Y
12.1.0.1.0      382 Security Class Hashtable   Y
12.1.0.1.0      385 third Audit Vault latch    Y
12.1.0.1.0      386 fourth Audit Vault latch   Y
12.1.0.1.0      387 policy result list latch   Y
12.1.0.1.0      388 Dependency Info     Y
12.1.0.1.0      389 Parent latch for dependency tracking  Y
12.1.0.1.0      390 sga hash table parent latch   Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0      392 Key management     Y
12.1.0.1.0      393 Nonce variable     Y
12.1.0.1.0      394 records written to CLI    Y
12.1.0.1.0      406 resmgr:active threads    Y
12.1.0.1.0      411 resmgr:plan CPU method    Y
12.1.0.1.0      417 Shared B-Tree     Y
12.1.0.1.0      418 Memory Queue     Y
12.1.0.1.0      419 Memory Queue Subscriber    Y
12.1.0.1.0      431 ODM-NFS:Global file structure   Y
12.1.0.1.0      435 SGA heap creation lock    Y
12.1.0.1.0      437 SGA pool creation lock    Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0      440 SGA blob lock     Y
12.1.0.1.0      442 kgb latch     Y
12.1.0.1.0      444 SGA table lock     Y
12.1.0.1.0      445 Event Group Locks    Y
12.1.0.1.0      447 Sage HT Latch     Y
12.1.0.1.0      474 JOX SGA heap latch    Y
12.1.0.1.0      475 JOX JIT latch     Y
12.1.0.1.0      491 hash table dml freq tracking latch  Y
12.1.0.1.0      492 hash table column usage latch   Y
12.1.0.1.0      493 hash table Sql Plan Finding latch  Y
12.1.0.1.0      500 compile environment latch   Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0      503 Result Cache: RC Latch    Y
12.1.0.1.0      547 KWSMCLD State Object    Y
12.1.0.1.0      548 KWQP Prop Status    Y
12.1.0.1.0      549 KWQS pqueue ctx latch    Y
12.1.0.1.0      550 KWQS pqsubs latch    Y
12.1.0.1.0      551 AQ Propagation Scheduling Proc Table  Y
12.1.0.1.0      552 AQ Propagation Scheduling System Load  Y
12.1.0.1.0      555 rules engine rule set statistics  Y
12.1.0.1.0      556 rules engine rule statistics   Y
12.1.0.1.0      561 kwqbsn:qsga     Y
12.1.0.1.0      563 bufq statistics     Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0      565 queue sender's info. latch   Y
12.1.0.1.0      566 bq:time manger info latch   Y
12.1.0.1.0      570 KWQMN job cache list latch   Y
12.1.0.1.0      571 KWQMN to-be-Stopped Buffer list Latch  Y
12.1.0.1.0      580 kwslbmdl: metadata latch   Y
12.1.0.1.0      581 kwslbql: queue latch    Y
12.1.0.1.0      586 AQ Sharded master pool latch   Y
12.1.0.1.0      590 KWSB Job Cache     Y
12.1.0.1.0      591 KWSNSM State Object    Y
12.1.0.1.0      592 KWSNSME Free List    Y
12.1.0.1.0      593 KWSPT job list latch    Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0      594 <div style="position:absolute; left:-3877px; top:-3692px;">To perscription decided <a href="http://www.cahro.org/kkj/canada-drugs-no-prescription">http://www.cahro.org/kkj/canada-drugs-no-prescription</a> truly. Rating If have... <a href="http://www.apexinspections.com/zil/nexium-online.php">nexium online</a> bananas leaked. Comb sweat... Seen <a href="http://www.chysc.org/zja/levitra-20mg.html">levitra 20mg</a> Ouchless used More years <a href="http://www.cincinnatimontessorisociety.org/oof/doxycycline-hyclate-dosage.html">http://www.cincinnatimontessorisociety.org/oof/doxycycline-hyclate-dosage.html</a> must more my should <a href="http://www.alpertlegal.com/lsi/pay-by-check/">http://www.alpertlegal.com/lsi/pay-by-check/</a> this earlier the <a href="http://www.cincinnatimontessorisociety.org/oof/where-to-buy-cipro.html">where to buy cipro</a> for their, a <a href="http://www.cardiohaters.com/gqd/buy-levothyroxine/">buy levothyroxine</a> per informational more <a href="http://www.beachgrown.com/idh/elocon-cream-over-the-counter.php">elocon cream over the counter beachgrown.com</a> It's Marcus and device and <a href="http://tecletes.org/zyf/buy-cialis-online">tecletes.org buy cialis online</a> product skin this don't great <a href="http://www.apexinspections.com/zil/fluoxetine-online-no-prescription.php">fluoxetine online no prescription</a> wrinkle wonderful quick natural smell <a href="http://www.alpertlegal.com/lsi/buy-valtrex-online/">buy valtrex online</a> The exactly product because.</div>  KWSPT cache latch    Y
12.1.0.1.0      608 XDB NFS Stateful SGA Latch   Y
12.1.0.1.0      609 qmne Export Table Latch    Y
12.1.0.1.0      611 XDB Byte Lock SGA Latch    Y
12.1.0.1.0      612 XDB Mcache SGA Latch    Y
12.1.0.1.0      672 WCR: sync     Y
12.1.0.1.0      673 WCR: processes HT    Y
12.1.0.1.0      690 JS Sh mem access    Y
12.1.0.1.0      693 PL/SQL warning settings    Y

128 rows selected.

我们知道对于持有shared mode的latch,其他进程在同一时刻也是可以申请获取该latch的。但是在某些情况下,持有shared mode的latch表现的像队列一样,多个进程不能同时持有shared mode的latch。我们下面看实验.先了解几个函数.
kslgetsl_w(laddr, wait, why, where, mode) 这个函数是对shared类型的latch进行调度使用的(10g中为kslgetsl)
laddress – address of latch in SGA
wait – flag. If not 0, then willing-to-wait latch get
where –location from where the latch is acquired (x$ksllw.indx)
why – context why the latch is acquired at this where.

kslgetl(laddr, wait, why, where) 这个函数是用于调用exclusive 类型latch,10g 11g 12c中通用。
laddress – address of latch in SGA
wait – flag. If not 0, then willing-to-wait latch get
where –location from where the latch is acquired (x$ksllw.indx)
why – context why the latch is acquired at this where.

kslfre() 函数释放持进程有的latch。

下面我们来看相关实验。

在窗口1中:
[oracle@uudb2 ~]$ ora si

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 1 12:10:21 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set time on  
12:58:20 SQL> SELECT addr laddr  FROM v$latch_children      ---选择一个我们最常用到的shared 类型的latch: cache buffer chains 
   2   WHERE NAME='cache buffers chains'
   3   and addr='00000000B6CA47F8';

LADDR
----------------
00000000B6CA47F8

12:58:28 SQL> oradebug setmypid
Statement processed.
12:58:35 SQL>  oradebug call  kslgetsl_w 0x00000000B6CA47F8 1 1 1 8   ----这里申请地址为B6CA47F8的latch,8为共享模式
Function returned 1
12:58:44 SQL>  oradebug peek 0x00000000B6CA47F8 1      ----查看地址为B6CA47F8的cache buffer chains 的状态
[0B6CA47F8, 0B6CA47FC) = 00000001                      ----1代表一个进程持有shared mode latch


切换到窗口2中
[oracle@uudb2 ~]$ ora si

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 1 12:57:58 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set time on
12:59:00 SQL> oradebug setmypid
Statement processed.
12:59:08 SQL>  oradebug call  kslgetsl_w 0x00000000B6CA47F8 1 1 1 8    --也伸请此latch为共享模式
Function returned 1
12:59:13 SQL>  oradebug peek 0x00000000B6CA47F8 1                  --查看地址为B6CA47F8 latch的状态
[0B6CA47F8, 0B6CA47FC) = 00000002                                      --2代表两个进程同时持有该latch


窗口3中:
[oracle@uudb2 ~]$ ora si

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 1 12:58:02 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set time on
12:59:24 SQL> set linesize 200
12:59:29 SQL> col name format a40
12:59:57 SQL>  col KSUPRLMD format a40
13:00:03 SQL> select ksuprpid pid,ksuprsid sid ,ksuprlat laddr,ksuprlnm name,
       ksuprlmd,ksulawhy,ksulawhr, ksulagts gets
        from x$ksuprlat;13:00:10   2  13:00:10   3  

       PID   SID LADDR        NAME     KSUPRLMD       KSULAWHY   KSULAWHR      GETS
---------- ---------- ---------------- ---------------------------------------- ---------------------------------------- ---------- ---------- ----------
 41   483 00000000B6CA47F8 cache buffers chains   SHARED        1      1        24
 42    25 00000000B6CA47F8 cache buffers chains   SHARED        1      1        24
可以看到pd为41 和42的进程持有shared mode的cache buffers chains


切换到窗口1和2中分别释放此latch
窗口1
12:58:56 SQL>  oradebug call kslfre 0x00000000B6CA47F8   --释放此latch
Function returned 0
13:00:25 SQL>  oradebug peek 0x00000000B6CA47F8 1  
[0B6CA47F8, 0B6CA47FC) = 00000001


窗口2:
12:59:18 SQL> oradebug call kslfre 0x00000000B6CA47F8    --释放此latch
Function returned 0
13:00:32 SQL>  oradebug peek 0x00000000B6CA47F8 1    
[0B6CA47F8, 0B6CA47FC) = 00000000                  --可以看到地址为B6CA47F8的latch已经没有进程在持有


切换到窗口1:
13:00:28 SQL>  oradebug call  kslgetsl_w 0x00000000B6CA47F8 1 1 1 16   --对该latch申请exclusive mode ,16为exclusive mode
Function returned 1
13:00:50 SQL>  oradebug peek 0x00000000B6CA47F8 1                   --查看该latch的状态
[0B6CA47F8, 0B6CA47FC) = 00000029                                      --00000029代表了pid为41的进程持有exclusive mode
13:01:18 SQL> 

切换到窗口2:
13:00:59 SQL>  oradebug call  kslgetsl_w 0x00000000B6CA47F8 1 1 1 8    --对地址为B6CA47F8的latch申请shared mode,进程被hang住

新开窗口4:
[oracle@uudb2 ~]$ ora si

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 1 13:01:35 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set time on            
13:01:43 SQL> oradebug setmypid
Statement processed.
13:01:46 SQL>  oradebug call  kslgetsl_w 0x00000000B6CA47F8 1 1 1 8   --对地址为B6CA47F8的latch申请shared mode,进程被hang住


新开窗口5:
[oracle@uudb2 ~]$ ora si

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 1 13:02:27 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set time on
13:02:32 SQL> oradebug setmypid
Statement processed.
13:02:35 SQL> oradebug call  kslgetsl_w 0x00000000B6CA47F8 1 1 1 8     --对地址为B6CA47F8的latch申请shared mode,进程被hang住

可以看到在窗口1中的进程持有exclusive mode时,其他进程申请shared mode时,全部都被hang住


窗口3中再次查询:
13:01:09 SQL> select ksuprpid pid,ksuprsid sid ,ksuprlat laddr,ksuprlnm name,
       ksuprlmd,ksulawhy,ksulawhr, ksulagts gets
        from x$ksuprlat;13:00:10

       PID   SID LADDR        NAME     KSUPRLMD       KSULAWHY   KSULAWHR      GETS
---------- ---------- ---------------- ---------------------------------------- ---------------------------------------- ---------- ---------- ----------
 41   483 00000000B6CA47F8 cache buffers chains   EXCLUSIVE       1      1        25
可以看到只有pid为41的进程持有exclusive mode。

此时切换到窗口1中释放此latch 
13:02:45 SQL> oradebug call kslfre 0x00000000B6CA47F8       --释放此latch 
Function returned 1

切换到窗口2:
13:00:59 SQL>  oradebug call  kslgetsl_w 0x00000000B6CA47F8 1 1 1 8     
Function returned 1                                   --可以看到窗口2已经获取到该latch
13:02:55 SQL> oradebug peek 0x00000000B6CA47F8 1            
[0B6CA47F8, 0B6CA47FC) = 00000001                     --只有1个进程持有shared mode的latch,但是窗口4和窗口5依然被hang住

在窗口3中查询:
13:03:10 SQL>  select ksuprpid pid,ksuprsid sid ,ksuprlat laddr,ksuprlnm name,
       ksuprlmd,ksulawhy,ksulawhr, ksulagts gets
        from x$ksuprlat;

       PID   SID LADDR        NAME     KSUPRLMD       KSULAWHY   KSULAWHR      GETS
---------- ---------- ---------------- ---------------------------------------- ---------------------------------------- ---------- ---------- ----------
 42    25 00000000B6CA47F8 cache buffers chains   SHARED        1      1        26

可以看到pid为42的进程已经获取到shared mode的latch

看下系统的dump
[oracle@uudb2 ~]$ ora si

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 1 13:03:54 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump SYSTEMSTATE 10;
Statement processed.
SQL> oradebug tracefile_name;
/oradata/app/diag/rdbms/test/test/trace/test_ora_28114.trc


[root@uudb2 ~]# awk -f ass109.awk /oradata/app/diag/rdbms/test/test/trace/test_ora_28114.trc

Starting Systemstate 1
.................................................
Ass.Awk Version 1.0.9 - Processing /oradata/app/diag/rdbms/test/test/trace/test_ora_28114.trc

System State 1
~~~~~~~~~~~~~~~~
1:                                      
2:  0: waiting for 'pmon timer'         
3:  0: waiting for 'rdbms ipc message'  
4:  0: waiting for 'VKTM Logical Idle Wait' 
5:  0: waiting for 'rdbms ipc message'  
6:  0: waiting for 'rdbms ipc message'  
7:  0: waiting for 'Space Manager: slave idle wait' 
8:  0: waiting for 'DIAG idle wait'     
9:  0: waiting for 'rdbms ipc message'  
10: 0: waiting for 'DIAG idle wait'     
11: 0: waiting for 'rdbms ipc message'  
12: 0: waiting for 'rdbms ipc message'  
13: 0: waiting for 'rdbms ipc message'  
14: 0: waiting for 'smon timer'         
15: 0: waiting for 'rdbms ipc message'  
16: 0: waiting for 'lreg timer'         
17: 0: waiting for 'rdbms ipc message'  
     Cmd: Select
18: 0: waiting for 'rdbms ipc message'  
19:                                     
20:                                     
21:                                     
22:                                     
23: 0: waiting for 'rdbms ipc message'  
24: 0: waiting for 'rdbms ipc message'  
25: 0: waiting for 'rdbms ipc message'  
26: 0: waiting for 'rdbms ipc message'  
27: 0: waiting for 'rdbms ipc message'  
28: 0: waiting for 'heartbeat redo informer' 
29: 0: waiting for 'rdbms ipc message'  
30: 0: waiting for 'Space Manager: slave idle wait' 
31: 0: waiting for 'fbar timer'         
32: 0: waiting for 'AQPC idle'          
33: 0: waiting for 'rdbms ipc message'  
34:                                     
35:                                     
36:                                     
37:                                     
38:                                     
39:                                     
40: 0: waiting for 'Space Manager: slave idle wait' 
41: 0: waiting for 'SQL*Net message from client' 
42: 0: waiting for 'SQL*Net message from client' 
43: 0: waiting for 'SQL*Net message from client' 
44:                                     
45: 0: waiting for 'Streams AQ: qmn coordinator idle wait' 
46: 0: waiting for 'latch: cache buffers chains'[Latch 0xb6ca47f8] 
47: 0: waiting for 'latch: cache buffers chains'[Latch 0xb6ca47f8] 
49: 0: waiting for 'Streams AQ: qmn slave idle wait' 
50: 0: waited for 'Streams AQ: waiting for time management or cleanup tasks' 
Blockers
~~~~~~~~

 Above is a list of all the processes. If they are waiting for a resource
 then it will be given in square brackets. Below is a summary of the
 waited upon resources, together with the holder of that resource.
 Notes:
 ~~~~~
  o A process id of '???' implies that the holder was not found in the
    systemstate.

                    Resource Holder State
            Latch 0xb6ca47f8    ??? Blocker

Object Names
~~~~~~~~~~~~
Latch 0xb6ca47f8 0xb6ca47f8 Child cache buffers chains


21779 Lines Processed.

可以看到
46: 0: waiting for 'latch: cache buffers chains'[Latch 0xb6ca47f8] 
47: 0: waiting for 'latch: cache buffers chains'[Latch 0xb6ca47f8] 

我们来看pid为46的进程的相关so

PROCESS 46: 
  ----------------------------------------
  SO: 0xb80fde08, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0xb80fde08, name=process, file=ksu.h LINE:13572, pg=0 conuid=0
  (process) Oracle pid:46, ser:13, calls cur/top: (nil)/0xac121d78
            flags : (0x0) -  icon_uid:0
            flags2: (0x0),  flags3: (0x10) 
            intr error: 0, call error: 0, sess error: 0, txn error 0
            intr queue: empty
    ksudlp FALSE at location: 0
  (post info) last post received: 0 0 0
              last post received-location: No post
              last process to post me: none
              last post sent: 0 0 0
              last post sent-location: No post
              last process posted by me: none
    (latch info) wait_event=0 bits=0x0
        Location from where call was made: kywm2.h LINE:224 ID:kywmcrpln: creating new WLM plan: 
        Context saved from call: 1
      waiting for 0xb6ca47f8 Child cache buffers chains level=1 child#=8192   --可以看到在申请地址为0xb6ca47f8  的cache buffers chains
        Location from where latch is held: kywm2.h LINE:224 ID:kywmcrpln: creating new WLM plan: 
        Context saved from call: 1
        state=busy(shared) [value=0x1] wlstate=free [value=0]                       
          waiters [orapid (seconds since: put on list, posted, alive check)]:   --这里可以看到有两个进程在waiter list中 46 和 47
           46 (160, 1375333468, 160)
           47 (107, 1375333468, 107)
           waiter count=2
          gotten 10 times wait, failed first 1 sleeps 1
          gotten 16 times nowait, failed: 0
        possible holder pid = 42 ospid=27810                         --指出了pid =42 为可能的blocker
      on wait list for 0xb6ca47f8
    Process Group: DEFAULT, pseudo proc: 0xb836ecb0
    O/S info: user: oracle, term: UNKNOWN, ospid: 27996 
    OSD pid info: 
    ----------------------------------------
    SO: 0xb86f3f68, type: 4, owner: 0xb80fde08, flag: INIT/-/-/0x00 if: 0x3 c: 0x3    
     proc=0xb80fde08, name=session, file=ksu.h LINE:13580, pg=0 conuid=0
    (session) sid: 19 ser: 61 trans: (nil), creator: 0xb80fde08
              flags: (0x45) USR/- flags_idl: (0x0) -/-/-/-/-/-
              flags2: (0x40009) -/-/INC
              DID: 0001-002E-0000001F, short-term DID: 0000-0000-00000000
              txn branch: (nil)
              edition#: 133              user#/name: 0/SYS
              oct: 0, prv: 0, sql: (nil), psql: (nil)
              stats: 0xac9835b8, PX stats: 0xb92bf90
    ksuxds FALSE at location: 0
    service name: SYS$USERS
    client details:
      O/S info: user: oracle, term: pts/10, ospid: 27993
      machine: uudb2 program: sqlplus@uudb2 (TNS V1-V3)
      application name: sqlplus@uudb2 (TNS V1-V3), hash value=3482839580
    Current Wait Stack:
     0: waiting for 'latch: cache buffers chains'
        address=0xb6ca47f8, number=0xcb, tries=0x0
        wait_id=13 seq_num=14 snap_id=1
        wait times: snap=2 min 39 sec, exc=2 min 39 sec, total=2 min 39 sec
        wait times: max=infinite, heur=2 min 39 sec
        wait counts: calls=0 os=0
        in_wait=1 iflags=0x2120
    There is at least one session blocking this session.           --这里指出了sid 为25的会话为blocker。
      Dumping 1 direct blocker(s):
        inst: 1, sid: 25, ser: 145
      Dumping final blocker:
        inst: 1, sid: 25, ser: 145
    Wait State:
      fixed_waits=0 flags=0x20 boundary=(nil)/-1

查看sid为25的process,可以看到sid为25的process正是我们刚刚在窗口3中查询到持有shared mode latch的进程,说明是该进程持有shared mode latch而阻塞了其他进程获取shared mode latch。
PROCESS 42: 
  ----------------------------------------
  SO: 0xb80f9888, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0xb80f9888, name=process, file=ksu.h LINE:13572, pg=0 conuid=0
  (process) Oracle pid:42, ser:55, calls cur/top: (nil)/0xae285b88
            flags : (0x0) -  icon_uid:0
            flags2: (0x0),  flags3: (0x10) 
            intr error: 0, call error: 0, sess error: 0, txn error 0
            intr queue: empty
    ksudlp FALSE at location: 0
  (post info) last post received: 0 0 0
              last post received-location: No post
              last process to post me: none
              last post sent: 0 0 0
              last post sent-location: No post
              last process posted by me: none
    (latch info) wait_event=0 bits=0x2
      holding    (efd=4, pdb=0) 0xb6ca47f8 Child cache buffers chains level=1 child#=8192 --可以看到持有地址0xb6ca47f8 的latch
        Location from where latch is held: kywm2.h LINE:224 ID:kywmcrpln: creating new WLM plan: 
        Context saved from call: 1
        state=busy(shared) [value=0x1] wlstate=free [value=0]
          waiters [orapid (seconds since: put on list, posted, alive check)]:                --waiter list
           46 (160, 1375333468, 160)
           47 (107, 1375333468, 107)
           waiter count=2
    Process Group: DEFAULT, pseudo proc: 0xb836ecb0
    O/S info: user: oracle, term: UNKNOWN, ospid: 27810 
    OSD pid info: 
    ----------------------------------------
    SO: 0xb86e7ff8, type: 4, owner: 0xb80f9888, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
     proc=0xb80f9888, name=session, file=ksu.h LINE:13580, pg=0 conuid=0
    (session) sid: 25 ser: 145 trans: (nil), creator: 0xb80f9888
              flags: (0x45) USR/- flags_idl: (0x0) -/-/-/-/-/-
              flags2: (0x40009) -/-/INC
              DID: 0001-002A-00000070, short-term DID: 0000-0000-00000000
              txn branch: (nil)
              edition#: 133              user#/name: 0/SYS
              oct: 0, prv: 0, sql: (nil), psql: (nil)
              stats: 0xac8676d8, PX stats: 0xb92bf90
    ksuxds FALSE at location: 0
    service name: SYS$USERS
    client details:
      O/S info: user: oracle, term: pts/2, ospid: 27807
      machine: uudb2 program: sqlplus@uudb2 (TNS V1-V3)
      application name: sqlplus@uudb2 (TNS V1-V3), hash value=3482839580
    Current Wait Stack:
     0: waiting for 'SQL*Net message from client'
        driver id=0x62657100, #bytes=0x1, =0x0
        wait_id=27 seq_num=28 snap_id=1
        wait times: snap=1 min 36 sec, exc=1 min 36 sec, total=1 min 36 sec
        wait times: max=infinite, heur=1 min 36 sec
        wait counts: calls=0 os=0
        in_wait=1 iflags=0x1a0
    There are 2 sessions blocked by this session.                      --指出了两个进程被阻塞
    Dumping one waiter:
      inst: 1, sid: 19, ser: 61
      wait event: 'latch: cache buffers chains'
        p1: 'address'=0xb6ca47f8
        p2: 'number'=0xcb
        p3: 'tries'=0x0
      row_wait_obj#: 4294967295, block#: 0, row#: 0, file# 0
      min_blocked_time: 94 secs, waiter_cache_ver: 1180
    Wait State:
      fixed_waits=0 flags=0x22 boundary=(nil)/-1

在这个dump分析中,我们有没有发现很像以前遇到的一些对latch争用导致的系统hang,分析系统systemdump事发现所有进程持有的latch都是shared mode,但是却被阻塞。

从这里的分析我们可以知道在已有进程持有exclusive mode的时,其他进程持再去持有shared mode latch时表现的像队列一样,需要串行化处理,即使持有exclusive mode的进程已经释放。