执行方式如下：
@d:\latchprofx.sql sid,name,laddr % cache 10000
@d:\latchprof.sql sid,name,laddr % cache 10000
latchprofx.sql
-------------------------------------------------------------------------------- -- -- File name: latchprofx.sql ( Latch Holder Profiler eXtended ) -- Purpose: Perform high-frequency sampling on V$LATCHHOLDER -- and present a profile of latches held by sessions -- including extended statistics about in which kernel -- function the latch held was taken -- -- Author: Tanel Poder -- Copyright: (c) http://www.tanelpoder.com -- -- Usage: @latchprofx<#samples> -- @latchprofx name 350 % 100000 - monitor all latches SID 350 is holding -- @latchprofx sid,name % library 1000000 - monitor which SIDs hold latches with "library" in their name -- @latchprofx sid,name,laddr % 40D993A0 100000 - monitor which SIDs hold child latch with address 0x40D993A0 -- @latchprofx sid,name,func % % 100000 -- - monitor all sessions and latches and show -- - latch get Where info (locations in kernel code -- - where the latch get was done) -- -- @latchprofx sid,name,hmode,func 89 "cache buffers chains" 100000 -- - monitor functions that took latch for SID 89 only -- - and report only "cache buffers chains" latch holders -- - also report hold mode HMODE (shared or exclusive) -- -- @latchprofx sid,name,hmode,func,object % 40D993A0 100000 -- - monitor for what object's access the child latch at -- - particular address was taken. for cache buffers chains -- - latch the object means data block address (DBA) -- - of the block accessed in buffer cache. -- - you can translate the 6-byte DBA to rfile#/block# -- - using dbms_utility or dba.sql script from TPT scripts -- -- Other: -- This script is based on X$ tables instead of V$ tables -- as some info required is not externalized to V$. -- So you need to run this script either as SYS or -- need to have relevant X$ proxy views created -- -- The sampling relies on NESTED LOOP join method and having -- X$KSUPRLAT as the inner (probed) table. -- -- If sampling always reports a single latch event even though -- many different events (or parameter values) are expected then -- the execution plan used is not right. -- -- The join in exec plan step 9 MUST be a NESTED LOOPS join, this is how -- the high speed sampling is done. -- -- ----------------------------------------------------------------------------------------------------- -- | Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem | -- ----------------------------------------------------------------------------------------------------- -- | 1 | MERGE JOIN CARTESIAN | | 1 | | | | -- | 2 | MERGE JOIN CARTESIAN | | 1 | | | | -- |* 3 | FIXED TABLE FULL | X$KSUTM | 1 | | | | -- | 4 | BUFFER SORT | | 1 | 9216 | 9216 | 8192 (0)| -- | 5 | VIEW | | 1 | | | | -- | 6 | SORT ORDER BY | | 1 | 2048 | 2048 | 2048 (0)| -- | 7 | HASH GROUP BY | | 1 | | | | -- | 8 | NESTED LOOPS OUTER | | 1 | | | | -- | 9 | NESTED LOOPS | | 1 | | | | -- | 10 | VIEW | | 1 | | | | -- | 11 | CONNECT BY WITHOUT FILTERING| | | | | | -- | 12 | FAST DUAL | | 1 | | | | -- |* 13 | FIXED TABLE FULL | X$KSUPRLAT | 1 | | | | -- |* 14 | FIXED TABLE FIXED INDEX | X$KSLLW (ind:2) | 1 | | | | -- | 15 | BUFFER SORT | | 1 | 9216 | 9216 | 8192 (0)| -- |* 16 | FIXED TABLE FULL | X$KSUTM | 1 | | | | -- ----------------------------------------------------------------------------------------------------- -- -- If you want to drill down to latch child level, include "name" in first parameter to latchprof -- -- Then you can use l