61 lines
1.5 KiB
SQL
61 lines
1.5 KiB
SQL
-- taken from metalink note 396940.1
|
|
-- added order by clause
|
|
|
|
prompt
|
|
prompt WARNING!!! This script will query X$KSMSP, which will cause heavy shared pool latch contention
|
|
prompt in systems under load and with large shared pool. This may even completely hang
|
|
prompt your instance until the query has finished! You probably do not want to run this in production!
|
|
prompt
|
|
pause Press ENTER to continue, CTRL+C to cancel...
|
|
|
|
|
|
col sga_heap format a15
|
|
col size format a10
|
|
col chunkcomment for a25
|
|
|
|
break on subpool on sga_heap skip 1 on status skip 1 on chunkcomment
|
|
|
|
select
|
|
KSMCHIDX subpool,
|
|
'sga heap('||KSMCHIDX||',0)'sga_heap,
|
|
ksmchcls Status,
|
|
substr(ksmchcom,1,decode(instr(ksmchcom,'^'),0,99,instr(ksmchcom,'^'))) ChunkComment,
|
|
decode(round(ksmchsiz/1024),
|
|
0,'0-1K',
|
|
1,'1-2K',
|
|
2,'2-3K',
|
|
3,'3-4K',
|
|
4,'4-5K',
|
|
5,'5-6k',
|
|
6,'6-7k',
|
|
7,'7-8k',
|
|
8,'8-9k',
|
|
9,'9-10k',
|
|
'> 10K') "SIZE",
|
|
count(*),
|
|
sum(ksmchsiz) "SUM(BYTES)",
|
|
min(ksmchsiz) MinBytes,
|
|
max(ksmchsiz) MaxBytes,
|
|
trunc(avg(ksmchsiz)) AvgBytes
|
|
from
|
|
x$ksmsp
|
|
where
|
|
1=1
|
|
and lower(KSMCHCOM) like lower('%&1%')
|
|
group by
|
|
ksmchidx,
|
|
ksmchcls,
|
|
'sga heap('||KSMCHIDX||',0)',
|
|
substr(ksmchcom,1,decode(instr(ksmchcom,'^'),0,99,instr(ksmchcom,'^'))),
|
|
decode(round(ksmchsiz/1024),0,'0-1K',1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',
|
|
6, '6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K')
|
|
order by
|
|
ksmchidx,
|
|
ksmchcls,
|
|
lower(substr(ksmchcom,1,decode(instr(ksmchcom,'^'),0,99,instr(ksmchcom,'^')))),
|
|
"SIZE"
|
|
/
|
|
|
|
|
|
|