How to Find out / Check SGA and PGA size of Oracle Database?

advertisements

_____________________________________________________________________________________________________________________

SGA Size 

Generally SGA_TARGET shows the value of SGA. It can be increased up to sga_max_size. But if you have enabled automatic memory management the (non-zero values for Memory_ parameters) the value for the memory_target will be consisderred for the SGA. In below mentioned example automatic memory management is enabled and its value is 10G.

SQL> show parameter target
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 10G
memory_target                        big integer 10G
parallel_servers_target              integer     384
pga_aggregate_target                 big integer 0
sga_target                           big integer 0

advertisements

 

PGA Size 

V$PGASTAT view will give the details of the PGA. Total PGA Allocated is the Current amount of PGA memory allocated by the instance. The Oracle Database attempts to keep this number below the value of the PGA_AGGREGATE_TARGET initialization parameter. However, it is possible for the PGA allocated to exceed that value by a small percentage and for a short period of time when the work area workload is increasing very rapidly or when PGA_AGGREGATE_TARGET is set to a small value. 

Maximum PGA Allocated: 

At single point of time the maximum PGA allocated or reached up to this value. All these values are in bytes and in below example it is converted into MB.

SQL> select name,value/1024/1024 inMB from v$pgastat where name in ('total PGA allocated','maximum PGA allocated');
NAME                                                                   INMB
---------------------------------------------------------------- ----------
total PGA allocated                                              187.724609
maximum PGA allocated                                            1087.04785

_____________________________________________________________________________________________________________________

Website Stats