Total System Global Area 1064706136 bytes Fixed Size 41880 bytes Variable Size 204623040 bytes Database Buffers 858992640 bytes Redo Buffers 1048576 bytesI 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,044Here, 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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in November 2002