Oracle memory management PGA

In Oracle10g, you need to set workarea_size_policy to AUTO, and set the pga_aggregate_target parameter to implement PGA memory management.

In Oracle 10 Gb, you need to set workarea_size_policy to AUTO, and set the pga_aggregate_target parameter to implement memory management of PGA.

In Oracle 10 Gb, you need to set workarea_size_policy to AUTO, and set the pga_aggregate_target parameter to implement memory management of PGA.

The pga_aggregate_target parameter can be set based on the experience value. A simple method to evaluate the PGA can be used to view the PGA Hit rate (PGA Cache Hit %) in the AWR report ), analyze whether the wait events of direct path read temp and direct path write temp are high. These wait events indicate the waiting events that result in a large number of temporary tablespace operations due to limited parameters set by PGA. Of course, there are also complicated methods to evaluate PGA. Let's take a look later.

Note: For 9I shared server connection, you must explicitly set SORT_AREA_SIZE and HASH_AREA_SIZE, that is, you cannot use the automatic management mode. 10 Gb does not have this restriction.
PGA_AGGREGATE_TARGET is an upper limit (theoretically the maximum, PL/SQL is easily exceeded). It is not allocated so much at ORACLE startup, you can even set the size larger than the physical MEM (do not do this in the production database, set pga_aggregate_target + sga) Remind yourself.

V $ pgastat:

Several important parameters are described as follows:
Target parameter value of PGA set by aggregate pga target parameter
In automatic management mode, aggregate PGA auto target can be used in the oracle Workspace.
Total PGA inuse the pga used by the current instance
Total PGA allocated the pga actually allocated by the current instance
Maximum PGA allocated maximum allocable pga
Over allocation count the PGA allocated by ORACLE exceeds the pga_aggregate_target times. This parameter can be used to determine whether pga_aggregate_target is set too small.
Cache hit percentage the PGA hit rate after the instance is started. If all operations are performed in MEM and not in TEMP, the cache hit percentage should be 100%.