Estimate Shared pool size

SAMPLE OUTPUT Go to Script
——————————————————————————–

Class Total Size Max Size Description
——– ———- ———- ———————————————————-
R-free 246440 19612 Free memory in the reserved pool
R-freea 552964 19612 Freeable chunk in the reserved pool
R-perm 379920 72264 Permanent chunk in the reserved pool
free 140476 3700 Free chunk of shared memory
freeabl 13772032 124152 Freeable, but not flushable, chunk of shared memory
perm 18750480 4063044 Permanently allocated, nonfreeable chunk of shared memory
recr 3906100 8776 Allocated, fluashable (recreatable) chunk of shared memory
Total 37748412 4063044 Total Size of memory

8 rows selected.

Elapsed: 00:00:00.18

BUCKET_NO CHUNK_SIZE CHUNKS FRBL_KB FREE_KB AVAIL_KB SML_BYTE LRG_BYTE
——— —————— ——- ——- ——- ——– ——— ———
0 16 to 76 bytes 799 12 24 35
2 80 to 136 bytes 1643 141 14 154
3 144 to 268 bytes 529 85 25 110
4 272 to 524 bytes 1067 380 9 389
5 528 to 1036 bytes 2977 1746 32 1779
6 1 to 2 Kb 1324 1924 27 1951
7 2 to 4 Kb 1256 3679 6 3685
8 4 to 7 Kb 1175 4826 0 4826
9 9 to 10 Kb 2 19 0 19
10 19 to 20 Kb 17 328 0 328
11 42 to 42 Kb 2 86 0 86
12 104 to 121 Kb 2 225 0 225
99 reserved pool 88 0 241 781 20 19612

13 rows selected.

SCRIPT
——————————————————————————–

set timing on

— select count(*) “Total entries”,
— sum(ksmchsiz) “Total Size”,
— min(ksmchsiz) “Min Size”,
— max(ksmchsiz) “Max Size”,
— avg(ksmchsiz) “Avg Size”,
— stddev(ksmchsiz) “Stand. Dev.”
— from sys.x$ksmsp;

REM — *** Shared pool summary display
select nvl(ksmchcls,’Total’) “Class”,
sum(ksmchsiz) “Total Size”,
max(ksmchsiz) “Max Size”,
decode(ksmchcls,
‘recr’, ‘Allocated, fluashable (recreatable) chunk of shared memory’,
‘freeabl’, ‘Freeable, but not flushable, chunk of shared memory’,
‘free’, ‘Free chunk of shared memory’,
‘perm’, ‘Permanently allocated, nonfreeable chunk of shared memory’,
‘R-free’, ‘Free memory in the reserved pool’,
‘R-freea’, ‘Freeable chunk in the reserved pool’,
‘R-recr’, ‘Recreatable chunk in the reserved pool’,
‘R-perm’, ‘Permanent chunk in the reserved pool’,
”,’Total Size of memory’) “Description”
from SYS.X$KSMSP
group by rollup(ksmchcls);

REM — *** Shared pool freelist display
set pages 50000
COLUMN lrg_byte FORMAT 99999999
COLUMN sml_byte FORMAT 99999999
COLUMN avail_kb FORMAT 999999
COLUMN free_kb FORMAT 999999
COLUMN FRBL_KB FORMAT 999999
COLUMN chunks FORMAT 999999
COLUMN chunk_size FORMAT a18
COLUMN bucket_no FORMAT 999
select /*+ RULE */
BUCKET bucket_no,
CASE
— when 1/log(min(ksmchsiz),2) < 10
when min(ksmchsiz) < 1024
then min(ksmchsiz)||' to '||max(ksmchsiz)||' bytes'
— when 1/log(min(ksmchsiz),2) < 20
when min(ksmchsiz) < (1024*1024)
then trunc(min(ksmchsiz)/1024)||' to '||trunc(max(ksmchsiz)/1024)||' Kb'
else trunc(min(ksmchsiz)/1024/1024)||' to '||trunc(max(ksmchsiz)/1024/1024)||' Mb'
end chunk_size,
count(*) chunks,
sum(decode(ksmchcls,'freeabl',ksmchsiz,0))/1024 frbl_kb,
sum(decode(ksmchcls,'free',ksmchsiz,0))/1024 free_kb,
sum(ksmchsiz/1024) avail_kb,
null sml_byte,
null lrg_byte
from (
select ksmchsiz,
ksmchcls,
decode(sign(ksmchsiz-80),-1,0,trunc(1/log(greatest(ksmchsiz-15,2),2))-4) "BUCKET"
from SYS.X$KSMSP
where ksmchcls like 'free%'
)
group by BUCKET
union
select
99 bucket_no,
'reserved pool',
count(*) chunks,
nvl(sum(decode(ksmchcls,'R-freeabl',ksmchsiz,0)),0)/1024 frbl_kb,
nvl(sum(decode(ksmchcls,'R-free',ksmchsiz,0)),0)/1024 free_kb,
nvl(sum(ksmchsiz/1024),0) avail_kb,
nvl(min(ksmchsiz),0) sml_byte,
nvl(max(ksmchsiz),0) lrg_byte
from SYS.X$KSMSP
where ksmchcls like 'R-free%'
order by 1;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: