Shared pool memory usage

Shared pool memory usage

Post by Chuck Hamilto » Fri, 31 Aug 2001 02:13:52



Is there a query that will show me the total free space, and largest single
chunk of free space in the shared pool?
 
 
 

Shared pool memory usage

Post by Pete Finniga » Fri, 31 Aug 2001 02:27:54


Hi

have a look on steve adams site, www.ixora.com.au he has a number of
sizing scripts.

cheers

Pete Finnigan
www.pentest-limited.com



Quote:>Is there a query that will show me the total free space, and largest single
>chunk of free space in the shared pool?

--
Pete Finnigan
IT Security Consultant
PenTest Limited

Office  01565 830 990
Fax     01565 830 889
Mobile  07974 087 885


www.pentest-limited.com

 
 
 

Shared pool memory usage

Post by MT » Fri, 31 Aug 2001 06:26:42


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


Quote:> Is there a query that will show me the total free space, and largest single
> chunk of free space in the shared pool?

 
 
 

Shared pool memory usage

Post by Chuck Hamilto » Fri, 31 Aug 2001 23:00:10


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

"Do not be deceived, God is not mocked; for whatever a man sows, this he
will also reap. (Gal 6:7 NASB)


> 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



> > Is there a query that will show me the total free space, and largest
single
> > chunk of free space in the shared pool?

 
 
 

Shared pool memory usage

Post by Graham Thornto » Sat, 01 Sep 2001 23:56:57


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_byte
from sys.x$ksmsp
where ksmchcls like 'free%'
group by decode(sign(ksmchsiz-80),-1,0,trunc(1/log(ksmchsiz-15,2))-5)
union
select
99 bucket_no,
'reserved pool',
to_char(count(*),'999,999') chunks,
to_char(nvl(sum(decode(ksmchcls,'R-freeabl',ksmchsiz,0)),0)/1024,'99,999')
frbl_kb,
to_char(nvl(sum(decode(ksmchcls,'R-free',ksmchsiz,0)),0)/1024,'99,999') free_kb,

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_byte
from sys.x$ksmsp
where ksmchcls like 'R-free%'
order by 1
/

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


> 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

> "Do not be deceived, God is not mocked; for whatever a man sows, this he
> will also reap. (Gal 6:7 NASB)



> > 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



> > > Is there a query that will show me the total free space, and largest
> single
> > > chunk of free space in the shared pool?

 
 
 

1. Shared pool usage

I have a problem that might sound trivial to most of you, but I need an
urgent help. I don't know that much about how
shared pool is working and can't find any answers myself so far.

I have a PL/SQL procedure that connects to the server running somewhere
and is listening for the data from the server,
until the server closes the connection.

The way it's done is as following: there is a server written in C that
opens up a socket for sending data. I wrote
a C program that has the following functions: open socket connection,
read from the socket, write to socket and close connection.
This C program is compiled as shared library, so I could call it's
functions from the PL/SQL package as external procedures.

Everything worked perfect, until I started running out of memory in
shared pool. My shared_pool_size is set to about 50M on
a Solaris box with 256M of RAM (this is just a development machine).

I made an experiment and saw the following. I restarted DB and looked at
the v$sgastat view. I had nothing running, so the
amount of free memory in shared pool stayed the same. As soon as I
started my procedure (it's done as a job using dbms_job
package), the amount of free memory started going down. At this point
the server was not sending any data. Nothing
was going on except, that my procedure in it's loop was constantly
trying to call the read_data external procedure.

Can someone explain to me, why shared pool memory was decreasing and
what it was used for?

It's a really urgent problem for me, because if I can't figure out what
is "eating" my memory, I'll have to re-design the
whole thing and I have a really tight deadline (like yesterday :-))))

Thank you very much for any help you can provide me with

I appreciate you taking your time to answer

/****************************************************************************/
                                 Alla Gribov

The greatest programming project of all took six days. On the seventh
day the programmer rested. We've been trying to debug the thing ever
since. Moral: design before you implement.
/****************************************************************************/

2. pgsql-server/src/interfaces/jdbc Makefile org/ ...

3. Shared pool Memory question.

4. SQL Server DATETIME problems

5. Checking shared memory usage

6. Printing

7. A question on shared memory usage

8. User built SQL querys?

9. Shared Virtual Memory Usage Question

10. Informix 7.11: Optical / Blobs and shared memory usage

11. 6.0 Shared Memory Usage

12. Large Pool vs Shared Pool

13. ADO Causes 100% Memory Usage/Increases in VM Usage