oracle实验记录 （oracle 分析shared pool(2)）

分析shared pool结构与控制

dictionary cache:
当一条SQL语句 比如select * from t1进入 shared pool library cache前，oracle服务进程会到dictionary cache（row cache）找与t1 table相关的 数据字典信息（表名，列名，权限等）如果没找到从system tablespace数据字典里读入buffer cache（从disk读入），然后将这些数据字典信息按行的方式 放入shared pool dictionary cache，然后再从dictionary cache取信息放入library cache

library cache：存SQL语句，SQL语句解析树，SQL语句执行计划，还存放控制结构(lock、pin、dependency table等)，也存放从dictionary cache获得的对象信息.
library cache使用hash 算法得到一个值 就是 hash bucket SQL语句应用hash算法计算出所在的hash bucket 进入该hash buckt进行扫描确定是否存在相同的语句 存在，当该bucket上 此对象的library cache handle不存在 就是第一次执行此时进程会构建一个library cache handle挂到该bucket上 装载对象 ，如果该对象的library cache handle存在但该handle指向的对象已经换出内存，此时对象将重新装载reload。
 library cache使用多个hash bucket管理 每个hash bucket上串联多个 library cache handle（存放对象name,namespace,对象标记,heap0对象的指针,heap0 存对象类型，相关的 表 ，实际 执行计划，执行pl/sql的机器码等,heap是由一个或多个chunk组成，这些chunk分布在library cache中 不需要连续）形成一个双向链表

 

libarary cache
hash bucket -------library cache handle-library cache handle(LCO(LIBRARY CACHE object))
hash bucket
.........

SQL> select table_name ,owner from all_tables where table_name='T1';

TABLE_NAME                     OWNER
------------------------------ ------------------------------
T1                             TR
T1                             XH

 

SQL> show user
USER 为 "XH"
SQL> select * from t1;

         A
----------
         1

SQL> show user
USER 为 "TR"
SQL> select * from t1;

         A
----------
         2

SQL> select sid,PREV_HASH_VALUE,SQL_HASH_VALUE from v$session where username='XH
';

       SID PREV_HASH_VALUE SQL_HASH_VALUE
---------- --------------- --------------
       120      2245880055       85427053

SQL> select sid,PREV_HASH_VALUE,SQL_HASH_VALUE from v$session where username='TR
';

       SID PREV_HASH_VALUE SQL_HASH_VALUE
---------- --------------- --------------
       123      2245880055              0

 

SQL> col sql_text format a40
SQL> select sql_text,executions,PARSE_CALLS,child_number from v$sql where hash_v
alue='2245880055';

SQL_TEXT                                 EXECUTIONS PARSE_CALLS CHILD_NUMBER
---------------------------------------- ---------- ----------- ------------
select * from t1                                  1           1            0
select * from t1                                  1           1            1

 

SQL> col sql_text format a30
SQL> select sql_text,version_count,executions,PARSE_CALLS,kept_versions from v$s
qlarea where hash_value='2245880055';

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
KEPT_VERSIONS
-------------
select * from t1                           2          2           2
            0

SQL> desc v$object_dependency;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 FROM_ADDRESS                                       RAW(4)
 FROM_HASH                                          NUMBER
 TO_OWNER                                           VARCHAR2(64)
 TO_NAME                                            VARCHAR2(1000)
 TO_ADDRESS                                         RAW(4)
 TO_HASH                                            NUMBER
 TO_TYPE 
                                          NUMBER
 FROM_ADDRESS  :一个包过程游标装载到shared pool中 指向语句的地址
 FROM_HASH:一个包过程游标在 shared pool中hash value
 TO_OWNER:对象依赖拥有者
 TO_NAME:依赖对象名
 TO_ADDRESS:依赖对象的handle
 TO_HASH：依赖对象的 hash 值
 TO_TYPE:依赖对象的 类型

