Database Reference
In-Depth Information
When the limit is reached, the database engine terminates calls or even kills sessions. To choose the session to
deal with, the database engine doesn't consider the maximum PGA utilization. Instead, the database engine considers
the session using the highest amount of untunable memory. When a call is terminated, the following error is raised:
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
When a session is killed, a typical ORA-03113 is raised:
ORA-03113: end-of-file on communication channel
Process ID: 5125
Session ID: 17 Serial number: 39
In addition, a corresponding message like the following is written to the
alert.log
:
PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT of 2048 MB
Immediate Kill Session#: 17, Serial#: 39
Immediate Kill Session: sess: 0x77eb7478 OS pid: 5125
The
pga_aggregate_limit
initialization parameter is dynamic and can be changed only at the instance level. In a
12.1 multitenant environment, it can't be set at the PDB level.
sort_area_size
If manual PGA management is enabled, the
sort_area_size
initialization parameter specifies (in bytes) the size of
the work areas used for merge joins, sorts, and aggregations (including hash group-bys). Be careful: this is the size
of one work area, and a single session may allocate several work areas (Chapter 14 provides some examples of this
behavior). As a result, the total amount of PGA used for the whole system depends on the number of allocated work
areas and not on the number of sessions. The default value is 64KB. Even though it's practically impossible to give
general advice regarding the suggested values, the default is very small, and usually at least 512KB/1MB should be
used. Significantly, work areas aren't always fully allocated. In other words, the value specified by the
sort_area_size
initialization parameter is only a limit. Consequently, specifying a value larger than is really needed isn't necessarily
a problem.
The
sort_area_size
initialization parameter is dynamic and can be changed at the instance and session levels.
In a 12.1 multitenant environment, it can also be set at the PDB level.
sort_area_retained_size
In the previous section, you saw that the
sort_area_size
initialization parameter specifies the maximum size of the
work areas used for sort operations. Strictly speaking, though, the
sort_area_size
initialization parameter specifies
only the amount of memory used while the sorting operation takes place. After the last row has been obtained and
included in the sorted result stored in the work area, memory is still required only as a buffer to return the sorted
result to the parent operation. The
sort_area_retained_size
initialization parameter specifies (in bytes) the amount
of memory retained for that read buffer. This initialization parameter is used only when manual PGA management is
enabled. Even though the default value is derived from the
sort_area_size
initialization parameter, the
v$parameter
view shows 0.
To set this initialization parameter, you must be aware that if it's set to a value lower than the
sort_area_size
initialization parameter, and the result set doesn't fit into the retained memory, data is spilled into a temporary
segment when the sort operation is completed. This might occur even if the sort operation itself is completely
executed in memory! Consequently, it's advisable to use the default value for better performance. Only when the
