Q

Total size of SGA greater than what was allocated

Please note that we are running Oracle 7.3.4.4 under SCO UNIX 5.0.5. The allocated SGA is as following:

 Total System Global Area    1064706136 bytes
 Fixed Size                       41880 bytes
 Variable Size                204623040 bytes
 Database Buffers             858992640 bytes
 Redo Buffers                   1048576 bytes
I observed that the behavior of the SGA is not acceptable because sometimes, when I execute the following query:
 SELECT SUM(BYTES)/1024/1024 FROM V$SGASTAT
 
 I get the total size of the SGA greater than what we
 allocated, and there is
 no free memory.
 
 Any ideas regarding this issue? 

V$SGASTAT does not include everything. For instance, it does not include the "Fixed Size" of the SGA. So you will see a difference between the SHOW SGA command and the sum of bytes from V$SGASTAT.

You should, however, see some free memory from V$SGASTAT. For instance, use the following query:

SQL> select * from v$sgastat where name='free memory';

POOL        NAME                                 
BYTES
----------- --------------------------
----------------
shared pool free memory                      
7,062,044
Here, I have 7MB free in my Shared Pool. You don't want a lot of free memory however since this will be wasting space.

For More Information


This was first published in November 2002

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close