有没有可以人为制造hang住某张表不给select

teapot

感觉，可以把kglob做个排它pin锁，就可以hang住某张表不给select，不知道怎么实现

Ling.QIu

没有明白为什么要把事情弄那么复杂

teapot

测试，当表被hang住时，应用程序的表现!

Maclean Liu(刘相兵

可以实现select 的hang ， 实际 是 对parse 的block

如：

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



SQL> select count(*) from maclean;

  COUNT(*)
----------
   6485248
   
alter table maclean add mac1 char(2000) default 'Maclean Liu';  




session 2:

First Parse :

select * from maclean where rownum<10;

hang here!


session 3 dump :


oradebug setmypid;
oradebug dump systemstate 266;
oradebug dump hanganalyze  3;
oradebug tracefile_name;




we will find wait chain:

Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/159/1750/0x84e5cd10/24561/No Wait>
-- <0/142/1490/0x84e5d4f8/24558/library cache lock>


session 142 is waiting for library cache lock


   SO: 0x84f5b4a8, type: 4, owner: 0x84e5d4f8, flag: INIT/-/-/0x00
    (session) sid: 142 trans: (nil), creator: 0x84e5d4f8, flag: (80000041) USR/- BSY/-/-/-/-/-
              DID: 0001-0010-00000102, short-term DID: 0000-0000-00000000
              txn branch: (nil)
              oct: 3, prv: 0, sql: 0x7d8eb1b0, psql: (nil), user: 0/SYS
    O/S info: user: oracle, term: pts/1, ospid: 24557, 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=83 wait_time=0 seconds since wait started=10
                handle address=7cce7b48, lock address=80036b60, 100*mode+namespace=c9
    Dumping Session Wait History
     for 'library cache lock' count=1 wait_time=983907
                handle address=7cce7b48, lock address=80036b60, 100*mode+namespace=c9
     for 'library cache lock' count=1 wait_time=2930517
                handle address=7cce7b48, lock address=80036b60, 100*mode+namespace=c9
     for 'library cache lock' count=1 wait_time=2931562
                handle address=7cce7b48, lock address=80036b60, 100*mode+namespace=c9
     for 'library cache lock' count=1 wait_time=2930099
                handle address=7cce7b48, lock address=80036b60, 100*mode+namespace=c9


handle address=7cce7b48       lock address=80036b60


   SO: 0x80036b60, type: 53, owner: 0x84f993c8, flag: INIT/-/-/0x00
        LIBRARY OBJECT LOCK: lock=80036b60 handle=7cce7b48 request=S
        call pin=(nil) session pin=(nil) hpc=0003 hlc=0000
        htl=0x80036be0[0x804560b0,0x804560b0] htb=0x804560b0 ssga=0x80455c98
        user=84f5b4a8 session=84f5b4a8 count=0 flags=RES/[0010] savepoint=0x2f17
        LIBRARY OBJECT HANDLE: handle=7cce7b48 mutex=0x7cce7c78(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=0004 hlc=0002
        lwt=0x7cce7bf0[0x80036b90,0x80036b90] ltm=0x7cce7c00[0x7cce7c00,0x7cce7c00]
        pwt=0x7cce7bb8[0x7cce7bb8,0x7cce7bb8] ptm=0x7cce7bc8[0x7cce7bc8,0x7cce7bc8]
        ref=0x7cce7c20[0x7cce7c20,0x7cce7c20] lnd=0x7cce7c38[0x7bef1768,0x7c8e14e0]
          LIBRARY OBJECT: object=80e25bd8
          type=TABL flags=EXS/LOC/UPD[0905] pflags=[0000] status=VALD load=0
          DATA BLOCKS:
          data#     heap  pointer    status pins change whr
          ----- -------- -------- --------- ---- ------ ---
              0 7d12d038 80e25cf0 I/P/A/-/-    0 NONE   00
              8 80e25f58 7d70b8c8 I/P/A/-/-    1 UPDATE 00
              9 7af0b9a0 7a61ba08 I/P/A/-/-    1 NONE   00
             10 7af0ba28 7ab60a88 I/P/A/-/-    1 NONE   00


the select parser ask to lock the library cache object in Share mode

but it has been already locked in X mode by session 159

    SO: 0x8103a0e8, type: 50, owner: 0x83ae49c8, flag: INIT/-/-/0x00
        row cache enqueue: count=3 session=0x84f70c60 object=0x7fed13e8, mode=X
        savepoint=0xdb2
        row cache parent object: address=0x7fed13e8 cid=8(dc_objects)
        hash=2b6e1cba typ=11 transaction=0x83ae49c8 flags=0000002a
        own=0x7fed14b8[0x8103a118,0x8103a118] wat=0x7fed14c8[0x7fed14c8,0x7fed14c8] mode=X
        status=VALID/UPDATE/-/-/-/-/-/-/-
        set=0, complete=FALSE
        data=
        00000000 414d0007 41454c43 0000004e 00000000 00000000 00000000 00000000
        00000000 00000001 00000000 00000000 00000000 00000000 00000000 00000000
        00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
        00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
        00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
        00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
        00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
        00000000 00000000 00000000 0000d4bd 00000002 0000d4bd 02707802 25211609
        1d027078 7814200f 0f1d0270 00011420 00000000 00000000 00000000 00000000
        00000000 00000006




414d0007 41454c43 0000004e =>  Maclean



        ----------------------------------------
        SO: 0x83a658d0, type: 36, owner: 0x83ae49c8, flag: INIT/-/-/0x00
        DML LOCK: tab=54461 flg=11 chi=0
                  his[0]: mod=6 spn=3506
        (enqueue) TM-0000D4BD-00000000  DID: 0001-000F-000003AD
        lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  flag: 0x6
        res: 84908490, mode: X, prv: 849084a0, own: 84f70c60, sess: 84f70c60, proc: 84e5cd10
        ----------------------------------------
        SO: 0x7f43e620, type: 54, owner: 0x83ae49c8, flag: INIT/-/-/0x00
        LIBRARY OBJECT PIN: pin=7f43e620 handle=7cce7b48 mode=X lock=0
        user=84f70c60 session=84f70c60 count=1 mask=0701 savepoint=0xda5 flags=[00]
        ----------------------------------------
        SO: 0x80055060, type: 53, owner: 0x83ae49c8, flag: INIT/-/-/0x00
        LIBRARY OBJECT LOCK: lock=80055060 handle=7cce7b48 mode=X
        call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
        htl=0x800550e0[0x81038438,0x81f23868] htb=0x81038438 ssga=0x81038020
        user=84f70c60 session=84f70c60 count=1 flags=[0000] savepoint=0xa41
        LIBRARY OBJECT HANDLE: handle=7cce7b48 mutex=0x7cce7c78(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=0004 hlc=0002
        lwt=0x7cce7bf0[0x80036b90,0x80036b90] ltm=0x7cce7c00[0x7cce7c00,0x7cce7c00]
        pwt=0x7cce7bb8[0x7cce7bb8,0x7cce7bb8] ptm=0x7cce7bc8[0x7cce7bc8,0x7cce7bc8]
        ref=0x7cce7c20[0x7cce7c20,0x7cce7c20] lnd=0x7cce7c38[0x7bef1768,0x7c8e14e0]
          LIBRARY OBJECT: object=80e25bd8
      type=TABL flags=EXS/LOC/UPD[0905] pflags=[0000] status=VALD load=0
          DATA BLOCKS:
          data#     heap  pointer    status pins change whr
          ----- -------- -------- --------- ---- ------ ---
              0 7d12d038 80e25cf0 I/P/A/-/-    0 NONE   00
              8 80e25f58 7d70b8c8 I/P/A/-/-    1 UPDATE 00
              9 7af0b9a0 7a61ba08 I/P/A/-/-    1 NONE   00
             10 7af0ba28 7ab60a88 I/P/A/-/-    1 NONE   00


We see that the  heap 8  has also been pined in X mode .  and the releated row cache queue has been hold in X mode .

teapot

SQL> exec print_table('select * from x$kglob where KGLNAOBJ=''T1'' and KGLNAOWN=''SCOTT''');
ADDR                          : 0000002A973C46D8
INDX                          : 2136
INST_ID                       : 1
KGLHDADR                      : 0000000069881400
KGLHDPAR                      : 0000000069881400
KGLHDCLT                      : 1
KGLNAOWN                      : SCOTT
KGLNAOBJ                      : T1
KGLFNOBJ                      : T1
KGLNADLK                      :
KGLNAHSH                      : 186111008
KGLNAHSV                      : 0497baf244d3df1ab14c512d0b17d420
KGLNATIM                      : 29-feb-2012 01:56:23
KGLNAPTM                      :
KGLHDNSP                      : 1
KGLHDLMD                      : 0
KGLHDPMD                      : 0
KGLHDFLG                      : 33554432
KGLHDOBJ                      : 00



LIBRARY OBJECT LOCK这个锁，可以手工制造出来吗，而不用SQL来执行DDL

Maclean Liu(刘相兵

"LIBRARY OBJECT LOCK这个锁，可以手工制造出来吗，而不用SQL来执行DDL"

理论上我们可以通过 kgl function 如 kglget 来手动制造lock ， 已知 kglget 必要的argument有 handle address和 mode ，但具体如何调用 仍存疑：

SQL> oradebug setmypid;
Statement processed.
SQL>
SQL> oradebug call kglget 2106300328  1 1
ORA-03113: end-of-file on communication channel
ORA-24323: value not allowed
SQL>

hctech

请教一下LIBRARY OBJECT LOCK这个锁的对应lock_type是啥~谢谢

Maclean Liu(刘相兵

你说的是 lock_type 具体指什么？

dba_kgllock.KGLLKTYPE?

SQL> desc dba_kgllock;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
KGLLKUSE                                           RAW(8)
KGLLKHDL                                           RAW(8)
KGLLKMOD                                           NUMBER
KGLLKREQ                                           NUMBER
KGLLKTYPE                                          VARCHAR2(4)


DBA_KGLLOCK lists all the locks and pins held on KGL objects (objects in the Kernel Generic Library cache).
Column         Datatype         NULL         Description
kgllkuse         RAW(4)                   Address of the user session that holds the lock or pin
kgllkhdl         RAW(4)                   Address of the handle for the KGL object
kgllkmod         NUMBER                   Current mode of the lock or pin
kgllkreq         NUMBER                   Mode in which the lock or pin was requested
kgllktype         VARCHAR2(4)                   Whether this is a lock or a pin

hctech

如果这个是一个lock的话~那应该对应于v$lock_type中有一个类型吧~？

Maclean Liu(刘相兵

V$LOCK

This view lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.

select *
  from v$fixed_view_definition
where view_name = 'GV$LOCK_TYPE'

  select rest.inst_id,
         rest.resname,
         rest.name,
         rest.id1,
         rest.id2,
         decode(bitand(eqt.flags, 1), 1, 'YES', 'NO'),
         rest.expl
          from X$KSIRESTYP rest, X$KSQEQTYP eqt
         where (rest.inst_id = eqt.inst_id)
           and (rest.indx = eqt.indx)
           and (rest.indx > 0)


=>  v$lock_type  来源于  X$KSIRESTYP        X$KSQEQTYP 这2个内部视图 存放的是 enqueue lock的type 类型
即 v$lock_type 中 仅存放 Oracle  中enqueue lock队列锁的type

What is an enqueue?
Enqueues are shared memory structures (locks) that serialize access to database resources.
They can be associated with a session or transaction. The following are some relevant reference documents:

Document 34566.1 "enqueue" Reference Note
Document 29787.1 Details of V$LOCK view and lock modes

A complete list of enqueues can be found at by querying V$LOCK_TYPE:

select substr(type,1,2),substr(name,1,30),substr(description,1,40)
from v$lock_type

or in the documentation under: v$lock types


而 library cache lock kgllk 、 library cache pin kglpn 是DDL lock ，他们虽然也有队列 enqueue机制 ， 但是不属于 enqueue lock  ， 所以这2种锁不出现在 v$lock 或 v$lock_type上。

hctech

谢谢刘总回答~
但是我第一次听说不属于enqueue lock的lock，求解这方面的资料~！

Maclean Liu(刘相兵

<Oracle8i Internal Services for Waits, Latches, Locks,and Memory>

或者

Tom kyte 的 9i 10g 编程艺术 都有介绍 这2种锁的区别

teapot

(gdb) bt
#0  0x000000000395bb54 in kglget ()
#1  0x000000000395a903 in kglgob ()
#2  0x00000000017d628d in kkdcloc ()
#3  0x00000000017d286e in kkdcacr ()
#4  0x00000000017d1e6f in kkdcacc ()
#5  0x0000000001816aa1 in kkmacr ()
#6  0x000000000230ad4a in opiprsdml ()
#7  0x0000000002307e6c in opiSem ()
#8  0x000000000230b249 in opiprs ()
#9  0x0000000001919afa in kksParseChildCursor ()
#10 0x0000000000743770 in rpiswu2 ()
#11 0x000000000191de3c in kksLoadChild ()
#12 0x000000000192f2cf in kkslod ()
#13 0x0000000003977228 in kglobld ()
#14 0x00000000039765a8 in kglobpn ()
#15 0x0000000003960010 in kglpim ()
#16 0x000000000395e40a in kglpin ()
#17 0x000000000183a392 in kxsGetRuntimeLock ()
#18 0x0000000001935b67 in kksfbc ()
#19 0x000000000193030e in kkspsc0 ()
#20 0x000000000192fa46 in kksParseCursor ()
#21 0x00000000022dda7e in opiosq0 ()
#22 0x00000000022dd3eb in opiosq ()
#23 0x000000000073eb98 in opiodr ()
#24 0x000000000074432e in rpidrus ()
#25 0x0000000003b4ae7a in skgmstack ()
#26 0x0000000000744678 in rpidru ()
#27 0x0000000000743770 in rpiswu2 ()
#28 0x0000000000743001 in rpidrv ()
#29 0x00000000007416ba in rpisplu ()
#30 0x000000000197ccfd in atbdfl ()
#31 0x0000000001972d24 in atbadd ()
#32 0x00000000019799cf in atbdrv ()
#33 0x0000000002311bfd in opiexe ()
#34 0x00000000022de0e9 in opiosq0 ()
#35 0x00000000022843ef in kpooprx ()


调试到这个状态，select被hang住

Maclean Liu(刘相兵

利用GDB 或DBX 是可以做到对 function 的断点调试breakpoint debug  , 如这个例子http://www.oracledatabase12g.com ... cache-recovery.html

但是 这样做也并不方便，如果能直接了解 kglget 函数涉及到哪些 argument 就可以直接callback 该函数达到此目的。

例如 使用kslget  hold 住某个latch

session A:
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug call kslgetl 766203160 1;
Function returned 1

/* kslgetl是Oracle内部用get latch的函数，oradebug 可以调用该函数*/

session B:

SQL> select * From v$latchholder;
       PID        SID LADDR    NAME                                                                   GETS
---------- ---------- -------- ---------------------------------------------------------------- ----------
        22        136 2DAB5518 redo copy                                                            297443