Databases Reference
In-Depth Information
We can use this method to estimate memory requirements to execute the sort in the memory.
The value in the
TempSpc
column of our example is 24 MB, which is the memory required to
execute the sort in memory.
In step 4, we restart the database to reset the values collected in dynamic performance views;
we can perform this operation because we are in a test environment and we want to compare
the statistic values generated by two different executions in a fast way for the sake of brevity.
In step 5, the script is shorter; in this script the same query on dynamic performance view as
in step 2 is executed, before and after a query on the
SALES
table with an order by clause.
The same group of queries is executed using the
AUTOMATIC
policy for memory management
and using a
MANUAL
policy, which allocates some space for the work, so it's executed on-disk.
We can use the recommended parameter
PGA_AGGREGATE_TARGET
to set the PGA memory available to all server processes attached to the
instance. This parameter can be set to a value expressed in
KB
,
MB
, or
GB
, and when this value is nonzero, the
WORKAREA_SIZE_POLICY
is automatically set to
AUTO
. Setting
PGA_AGGREGATE_TARGET
to
0
automatically sets
WORKAREA_SIZE_POLICY
to
MANUAL
.
