95 lines
2.2 KiB
MySQL
95 lines
2.2 KiB
MySQL
--------------------------------------------------------------------------------
|
|
--
|
|
-- File name: lhp.sql ( Latch Holder Profile )
|
|
-- Purpose: Perform high-frequency sampling on V$LATCHHOLDER
|
|
-- and present a profile of latches held by sessions
|
|
--
|
|
-- Author: Tanel Poder
|
|
-- Copyright: (c) http://www.tanelpoder.com
|
|
--
|
|
-- Usage: @lhp <sid> <latch name> <#samples>
|
|
-- @lhp 350 library 100000
|
|
-- @lhp % % 1000000
|
|
-- Other:
|
|
-- The sampling relies on NESTED LOOP join method and having
|
|
-- V$LATCHHOLDER as the inner (probed) table. Note that on 9i
|
|
-- you may need to run this script as SYS as it looks like otherwise
|
|
-- the global USE_NL hint is not propagated down to X$ base tables
|
|
--
|
|
-- 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.
|
|
--
|
|
-- If you want to drill down to latch child level, uncomment the
|
|
-- l.laddr fields from select and group by list.
|
|
-- Then you can use la.sql (V$LATCH_PARENT/V$LATCH_CHILDREN) to
|
|
-- map the latch address back to latch child#
|
|
--
|
|
--------------------------------------------------------------------------------
|
|
|
|
DEF _lhp_sid="&1"
|
|
DEF _lhp_name="&2"
|
|
DEF _lhp_samples="&3"
|
|
|
|
COL lhp_name HEAD NAME
|
|
|
|
BREAK ON lhp_name SKIP 1
|
|
|
|
|
|
SELECT /*+ ORDERED USE_NL(l.gv$latchholder.x$ksuprlat) */
|
|
l.name lhp_name,
|
|
l.laddr,
|
|
l.sid,
|
|
-- l.pid,
|
|
COUNT(*) "COUNT"
|
|
FROM
|
|
(SELECT /*+ NO_MERGE */ 1 FROM DUAL CONNECT BY LEVEL <= &_lhp_samples) s,
|
|
v$latchholder l
|
|
WHERE
|
|
l.sid LIKE '&_lhp_sid'
|
|
AND LOWER(l.name) LIKE LOWER('%&_lhp_name%')
|
|
GROUP BY
|
|
-- l.pid,
|
|
l.sid,
|
|
l.laddr,
|
|
l.name
|
|
ORDER BY
|
|
l.name,
|
|
"COUNT" DESC
|
|
/
|
|
|
|
-- UNDEF _lhp_sid
|
|
-- UNDEF _lhp_samples
|
|
|
|
|
|
|
|
-- ROW_WAIT_OBJ#
|
|
-- ROW_WAIT_FILE#
|
|
-- ROW_WAIT_BLOCK#
|
|
-- ROW_WAIT_ROW#
|
|
|
|
-- SQL_ADDRESS
|
|
-- SQL_HASH_VALUE
|
|
-- SQL_ID
|
|
-- SQL_CHILD_NUMBER
|
|
-- SQL_EXEC_START
|
|
-- SQL_EXEC_ID
|
|
-- PREV_SQL_ADDR
|
|
-- PREV_HASH_VALUE
|
|
-- PREV_SQL_ID
|
|
-- PREV_CHILD_NUMBER
|
|
-- PREV_EXEC_START
|
|
-- PREV_EXEC_ID
|
|
-- PLSQL_ENTRY_OBJECT_ID
|
|
-- PLSQL_ENTRY_SUBPROGRAM_ID
|
|
-- PLSQL_OBJECT_ID
|
|
-- PLSQL_SUBPROGRAM_ID
|
|
-- MODULE
|
|
-- MODULE_HASH
|
|
-- ACTION
|
|
-- ACTION_HASH
|
|
-- CLIENT_INFO
|
|
-- FIXED_TABLE_SEQUENCE
|
|
--
|
|
-- @lm
|