Brief introduction of PGA automatic management of Orace

Prior to Oracle 9i, we managed the use of the PGA primarily by setting parameter values such as Sort_area_size, hash_area_size (usually called *_area_size), but, strictly speaking, managed the UGA in the PGA. However, there is a problem, that is, these parameters are for a session, that is, the set of parameter values for all log in to the database session is effective. In the actual operation of the database, there are always some sessions need more PGA, and some sessions need less PGA. If you set a small *_area_size, you will make certain SQL statements run with inefficiencies due to the need to exchange temporary data to disk. If you set a large value, it is possible to waste space on the one hand, and on the other hand, consuming too much memory may result in a shortage of memory required by other components of the operating system, resulting in a decrease in overall database performance. So how to set the *_area_size value has always been a problem for DBAs.

A new feature introduced from Oracle 9i (and, of course, Oracle 10g) can effectively solve this problem, which is the automatic PGA management.

First, set the Workarea_size_policy parameter. When this parameter is auto (also the default), the PGA Automatic Management is enabled, and when this parameter is set to Manual, the PGA Automatic Management is disabled, still followed the Oracle 9i approach, that is, using *_area_size to manage the PGA.

The DBA can then estimate the total amount of the PGA memory that all sessions need to consume based on the load of the database, and then set the value to the value of the initialization parameter pga_aggregate_target. Oracle assigns the PGA to the needs of each session, while maintaining that the entire PGA memory sum does not exceed the value defined by the parameter. In this way, Oracle will try to avoid the entire PGA's memory capacity to grow abnormally and affect the performance of the entire database. Thus, it is effective to solve the problems caused by setting up *_area_size.

Unfortunately, the PGA automatic management under Oracle 9i is only valid for private connections and is not valid for shared connections. Oracle 10g is valid for both connection modes later.

In the PGA, the most significant performance impact is the SQL workspace. Generally, the larger the SQL Workspace is, the more efficient the execution of the SQL statement is, and the less time it will take to respond to the user. Ideally, the SQL workspace should be able to accommodate all the input and control information involved in the SQL execution process. Of course, this is only the ideal situation, the reality is often not satisfactory, in many cases, the SQL workspace can not accommodate the memory space required to execute SQL, and thus have to switch to temporary tablespace. To measure the degree of fit between the memory required to execute SQL and the SQL workspace that is actually allocated to that SQL, Oracle divides the allocated SQL workspace into the following three types.

Optimal dimensions: SQL statements can complete all operations within the allocated SQL workspace. This is the best performance.

OnePass Dimensions: The SQL statement needs to interact with the temporary tablespace on the disk once to complete all the operations in the assigned SQL workspace.

Multipass size: Because the SQL Workspace is too small, the SQL statement needs to interact with the temporary table space on the disk multiple times to complete all operations. This time the performance will drop dramatically.

Oracle tends to allocate optimal size SQL workspaces for each session of the PGA when the overall system load is small.

As the load goes up, as the session of the connection increases, resulting in a growing number of simultaneous SQL statements, Oracle tends to allocate onepass size SQL workspaces for each session of the PGA, or even multipass sized SQL workspaces.

Once we set the Pga_aggregate_target, all the *_area_size will be ignored. So, how do we set the value of this parameter? This depends on the purpose of the database, if the database is used for OLTP (online transaction processing), its application is typically a small, short process, and the required PGA is smaller, so the value is typically 20% of the total assigned to the Oracle instance, and the other 80% gives the SGA If the database is applied to OLAP (DSS) (Data Warehouse or decision analysis), its application is generally very large, the process of running for a long time, so need more PGA. Therefore, the PGA is usually allocated 50% of the memory. If the database is a mixed type, the situation is more complex, the general will first allocate 40% of the initial value, and then with the application of the database, and continue to monitor the PGA, and adjust accordingly.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/