Database Reference
In-Depth Information
Generally speaking, this section doesn't provide “good” values for the initialization parameter it describes.
The only way to find good values for a specific application is to test and measure how much PGA is required to
achieve good performance. In fact, the amount of memory has an impact only on performance and not on how an
operation works.
workarea_size_policy
The workarea_size_policy initialization parameter specifies how the sizing of the work areas is performed. It can be
set to one of the following two values:
auto : The sizing of the single work areas is delegated to the memory manager . Through the
pga_aggregate_target initialization parameter, only the amount of PGA for the whole system
is specified. This is the default value.
manual : Through the hash_area_size , sort_area_size , sort_area_retained_size , and
bitmap_merge_area_size initialization parameters, you have full control over the size of the
work areas.
In most situations, the memory manager does a good job, so it's highly recommended to delegate the
PGA management to it. Only in rare cases does manual fine-tuning provide better results than automatic PGA
management.
The workarea_size_policy initialization parameter is dynamic and can be changed at the instance and
session levels. It's therefore possible to enable automatic PGA management at the system level and then, for special
requirements, to switch to manual PGA management at the session level. In a 12.1 multitenant environment, it can
also be set at the PDB level.
pga_aggregate_target
If automatic PGA management is enabled, the pga_aggregate_target initialization parameter specifies (in bytes)
the total amount of PGA dedicated to one database instance. Values from 10MB up to 4TB are supported. The default
value is 20 percent of the size of the system global area (SGA). It's difficult to give any specific advice on what value
should be used. On all systems, however, at least a few megabytes per concurrent session are needed.
as of version 11.1, the memory_target and memory_max_target initialization parameters can be used to
specify the total amount of memory (that is, the Sga plus the aggregate pga) used by a database instance. When they're
set, the database engine automatically redistributes memory as needed between the Sga and the pga. In such a
configuration, the pga_aggregate_target initialization parameter is used to set the minimum size of the pga only.
Note
To illustrate how the memory manager works, in version 11.2.0.3 I executed a query requiring about 60MB of
PGA with an increasing number of concurrent sessions (from 1 to 50). For each iteration, I checked the maximum
amount of PGA allocated by the database instance and looked at the average amount of PGA allocated by the sessions
executing the query. The pga_aggregate_target initialization parameter was set to 1GB. This means that, if the target
is honored, at most 17 sessions (1GB/60MB) should be able to get the PGA necessary to execute the whole statement
in memory. Figure 9-5 shows the results of the test. As you can see, the maximum PGA allocated by the database
instance increased, as configured, up to 1GB. Notice that the system PGA increased almost proportionally with the
number of sessions only up to 19 concurrent sessions. With more than 17 sessions, the system started reducing the
amount of the PGA provided to each session.
 
 
Search WWH ::




Custom Search