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;

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

If the PGA set too small, leading to sometimes more than PGA_AGGREGATE_TARGET PGA value here is 0, indicating no expansion of PGA values greater than the TARGET, if this value occurred, then the increase in PGA size.
bytes processed 124434432 bytes
extra bytes read / written 0 bytes
cache hit percentage 100 percent — 100% hit rate, if the small increase in PGA

recompute count (total) 6651

19 rows selected

SQL> select max (pga_used_mem) / 1024/1024 M from v $ process; —- the current maximum memory consumption of a process

M
———-
9.12815189

SQL> select min (pga_used_mem) / 1024/1024 M from v $ process where pga_used_mem> 0; — process consumes less memory

M
———-
0.19186878

SQL> select max (pga_used_mem) / 1024/1024 M from v $ process; —- process has the maximum memory consumption

M
———-
9.12815189

SQL> select sum (pga_used_mem) / 1024/1024 from v $ process; —- total current consumption of the PGA process

SUM (PGA_USED_MEM) / 1024/1024
—————————
28.8192501068115

How to set the PGA do? We can stress test stage, the simulation about the operation of the system, then run

select (select sum (pga_used_mem) / 1024/1024 from v $ process) / (select count (*) from v $ process) from dual; be a process taking up about how much memory, and then estimate how much total system connection, For example, a total of 500 connections,

So Sessions = 1.1 * process +5 = 500, then the processes = 450, multiplied by a process consumes memory, you can roughly estimate how much PGA need to set up.

Best to set the value of PGA than the calculated value of larger, PGA values configured, you can depending on the system of nature, if the system is OLTOP, then the total memory can be set to PGA/0.16, eventually able to estimate the SGA the size of the proposal, with some memory or more, cheaper anyway.

The following excerpt eygle on a process to allocate the maximum memory (serial operation) of the rules:

10gR1 before the operation for the serial (non-parallel) of a process to allocate the maximum memory is min (5% pga_aggregate_target, 100m)

10gR2 after the operation for the serial (non-parallel) of a process to allocate the maximum memory the following rules:

If pga_aggregate_target <= 500m, then the largest memory for the 20% * pga_aggregate_target.

If the 500m <pga_aggregate_target <= 1000m, then the maximum memory 100m.

If the 1000m <pga_aggregate_target <= 2.5G, then the maximum amount of memory to 10% * pga_aggregate_target.

If pga_aggregate_target> 2.5G, then the maximum amount of memory to 2.5G.

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x $ ksppi x, SYS.x $ ksppcv y
3 WHERE x.inst_id = USERENV (‘Instance’)
4 AND y.inst_id = USERENV (‘Instance’)
5 AND x.indx = y.indx
6 AND x.ksppinm LIKE ‘% & par%’
7 /

NAME VALUE DESCRIB
————————————————– —————————— ——————– ————————————————– ———- —————————————- —————————————-
_smm_max_size 20480 maximum work area size in auto mode (serial)

SQL> show parameter pga

NAME TYPE VALUE
———————————— ———– — —————————
pga_aggregate_target big integer 100M
Here I have a process to allocate the maximum memory 20M, because my PGA = 100M, consistent with the above rules.

Implicit argument that a process to allocate _smm_max_size largest memory.

Buy piner the “oracle high availability environment,” a book, just to take advantage of this time to learn about.
To summarize what you see published here, starting today, the first chapter on the content of SGA and PGA.

Will continue to be summarized later in this publication and share.

SGA and PGA is structured as follows chart:

SGA:

View SGA:

Sqlp> show sga

Or select * from v $ sga;

Total System Global Area 289406976 bytes

Fixed Size 1248600 bytes

Variable Size 176161448 bytes

Database Buffers 109051904 bytes

Redo Buffers 2945024 bytes

Fixed Size: includes database and examples of control information, status information, dictionary information, startup was fixed in the SGA, the will not change.

Variable Size: include shard pool, large pool, java pool, stream pool, cursor area and other structures

Database Buffers: a database where the data block buffer, is the largest local SGA decided to database performance

Redo Buffers: REDO buffers provide a place, too much is not needed in the OLAP

