Suggestions on the size of the initial SGA database and the size of the PGA database, sgapga

Suggestions on the size of the initial SGA database and the size of the PGA database, sgapga
1. Background information

Many new business systems are launched, and most DBAs do not understand the business, so they are bored with the header database creation. The SGA value is set to a large value, and the PGA is set to a large value. After a period of operation, it may be because this value is randomly specified. For example, if the SGA + PGA is greater than the total physical memory, the SGA value or PGA value is too small, it is not uncommon to cause a DOWN machine or a low performance.

In fact, ORACLE has a saying about the initial setting of SGA, the initial setting of PGA, The OLTP system, and the OLAP system.

2. Plan the total memory usage of ORACLE instances. 2.1 The following is an official ORACLE suggestion.

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

2.2 meaning understanding:

If the machine running the ORACLE database runs only one instance without considering other special programs, you can allocate 80% of the total physical memory to the database instance. For example, if the total physical memory is 4 GB, you can configure 3.2GB memory for the database (SGA + PGA)

3. How much should the initial values of SGA and PGA be set to 3.1? The following is an official ORACLE recommendation.

■ 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:

3.2 meaning understanding

(1) For OLTP systems, SGA accounts for 80% of the total memory used by the database, and PGA accounts for 20% of the memory used by the database (for example, the total physical memory is 4 GB, And the PGA consumes about 655 MB)

(2) For OLAP (DSS) systems, for intensive and large Query Systems, PGA can account for 70% of the total memory used by the database (for example, the total physical memory is 4 GB, the PGA consumes about 2.2 GB (2.24 GB). We recommend that you set the database to use 50% of the total memory at first.

3.3 Recommended Calculation Formula

(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. Reference for adjusting the value after going online

All the above initial recommended values are the best configuration recommended values before the launch. After the launch for a period of time, the system running features will gradually become apparent, adjust the values of SGA_TARGET and PGA_AGGREGATE_TARGET in a timely manner based on actual operation requirements.

For more information about SGA_TARGET, see V $ SGA_TARGET_ADVICE.

For more information about PGA_AGGREGATE_TARGET, see V $ PGA_TARGET_ADVICE.

 

 

Author: LI Junjie (Network Name: Step-by-Step), engaged in "system architecture, operating system, storage device, database, middleware, application" six levels of systematic performance optimization work

Join the system performance optimization professional group to discuss performance optimization technologies. GROUP: 258187244