SQL> select from_address,from_hash,to_owner,to_name,to_address,to_char(to_hash,'
xxxxxxxx') from v$object_dependency where to_name='T1';

FROM_ADD  FROM_HASH TO_OWNER   TO_NAME    TO_ADDRE TO_CHAR(T
-------- ---------- ---------- ---------- -------- ---------
1C6B7124 2245880055 XH         T1         18F71378  bbf0d9e5 ~~~~~~~来自不同的对象,所以有2个子游标,2个执行计划
1C6B7124 2245880055 TR         T1         2092EE18  3d0f9e97

ALTER SESSION SET EVENTS 'immediate trace name library_cache level 16';

使用 name既SQL语句本身 or 1C6B7124(v$object_dependency.from_address一个包过程游标装载到shared pool中 指向语句的地址) 在trace中找
BUCKET 92407:
  LIBRARY OBJECT HANDLE: handle=1c6b7124 mutex=1C6B71D8(2)
  name=select * from t1
  hash=cac0083a10f7079b23ea1a1585dd68f7 timestamp=10-08-2009 23:50:22
  namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/SML/KST/DBN/MTX/[120100d4]
  kkkk-dddd-llll=0001-0001-0001 lock=0 pin=0 latch#=2 hpc=0000 hlc=0000
  lwt=1C6B7180[1C6B7180,1C6B7180] ltm=1C6B7188[1C6B7188,1C6B7188]
  pwt=1C6B7164[1C6B7164,1C6B7164] ptm=1C6B716C[1C6B716C,1C6B716C]
  ref=1C6B71A0[1C6B71A0,1C6B71A0] lnd=1C6B71AC[20A78204,18D08B00]
    LIBRARY OBJECT: bject=1de670c0
    type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
    CHILDREN: size=16
    child#    table reference   handle
    ------ -------- --------- --------
         0 1de6704c  1de66d00 1c50ed24~~~~这个handle 就是不同子游标指向各自的heap的指针
         1 1de6704c  1de66e60 20906790
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr alloc(K)  size(K)
    ----- -------- -------- --------- ---- ------ --- -------- --------
        0 18eaf050 1de67158 I/P/A/-/-    0 NONE   00      0.67     1.05
    HEAP DUMP OF DATA BLOCK 0:
******************************************************
HEAP DUMP heap name="PCursor"  desc=18EAF050
 extent sz=0x424 alt=32767 het=16 rec=9 flg=2 pc=0
 parent=03C38510 wner=00000000 nex=00000000 xsz=0x424
EXTENT 0 addr=1DE66C68
  Chunk 1de66c70 sz=      596    perm      "perm           "  alo=520
Dump of memory from 0x1DE66C70 to 0x1DE66EC4
1DE66C70 40000255 00000000 1DE670B0 00000208  [U..@.....p......]
1DE66C80 00000000 00000000 00000000 00000000  [................]
1DE66C90 1DE66CF0 00000010 00000004 00000000  [.l..............]

 

 

查找heap 0,用 子游标的handle  1c50ed24 需要共享到这个heap0才算 真正共享执行计划~~~否则即使SQL语句一样也会产生不同执行计划,产生子游标,并且第一次产生的话是hard parse.

LIBRARY OBJECT HANDLE: handle=1c50ed24 mutex=1C50EDD8(0)
  namespace=CRSR flags=RON/KGHP/PN0/[10010000]
  kkkk-dddd-llll=0000-0041-0041 lock=0 pin=0 latch#=2 hpc=0000 hlc=0000
  lwt=1C50ED80[1C50ED80,1C50ED80] ltm=1C50ED88[1C50ED88,1C50ED88]
  pwt=1C50ED64[1C50ED64,1C50ED64] ptm=1C50ED6C[1C50ED6C,1C50ED6C]
  ref=1C50EDA0[1DE66D00,1DE66D00] lnd=1C50EDAC[1C50EDAC,1C50EDAC]
    LIBRARY OBJECT: bject=1de66860
    type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
    DEPENDENCIES: count=1 size=16
    dependency#    table reference   handle position flags
    ----------- -------- --------- -------- -------- -------------------
              0 1de823cc  1de820cc 20a7817c       14 DEP[01]
    READ ONLY DEPENDENCIES: count=1 size=16
    dependency#    table reference   handle flags
    ----------- -------- --------- -------- -------------------
              0 1de66c1c  1de6699c 1c6b7124 /ROD/KPP[60]
    ACCESSES: count=1 size=16
    dependency# types
    ----------- -----
              0 0009
    TRANSLATIONS: count=1 size=16
    original    final
    -------- --------
    20a7817c 20a7817c
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr alloc(K)  size(K)
    ----- -------- -------- --------- ---- ------ --- -------- --------
        0 20af3c38 1de669b0 I/-/A/-/-    0 NONE   00      2.27     3.14
        6 1de82000 1d19bec8 I/-/A/-/-    0 NONE   00      2.88     4.00

看到data#部分0 就是heap 0, 6就是heap 6,其中heap 列就是在内存中的实际地址
    HEAP DUMP OF DATA BLOCK 0:
******************************************************
HEAP DUMP heap name="CCursor"  desc=20AF3C38
 extent sz=0x424 alt=32767 het=16 rec=9 flg=2 pc=3
 parent=03C38510 wner=1DE66860 nex=00000000 xsz=0x424
EXTENT 0 addr=1DE81FE8
  Chunk 1de81ff0 sz=      636    perm      "perm           "  alo=392
Dump of memory from 0x1DE81FF0 to 0x1DE8226C
1DE81FF0 4000027D 00000000 1DE66BA8 00000188  [}..@.....k......]
1DE82000 03C38510 00000FF4 1DE66860 1D19B08C  [........`h......]
1DE82010 00000000 00000000 00000000 020A0200  [................]

******************************************************
    HEAP DUMP OF DATA BLOCK 6:
******************************************************
HEAP DUMP heap name="sql area"  desc=1DE82000
 extent sz=0xff4 alt=32767 het=16 rec=0 flg=2 pc=2
 parent=03C38510 wner=1DE66860 nex=00000000 xsz=0xfe4
EXTENT 0 addr=1D19B08C
  Chunk 1d19b094 sz=     1116    free      "               "
Dump of memory from 0x1D19B094 to 0x1D19B4F0
1D19B090          C000045D 00000000 1DE8206C      [].......l ..]
1D19B0A0 1DE8206C 1D19A26C 1D1BA114 00008100  [l ..l...........]
1D19B0B0 0000001D 1D19B098 607D0318 00000A5C  [..........}`\...]
1D19B0C0 534B0008 46455355 0000474C 00000015  [..KSUSEFLG......]
1D19B0D0 1D19B0B0 607D0300 1D19B160 1D1FC834  [......}``...4...]
1D19B0E0 00000075 1D19B0CC 607B796C 00080000  [u.......ly{`....]

 

以下解释(来自IT168)

    Heap是通过调用服务器进程进行分配的，任何对象都具有heap 0，至于还应该分配哪些其他的heap则是由对象的类型决定的，比如SQL游标具有heap 1和 6，而PL/SQL程序包则具有heap 1、2、3和4。按照heap的使用情况，oracle会在SGA（library cache）、PGA或UGA中分配heap，但是heap 0始终都是在library cache中进行分配的。如果所请求的heap已经在SGA中分配了，则不会在PGA中再次分配heap。Heap是由一个或多个chunk组成的，这些 chunk可以是分散的分布在library cache中的，不需要连续分布。

1) object type：library cache中的对象类型包括：表、视图、索引、同名词等等。每个对象只能有一个object type，根据object type将对象归类到不同的namespace里。一个object type对应一个namespace，但是一个namespace可能对应多个object type。这样的话，查找一个对象时，只要在该对象所属的namespace中去找就可以了。比较常见的namespace包括：
a) SQL AREA：也可以叫做CRSR，表示shared cursor，存放共享的SQL语句。
b) TABLE/PROCEDURE：存放的object type包括：table、view、sequence、synonym、 procedure的定义、function的定义以及package的定义。
c) BODY：存放procedure的实际代码、function的实际代码以及package的实际代码。
d) TRIGGER：存放的object type为trigger。
e) INDEX：存放的object type为index。
2) object name：对象名称由三部分组成：
a) Schema的名称，对于共享游标（SQL语句或PL/SQL程序块）来说为空。
b) 对象名称。分为两种情况：对于共享游标（SQL语句或PL/SQL程序块）来说，其对象名称就是SQL的语句本身；而对于其他对象（比如表、视图、索引等）就是其在数据字典中的名称。
c) Database link的名称。这是可选的，如果是本地对象，则为空。
这样，对象的名称的格式为：SCHEMA.NAME@DBLINK。比如，可以为hr.employees@apac.com，也可以为hr.employees等。
3) flags：flags主要用来描述对象是否已经被锁定。对象具有三种类型的flag：
a) public flag：表示对象上没有锁定（pin）或者latch。
b) status flag：表示对象上存在锁定（pin），说明对象正在被创建或删除或修改等。
c) specitial flag：表示对象上存在library cache latch。
4) tables：对每个对象，都会维护以下一串tables中的若干个：
a) dependency table：含有当前对象所依赖的其他对象。比如一个视图可能会依赖其组成的多个表、一个存储过程可能依赖其中所调用的其他存储过程、一个游标可能依赖其中所涉及到的多个表等。Dependency table中的每个条目都指向一块物理内存，该物理内存中含有当前对象所依赖的对象的句柄。
b) child table：含有当前对象的子对象，只有游标具有child table。Child table中的每个条目都指向一个可执行的SQL命令所对应的句柄。
c) translation table：包含当前对象所引用的名称是如何解释为oracle底层对象的名称，只有游标具有translation table。
d) authorization table：包含该对象上所对应的权限，一个条目对应一个权限。
e) access table：对于dependency table中的每一个条目，都会在access table中存在对应的一个或多个条目。比如，假设对象A依赖对象B，那么在A的dependency table和access table中都会存在一个条目指向B。位于access table中的指向B的条目说明了对B具有什么样的访问类型，从而也就说明了用户要执行A则必须具有对B的权限。
f) read-only dependency table：类似于dependency table，但是存放只读的对象。
g) schema name table：包含authorization table中的条目所属的schema。
5) data blocks：对象的其他信息会存放在不同的heap中，为了找到这些heap，会在heap 0中存放多个（最多16个，但是这16个data block不会都用到）data blocks结构，每个data block含有指向这些实际heap内存块的指针。
除了heap 0以外，还有11个heap，根据对象的不同进行分配，并存放了不同的内容：
1) Heap 1：存放PL/SQL对象的源代码。
2) Heap 2：存放PL/SQL对象的解析树，这有个好听的名字： DIANA。
3) Heap 3：存放PL/SQL对象的伪代码。
4) Heap 4：存放PL/SQL对象的基于硬件的伪代码。
5) Heap 5：存放了编译时的错误信息。
6) Heap 6：存放了共享游标对象的SQL文本。
7) Heap 7：可用空间。
8) Heaps 8–11：根据对象的不同而使用的子heap。

 

 

用heap0中 依赖表部分 这个handle 20a7817c 找到实际依赖对象为tr.t1
 LIBRARY OBJECT HANDLE: handle=20a7817c mutex=20A78230(0)
  name=TR.T1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  hash=e45950492b8f05a2c7475e953d0f9e97 timestamp=10-08-2009 22:37:28
  namespace=TABL flags=KGHP/TIM/SML/[02000000]
  kkkk-dddd-llll=0000-0701-0701 lock=N pin=0 latch#=2 hpc=0002 hlc=0002
  lwt=20A781D8[20A781D8,20A781D8] ltm=20A781E0[20A781E0,20A781E0]
  pwt=20A781BC[20A781BC,20A781BC] ptm=20A781C4[20A781C4,20A781C4]
  ref=20A781F8[20A781F8,20A781F8] lnd=20A78204[1C4AF434,1C6B71AC]
    LIBRARY OBJECT: bject=1de81be0
    type=TABL flags=EXS/LOC[0005] pflags=[0000] status=VALD load=0
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr alloc(K)  size(K)
    ----- -------- -------- --------- ---- ------ --- -------- --------
        0 1c5f5928 1de81c78 I/-/A/-/-    0 NONE   00      0.52     0.00
        8 1de81e08 1dd61214 I/-/A/-/-    0 NONE   00      0.39     1.05
        9 1de81ea0 1dd60a24 I/-/A/-/-    0 NONE   00      0.17     1.05
       10 1de81ef0 1df09b30 I/-/A/-/-    0 NONE   00      0.11     1.05
    HEAP DUMP OF DATA BLOCK 0:
******************************************************
HEAP DUMP heap name="Heap0: KGL"  desc=1C5F5928
 extent sz=0x424 alt=32767 het=16 rec=9 flg=2 pc=0
 parent=03C38510 wner=1DE81BE0 nex=00000000 xsz=0x414
EXTENT 0 addr=1DE81BC8
  Chunk 1de81bd0 sz=     1036    perm      "perm           "  alo=880
Dump of memory from 0x1DE81BD0 to 0x1DE81FDC
1DE81BD0 5000040D 00000000 00000000 00000370  [...P........p...]
1DE81BE0 20A7817C 1DE81BE4 1DE81BE4 1DE81BEC  [|.. ............]
1DE81BF0 1DE81BEC 00000000 00000000 02010005  [................]

 

关于library cache的控制:
oracle 用lock,pin,library cache latch(修改时用) 控制shared pool library cache结构,如果oracle进程需要修改library cache中信息 ,那么再对应的bucket上,先要获得library cache latch,再接着在相应的handle(handle 可以理解为library cache object 的buffer header)上获得lock,再在heap上获得pin
修改完后释放 PIN,LOCK ,LIBRARY CACHE LATCH

比如user 1 进程 向library cache 填加一个子游标,此时user 2进程也填加 它们俩所填加的子游标完全一样 这样就浪费shared pool空间,还需要防止heap 内存被多个进程同时写入

当用户 比如实验中 user xh,执行select * from t1时 对这条SQL语句进行hash运算,找到了bucket  92407 中对应的library cache  handle(bucket 就是由 多个library cache handle 串联的 双项链表),但发现不能共享以前的执行计划因为T1来自不同的schema,这时候 需要产生新的执行计划,新的子游标 向bucket 92407 中对应的library cache object handle 的 CHILDREN中添加一个新handle指向heap0 新的执行计划,解析树,对象信息等,此时这个进程 必须获得 这个library cache  handle上的lock,其他的进程要等待(library cache lock 等待时间 就是这么产生的,方式并发 若另一个进程也添加 同样的children信息 那浪费空间了)

另外 如果根据SQL语句HASH 找到了bucket找到了 library cache handle 但里面的子游标handle(指向heap 0)不存在了 表明可能语句执行过,但换了出去,那么将该sql语句的handle(游标handle)重新加载到shared pool (v$librarycache.reloads)

 

SQL> select event,total_waits from v$system_event where event='library cache lock';

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
library cache lock                                                       4

This event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so that either:

One client can prevent other clients from accessing the same object

The client can maintain a dependency for a long time (for example, no other client can change the object)

This lock is also obtained to locate an object in the library cache.

Wait Time: 3 seconds (1 second for PMON)

Parameter Description
handle address Address of the object being loaded
lock address Address of the load lock being used. This is not the same thing as a latch or an enqueue, it is a State Object.
mode Indicates the data pieces of the object which need to be loaded
namespace See "namespace"

 

lock 分3类:null,shared,exclusive,读取取时一般是获取null,shared,此时其他进程也可以对相同的handle 加null,shared 的lock,修改时,此时要exclusive 的lock,此时其他进程不能再加exclusived的lock,只能加null 的lock

关于pin:pin是针对heap,主要是防止多个进程对一个heap(内存)进行更新操作,获取PIN 前必须获取lock,实验中当user xh执行SQL语句时,在bucket bucket  92407中先获得library cache lock 查看发现子游标不能共享(来自不同的SCHEMA) 此时将产生一个hard parse,生成新的执行计划,新子游标 要写到这个bucket 的library cache handle中,shared pool会分配chunk(连续内存)做为heap 0,heap 6等,获得该heap上的 PIN ,并在该bucket的 对应 library cache handle 中的children部分加一条子游标记录 其handle 指向heap 0 然后 向PIN主的HEAP上写记录,然后释放PIN,然后释放lock ,PIN不住就是等待事件library cache pin,如果heap不在 将heap加载到library cache 同时pin住

PIN有2种模式:shared ,exclusive,读时 shared pin (此时其它进程不能 exclusive pin),修改时 先shared pin heap进行错误安全检查,如果没错就是exclusive pin heap,然后修改

********************
总结:整体shared pool 操作过程 基本是这样的:
oracle先 将sql语句进行hash 运算,获取library cahe latch(保护内存中执行计划等,解析时候向library cache中加新执行计划时候需要获取该latch)根据sql hash_value在shared pool=>library cache=>bucket=>library cache handle 上先获得library cache lock(shared ,null 获取lock防止并发修改handle) 然后查找该handle子游标信息 其中子游标信息handle 指向heap 0,获得这个HEAP 0的 PIN(SHARED),如果可以共享那么就使用这个执行计划对象信息 解析树等 是一次soft parse,如果不能共享 比如来自不同schema,SQL语句引用列的 数据类型不一样等

如果不是这样的话oracle将释放library cache latch 获得shared pool latch(负责分配shared pool空间的latch)分配自由空间chunk 为heap (heap 0,heap 6等)分配后 释放shared pool latch 再获得library cache latch(解析过程插入新执行计划时需要它)并且获得 该library cache handle的 lock(exclusive因为要修改handle加入新的子游标信息)然后获得 分配的chunk 既这些chunk组成的heap 0,6等 获得这些heap上的 pin(exclusive),并在该bucket的 对应 library cache handle ,中的children部分加一条子游标记录 其handle 指向heap 0 (新分配的)然后 向PIN主的HEAP上写记录,然后释放PIN ,SQL及其执行计划写入library cahce 后,释放library cache latch 保持null模式的library cache lock,这是一个hard prase.

从实验中看
当user xh执行时
先用hash value 找到  BUCKET 92407:  持有library cache latch
  LIBRARY OBJECT HANDLE: handle=1c6b7124 mutex=1C6B71D8(2)
  name=select * from t1
获得一个null的lock 查看 chidren信息

    CHILDREN: size=16
    child#    table reference   handle
    ------ -------- --------- --------
         0 1de6704c  1de66d00 1c50ed24~~~~这个handle 就是不同子游标指向各自的heap的指针(不是user xh执行的)
         1 1de6704c  1de66e60 20906790(先忽略这个)

根据 1c50ed24找到
LIBRARY OBJECT HANDLE: handle=1c50ed24 mutex=1C50EDD8(0)
  namespace=CRSR flags=RON/KGHP/PN0/[10010000]
获取上面的lock (NULL,SHARED) PIN(SHARED)进行查看
从依赖表部分发现来自不同的SCHEMA ,需要产生新执行计划,此时候释放library cache latch,获得shared pool latch 分配chunk为heap,释放shared pool latch 后 再获得library cache latch 并且获得lock(exclusive) 因为(要往 BUCKET 92407:  LIBRARY OBJECT HANDLE: handle=1c6b7124 mutex=1C6B71D8(2) 这个handle上添加 子游标信息了)

  1 1de6704c  1de66e60 20906790 添加了这条,然后到新分配的heap 所在的bucket上 获得lock(exclusive)

BUCKET 92501
 LIBRARY OBJECT HANDLE: handle=20a7817c mutex=20A78230(0)   在这个 handle中添加信息 (lock(exclusive) )
  name=XH.T1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
并在heap上获得pin(exclusive)添加新执行计划SQL代码,解析树等

释放library cache latch,释放PIN ,LOCK

************************

关于library cache latch:保护library cache中的 SQL及执行计划,当oracle搜索library cache查找匹配的SQL及执行计划,若没有oracle将进行hard parse,获得library cache latch向library cache中加 新执行计划SQL代码等，搜索bucket时也要持有library cache latch.
另外 LIBRARY CACHE  LATCH 也管理LOCK 获得lock前必须持有library cache latch，获得不到latch那么将等待,lock结束 释放library cache latch

关于library cache latch的数量由  _kgl_latch_count 控制

NAME                           VALUE                     ISDEFAULT ISMOD      ISADJ

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

KSPPDESC
-----------------------------------------------------------------------------------
-------------------------------------------------
_kgl_latch_count               0                         TRUE      FALSE      FALSE

number of library cache latches

我的是 10GR2

0 simply means it's default. Actual number of latches is determined by cpu_count. Manually setting cpu_count can change the number of library cache child latches (check in v$latch_children).

该参数缺省值为大于等于系统中CPU个数的最小的素数

SQL> select name,gets,misses,sleeps from v$latch_children where name='library cache
';

NAME                                                     GETS     MISSES
-------------------------------------------------- ---------- ----------
    SLEEPS
----------
library cache                                          212571        102
         5

library cache                                          192186         35
         1

library cache                                          232298         53
         3

 

SQL> col name format a30
SQL> select name,gets,misses,sleeps from v$latch_children where name='library cache
';

NAME                                 GETS     MISSES     SLEEPS
------------------------------ ---------- ---------- ----------
library cache                      213125        102          5
library cache                      192499         35          1
library cache                      232640         53          3

library cache latch负责哪个bucket ,latch号=mod(bucket号,latch的数量)

 

SQL> select latch#,name,gets,misses,sleeps from v$latch_children where name='shared
 pool';

    LATCH# NAME                                 GETS     MISSES     SLEEPS
---------- ------------------------------ ---------- ---------- ----------
       213 shared pool                            48          0          0
       213 shared pool                            48          0          0
       213 shared pool                            48          0          0
       213 shared pool                            48          0          0
       213 shared pool                            48          0          0
       213 shared pool                            48          0          0
       213 shared pool                        308211        227         43

已选择7行。 只有一个shared pool latch,受sub pool影响每个sub pool都有一个shared pool latch ,_kghdsidx_count控制sub pool数 既控制shared pool latch数量

 

EYGEL大牛有一个比较经典针对library cache pin的等待 用grant 制造的等待,不过我现在的版本模拟不出来了,10R2 PIN 基本要被mutex 一种互斥机制取代了
但还是采用重新编译或drop 也可以做出来 library cache pin  并针对这个PIN进行分析跟踪解决

 

SQL> execute calling;~~hang

SQL> ALTER SESSION SET EVENTS 'immediate trace name library_cache level 16';

会话已更改。

 

BUCKET 51272:
  LIBRARY OBJECT HANDLE: handle=2c93d46c mutex=2C93D520(1)
  name=BEGIN calling; END;
  hash=55ee1c8381a7913b5e42b9d8e98cc848 timestamp=10-10-2009 15:45:23
  namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/SML/KST/DBN/MTX/[120100d4]
  kkkk-dddd-llll=0001-0001-0001 lock=N pin=0 latch#=1 hpc=0004 hlc=0004
  lwt=2C93D4C8[2C93D4C8,2C93D4C8] ltm=2C93D4D0[2C93D4D0,2C93D4D0]
  pwt=2C93D4AC[2C93D4AC,2C93D4AC] ptm=2C93D4B4[2C93D4B4,2C93D4B4]
  ref=2C93D4E8[2C93D4E8,2C93D4E8] lnd=2C93D4F4[2C94A9C8,2C93E2C0]
    LIBRARY OBJECT: bject=2ef6165c
    type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
    CHILDREN: size=16
    child#    table reference   handle
    ------ -------- --------- --------
         0 2ef615e8  2ef6129c 2c93d388
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr alloc(K)  size(K)
    ----- -------- -------- --------- ---- ------ --- -------- --------
        0 20679280 2ef616f4 I/P/A/-/-    0 NONE   00      0.67     1.05
  BUCKET 51272 total object count=1

 

 

BUCKET 47958:
  LIBRARY OBJECT HANDLE: handle=2c93dcfc mutex=2C93DDB0(0)
  name=SYS.CALLING
  hash=eeae667a13b1f1e561f384071b10bb56 timestamp=10-10-2009 15:45:05
  namespace=TABL flags=KGHP/TIM/SML/[02000000]
  kkkk-dddd-llll=0000-001d-20bf lock=N pin=S latch#=3 hpc=0006 hlc=0006
  lwt=2C93DD58[2C93DD58,2C93DD58] ltm=2C93DD60[2C93DD60,2C93DD60]
  pwt=2C93DD3C[2C93DD3C,2C93DD3C] ptm=2C93DD44[2C93DD44,2C93DD44]
  ref=2C93DD78[2C93DD78,2C93DD78] lnd=2C93DD84[2C95249C,2C93DFC0]
    LIBRARY OBJECT: bject=3126ab60
    type=PRCD flags=EXS/LOC[0005] pflags=NST[0001] status=VALD load=0
    DEPENDENCIES: count=4 size=16
    dependency#    table reference   handle position flags
    ----------- -------- --------- -------- -------- -------------------
              0 3126af1c  3126acb4 2c946f38        0 DEP[01]
              1 3126af1c  3126ace8 33e94310        0 DEP[01]
              2 3126af1c  3126ad1c 33e70f94        0 DEP[01]
              3 3126af1c  3126ad50 33e14d10        0 DEP[01]
    ACCESSES: count=2 size=16
    dependency# types
    ----------- -----
              0 000c
              2 000c
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr alloc(K)  size(K)
    ----- -------- -------- --------- ---- ------ --- -------- --------
        0 33e810ec 3126abf8 I/P/A/-/-    0 NONE   00      0.75     0.00
        2 3126a720 2b9a1b58 I/-/A/-/-    0 NONE   00      4.93     8.00
        4 3126a770 2b99db58 I/P/A/-/-    2 NONE   00      0.63     4.00
  BUCKET 47958 total object count=1

可以看到这时 访问这个HEAP0 那么lock为 NULL,PIN为Shared 不许添加exclusive pin

 

SQL> alter procedure calling compile;~~hang

SQL> ALTER SESSION SET EVENTS 'immediate trace name library_cache level 16';

会话已更改。

 

 

BUCKET 47958:
  LIBRARY OBJECT HANDLE: handle=2c93dcfc mutex=2C93DDB0(0)
  name=SYS.CALLING
  hash=eeae667a13b1f1e561f384071b10bb56 timestamp=10-10-2009 15:45:05
  namespace=TABL flags=KGHP/TIM/SML/[02000000]
  kkkk-dddd-llll=0000-001d-20bf lock=X pin=S latch#=3 hpc=0006 hlc=0006
  lwt=2C93DD58[2C93DD58,2C93DD58] ltm=2C93DD60[2C93DD60,2C93DD60]
  pwt=2C93DD3C[31B1C61C,31B1C61C] ptm=2C93DD44[2C93DD44,2C93DD44]
  ref=2C93DD78[2C93DD78,2C93DD78] lnd=2C93DD84[2C95249C,2C93DFC0]
    LIBRARY OBJECT: bject=3126ab60
    type=PRCD flags=EXS/LOC[0005] pflags=NST[0001] status=VALD load=0
    DEPENDENCIES: count=4 size=16
    dependency#    table reference   handle position flags
    ----------- -------- --------- -------- -------- -------------------
              0 3126af1c  3126acb4 2c946f38        0 DEP[01]
              1 3126af1c  3126ace8 33e94310        0 DEP[01]
              2 3126af1c  3126ad1c 33e70f94        0 DEP[01]
              3 3126af1c  3126ad50 33e14d10        0 DEP[01]
    ACCESSES: count=2 size=16
    dependency# types
    ----------- -----
              0 000c
              2 000c
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr alloc(K)  size(K)
    ----- -------- -------- --------- ---- ------ --- -------- --------
        0 33e810ec 3126abf8 I/P/A/-/-    0 NONE   00      0.75     0.00
        2 3126a720 2b9a1b58 I/-/A/-/-    0 NONE   00      4.93     8.00
        4 3126a770 2b99db58 I/P/A/-/-    2 NONE   00      0.63     4.00
  BUCKET 47958 total object count=1

此时执行修改,那么lock 为exclusive,以前是NULL 所以现在可以加上,但PIN 已经是shared 所以 EXCLUSIVE不可以加上,所以等待 产生library cache pin

 

 

 

具体抓到 堵塞SQL 及其 执行SEESION
SQL> select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state from v$session_wait whe
re event like 'library%'
  2  ;

       SID       SEQ#
---------- ----------
EVENT                                                                    P1
---------------------------------------------------------------- ----------
P1RAW            P2 P2RAW            P3 P3RAW    STATE
-------- ---------- -------- ---------- -------- -------------------
       139         53
library cache pin                                                 747887868
2C93DCFC  833734144 31B1C600        301 0000012D WAITING

查看等待 发现,有library cache pin ,关于里面p1,p2,p3值的意义 可以查看 p1text,p2text,p3text, 查看后p1text: handle address
表示这个HANDLE的地址 trace中BUCKET 47958 上有这个handle

 

SQL> col KGLNAOWN for a10
SQL> col KGLNAOBJ for a20
SQL> select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ
  2  from X$KGLOB
  3  where KGLHDADR ='2C93DCFC';

ADDR     KGLHDADR KGLHDPAR KGLNAOWN   KGLNAOBJ               KGLNAHSH KGLHDOBJ
-------- -------- -------- ---------- -------------------- ---------- --------
0805F118 2C93DCFC 2C93DCFC SYS        CALLING               454081366 3126AB60

查看 谁持有这个handle 可以看到owner sys, 对象是calling ,KGLNAHSH:object hash value

 

SQL> select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KG
LPNHDL,b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ from v$session a,x$kglpn b where a.saddr=
b.kglpnuse and b.kglpnhdl = '2C93DCFC' and b.KGLPNMOD<>0;

       SID USERNAME
---------- ------------------------------
PROGRAM                                                          ADDR
---------------------------------------------------------------- --------
KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK   KGLPNMOD   KGLPNREQ
-------- -------- -------- -------- -------- ---------- ----------

       133 SYS
sqlplus.exe                                                      0805FB98
31BE1B5C 343209D4 343209D4 2C93DCFC 31BD5544          2          0

查到 持有这个HANDLE的SESSION SID

SQL> select sid,seq#,event,p1text,state from v$session_wait where sid=133;

       SID       SEQ#
---------- ----------
EVENT
----------------------------------------------------------------
P1TEXT
----------------------------------------------------------------
STATE
-------------------
       133          9
PL/SQL lock timer
duration
WAITING

查看这个占有handle的用户在做什么

用sid 查到hash_value 查到他的 sql_text

SQL> select PREV_HASH_VALUE,sql_hash_value from v$session where sid=133;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
              0     3918317640

trace中  BUCKET 51272

SQL> select sql_text from v$sqlarea where hash_value='3918317640';

SQL_TEXT
--------------------------------------------------------------------------------
BEGIN calling; END;

 

 

 

另一个SESSION
SQL> alter procedure calling compile; hang

这会产生lock因为handle上已经有了 EXCLUSIVE的lock 所以加不上了 ,library cache lock出现

具体的抓SQL方法跟上面一样

SQL> select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state from v$session_wait whe
re event like 'library%';

       SID       SEQ#
---------- ----------
EVENT                                                                    P1
---------------------------------------------------------------- ----------
P1RAW            P2 P2RAW            P3 P3RAW    STATE
-------- ---------- -------- ---------- -------- -------------------
       139         53
library cache pin                                                 747887868
2C93DCFC  833734144 31B1C600        301 0000012D WAITING

       159          9
library cache lock                                                747887868
2C93DCFC  834493284 31BD5B64        301 0000012D WAITING

       SID       SEQ#
---------- ----------
EVENT                                                                    P1
---------------------------------------------------------------- ----------
P1RAW            P2 P2RAW            P3 P3RAW    STATE
-------- ---------- -------- ---------- -------- -------------------

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12020513/viewspace-616237/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12020513/viewspace-616237/