--------------------------------------------------------------------------------
--
-- 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