The parameter pga_aggregate_target specifies the PGA memory which is made available to the Oracle server processes.
If pga_aggregate_target is set to a non-zero value then it will automatically enable the workarea size policy, meaning that operations involving memory intensive operations will be automatically sized. These operations include:
- SQL which involve Sorts e.g. "Order By" clauses or "Group By" clauses
- SQL which involves Hash Joins
If pga_aggregate_target is non-zero, then the minimum value is 10M, but can be any much greater if required by your Oracle workload.
An Oracle instance with an inadequate pga_aggregate_target i.e. the setting is too low, will often suffer from I/O related bottlenecks, and Oracle will often spend time in direct path read temp and direct path write temp wait states.
The screenshot below displays this problem in a pre-production environment. The top queries in this case take in excess of 5 seconds on average to complete, while the instance suffers from an I/O bottleneck related to direct path I/O caused by disk sorts. Once the pga_aggregate_target had been re-sized appropriately these queries where improved dramatically as sorts were performed in memory.
To dynamically re-size pga_aggregate_target run the following command (this example changes the value to 100 megabytes):
The bottom line is that you need to monitor in order to understand exactly what's going on in your Oracle instance, and tune accordingly!