How large is the initial build of the SGA, and how large is the PGA recommendation

1. Background information

Many new business systems on-line, most DBAs do not understand the business, the stuffy Head Building library, the SGA value is set how big, the PGA set how big, arbitrarily specify a value to come, after running for a period of time, perhaps because this value is casually specified, for example SGA+PGA is greater than total physical memory, SGA value or PGA value is too small, etc. The situation that causes down machine or performance is low, not uncommon.

In fact, the initial setting of the SGA, how big the PGA initial setup, on the OLTP system on how to set up on the OLAP system, and how to set up, Oracle has a statement.

2.ORACLE instance total footprint Memory Planning 2.1 Here's what Oracle's official recommendation is.

Assume that an Oracle database instance is configured to run on a system with 4 GB of physical memory. Part of that memory should is left for the operating system and other non-oracle applications running on the same hardware System. You might decide to dedicate is 80% (3.2 GB) of the available memory to the Oracle database instance.

2.2 Meanings understood:

If you are running an Oracle database on a machine that does not consider running other special programs, and assuming that only one instance is running, you can allocate 80% of total physical memory to the DB instance, for example, the total physical memory is 4GB, then you can configure 3.2GB memory for the database (sga+ PGA)

How large the 3.SGA and PGA initial values should be set to 3.1 The following are the official Oracle recommendations

For OLTP systems, the PGA memory typically accounts for a small fraction of the total memory available (for example, 20%) , leaving 80% for the SGA. For DSS systems running large, memory-intensive queries, PGA memory can typically use up to 70% of the available memory. Oracle recommends initially dedicating 50% of the available memory to the PGA, and 50% to the SGA. Therefore, the initial value of the Pga_aggregate_target parameter for a DSS system can be calculated as:

3.2 Meaning understanding

(1) For OLTP systems, the SGA accounts for 20% of the total memory used in the database (for example, the total physical memory 4GB,PGA 80%,PGA about 655MB).

(2) for OLAP (DSS) systems, for systems with dense queries, the PGA can account for 70% of the total memory used by the database (for example, total physical memory 4GB,PGA takes about 2.2GB (2.24GB), and it is recommended to initially set the database to use 50% of the total memory

3.3 Suggested formulas for calculation

(1) OLTP system:

Sga_target = (TOTAL_MEM * 0.8) * 0.8

pga_aggregate_target= (TOTAL_MEM * 0.8) * 0.2

(2) OLAP (DSS) System:

sga_target= (TOTAL_MEM * 0.8) * 0.5

Pga_aggregate_target = (TOTAL_MEM * 0.8) * 0.5

4. After-line adjustment value Reference

Above all the initial recommended values, are in the line before the best configuration recommended value, on-line running for a period of time, the system running characteristics of the true face slowly reflected, then, should be based on the actual needs of the operation of timely adjustment sga_target and Pga_aggregate_target value.

Sga_target The actual demand recommended value, you can refer to V$sga_target_advice

Pga_aggregate_target actual demand recommendations, you can refer to the V$pga_target_advice view

This article Li Junjie (Network Name: casing), engaged in "system architecture, operating systems, storage devices, databases, middleware, applications" six levels of systematic performance optimization work

Welcome to the System performance Optimization Professional group, to discuss performance optimization technology together. Group number: 258187244

How large the initial database SGA should be, and how much the PGA should be recommended