| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Shared pool memory usage
Chuck
This might help you:
to_number(decode(sign(ksmchsiz-80),-1,0,trunc(1/log(ksmchsiz-15,2))-5),'99')
bucket_no,
substr( decode(decode(sign(ksmchsiz-80),-1,0,trunc(1/log(ksmchsiz-15,2))-5),
0,'up to 79 bytes',1,'80 to 143 bytes',2,'144 to 271 bytes',
3,'272 to 527 bytes',4,'528 bytes to 1Kb',5,'1Kb',
(to_char(power(2,max(trunc(1/log(ksmchsiz-15,2))-10))))||'Kb'),1,16)
chunk_size,
to_char(count(*),'999,999') chunks, to_char(sum(decode(ksmchcls,'freeabl',ksmchsiz,0))/1024,'99,999') frbl_kb, to_char(sum(decode(ksmchcls,'free',ksmchsiz,0))/1024,'99,999') free_kb, to_char(sum(ksmchsiz/1024),'999,999') avail_kb, to_char(min(ksmchsiz),'999,999') sml_byte, to_char(max(ksmchsiz),'999,999') lrg_bytefrom sys.x$ksmsp
to_char(nvl(sum(ksmchsiz/1024),0),'999,999') avail_kb, to_char(nvl(min(ksmchsiz),0),'999,999') sml_byte, to_char(nvl(max(ksmchsiz),0),'999,999') lrg_bytefrom sys.x$ksmsp
This is an adaptaion of Steve Adams' script. This version is formatted for use with SQL*Plus or Server Manager, but must be run as SYS. Here is the output from one of our development databases:
BUCKET_NO CHUNK_SIZE CHUNKS FRBL_KB FREE_KB AVAIL_KB SML_BYTE LRG_BYTE
---------- ---------------- -------- ------- ------- -------- -------- --------
0 up to 79 bytes 531 2 24 26 32 72
1 80 to 143 bytes 565 16 43 59 80 136
2 144 to 271 bytes 3,459 474 149 623 144 264
3 272 to 527 bytes 4,010 1,264 484 1,747 272 520
4 528 bytes to 1Kb 13,213 7,255 2,350 9,605 528 1,032
5 1Kb 4,788 4,408 2,240 6,648 1,040 2,056
6 2Kb 11,124 19,923 3,952 23,875 2,064 4,104
7 4Kb 215 603 347 949 4,112 7,864
9 16Kb 1 17 0 17 17,288 17,288
10 32Kb 1 58 0 58 58,952 58,952
99 reserved pool 39 0 2,368 2,560 40 ########
From this you can see the smallest and largest free chunks within each bucket category. It also shows any freeable memory. The reserved pool always shows up with a smallest-byte setting of 40 bytes. I don't know why.
If you want a more complete explanation of this query I would strongly recommend
investing in a copy of Steve's book "Oracle8i Internal Services for Waits,
Latches,
Locks and Memory". One of the best $20 I have ever spent.
Hope that helps
Graham
Chuck Hamilton wrote:
> That gives you the total free memory of the shared pool, but I want to know
> what the largest free chunk is. I frequently get ORA-4031 trying to allocate
> 4k of memory when there's over 20m of free memory in the shared pool.
> Problem is it's all in chunks < 4k. Any ideas?
> --
> Chuck Hamilton
> chuck_hamilton_at_yahoo.com
>
> "Do not be deceived, God is not mocked; for whatever a man sows, this he
> will also reap. (Gal 6:7 NASB)
>
> "MT" <mtechera_at_wpmc.com> wrote in message
> news:9mjmhk$8n1$1_at_news.online.de...
> >
> > Hi Chuck:
> >
> > Here is what I use for the free space:
> >
> > select
> > to_number(p.value) "Total Pool",
> > s.bytes "Free Bytes",
> > round(( s.bytes / p.value ) * 100,1) "Free"
> > from
> > v$parameter p,
> > v$sgastat s
> > where
> > p.name = 'shared_pool_size' and
> > s.name = 'free memory'
> >
> >
> > Regards,
> > Mario
> >
> >
> >
> > "Chuck Hamilton" <chuck_hamilton_at_yahoo.com> wrote in message
> > news:9mj7sh$2ganv$1_at_ID-85580.news.dfncis.de...
> > > Is there a query that will show me the total free space, and largest
> single
> > > chunk of free space in the shared pool?
> > >
> > >
> >
> >
Received on Fri Aug 31 2001 - 09:56:57 CDT
 |
 |