先library cache pin还是先library cache lock??

Mille

先library cache pin还是先library cache lock??
session1 给test 建主键
session2 select  test  出现library cache lock
session3 select  test  出现library cache pin


不是说先获得library cache lock再library cache pin吗

session1以exclusive模式获得 library cache lock
session2 以shared模式请求 library cache lock ，session1未释放，所以session2 wait
那session3 什么解释呢？？？？

Maclean Liu(刘相兵

Both Locks and Pins are provided instead of one locking mechanism so that there is more flexibility so as to allow as much access to objects while still maintaining consistency in the library cache.
To locate an object in cache also requires acquiring a lock.  A process locates and locks the object in a single operation. In order to read / modify an object a pin is required.  Note that the lock  has a higher level than the pin and so a lock must be acquired on an object before a pin.


Note that the lock  has a higher level than the pin and so a lock must be acquired on an object before a pin.


至于你的问题 需要具体分析， 我给出一种猜测


session1 给test 建主键           =》　session 1 is doing DDL on Test, then it hold both handle lock and heap pin  in X exclusive mode .

session2 select  test  出现library cache lock           =》 session 2 trying to parse SQL Query select on test, then it need to lock Table test handle in NULL MODE , Yes it's a breakable  parse lock  ,    and it would pin the current Cursor  in X mode

session3 select  test  出现library cache pin      => session 3 also want to parse A Same Select statement like session 2 , but session 2  has already pin the current cursor,   then session 3 has to wait for a " library cache pin" here!

Maclean Liu(刘相兵

举一个例子：

session 1: SQL> alter table maclean add a10 char(2000) default 'maclean';

session 2:  select 1 from maclean where rownum=1;      ==> hang here !

session 3:  select 1 from maclean where rownum=1;   ==> SAME SQL, hang here !


SQL> select event from v$session where event like 'library%';

EVENT
----------------------------------------------------------------
library cache lock
library cache pin



取消session 3 运行 另一条 SQL

session 3 :
SQL> select /* maclean_change_this */ * from maclean where rownum=1;          == hang here too!



SQL> select event from v$session where event like 'library%';

EVENT
----------------------------------------------------------------
library cache lock
library cache lock

Mille

测试了一次，果真如此，也就是说。同样的查一个表，不同语句，lock/pin的资源不一样？？

Maclean Liu(刘相兵

session2 select  test  出现library cache lock           =》 session 2 trying to parse SQL Query select on test, then it need to lock Table test handle in NULL MODE , Yes it's a breakable  parse lock  ,    and it would pin the current Cursor  in X mode

session3 select  test  出现library cache pin      => session 3 also want to parse A Same Select statement like session 2 , but session 2  has already pin the current cursor,   then session 3 has to wait for a " library cache pin" here!

仔细读一下 上面的描述

whiterain

肯定是先  library cache lock?? 后pin

Maclean Liu(刘相兵

Another Example :



SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

session  A:

SQL> alter table test add t10 char(2000) default 'maclean';

session B:

SQL> select * from test where rownum=1;


session C:

SQL> select * from test where rownum=1;


SQL> select event from v$session where wait_class='Concurrency';

EVENT
----------------------------------------------------------------
cursor: pin S wait on X
library cache lock


why session C is waiting for Cursor:pin S wait on X now?  Because of   in 11g oracle use mutex protect cursor pin now , replaced library cache lock .

Session 2 has already pined the current cursor "select * from test where rownum=1;" , while it wait to parse the cursor.
Session 3 also try to parse a Same Cursor  which has been already pined by session 2, so it has to wait  until the mutex released

linzy

楼主db版本？
select * from v$version;

Mille

good  good  good  good

select.简单

hi maclean

session2 select  test  出现library cache lock           =》 session 2 trying to parse SQL Query select on test, then it need to lock Table test handle in NULL MODE , Yes it's a breakable  parse lock  ,    and it would pin the current Cursor  in X mode

i think there the session 2 tring to parse SQL query select on test ,the handle should be share mode，you can see the example：
session A
alter table LIUHAO_TEST add  f varchar2(255) default 'aaaaaaa';
session B
select * from  LIUHAO_TEST;
session C
select * from  LIUHAO_TEST;
we can see the wait event
SQL> select event,p3 from v$session_wait where sid in (120,137)
  2  ;

EVENT                                                                    P3
---------------------------------------------------------------- ----------
library cache lock                                                      201
library cache lock                                                      201

we know the p3 =100*mode+namespace  ，so the mode is 2
0           no lock/pin held
1           null mode
2           share mode
3           exclusive mode

Maclean Liu(刘相兵

You are right,  session 2 should lock associated table handle  in Share mode  First, and then downgrade to NULL mode.

I have made mistake here!

library cache mode :

share (S) held to read a object
Exclusive(X) to modify/create objects
NULL(N) special for session persistency

Stored objects can be locked in any of three modes.
Transient objects (cursors) can only be held in NULL(N) mode
Null locks are special and only acquired on executable  objects(child cursors , procedures ,functions ,packages)

Lock mode compatibility to prevent deadlocks:
If an object is locked in null mode, then other null, shared and exclusive locks are allowed on it
If an object is locked shared, then only other null, shared locks are allowed on it
If an object is locked exclusive, then only other null locks are allowed  on it
If an attempt is made to acquire a lock in non-compatible mode, then an error is raised


A null lock is gotten to indicate a long term interest on an object like to detect if it becomes invalid.  This lock can be broken at any time and is used as a mechanism to notify a session that an executable object is no longer valid. If a null lock is broken, and thus the object is invalidated, it is an indication to the user who was holding the null lock that the object  may need to be recompiled.

wzhihua

实验了下，确实是这样，但是还是不明白为啥session 2出现library cache lock，session 3出现library cache pin。若是pin在lock之前就能解释通了。
但是“Note that the lock  has a higher level than the pin and so a lock must be acquired on an object before a pin”
刘大能否再解释下？？？

[ 本帖最后由 wzhihua 于 2012-3-7 23:03 编辑 ]

wzhihua

明白了一点。
是否可以这样理解：session2 lock/pin cursor后需要继续lock/pin table handle,由于模式不兼容，只能等待lock table handle。
而session3 就只能等待pin cursor了。

Maclean Liu(刘相兵

SQL> select * from V$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production



session A  SQL> alter table maclean add a10 char(2000) default 'maclean';

session B:  select 1 from maclean where rownum=1;      ==> hang here !

session C:  select 1 from maclean where rownum=1;   ==> SAME SQL, hang here !


SQL> select event from v$session where event like 'library%';

EVENT
----------------------------------------------------------------
library cache lock
library cache pin


session 4:

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump systemstate 266;
Statement processed.

SQL> oradebug tracefile_name;
/s01/admin/G10R21/udump/g10r21_ora_6208.trc

    ----------------------------------------
    SO: 0x84f5b4a8, type: 4, owner: 0x84e5d4f8, flag: INIT/-/-/0x00
    (session) sid: 142 trans: (nil), creator: 0x84e5d4f8, flag: (41) USR/- BSY/-/-/-/-/-
              DID: 0001-0010-00000027, short-term DID: 0000-0000-00000000
              txn branch: (nil)
              oct: 3, prv: 0, sql: 0x7bf10088, psql: 0x7bf582f0, user: 0/SYS
    O/S info: user: oracle, term: pts/1, ospid: 6159, machine: vrh8.oracle.com
              program: sqlplus@vrh8.oracle.com (TNS V1-V3)
    application name: sqlplus@vrh8.oracle.com (TNS V1-V3), hash value=0
    waiting for 'library cache lock' blocking sess=0x(nil) seq=23 wait_time=0 seconds since wait started=17
                handle address=7c3a5560, lock address=8003b350, 100*mode+namespace=c9
    Dumping Session Wait History
     for 'library cache lock' count=1 wait_time=2149666
                handle address=7c3a5560, lock address=8003b350, 100*mode+namespace=c9
     for 'library cache lock' count=1 wait_time=2930643
                handle address=7c3a5560, lock address=8003b350, 100*mode+namespace=c9
     for 'library cache lock' count=1 wait_time=2930300
                handle address=7c3a5560, lock address=8003b350, 100*mode+namespace=c9
     for 'library cache lock' count=1 wait_time=2930715
                handle address=7c3a5560, lock address=8003b350, 100*mode+namespace=c9
     for 'library cache lock' count=1 wait_time=2930545
                handle address=7c3a5560, lock address=8003b350, 100*mode+namespace=c9
     for 'library cache lock' count=1 wait_time=2929985


session 142 is  B  waiting for library cache lock

the lock handle address is 7c3a5560

        SO: 0x8003b350, type: 53, owner: 0x84f98ba0, flag: INIT/-/-/0x00
        LIBRARY OBJECT LOCK: lock=8003b350 handle=7c3a5560 request=S
        call pin=(nil) session pin=(nil) hpc=0005 hlc=0000
        htl=0x8003b3d0[0x7d034330,0x7d034330] htb=0x7d034330 ssga=0x7e9f2168
        user=84f5b4a8 session=84f5b4a8 count=0 flags=RES/[0010] savepoint=0x1f9
        LIBRARY OBJECT HANDLE: handle=7c3a5560 mutex=0x7c3a5690(0)
        name=SYS.MACLEAN
        hash=458787ae49fd6f284ccb04a892b38231 timestamp=02-09-2012 21:32:36
        namespace=TABL flags=KGHP/TIM/SML/[02000000]
        kkkk-dddd-llll=0000-0701-0701 lock=X pin=X latch#=3 hpc=0006 hlc=0004
        lwt=0x7c3a5608[0x8003b380,0x8003b380] ltm=0x7c3a5618[0x7c3a5618,0x7c3a5618]
        pwt=0x7c3a55d0[0x7c3a55d0,0x7c3a55d0] ptm=0x7c3a55e0[0x7c3a55e0,0x7c3a55e0]
        ref=0x7c3a5638[0x7c3a5638,0x7c3a5638] lnd=0x7c3a5650[0x7bf75a18,0x7bf90650]
          LIBRARY OBJECT: object=7c1dec60
          type=TABL flags=EXS/LOC/UPD[0905] pflags=[0000] status=VALD load=0
          DATA BLOCKS:
          data#     heap  pointer    status pins change whr
          ----- -------- -------- --------- ---- ------ ---
              0 7c3a54a0 7c1ded78 I/P/A/-/-    0 NONE   00
              8 7c1de7f0 7e33ed48 I/P/A/-/-    1 UPDATE 00
              9 7c1de8c0 7bf109e8 I/P/A/-/-    1 NONE   00
             10 7c1de948 7bf10600 I/P/A/-/-    1 NONE   00

关于session B的 library cache lock ， 其原因是 add column 的session A 以 X mode lock SYS.MACLEAN, X mode pin  SYS.MACLEAN 且不释放， 所以session B的 library cache lock不用多解释



session C:

    SO: 0x84f5dd18, type: 4, owner: 0x84e5dce0, flag: INIT/-/-/0x00
    (session) sid: 144 trans: (nil), creator: 0x84e5dce0, flag: (41) USR/- BSY/-/-/-/-/-
              DID: 0001-0011-0000000A, short-term DID: 0000-0000-00000000
              txn branch: (nil)
              oct: 3, prv: 0, sql: 0x7bf10088, psql: 0x7bf582f0, user: 0/SYS
    O/S info: user: oracle, term: pts/2, ospid: 6183, machine: vrh8.oracle.com
              program: sqlplus@vrh8.oracle.com (TNS V1-V3)
    application name: sqlplus@vrh8.oracle.com (TNS V1-V3), hash value=0
    waiting for 'library cache pin' blocking sess=0x(nil) seq=19 wait_time=0 seconds since wait started=17
                handle address=7bf46e40, pin address=7f03f890, 100*mode+namespace=c8
    Dumping Session Wait History
     for 'library cache pin' count=1 wait_time=2568684
                handle address=7bf46e40, pin address=7f03f890, 100*mode+namespace=c8
     for 'library cache pin' count=1 wait_time=2930677
                handle address=7bf46e40, pin address=7f03f890, 100*mode+namespace=c8
     for 'library cache pin' count=1 wait_time=2929805
                handle address=7bf46e40, pin address=7f03f890, 100*mode+namespace=c8
     for 'library cache pin' count=1 wait_time=2931420
                handle address=7bf46e40, pin address=7f03f890, 100*mode+namespace=c8
     for 'library cache pin' count=1 wait_time=2930258
                handle address=7bf46e40, pin address=7f03f890, 100*mode+namespace=c8

session 144 is session C , waiting for library cache pin

handle address 7bf46e40=>  指向 一个 child cursor namespace=CRSR， 而这个child cursor已经被 session B pin住了：



      SO: 0x7f03f890, type: 54, owner: 0x84f5dd18, flag: INIT/-/-/0x00
      LIBRARY OBJECT PIN: pin=7f03f890 handle=7bf46e40 request=S lock=0
      user=84f5dd18 session=84f5dd18 count=0 mask=0000 savepoint=0x3f flags=[00]
      ----------------------------------------
      SO: 0x7ec4cc80, type: 53, owner: 0x84f5dd18, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=7ec4cc80 handle=7bf46e40 mode=N
      call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
      htl=0x7ec4cd00[0x7e449348,0x80c35108] htb=0x80c35108 ssga=0x80c34ff0
      user=84f5dd18 session=84f5dd18 count=1 flags=[0000] savepoint=0x0
      LIBRARY OBJECT HANDLE: handle=7bf46e40 mutex=0x7bf46f70(0)
      namespace=CRSR flags=RON/KGHP/PN0/[10010000]
      kkkk-dddd-llll=0000-0001-0000 lock=N pin=X latch#=3 hpc=0004 hlc=0004
      lwt=0x7bf46ee8[0x7bf46ee8,0x7bf46ee8] ltm=0x7bf46ef8[0x7bf46ef8,0x7bf46ef8]
      pwt=0x7bf46eb0[0x7f03f8c0,0x7f03f8c0] ptm=0x7bf46ec0[0x7bf46ec0,0x7bf46ec0]
      ref=0x7bf46f18[0x7bf7bfe0,0x7bf7bfe0] lnd=0x7bf46f30[0x7bf46f30,0x7bf46f30]
        LIBRARY OBJECT: object=7bf29018
        type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
        READ ONLY DEPENDENCIES: count=1 size=16
        DATA BLOCKS:
        data#     heap  pointer    status pins change whr
        ----- -------- -------- --------- ---- ------ ---
            0 7bf20060 7bf28ba8 I/P/A/-/-    0 NONE   00
            6 7bf77a20 7bf776f8 I/P/A/-/-    1 NONE   00




我们可以找到 上面这个child cursor 的 parent cursor :


      SO: 0x7d03b620, type: 53, owner: 0x84f5b4a8, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=7d03b620 handle=7bf10088 mode=N
      call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
      htl=0x7d03b6a0[0x7d034030,0x7c03c9f8] htb=0x7d034030 ssga=0x7e9f2168
      user=84f5b4a8 session=84f5b4a8 count=1 flags=[0000] savepoint=0x1f7
      LIBRARY OBJECT HANDLE: handle=7bf10088 mutex=0x7bf101b8(0)
      name=select 1 from maclean where rownum=1
      hash=324793c639b13d0954bd5421eaed6701 timestamp=03-08-2012 02:29:24
      namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/SML/DBN/[12010044]
      kkkk-dddd-llll=0001-0001-0001 lock=N pin=0 latch#=3 hpc=0004 hlc=0004
      lwt=0x7bf10130[0x7bf10130,0x7bf10130] ltm=0x7bf10140[0x7bf10140,0x7bf10140]
      pwt=0x7bf100f8[0x7bf100f8,0x7bf100f8] ptm=0x7bf10108[0x7bf10108,0x7bf10108]
      ref=0x7bf10160[0x7bf10160,0x7bf10160] lnd=0x7bf10178[0x82f4f2f8,0x7bf4d608]
        LIBRARY OBJECT: object=7bf7c8a8
        type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
        CHILDREN: size=16
        child#    table reference   handle
        ------ -------- --------- --------
             0 7bf7c370  7bf7bfe0 7bf46e40                           => 只有一个child  handle 7bf46e40                           
        DATA BLOCKS:
        data#     heap  pointer    status pins change whr
        ----- -------- -------- --------- ---- ------ ---
            0 7bf2a428 7bf7c9c0 I/P/A/-/-    0 NONE   00

即在session B parse SQL "select 1 from maclean where rownum=1" 的时候， 会生成一个child cursor 并 X mode pin住这个child cursor ，  而session C 同时发起 一样的SQL语句 "select 1 from maclean where rownum=1"  时 需要 share 这个child cursor ， 即以 S mode pin 这个child cursor ， 但是session B 还没有完成 optimize 没有生成完整的child cursor ， 需要等待 session A 释放  library cache lock才能 完成， 所以 session C 要等 session B build child cursor ， 此时session C等" library cache pin" ；

如果 session C 执行的是不一样的SQL，那么 session C 不share 同一个child cursor ， session C 会wait for library cache lock.

Maclean Liu(刘相兵

since 10.2.0.3  "_kks_use_mutex_pin"=TRUE or 11g  开始 使用mutex 保护cursor pin ，所以 session C 若执行 与session B 一样的SQL，那么 wiat for cursor pin S on X


SQL> select * from V$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


session  A:

SQL> alter table test add t11 char(2000) default 'maclean';

session B:

SQL> select * from test where rownum=1;


session C:

SQL> select * from test where rownum=1;


SQL> select event from v$session where wait_class='Concurrency';

EVENT
----------------------------------------------------------------
cursor: pin S wait on X
library cache lock


SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump systemstate 266;
Statement processed.



session C:

    SO: 0x9e2256b8, type: 4, owner: 0x9e59a1c0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
     proc=0x9e59a1c0, name=session, file=ksu.h LINE:12624, pg=0
    (session) sid: 179 ser: 41307 trans: (nil), creator: 0x9e59a1c0
              flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
              flags2: (0x40009) -/-/INC
              DID: , short-term DID:
              txn branch: (nil)
              oct: 3, prv: 0, sql: 0x956e18b8, psql: 0x956e18b8, user: 0/SYS
    ksuxds FALSE at location: 0
    service name: SYS$USERS
    client details:
      O/S info: user: oracle, term: pts/3, ospid: 26823
      machine: vrh1.oracle.com program: sqlplus@vrh1.oracle.com (TNS V1-V3)
      application name: sqlplus@vrh1.oracle.com (TNS V1-V3), hash value=1481565533
    Current Wait Stack:
     0: waiting for 'cursor: pin S wait on X'
        idn=0xe76d0d8c, value=0xca00000000, where=0x500000000
        wait_id=17 seq_num=18 snap_id=1
        wait times: snap=12.671273 sec, exc=12.671273 sec, total=12.671273 sec
        wait times: max=infinite, heur=12.671273 sec
        wait counts: calls=1148 os=1148
        in_wait=1 iflags=0x15b2
    There is at least one session blocking this session.
      Dumping 1 direct blocker(s):
        inst: 1, sid: 202, ser: 15511
      Dumping final blocker:
        inst: 1, sid: 9, ser: 1
    Wait State:
      fixed_waits=0 flags=0x22 boundary=(nil)/-1


idn=0xe76d0d8c=> 这个是mutex的标示

      KGX Atomic Operation Log 0x94aa8ca8
       Mutex 0x8a328978(202, 0) idn e76d0d8c oper GET_SHRD
       Cursor Pin uid 179 efd 0 whr 5 slp 1148
       opr=2 pso=0x8b5a8c48 flg=0
       pcs=0x8a3288e0 nxt=(nil) flg=35 cld=1 hd=0x93d4bbb8 par=0x8a328048
       ct=1 hsh=0 unp=(nil) unn=0 hvl=8a328ef0 nhv=1 ses=0x9e1e0ea0
       hep=0x8a328978 flg=80 ld=1 ob=0x939a30b0 ptr=0x935e0348 fex=0x935df6f0

这个mutex的 oper是 GET_SHRD 即 pin S 它指向 0x93d4bbb8  是一个child cursor

以下是parent cursor:

      SO: 0x957fa9d8, type: 78, owner: 0x9e1e0ea0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
       proc=0x9e593da0, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8547, pg=0

      LibraryObjectLock:  Address=0x957fa9d8 Handle=0x956e18b8 Mode=N CanBeBrokenCount=1 Incarnation=1 ExecutionCount=0

        User=0x9e1e0ea0 Session=0x9e1e0ea0 ReferenceCount=1 Flags=CNB/[0001] SavepointNum=4f5864b8
      LibraryHandle:  Address=0x956e18b8 Hash=e76d0d8c LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
        ObjectName:  Name=select * from test where rownum=1
          FullHashValue=7e277fabf95d7c80e8924ed6e76d0d8c Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=3882683788 OwnerIdn=0
        Statistics:  InvalidationCount=1 ExecutionCount=2 LoadCount=3 ActiveLocks=2 TotalLockCount=4 TotalPinCount=1
        Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 BucketInUse=3 HandleInUse=3 HandleReferenceCount=0
        Concurrency:  DependencyMutex=0x956e1968(0, 2, 0, 0) Mutex=0x956e19e8(0, 45, 0, 0)
        Flags=RON/PIN/TIM/PN0/DBN/[10012841]
        WaitersLists:
          Lock=0x956e1948[0x956e1948,0x956e1948]
          Pin=0x956e1928[0x956e1928,0x956e1928]
          LoadLock=0x956e19a0[0x956e19a0,0x956e19a0]
        Timestamp:  Current=03-08-2012 02:45:45
        HandleReference:  Address=0x956e1a78 Handle=(nil) Flags=[00]
        LibraryObject:  Address=0x8a327fa8 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
          ChildTable:  size='16'
            Child:  id='0' Table=0x8a328e58 Reference=0x8a3288b8 Handle=0x956db988
            Child:  id='1' Table=0x8a328e58 Reference=0x8a328b80 Handle=0x93d4bbb8
        NamespaceDump:
          Parent Cursor:  sql_id=fj4kfuvmqu3cc parent=0x8a328048 maxchild=2 plk=y ppn=n


但是很可惜  0x93d4bbb8  这个 child cursor 被 session B pin住了：

     SO: 0x957fa8d8, type: 78, owner: 0x9e1e0ea0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
       proc=0x9e593da0, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8547, pg=0

      LibraryObjectLock:  Address=0x957fa8d8 Handle=0x93d4bbb8 Mode=N CanBeBrokenCount=1 Incarnation=1 ExecutionCount=0
        ClusterLock=0x8f1945f8 Context=0x7fd379518308 User=0x9e1e0ea0 Session=0x9e1e0ea0 ReferenceCount=1
        Flags=CBK/[0020] SavepointNum=0
      LibraryHandle:  Address=0x93d4bbb8 Hash=0 LockMode=N PinMode=X LoadLockMode=0 Status=VALD
        Name:  Namespace=SQL AREA(00) Type=CURSOR(00)
        Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=2 TotalLockCount=2 TotalPinCount=3
        Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
        Concurrency:  DependencyMutex=0x93d4bc68(0, 0, 0, 0) Mutex=0x956e19e8(0, 45, 0, 0)
        Flags=RON/PIN/PN0/EXP/CHD/[10012111]
        WaitersLists:
          Lock=0x93d4bc48[0x93d4bc48,0x93d4bc48]
          Pin=0x93d4bc28[0x93d4bc28,0x93d4bc28]
          LoadLock=0x93d4bca0[0x93d4bca0,0x93d4bca0]
        LibraryObject:  Address=0x939a30b0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
          DataBlocks:
            Block:  #='0' name=KGLH0^e76d0d8c pins=0 Change=NONE
              Heap=0x93d1a808 Pointer=0x939a3150 Extent=0x939a3030 Flags=I/-/P/A/-/-
              FreedLocation=0 Alloc=1.546875 Size=4.000000 LoadTime=4385736620
            Block:  #='6' name=SQLA^e76d0d8c pins=0 Change=NONE
              Heap=0x8a328a20 Pointer=0x935e0348 Extent=0x935df6f0 Flags=I/-/P/A/-/E
              FreedLocation=0 Alloc=0.000000 Size=0.000000 LoadTime=0
        NamespaceDump:
          Child Cursor:  Heap0=0x939a3150 Heap6=0x935e0348 Heap0 Load Time=03-08-2012 02:50:16 Heap6 Load


PinMode=X

保持这个 X mode pin的是另外一个 mutex ， 这个mutex的 oper是 LONG_EXCL

Time=03-08-2012 02:50:16       ----------------------------------------
      KGX Atomic Operation Log 0x8f1945f8
       Mutex 0x8a328978(202, 0) idn e76d0d8c oper LONG_EXCL
       Cursor Pin uid 202 efd 0 whr 1 slp 0
       opr=3 pso=0x957fa8d8 flg=0
       pcs=0x8a3288e0 nxt=(nil) flg=35 cld=1 hd=0x93d4bbb8 par=0x8a328048
       ct=1 hsh=0 unp=(nil) unn=0 hvl=8a328ef0 nhv=1 ses=0x9e1e0ea0
       hep=0x8a328978 flg=80 ld=1 ob=0x939a30b0 ptr=0x935e0348 fex=0x935df6f0

Mille

good job good job good job

Maclean Liu(刘相兵

KGX -- Kernel Generic Mutex

As of Oracle 10.2 KGX Mutexes replace latches (Kernel Generic MuteX – KGX). Mutexes resemble latches (a physical allocation of memory) only they are lighter weight and consume less memory space. They can also be embedded inside other structures, they have flexible spin/yield/wait strategies defined by the client process and they do not factor into the accounting for GETS,SPINS or YIELDS, only WAITS.

KGX = Kernel Generic muteX
Introduced in Oracle 10.2
Physically like a latch (a piece of memory)
• only more lightweight
• and smaller
Can be embedded inside other structures (KGL HD)
Can have flexible spin/yield/wait strategy defined by client
Do not account GETS,SPINGETS,YIELDS, only WAITS
KGX mutexes are not OS mutexes!!!

wzhihua

非常感谢刘大的精彩解析，学习了。
另外刘大能否提供些资料：一个查询要大致要经过哪些过程，这些过程需要占用哪些资源？
呵呵，要求越来越多了，自己都觉得不好意思了！！！

Maclean Liu(刘相兵

"一个查询要大致要经过哪些过程，这些过程需要占用哪些资源？"

推荐读一下 这个《Oracle版的The Life of a Query》
http://www.oracledatabase12g.com ... ife-of-a-query.html

overmars

Maclean:根据您14楼的分析结果
SQL> select * from V$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

session A  SQL> alter table maclean add a10 char(2000) default 'maclean';
session B:  select 1 from maclean where rownum=1;      ==>hang， 以shared模式等待持有library cache lock ，因为session A已经X模式持有这个handle的lock
session C:  select 1 from maclean where rownum=1;   ==>hang， 以shared模式等待持有library cache pin，因为sessoin B已经X模式持有这个pin了！

这里不明白的是，Session B，在没有获得library cache lock的情况，它却可以X模式持有library cache pin?
这个是不是和"the client must first lock the object handle  and then pin the object itself"矛盾呢？

Maclean Liu(刘相兵

"Session B，在没有获得library cache lock的情况，它却可以X模式持有library cache pin?"


as maclean said:

请注意 lock 是lock什么对象 ，pin 是pin的什么对象； 很明显 你没有仔细阅读这个帖子中的文字。

alicejapan

Maclean ，这段资料哪里能找到？

alicejapan

原帖由 maclean 于 2012-3-8 00:32 发表
KGX -- Kernel Generic Mutex

As of Oracle 10.2 KGX Mutexes replace latches (Kernel Generic MuteX – KGX). Mutexes resemble latches (a physical allocation of memory) only they are lighter weight and c ...

这段资料哪里能找到？

beckhann

这个实验真的很有帮助！

casper0511

没积分，先收藏了

casper0511

谢谢分享，学习！！！！！！

fufuh2o

不错,学习了