V $ sgastat recorded some statistical information SGA

V $ sga_dynamic_components save SGA can be adjusted manually adjust the records of some of the region

Shard pool:

Shard_pool_size determine its size, 10g automatic management of the future

Shard_pool in the data dictionary and the control region of the user can not directly control, and the users only sql buffer (library cache).

The process will often visit with DBMS_SHARED_POOL.KEEP stored procedure or package the package pin in the shared pool.

Manually remove the contents of the shared pool: alter system flush shard_pool;

Shared pool related to several common view:

V $ sqlarea records all sql statistics, including the implementation of the number of physical time, logical time, time-consuming

V $ sqltext_with_newline full show sql statement to mark through hash_value statement, piece sorting

V $ sql_plan save the sql in the implementation plan, through the tool to view

V $ shared_pool_advice forecast on the shared pool can be adjusted reference SGA

Data buffer:

In the OLTP system requirements data buffer hit ratio above 95%

select sum (pins) “execution”, sum (pinhits) “hits”,

((Sum (pinhits) / sum (pins)) * 100) “pinhitration”,

sum (reloads) “misses”, ((sum (pins) / (sum (pins)

+ Sum (reloads))) * 100) “relhitratio”

from V $ librarycache

PINS	NUMBER	Number of times a PIN was requested for objects of this namespace
PINHITS	NUMBER	Number of times all of the metadata pieces of the library object were found in memory
RELOADS	NUMBER	Any PIN of an object that is not the first PIN performed since the object handle was created, and which requires loading the object from disk

Oracle to obtain from the data buffer in the database called cache hit, to get the feet from the disk cache miss

Data buffer in the data block through the dirty list (dirty list) and the LRU list (LRU list) to manage.

Data buffer can be broken down into: default pool, keep pool, recycle pool corresponding parameters db_cache_size, db_keep_cache_size, db_recycle_size buffer size, respectively,

From the start oracle 9i support different block sizes of table space, the corresponding block sizes for different tablespaces of different block size specified data buffer, different data block size corresponding db_nk_cache_size buffer can be specified, where n can be 2,4,6,16 or 32

V $ db_cache_advice forecast data buffer, you can do to adjust the reference data buffer

V $ bh, x $ bh records the data block buffer in the data buffer in the case, by this view, the system can find the hot block. By the following statement in the top 10 hot spots to find systems where hot objects fast:

Select / * + rule * / owner, object_name from dba_objects

Where data_object_id in

(Select obj from

(Select obj from x $ bh order by tch desc)

Where rownum <11);

PGA:

Related to the process used to preserve the user’s memory segment.

Started from 9i PGA automatic management, pga_aggregate_target parameter specifies the session to use the maximum total PGA memory limit. Workarea_size_policy parameters for the switch automatic PGA memory management functions, auto / manual

In OLTP environments, automatic PGA management if set to some value, such as 2G or so will be able to meet the system requirements.

Automatic memory management:

Starting from 9i, sga_max_size parameter SGA memory size, not the dynamic changes

From the 10g start sga_target specified parameters, all the SGA components such as: shared pool, data buffer, large pool do not have to manually specified, Oracle will automatically manage. This feature is the automatic shared memory management ASMM. If you set sga_target = 0, will automatically turn off the automatic shared memory management functions. Sga_target size can not exceed sga_max_size size.

Manual management of SGA:

Alter system set sga_target = 2000m;

Alter system set db_cache_size = 1000m;

Alter system set shared_pool = 200m;

Alter system set sga_target = 0 ——— turn off automatic shared memory management ASMM

11G after the sga + pga memory can automatically manage the AMM, the relevant parameters memory_max_target memory_target. Set up after these two parameters do not care about the SGA and PGA

11g manual memory management:

Alter system set memory_target = 3000m;

Alter system set sga_target = 2000m;

Alter system set pga_aggregate_target = 1000m;

Alter system set memory_target = 0 ;——— turn off the automatic memory management AMM

SGA + PGA is best not to exceed 70% of the total memory

(source : http://www.kods.net/how-to-estimate-the-pga-sga-size-configuration-database-server-memory/)