该script 显示各个cursor占用的heap大小

——————————————————————————–
—
— 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
—
— @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’);

For example:

SQL> @cursor 2781999655 65535
old 20: KGLNAHSH in (&1)
new 20: KGLNAHSH in (2781999655)
old 21: and KGLOBT09 like (‘&2’)
new 21: and KGLOBT09 like (‘65535’)

KGLNAHSH KGLHDPAR CHILD# KGLHDADR KGLOBHD0 SIZE0 SIZE1 SIZE2
———- ——– ———- ——– ——– ——– ——– ——–
SIZE3 KGLOBHD4 SIZE4 SIZE5 KGLOBHD6 SIZE6 SIZE7 STATUS
——– ——– ——– ——– ——– ——– ——– ———-
2781999655 2C8E8408 65535 2C8E8408 2CAF987C 1781 0 0
0 00 0 0 00 0 0 1

old 10: KSMCHDS = hextoraw(‘&v_curheaps_kglobhd0’)
new 10: KSMCHDS = hextoraw(‘2CAF987C’)

HEAP CLASS ALLOC_COMMENT BYTES CHUNKS
—– ——– —————- ———- ———-
HEAP0 perm permanent memor 1608 2
HEAP0 free free memory 364 2
HEAP0 freeabl kgltbtab 76 1
HEAP0 freeabl kksfbc:hash1 40 2

old 10: KSMCHDS = hextoraw(‘&v_curheaps_kglobhd4′)
new 10: KSMCHDS = hextoraw(’00’)

no rows selected

old 10: KSMCHDS = hextoraw(‘&v_curheaps_kglobhd6′)
new 10: KSMCHDS = hextoraw(’00’)

no rows selected