DEF _lhp_what=”&1″
DEF _lhp_sid=”&2″
DEF _lhp_name=”&3″
DEF _lhp_samples=”&4″
COL name FOR A35 TRUNCATE
COL latchprof_total_ms HEAD “Held ms” FOR 999999.999
COL latchprof_pct_total_samples head “Held %” format 999.99
COL latchprof_avg_ms HEAD “Avg hold ms” FOR 999.999
COL dist_samples HEAD Gets
COL total_samples HEAD Held
BREAK ON lhp_name SKIP 1
DEF _IF_ORA_10_OR_HIGHER=”–”
PROMPT
PROMPT — LatchProf 2.00 by Tanel Poder ( http://www.tanelpoder.com )
COL latchprof_oraversion NOPRINT NEW_VALUE _IF_ORA_10_OR_HIGHER
SET TERMOUT OFF
SELECT DECODE(SUBSTR(BANNER, INSTR(BANNER, ‘Release ‘)+8,1), 1, ”, ‘–‘) latchprof_oraversion
FROM v$version WHERE ROWNUM=1;
SET TERMOUT ON
WITH
t1 AS (SELECT hsecs FROM v$timer),
samples AS (
SELECT /*+ ORDERED USE_NL(l) USE_NL(s) USE_NL(l.gv$latchholder.x$ksuprlat) NO_TRANSFORM_DISTINCT_AGG */
&_lhp_what
&_IF_ORA_10_OR_HIGHER , COUNT(DISTINCT gets) dist_samples
, COUNT(*) total_samples
, COUNT(*) / &_lhp_samples total_samples_pct
FROM
(SELECT /*+ NO_MERGE */ 1 FROM DUAL CONNECT BY LEVEL <= &_lhp_samples) s,
v$latchholder l,
(SELECT
sid indx
, sql_hash_value sqlhash
, sql_address sqladdr
&_IF_ORA_10_OR_HIGHER , sql_child_number sqlchild
&_IF_ORA_10_OR_HIGHER , sql_id sqlid
FROM v$session) s
WHERE
l.sid LIKE ‘&_lhp_sid’
AND (LOWER(l.name) LIKE LOWER(‘%&_lhp_name%’) OR LOWER(RAWTOHEX(l.laddr)) LIKE LOWER(‘%&_lhp_name%’))
AND l.sid = s.indx
GROUP BY
&_lhp_what
ORDER BY
total_samples DESC
),
t2 AS (SELECT hsecs FROM v$timer)
SELECT /*+ ORDERED */
&_lhp_what
, s.total_samples
&_IF_ORA_10_OR_HIGHER , s.dist_samples
— , s.total_samples_pct
, s.total_samples / &_lhp_samples * 100 latchprof_pct_total_samples
, (t2.hsecs – t1.hsecs) * 10 * s.total_samples / &_lhp_samples latchprof_total_ms
— s.dist_events,
&_IF_ORA_10_OR_HIGHER , (t2.hsecs – t1.hsecs) * 10 * s.total_samples / dist_samples / &_lhp_samples latchprof_avg_ms
FROM
t1,
samples s,
t2
WHERE ROWNUM <= 30
/