Introduction and modification of Oraclesga and pga

Oracle recommends that the OLTP (on-line TransactionProcessing) system accounts for 80% of the total system memory, and then allocates 80% to SGA and 20% to PGA. That is
SGA = system_total_memory * 80% * 80%
PGA = system_total_memory * 80% * 20%

1. Global zone of the SGA System

SGA refers to the System GlobalArea, which is the memory area used to store database information. This information is shared by database processes.

When SGA is set, in theory, SGA can occupy 1/2 of the OS physical memory-1/3
Principle: SGA + PGA + OS memory usage <total physical RAM-ratio is generally 2 (SGA): 1 (PGA): 1 (OS system)
SGA = (db_block_buffers * blocksize) + (shared_pool_size + large_pool_size + java_pool_size + log_buffers) + 1 MB

View desc v $ sga select * fromv $ sga; select * From v $ sgastat t;

Serial number	Memory name	Byte 1024 byte = 1 k	Description	Size setting
1	Fixed Size	1415972	Stores the information of various SGA components, which can be seen as the region for guiding the establishment of SGA.	Fixed size, not modified
2	Variable Size	1059677404	Including shared_pool_size, Java_pool_size, large_pool_size	Shared_pool_size 10% Java_pool_size> 20 M (java 30 M not required) Large_pool_size 5-10 M (not too large to use MTS)
3	Database Buffers	511705088	Data Cache pool (cache data)	40%
4	Redo Buffers	4259840	Log cache pool (modify Information)	Between 128 K ---- 1 m, should not be too large

Statement: (Oracle10 server needs to be restarted)

Serial number	View statements	Modify statement
1	Show parameter sga_max_size;	Alter system set sga_max_size = 2000 m scope = spfile;
2	Show parameter sga_target;	Alter system set sga_target = 2000 m scope = spfile;
3	Show parameter pga_aggregate_target;	Alter system set pga_aggregate_target = 500 m scope = spfile;

2. PGA program global Zone

PGA: contains the data and control information of a single server process or a single background process. The SGA shared with several processes is the opposite. PGA is only used by one process, PGA is allocated during Process Creation and recycled upon Process Termination.

A. Sort_area_size memory occupied by sorting
B. Hash_area_size is used for hash join and bitmap index.
These two parameters belong to PGA rather than SGA in non-MTS, and are allocated separately for each session. In addition to OS + SGA on our servers, we must consider these two parameters.

Principle: OS Memory + SGA + number of concurrent execution processes * (sort_area_size + hash_ara_size + 2 M) <0.7 * total memory

Oracle 10 Gb provides automatic management of PGA memory. The pga_aggregate_target parameter can specify the maximum PGA memory. When pga_aggregate_target is greater than 0, Oracle automatically manages the pga memory, and the sum of the PGA occupied by each process is not greater than the value specified by pga_aggregate_target.