"I have recently problems with a DB where the
permanent memory went up to 185m
due to heavy fragmentation"

I guess "permanent mem" in x$ksmsp never grows unless
SGA is resized dynamically.

"Can we make the assumption that the part of free
memory hidden into the
permanent memory is equal to the value reported in
v$SGASTAT - value reported
into X$KSMSP ?"

The "free memory" reported in v$sgastat is the correct
value, which includes "free memory" from x$ksmsp and
the free memory hidden into the "permanent mem".
Oracle would release free memory from "permanent mem"
as the memory pressure increases.

Thanks,
Sai.
http://sai-oracle.blogspot.com


just to check the validity of a theory :

I have recently problems with a DB where the
permanent memory went up to 185m
due to heavy fragmentation :

sums taken from x$ksmsp excluding ksmchon like 'R-%'
:

CONTENTS                  CHUNKS RECREATABLE
FREEABLE    TOTAL
------------------------------ ---------- -----------
---------- ----------
sql area                   11975    614856
10337272  10952128
library cache                8240   1511400
 3245888   4757288
free memory                 11637        
        16522128
permanent memor              29040        
       183711904
.
.
.

An alter system flush shared poll has no effect on
the fragmentation of the
free memory we were forced to rebounce the DB in order
to reclaim these 29040
chunks of free memory for a total of 184 meg.

Just after the reboot we got the following figures :


CONTENTS                  CHUNKS RECREATABLE
FREEABLE    TOTAL
------------------------------ ---------- -----------
---------- ----------
sql area                    671    403296
 1293944   1697240
library cache                1975    424880
  852464   1277344
free memory                  219        
       251236016
permanent memor                 4        
        47272080


These figures are taken from x$ksms. at the same time
v$sgastat show for free
memory :


Pool      Name                        
BYTES
------------ ------------------------------
----------------
buffer_cache                        
385,875,968
fixed_sga                            
742,864
java pool   free memory                
83,886,080
large pool  free memory                
16,777,216
log_buffer                            
787,456
shared pool free memory                
277,666,888
        miscellaneous              
13,269,208
        dictionary cache              
4,274,432
        library cache                
3,945,616

So V$SGASTAT show a free memory of 277 m and X$KSMSP
show 251 m.

So here is my question :

Can we make the assumption that the part of free
memory hidden into the
permanent memory is equal to the value reported in
v$SGASTAT - value reported
into X$KSMSP ?


In SQL :

select fa-fb hidden_mem from
      (select
          /* SGASTAT */sum(ksmsslen)/1048576 fa
       from x\$ksmss
       where
           ksmssnam='free memory' and
           ksmsslen > 1 group by ksmssnam),
      (select
          /* X$KSMSP */ sum(ksmchsiz)/1048576 fb

       from sys.x\$ksmsp
       where
           ksmchcom = 'free memory'    and
           inst_id = userenv('Instance') and
           ksmchcls not like 'R%'
       group by ksmchcom)


B. Polarski
http://www.smenu.org

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
http://www.freelists.org/webpage/oracle-l