Look Oracle from inside (tracing internal calls) --- Lock (Enqueue)

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2010-06-11 07:41:48

Share to 

WARNING: Never play OraTracer in any crytical system!  Lock (Enqueue)     The kernal function to get an enqueue is _ksqgtl, and the kernal function to release an enqueue is _ksqrcl. By tracing these 2 functions, we will monitor the lock request and release.     Let's set the trace point first. SQL代码 _ksqrcl (0,0)    _ksqgtl (0,0,0,0,0,0,0,0)       Use OraTracer to trace below session. SQL代码 HELLODBA.COM>select distinct spid from v$mystat m, v$session s, v$process p where m.sid=s.sid and s.paddr=p.addr;       SPID    ------------    9800       Then execute an UPDATE sql in the session. SQL代码 HELLODBA.COM>update demo.tt set x=111 where x=1;       2 rows updated.       We got the trace records from the log window. SQL代码 [2010-06-10 10:32:58.681]User call: _ksqgtl (TID: 9800)         [Args(8)]:             0x1dec4890            3            0            0x147ae14            0x31            0x310ef            0            0x401    [2010-06-10 10:32:58.681]User call: _ksqgtl (TID: 9800)         [Args(8)]:             0x1dfd4150            6            0            0            0x3b            0x60007            0x1C52F            0x401       The 1st argument is the lock address;     The 2nd argument is lock mode in which the session holds the lock;     The 3rd argument is lock mode in which the process requests the lock;     The 5th argument is lock type;     The 6th argument is ID1;     The 7th argument is ID2.     For the 1st one, we will understand that it's a TM lock by querying v$lock. SQL代码 HELLODBA.COM>select name, resname, id1, id2 from X$KSQEQTYP where indx=to_number('31', 'XXXXXXXX');       NAME        RESNAME ID1                   ID2    ----------- ------- --------------------- ----------------------    DML         TM      object #              table/partition       HELLODBA.COM>select * from V$LOCK where kaddr='1DEC4890';       ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK    -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------    1DEC4878 1DEC4890        309 TM     200943          0          3          0          6          0       Similarly, we got that the 2nd one is a TX lock. SQL代码 HELLODBA.COM>select name, resname, id1, id2 from X$KSQEQTYP where indx=to_number('3B', 'XXXXXXXX');       NAME        RESNAME ID1                   ID2    ----------- ------- --------------------- ----------------------    Transaction TX      usn<<16 | slot        sequence      HELLODBA.COM>select * from V$LOCK where kaddr='1DFD4150';       ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK    -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------    1DFD4034 1DFD4150        309 TX     393223     116015          6          0        207          0       Then, let's rollback the transaction. SQL代码 HELLODBA.COM>rollback;       Rollback complete.       We got the lock releasing trace records. SQL代码 [2010-06-10 10:33:01.090]User call: _ksqrcl (TID: 9800)         [Args(2)]:             0x1dfd4150            1    [2010-06-10 10:33:01.105]User call: _ksqrcl (TID: 9800)         [Args(2)]:             0x1dec4890            2       Obviously, the 1st argument is the address of the lock to be released.     You can download the OraTracer at here:     http://www.HelloDBA.com/Download/OraTracer.zip     --- Fuyuncat ---