Oracle instance memory (SGA and PGA) Adjustment [to original address: http://log-cd.javaeye.com/blog/562052]

I. Glossary
(1) SGA: System Global Area is a basic component of Oracle Instance, which is allocated when the Instance is started. The System fully-local SGA consists of three parts: the Shared Pool, data buffer, and log buffer.

(2) Shared Pool: Shared Pool is used to cache recently executed SQL statements and recently used data definitions, including Library cache (Shared SQL zone) and Data dictionary cache (Data dictionary Buffer ). The shared SQL zone is the area where user SQL commands are stored. The Data Dictionary Buffer Zone stores dynamic information about database operations.

(3) Buffer Cache: Database Buffer Cache is used to Cache data blocks retrieved from data files, which can greatly improve data query and update performance.

(4) Large Pool: Large Pool is an optional memory area in SGA and is only used in shared server environments.

(5) Java Pool: the Java Pool serves the syntax analysis of Java commands.

(6) PGA: Process Global Area is the memory reserved for each user Process connected to the Oracle database.

Ii. Analysis and Adjustment
(1) system full-region:
SGA depends on the operating system, memory size, cpu, and number of simultaneous logon users. It can occupy 1/3 to 1/2 of the OS physical memory.
A. Shared Pool:
View the usage of the shared SQL zone: SQL code

1. Select (sum (pins-reloads)/sum (pins) "Library cache" from v $ librarycache; -- Dynamic Performance Table

Select (sum (pins-reloads)/sum (pins) "Library cache" from v $ librarycache; -- Dynamic Performance Table

This usage should be above 90%; otherwise, the size of the shared pool needs to be increased.

View the usage of the Data Dictionary Buffer: SQL code

1. Select (sum (gets-getmisses-usage-fixed)/sum (gets) "Data dictionary cache" from v $ rowcache; -- Dynamic Performance Table

Select (sum (gets-getmisses-usage-fixed)/sum (gets) "Data dictionary cache" from v $ rowcache; -- Dynamic Performance Table

This usage should also be above 90%, otherwise the size of the shared pool needs to be increased.

Modify the size of the Shared Pool: SQL code

1. Alter system set SHARED_POOL_SIZE = 64 M;

ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;

B. buffer cache:
View database data buffer usage: SQL code

1. SELECT name, value FROM v $ sysstat order by name WHERE name IN (''db BLOCK gets', ''consistent gets', ''physical reads '');

SELECT name,value FROM v$sysstat order by name WHERE name IN(''DB BLOCK GETS'',''CONSISTENT GETS'',''PHYSICAL READS'');

The usage hit rate of the calculated data buffer is 1-(physical reads/(db block gets + consistent gets). The hit rate should be above 90%. Otherwise, the data buffer size needs to be increased.

C. Log Buffer
View log buffer usage: SQL code

1. SELECT name, value FROM v $ sysstat WHERE name IN (''redo entries '', ''redo log space requests '');

SELECT name, value  FROM v$sysstat WHERE name IN (''redo entries'',''redo log space requests'');

The query results can calculate the request failure rate of the log Buffer:
Application Failure Rate = requests/entries. The application failure rate should be close to 0. Otherwise, the log buffer is too small and the log buffer of the ORACLE database needs to be increased.

D. large pool:
This reduces the burden on the shared pool and can be used for backup, recovery, and other operations without the use of the LRU Algorithm for management. The size of the database depends on the 'sharing mode/db mode' of the database. If it is a sharing mode, it needs to be allocated larger.
Specify the size of the Large Pool: SQL code

1. Alter system set LARGE_POOL_SIZE = 64 M

ALTER SYSTEM SET LARGE_POOL_SIZE=64M

E. Java pool:
It is used when Java is installed and used.

(2) PGA Adjustment
A. PGA_AGGREGATE_TARGET initialization settings

The PGA_AGGREGATE_TARGET value should be set based on the total amount of memory available for the Oracle instance. This parameter can be dynamically modified. Assume that the Oracle instance can allocate 4 GB of physical memory, and the remaining memory is allocated to the operating system and other applications. You may allocate 80% of the available memory to the Oracle instance, that is, 3.2 GB. Now you must divide the SGA and PGA regions in the memory.

In the OLTP (online transaction processing) system, the typical PGA memory settings should be a small part of the total memory (such as 20%), and the remaining 80% will be allocated to SGA.
OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%

In a DSS (Dataset) system, a typical PGA memory can be allocated with a maximum of 70% of memory because it runs a large number of queries.
DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%

In this example, the total memory is 4 GB. For the DSS system, you can set PGA_AGGREGATE_TARGET to 1600 MB and OLTP to 655 MB.

B. Configure Automatic PGA Management

You do not need to restart the database and directly modify it online.

SQL> alter system set workarea_size_policy = auto scope = both;

System altered.

SQL> alter system set pga_aggregate_target = 512 m scope = both;

System altered.

SQL> show parameter workarea

NAME TYPE VALUE
-----------------------------------------------------------------------------
Workarea_size_policy string AUTO -- set this to AUTO
SQL> show parameter pga

NAME TYPE VALUE
-----------------------------------------------------------------------------
Pga_aggregate_target big integer 536870912

SQL>

C. monitor the performance of automatic PGA Memory Management

V $ PGASTAT: This view provides an instance-level PGA memory usage and automatic allocation statistics.

SQL> set lines 256
SQL> set pages 42
SQL> SELECT * FROM V $ PGASTAT;

NAME VALUE UNIT
--------------------------------------------------------------------------------------
Aggregate PGA target parameter 536870912 bytes -- current PGA_AGGREGATE_TARGET Value
Aggregate PGA auto target 477379584 bytes -- the current PGA size that can be automatically allocated should be smaller than PGA_AGGREGATE_TARGET
Global memory bound 26843136 bytes -- the maximum size of the work area in auto mode. Oracle automatically adjusts the size according to the workload.
Total PGA inuse 6448128 bytes
Total PGA allocated 11598848 bytes -- maximum PGA allocation
Maximum PGA allocated 166175744 bytes
Total freeable PGA memory 393216 bytes -- maximum idle size of PGA
PGA memory freed back to operating system 69074944 bytes
Total PGA used for auto workareas 0 bytes -- size of the PGA allocated to auto workareas
Maximum PGA used for auto workareas 1049600 bytes
Total PGA used for manual workareas 0 bytes
Maximum PGA used for manual work areas 530432 bytes
Over allocation count 1118 -- number of allocations after the instance is started. If the value is greater than 0, you need to increase the pga value.
Bytes processed 114895872 bytes
Extra bytes read/written 4608000 bytes
Cache hit percentage 96.14 percent -- hit rate

16 rows selected.

-- V $ PGA_TARGET_ADVICE

SQL> SELECT round (PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM v $ pga_target_advice;

The output of this query might look like the following:

TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
--------------------------------------------
63 23 367
125 24 30
250 30 3
375 39 0
500 58 0
600 59 0
700 59 0
800 60 0
900 60 0
1000 61 0
1500 67 0
2000 76 0
3000 83 0
4000 85 0

It can be seen that when TARGET_MB is 375M is ESTD_OVERALLOC_COUNT = 0, you can set PGA_AGGREGATE_TARGET to 375 M.

Appendix: differences between oracle SGA and PGA:
SGA is the memory area used to store database information, which is shared by database processes. It contains data and control information of the Oracle server. It is allocated in the actual memory of the computer where the Oracle server resides. If the actual memory is insufficient, it is written into the virtual memory.
PGA: contains the data and control information of a single server process or a single background process. Unlike the SGA shared by several processes, PGA is only used by one process, PGA is allocated when a process is created and recycled when the process is terminated.
 

Address: http://log-cd.javaeye.com/blog/562052