Home » Posts tagged 'aggregate'

Tag Archives: aggregate

How to estimate the PGA, SGA size, configuration, database server memory

ORACLE to the proposal: OLTP systems PGA = (Total Memory) * 80% * 20%. DSS systems PGA = (Total Memory) * 80% * 50%.

ORACLE recommends a database server, sub-80% of the memory to the database, 20% of the memory to the operating system, how it to a database server with memory?

SQL> select * from v $ pgastat;

————————————————– ————– ———- ————
aggregate PGA target parameter 104857600 bytes

—– This value is equal to the value of parameter PGA_AGGREGATE_TARGET, if this value is 0, disable the automatic PGA management.
aggregate PGA auto target 75220992 bytes

—– Said how much memory to the PGA also provides automatic operation mode, this value is usually close to pga_aggregate_target-total pga inuse.
global memory bound 20971520 bytes

—– Workspace implementation of the maximum, if the value is less than 1M, immediately increase the size of PGA
total PGA inuse 30167040 bytes

—– Total size of the current allocation of PGA, this value may be greater than the PGA, PGA settings if too small. This value is close to the select sum (pga_used_mem) from v $ process.
total PGA allocated 52124672 bytes

—– Take the total size of work area
maximum PGA allocated 67066880 bytes
total freeable PGA memory 0 bytes —- no idle PGA
process count 23 —- 23 present a process
max processes count 25
PGA memory freed back to OS 0 bytes
total PGA used for auto workareas 8891392 bytes
maximum PGA used for auto workareas 22263808 bytes
total PGA used for manual workareas 0 bytes — 0 automated management
maximum PGA used for manual workareas 0 bytes — 0 automated management
over allocation count 0