Memory Parameters of DB Instance

We can check our DB instance's memory parameters info, their usage, free space and also increase the value if required. Below are some queries which are frequently used by me to take snap of my db instance's memory

SGA:
----
sgainfo can be found by

SQL> select name, bytes/1024/1024 MB from v$sgainfo;

NAME MB
-------------------------------- ----------
Fixed SGA Size 2.09999084
Redo Buffers 29.5078125
Buffer Cache Size 27872
Shared Pool Size 4096
Large Pool Size 608
Java Pool Size 160
Streams Pool Size 0
Granule Size 16
Maximum SGA Size 32768
Startup overhead in Shared Pool 240
Startup NUMA Shared Pool memory 320
Free SGA Memory Available 0

At present db instance is using how much sga and how much sga is free for use can be
found by below query

select round(used.bytes / 1024 / 1024, 2) used_mb,
round(free.bytes / 1024 / 1024, 2) free_mb,
round(tot.bytes / 1024 / 1024, 2) total_mb
from (select sum(bytes) bytes from v$sgastat where name != 'free memory') used,
(select sum(bytes) bytes from v$sgastat where name = 'free memory') free,
(select sum(bytes) bytes from v$sgastat) tot;


USED_MB FREE_MB TOTAL_MB
---------- ---------- ----------
28124.83 4322.79 32447.61

If you are going to increase you memory you can check the advice of oracle

SQL> select * from v$sga_target_advice order by sga_size;

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
1 8192 0.25 11370 1.0082 16094877
2 16384 0.5 11324 1.0041 16067609
3 24576 0.75 11278 1 16040340
4 32768 1 11278 1 16040340
5 40960 1.25 11278 1 16040340
6 49152 1.5 11278 1 16040340
7 57344 1.75 11274 0.9996 16037132
8 65536 2 11274 0.9996 16037132

If you want to increase the SGA please proceed with below query

SQL> alter system set sga_target=28G scope=spfile;

System altered.

PGA:
----
How much pga is allocated and used by a particular session

select s.sid,s.serial#,S.SCHEMANAME,S.PROGRAM, p.pga_used_mem/1024/1024 PGA_USED_MB, p.PGA_ALLOC_MEM/1024/1024 PGA_ALLOC_MB
from v$process p, v$session s
where P.ADDR=S.PADDR and schemaname <> 'SYS';

SID SERIAL# SCHEMANAME PROGRAM PGA_USED_MB PGA_ALLOC_MB
---- ------- ---------- --------- ----------- ------------
137 457 TEST TOAD.exe 4.10361481 4.91355896

How much PGA allocated for the DB Instance

SQL> select name, value/1024/1024 MB from v$pgastat where name='maximum PGA allocated';

NAME MB
--------------------------------------------------------- ----------
maximum PGA allocated 4885.5585

If you are going to increase you pga memory you can check the advice of oracle

SQL> select * from v$pga_target_advice order by PGA_TARGET_FOR_ESTIMATE;