126 lines
2.9 KiB
SQL
126 lines
2.9 KiB
SQL
--------------------------------------------------------------------------------
|
|
--
|
|
-- File name: curheaps.sql
|
|
-- Purpose: Show main cursor data block heap sizes and their contents
|
|
-- (heap0 and heap6)
|
|
--
|
|
-- Author: Tanel Poder
|
|
-- Copyright: (c) http://www.tanelpoder.com
|
|
--
|
|
-- Usage: @curheaps <hash_value> <child#>
|
|
--
|
|
-- @curheaps 942515969 % -- shows a summary of cursor heaps
|
|
-- @curheaps 942515969 0 -- shows detail for child cursor 0
|
|
--
|
|
-- Other: "Child" cursor# 65535 is actually the parent cursor
|
|
--
|
|
--------------------------------------------------------------------------------
|
|
|
|
col curheaps_size0 heading SIZE0 for 9999999
|
|
col curheaps_size1 heading SIZE1 for 9999999
|
|
col curheaps_size2 heading SIZE2 for 9999999
|
|
col curheaps_size3 heading SIZE3 for 9999999
|
|
col curheaps_size4 heading SIZE4 for 9999999
|
|
col curheaps_size5 heading SIZE5 for 9999999
|
|
col curheaps_size6 heading SIZE6 for 9999999
|
|
col curheaps_size7 heading SIZE7 for 9999999
|
|
|
|
col KGLOBHD0 new_value v_curheaps_kglobhd0 print
|
|
col KGLOBHD1 new_value v_curheaps_kglobhd1 noprint
|
|
col KGLOBHD2 new_value v_curheaps_kglobhd2 noprint
|
|
col KGLOBHD3 new_value v_curheaps_kglobhd3 noprint
|
|
col KGLOBHD4 new_value v_curheaps_kglobhd4 print
|
|
col KGLOBHD5 new_value v_curheaps_kglobhd5 noprint
|
|
col KGLOBHD6 new_value v_curheaps_kglobhd6 print
|
|
col KGLOBHD7 new_value v_curheaps_kglobhd7 noprint
|
|
|
|
|
|
select
|
|
KGLNAHSH,
|
|
KGLHDPAR,
|
|
kglobt09 CHILD#,
|
|
KGLHDADR,
|
|
KGLOBHD0, KGLOBHS0 curheaps_size0,
|
|
KGLOBHD1, KGLOBHS1 curheaps_size1,
|
|
KGLOBHD2, KGLOBHS2 curheaps_size2,
|
|
KGLOBHD3, KGLOBHS3 curheaps_size3,
|
|
KGLOBHD4, KGLOBHS4 curheaps_size4,
|
|
KGLOBHD5, KGLOBHS5 curheaps_size5,
|
|
KGLOBHD6, KGLOBHS6 curheaps_size6,
|
|
KGLOBHD7, KGLOBHS7 curheaps_size7,
|
|
-- KGLOBT00 CTXSTAT,
|
|
KGLOBSTA STATUS
|
|
from
|
|
X$KGLOB
|
|
-- X$KGLCURSOR_CHILD
|
|
where
|
|
KGLNAHSH in (&1)
|
|
and KGLOBT09 like ('&2')
|
|
order by
|
|
KGLOBT09 ASC
|
|
/
|
|
|
|
-- Cursor data block summary
|
|
select
|
|
'HEAP0' heap
|
|
, ksmchcls class
|
|
, ksmchcom alloc_comment
|
|
, sum(ksmchsiz) bytes
|
|
, count(*) chunks
|
|
from
|
|
x$ksmhp
|
|
where
|
|
KSMCHDS = hextoraw('&v_curheaps_kglobhd0')
|
|
group by
|
|
'HEAP0'
|
|
, ksmchcls
|
|
, ksmchcom
|
|
order by
|
|
sum(ksmchsiz) desc
|
|
/
|
|
|
|
select
|
|
'HEAP4' heap
|
|
, ksmchcls class
|
|
, ksmchcom alloc_comment
|
|
, sum(ksmchsiz) bytes
|
|
, count(*) chunks
|
|
from
|
|
x$ksmhp
|
|
where
|
|
KSMCHDS = hextoraw('&v_curheaps_kglobhd4')
|
|
group by
|
|
'HEAP6'
|
|
, ksmchcls
|
|
, ksmchcom
|
|
order by
|
|
sum(ksmchsiz) desc
|
|
/
|
|
|
|
|
|
|
|
select
|
|
'HEAP6' heap
|
|
, ksmchcls class
|
|
, ksmchcom alloc_comment
|
|
, sum(ksmchsiz) bytes
|
|
, count(*) chunks
|
|
from
|
|
x$ksmhp
|
|
where
|
|
KSMCHDS = hextoraw('&v_curheaps_kglobhd6')
|
|
group by
|
|
'HEAP0'
|
|
, ksmchcls
|
|
, ksmchcom
|
|
order by
|
|
sum(ksmchsiz) desc
|
|
/
|
|
|
|
|
|
-- Cursor data block details
|
|
|
|
-- select * from x$ksmhp where KSMCHDS = hextoraw('&v_curheaps_kglobhd0');
|
|
-- select * from x$ksmhp where KSMCHDS = hextoraw('&v_curheaps_kglobhd6');
|
|
|