Sorry for being so vague, but sometimes I can't
help it...
It was my understanding in the Oracle7 days that
the name of the statistic "free memory" was actually a verb and a
noun (i.e. as in "free Nelson Mandela" or "free Willy"), and the number
shown alongside this statistic was the cumulative number of bytes freed in the
Shared Pool. In other words, every time "N" bytes were freed from the
Shared Pool, then the statistic was incremented by "N". At least, this
explanation would have accounted for the absurdly huge numbers seen in the
V$SGASTAT view for this statistic in those versions and the unreliability in
attempting to add the numbers seen in V$SGASTAT to sum to
SHARED_POOL_SIZE...
Then, sometime in the Oracle8 or Oracle8i
timeframe, the meaning of the statistic was changed so that the term "free
memory" became what everyone had thought it was, an adjective and a noun (i.e.
as in "free beer" or "free time"). A much more useful statistic,
certainly...
Is this true? If not, is it
close?
The sum of the information in V$SGASTAT still does
not add to SHARED_POOL_SIZE, though (query from v8.1.7.4.0 shown
below):
SQL> select name, bytes from v$sgastat2 where pool = 'shared pool';NAME BYTES
-------------------------- ----------
free memory 18208352
miscellaneous 2378964
DML locks 120000
PLS non-lib hp 2096
trigger inform 944
PL/SQL MPCODE 1146204
PL/SQL DIANA 1223360
PX subheap 123476
db_block_hash_buckets 1411080
sessions 377300
KGK heap 48124
State objects 267420
message pool freequeue 124552
Checkpoint queue 885168
enqueue_resources 222912
db_files 370988
KGFF heap 649844
KQLS heap 1709904
dictionary cache 12670280
table definiti 3228
transactions 171264
ksfv subheap 4248
fixed allocation callback 1280
library cache 89490788
simulator trace entries 240000
sql area 187432036
table columns 19520
processes 123380
partitioning d 152976
db_block_buffers 10880000
event statistics per sess 607600
----------
sum 331067288SQL> show parameter shared_pool_sizeNAME TYPE VALUE
------------------- ------- ---------
shared_pool_size string 314572800
I'm curious about the 16,494,488 bytes
difference. Is it possible that V$SGASTAT is another "unlatched" data
structure in memory, allowing errors in the interest of eliminating
contention? There are other similar structures in the SGA (i.e. the data
structure underlying table MONITORING statistics later flushed to
SYS.TABMOD$)...
Thanks for any and all insight!
----- Original Message -----
From: "Jonathan Lewis" <[EMAIL PROTECTED]>
To: "Multiple recipients of list ORACLE-L"
<[EMAIL PROTECTED]>
Sent: Thursday, January 02, 2003 3:28
AM
Subject: Re: Free Shared pool
memory
> I think it's safe to say that if the free memory is always very large
> then you can reinterpret it as 'wasted memory'.
>
> If the free memory is alway very small, I don't think it is possible
> to make any decision without know the application. It is possible
> that you need to increase the shared pool slightly (good app), it is
> also possible that your shared pool is just about the right size
> (great
> app) , but it is possible that your application design has a flaw in
> it.
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Coming soon a new one-day tutorial:
> Cost Based Optimisation
> (see http://www.jlcomp.demon.co.uk/tutorial.html )
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____England______January 21/23
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
>
> -----Original Message-----
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Date: 02 January 2003 05:39
>
>
> >
> >Is it Correct to Look at FREE Memory in the Shared Pool ?
> >Memory when used once thereafter when NO Longer in use does the FREE
> Memory again Come up ?
> >Are there any ideal Values for percentage of Free memory for the
> Shared Pool
> >
> >The Respective Hybrid Application mostly uses Bind Variables
> >
> >Thanks
> >
> >
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.net
> >--
> >Author: VIVEK_SHARMA
> > INET: [EMAIL PROTECTED]
> >
> >Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> >San Diego, California -- Mailing list and web hosting services
> >---------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from). You may
> >also send the HELP command for other information (like subscribing).
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jonathan Lewis
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>