New Features of automatic PGA management for Oracle9i

Welcome to the Oracle community forum and interact with 2 million technical staff before entering Oracle9i. The calculation and control of PGA is complicated, starting from Oracle9i, oracle provides a new SQL Memory Management Method: Automatic SQL Execution memory management (AutomatedSQLExecutionMemoryManagement ).

Welcome to the Oracle community forum and interact with 2 million technical staff> before entering Oracle9i, the calculation and control of PGA is complicated, starting from Oracle9i, oracle provides a new method for SQL Memory Management: Automatic SQL Execution Memory Management.

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

Before Oracle9i, the calculation and control of PGA were complicated. From Oracle9i, Oracle provided a new method for SQL Memory Management: with this new feature, Oracle can automatically adjust the s q l Memory zone without shutting down the database, this improvement greatly simplifies DBA work and improves the performance of Oracle databases.

To achieve automatic PGA management, Oracle introduces several new initialization parameters:

1. PGA_AGGREGATE_TARGET-this parameter is used to specify the maximum PGA memory available for all sessions. This parameter can be dynamically changed, with a value range of 10 M-(4096G-1) bytes. 2. WORKAREA_SIZE_POLICY-this parameter is used to enable automatic PGA memory management. This parameter has two options: AUTO and MANUAL. When set to AUTO, the database uses the automatic PGA management function provided by Oracle9i, when it is set to MANUAL, it is still manually managed before Oracle9i.

By default, WORKAREA_SIZE_POLICY in Oracle9i is set to AUTO.

Note that in Oracle9i, The PGA_AGGREGATE_TARGET parameter is only valid for Dedicated connections of the Dedicated Server in Dedicated Server mode, but not for Shared Server connections; PGA_AGGREGATE_TARGET takes effect for both dedicated server connections and shared server connections starting from Oracle10g.

The PGA_AGGREGATE_TARGET parameter limits both global PGA allocation and private Workspace Memory Allocation:

1. For serial operations, the PGA memory that can be used by a single SQL operation is allocated according to the following principles: MIN (5% PGA_AGGREGATE_TARGET, 100 MB)

2. for parallel operations 30% PGA_AGGREGATE_TARGET/DOP (DOP = Degree Of Parallelism concurrency)

To understand the automatic adjustment of PGA, you also need to distinguish between the tunable memory size and the untunable memory size ). The adjustable memory is used by the SQL workspace, and the remaining memory cannot be adjusted.

After automatic PGA adjustment is enabled, Oracle still needs to follow the following principles:

Untunable memory size + tunable memory size <= PGA_AGGREGATE_TARGET

The database system can only control the memory allocation of the adjustable part. If the adjustable part is too small, Oracle will never force this equation to be enabled.

In addition, the PGA_AGGREGATE_TARGET parameter affects the SQL Execution Plan in the CBO optimizer mode. Oracle evaluates the maximum or minimum memory used in Sort, HASH-JOIN, or Bitmap operations based on the PGA_AGGREGATE_TARGET parameter to select the optimal execution plan.

For the PGA_AGGREGATE_TARGET parameter settings, Oracle provides such a recommended solution 1. For the OLTP system PGA_AGGREGATE_TARGET = ( * 80%) * 20% 2. For the DSS System PGA_AGGREGATE_TARGET = ( * 80%) * 50%

That is to say, for a pure database server, we usually need to reserve 20% of the physical memory for the operating system, and the remaining 80% can be allocated to Oracle. The memory used by Oracle is divided into two parts: SGA and PGA. Therefore, PGA can occupy 20% of the total memory consumed by Oracle (OLTP system) to 50% (DSS System ).

This is just a recommended setting. Further, we should adjust and optimize the usage of PGA based on the specific performance indicators of the database.