Home » 2010 » June

Monthly Archives: June 2010

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

June 19, 2010 9:30 am / Leave a comment


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;

NAME VALUE UNIT
————————————————– ————– ———- ————
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
(more…)

Oracle memory structure – SGA (Just for memory refresh)

June 18, 2010 3:23 pm / Leave a comment


First, an overview of the “Oracle memory structure of-PGA Chapter” mentioned in the article, PGA is a dedicated server process private memory area, while the SGA is the shared memory area.

SGA by a number of parts:
1, the fixed SGA (Fixed SGA)
2, block buffer (Db cache)
3, redo log buffer (Redo log buffer)
4, Java pool (Java pool)
5, large pool (Large pool)
6, shared pool (Shared pool)
7, stream pool (Stream pool)

The following parameters to control the size of shared pool of related components:
1, JAVA_POOL_SIZE: Control Java pool size.
2, SHARED_POOL_SIZE: 9i shared pool to control the largest part of the occupier, 10g shared pool size over the control.
3, LARGE_POOL_SIZE: control of large pool size.
4, DB_ * K_CACHE_SIZE: control the size of the different pieces of the buffer size.
5, LOG_BUFFER: redo log buffer size control.
6, SGA_TARGET: 10g or more automatic SGA memory management control of the total memory size.
7, SGA_MAX_SIZE: can control the maximum SGA size, need to restart the database to change.

The following will describe in more detail the role of the various parts and recommended settings. (more…)