Databases Reference
In-Depth Information
The output of this query is as follows:
With the help of this query we can find the optimal size for the PGA, because for multiple PGA
Target sizes (in MB) there is an estimated over-allocation (which we want to avoid, so we'll
consider only those rows with a value of zero in this field) and an estimated percentage of
cache hit for PGA. We will choose the last value after which the increase in the value of the
second field is very small. In our example, values of more than 236 MB are useless, because
there is no increase in the
ESTD_PGA_CACHE_HIT_PERCENTAGE
field even if we increase
the PGA size (we have already reached 100 percent, so using more memory won't get a
further performance increase).
In our example, a value of 59 MB is the optimal value, because there is more than 85 percent
in cache hit (exactly 87 percent), and to increase this percentage we need to multiply the
memory usage by 4 up to 236 MB to obtain an increase of 13 percent in the cache hit value.
There's more...
For a better understanding of the concepts introduced in this recipe, let's see how the
database manages sort operations. We will also look at some concepts of database
memory architecture.
The sort operation can be done in memory if there is enough space in the sort area, which
is a part of the User Global Area; in this case we have an optimal sort.
