Memory

Advice

A MEMORY_SIZE_FACTOR column value of 1 is the current size via the MEMORY_TARGET parameter.

SELECT * FROM v$memory_target_advice ORDER BY memory_size;

Display

-- SGA

SQLPLUS> show parameter sga_
SELECT name,value FROM v$sga;
SELECT sum(value)/(1024*1024) "Total SGA (MB)" FROM v$sga;
SELECT * FROM v$sgainfo;
SELECT * FROM V$sgastat;

-- PGA

SQLPLUS> show parameter pga
SELECT * FROM V$PGASTAT;

Resize SGA

SQLPLUS> show parameter sga_
SQLPLUS> ALTER SYSTEM SET sga_max_size=1g SCOPE=SPFILE;
SQLPLUS> ALTER SYSTEM SET sga_target=750m SCOPE=SPFILE;
SQLPLUS> shutdown immediate
SQLPLUS> startup
SQLPLUS> show parameter sga_

RAC Example

 ALTER SYSTEM SET sga_max_size=1024m SCOPE=SPFILE SID='*';
 ALTER SYSTEM SET sga_target=1024m   SCOPE=SPFILE SID='*';

Resize PGA

SQLPLUS> show parameter pga
SQLPLUS> ALTER SYSTEM SET pga_aggregate_target=256m SCOPE=both;
SQLPLUS> show parameter pga

RAC Example

 ALTER SYSTEM SET pga_aggregate_target=256m SCOPE=SPFILE SID='*';

Global Memory, Enable (11g and later)

-- Backup original settings.
SQLPLUS> CREATE pfile='c:\temp\pfile.ora' FROM spfile;

SQLPLUS> ALTER SYSTEM SET memory_target=750m       SCOPE=SPFILE;
SQLPLUS> ALTER SYSTEM SET sga_max_size='0'         SCOPE=SPFILE;
SQLPLUS> ALTER SYSTEM SET sga_target='0'           SCOPE=SPFILE;
SQLPLUS> ALTER SYSTEM SET pga_aggregate_target='0' SCOPE=SPFILE;
SQLPLUS> shutdown immediate
SQLPLUS> startup
SQLPLUS> show parameter memory

Global Memory, Resize (11g and later)

SQLPLUS> show parameter memory
SQLPLUS> ALTER SYSTEM SET memory_max_target=1g SCOPE=SPFILE;
SQLPLUS> ALTER SYSTEM SET memory_target=750m   SCOPE=SPFILE;
SQLPLUS> shutdown immediate
SQLPLUS> startup
SQLPLUS> show parameter memory

<- System