Look Oracle from inside (tracing internal calls) --- Latches

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2010-06-15 06:54:07

Share to 

WARNING: Never play OraTracer in any crytical system!  Latches     Latch is a mechanism to serialize access to shared data structures. Not like lock, it's more transparent for user. It's hard for us to expect what exactly latches that an operation will request. Now, tracing the kernel function that get a latch, _kslgetl, it's possible for us to monitor the latch requestes.     set the _kslgetl as trace point. SQL代码 _kslgetl (0*2,0*2,0*2,0*2,0*2,0*2)       Get a session to be traced in OraTracer. 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    ------------    6996       Then, run a SQL that never be executed since the instance startup. SQL代码 HELLODBA.COM>select /*6*/* from demo.tt;                X    ----------             8           101           101            11             1             1             2             3       8 rows selected.       We got a lots of trace records from the log window. SQL代码 [2010-06-10 15:51:04.749]User call: _kslgetl (TID: 6996)         [Args(6)]:             0x1c90fd48(=>0)            1            0            0x9f9(=>NULL)            0x1b08a580(=>0x370000(=>0))            0    [2010-06-10 15:51:04.764]User call: _kslgetl (TID: 6996)         [Args(6)]:             0x1c90fd48(=>0)            1            0            0x9f9(=>NULL)            0x1b08a580(=>0x370000(=>0))            0    [2010-06-10 15:51:04.764]User call: _kslgetl (TID: 6996)         [Args(6)]:             0x4e4cb50(=>0)            1            0            0x980(=>NULL)            0x911d1ec(=>0x911d26c(=>0x911d2a8))            0x4674cc(=>0x8b0cc483(=>NULL))    ...       By counting the _kslgetl, we found the SQL execution requested latch for 360 times (This number is not exactly same for each execution).       The 1st argument of the function is the address of the latch child, or the address of the latch without any child. Choose 5 of them to check the v$latch_children/v$latch, we will get to know what's kind of latches they are. SQL代码 HELLODBA.COM>select name from v$latch_children where addr='1C90FD48';       NAME   --------------------------------------------------    library cache       HELLODBA.COM>select name from v$latch_children where addr='1C90FFB8';       NAME   --------------------------------------------------    library cache lock       HELLODBA.COM>select name from v$latch_children where addr='1C90FE80';       NAME   --------------------------------------------------    library cache pin       HELLODBA.COM>select name from v$latch_children where addr='04E4CB50';       NAME   --------------------------------------------------    shared pool       HELLODBA.COM>select name from v$latch_children where addr='1E8C7D68';       NAME   --------------------------------------------------    enqueue hash chains       HELLODBA.COM>select name from v$latch where addr='03C3D858';       NAME   --------------------------------------------------    enqueues       Tips: The kernal function that release a latch is named _kslfre.     You can download the OraTracer at here:     http://www.HelloDBA.com/Download/OraTracer.zip     --- Fuyuncat ---