Re: measure hidden free memory

From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
Date: Mon, 24 Jul 2006 08:16:31 -0700 (PDT)
Message-ID: <20060724151631.60790.qmail@web56009.mail.re3.yahoo.com>

"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

Received on Mon Jul 24 2006 - 10:16:31 CDT