measure hidden free memory

From: Bernard Polarski <bpolarsk_at_yahoo.com>
Date: Wed, 5 Jul 2006 06:02:49 -0700 (PDT)
Message-ID: <20060705130249.23586.qmail@web36508.mail.mud.yahoo.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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 05 2006 - 08:02:49 CDT