Mailing List
Home
Forum Home
Oracle List - by freelists.org
Oracle on SUSE Linux - Runing Oracle on SUSE Linux
Oracle database error code ...
www.freelists.org
Subjects
• ORA 12540: TNS:internal limit restriction exceeded
• ORA 12838 please : Is possible to append two times to the same table befo
• ORA 12838 please : Is possible to append two times to the same table before
• ora 04031
• ora 12500 on windows
• ORA 32004: obsolete and/or deprecated parameter(s) specified
• ORA 01925: maximum of 30 enabled roles exceeded
• ORA 01925: maximum of 30 enabled roles exceeded
• ora 12500 on windows
• ORA 01650, one idea
• ORA 01650
• ORA 4030
• ORA 12838 please : Is possible to append two times to thesametable before do
• ORA 12838 please : Is possible to append two times to thesame table before d
• ORA 01536
• ORA 03113 end of file on communication channel
• ORA 32004: obsolete and/or deprecated parameter(s) specified
• ORA 00600:
• ORA 00020: maximum number of processes (%s) exceeded
• ORA 01925: maximum of 30 enabled roles exceeded
• ORA 3113 while creating a cluster database 9201 RAC on Linux with OCFS
• ora 12500 on windows
• ora 12500 on windows
• ora 12500 on windows
• ORA 01650, one idea
• ora 12500 on windows
• ora 12500 on windows
• ora 12500 on windows
• ORA 2000 Error Using DBMS STATS GATHER SCHEMA STATS
• ORA 01650, one idea
• ORA 01650, one idea
• ORA 01650, one idea
• ORA 01650
• ORA 01650
• ORA 01031
• ORA 4030
• ORA 4030
• ORA 06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
• ORA 01722 invalid number
 
measure hidden free memory

measure hidden free memory

2006-07-05       - By Bernard Polarski

 Back
Reply:     1     2  

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


<html><head><style type="text/css"><!-- DIV {margin:0px} --></style></head>
<body><div style="font-family:courier, monaco, monospace, sans-serif;font-size
:8pt"><div>just to check the validity of a theory :<br>
<br>
I have recently problems with a DB where the permanent memory went up to 185m
due to heavy fragmentation :<br>
<br>
sums taken from x$ksmsp excluding ksmchon like 'R-%' :<br>
<br>
CONTENTS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
CHUNKS RECREATABLE&nbsp;&nbsp; FREEABLE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
TOTAL<br>
-- ---- ---- ---- ---- ---- -- -- ---- -- -- ---- --- -- ---- -- -- ---- --<br>
sql
area&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;
11975&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 614856&nbsp;&nbsp;
10337272&nbsp;&nbsp; 10952128<br>
library
cache&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
8240&nbsp;&nbsp;&nbsp;&nbsp; 1511400&nbsp;&nbsp;&nbsp;
3245888&nbsp;&nbsp;&nbsp; 4757288<br>
free
memory&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
11637&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
16522128<br>
permanent
memor&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
29040&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
183711904<br>
.<br>
.<br>
.<br>
<br>
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.<br>
<br>
Just after the reboot we got the following figures :<br>
<br>
<br>
CONTENTS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
CHUNKS RECREATABLE&nbsp;&nbsp; FREEABLE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
TOTAL<br>
-- ---- ---- ---- ---- ---- -- -- ---- -- -- ---- --- -- ---- -- -- ---- --<br>
sql
area&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;
671&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 403296&nbsp;&nbsp;&nbsp;
1293944&nbsp;&nbsp;&nbsp; 1697240<br>
library
cache&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
1975&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 424880&nbsp;&nbsp;&nbsp;&nbsp;
852464&nbsp;&nbsp;&nbsp; 1277344<br>
free
memory&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
219&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
251236016<br>
permanent
memor&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
47272080<br>
<br>
<br>
These figures are taken from x$ksms. at the same time v$sgastat show for free
memory :<br>
<br>
<br>
Pool&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
BYTES<br>
-- ---- ---- -- ---- ---- ---- ---- ---- -- -- ---- ---- ---<br>
buffer_cache&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
385,875,968<br>
fixed_sga&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
742,864<br>
java pool&nbsp;&nbsp;&nbsp; free
memory&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
83,886,080<br>
large pool&nbsp;&nbsp; free
memory&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
16,777,216<br>
log_buffer&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
787,456<br>
shared pool&nbsp; free
memory&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
277,666,888<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
miscellaneous&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
13,269,208<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
dictionary
cache&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
4,274,432<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
library
cache&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
3,945,616<br>
<br>
So V$SGASTAT show a free memory of 277 m and X$KSMSP show 251 m.<br>
<br>
So here is my question : <br>
<br>
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 ?<br>
<br>
<br>
In SQL :<br>
<br>
select fa-fb hidden_mem from<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (select <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp; /* SGASTAT */sum(ksmsslen)/1048576 fa <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from x\$ksmss <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where&nbsp; <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp; ksmssnam='free memory' and <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;
ksmsslen &gt; 1 group by ksmssnam),<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (select <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp; /* X$KSMSP */ sum(ksmchsiz)/1048576 fb <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from sys.x\$ksmsp
<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;
ksmchcom = 'free memory'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;
inst_id = userenv('Instance') and <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp; ksmchcls not like 'R%' <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; group by ksmchcom)
<br>
<br>
<br>
B. Polarski<br>
<span><a target="_blank" href="http://www.smenu.org">http://www.smenu.org</a><
/span><br></div></div></body></html>