Tuesday, July 29, 2008

How to determine free memory and used memory in Oracle

The summary information of the SGA is determined by,
SQL> select * from V$SGA;

NAME VALUE
------------------------------------------------------------ ----------
Fixed Size 2073376
Variable Size 1056967904
Database Buffers 1509949440
Redo Buffers 14700544

To know the current settings of the sga dynamic components ( can be set in memory by ALTER SYSTEM)
SQL> col COMPONENT format a30
SQL> select COMPONENT, CURRENT_SIZE from V$SGA_DYNAMIC_COMPONENTS;


COMPONENT CURRENT_SIZE
------------------------------ ------------
shared pool 989855744
large pool 16777216
java pool 16777216
streams pool 33554432
DEFAULT buffer cache 603979776
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 33554432
DEFAULT 4K buffer cache 637534208
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 67108864

COMPONENT CURRENT_SIZE
------------------------------ ------------
DEFAULT 32K buffer cache 167772160
ASM Buffer Cache 0

13 rows selected.

To know the details of free and used memory use,

col total_sga format a20
col used format a20
col free format a20
SQL> select round(sum(bytes)/1024/1024,2)||' MB' total_sga, round(round(sum(bytes)/1024/1024,2) - round(sum(decode(name,'free memory',bytes,0))/1024/1024,2))||' MB' used, round(sum(decode(name,'free memory',bytes,0))/1024/1024,2)||' MB' free from v$sgastat;

TOTAL_SGA USED FREE
-------------------- -------------------- --------------------
2608.11 MB 2382 MB 226.12 MB


Related Documents
Memory Usage of Solaris Operating System

No comments:

Post a Comment