Databases Reference
In-Depth Information
7.
Investigate the optimal
PGA
size:
SELECT
round(PGA_TARGET_FOR_ESTIMATE / 1024 / 1024)
AS PGA_Target_MB,
ESTD_PGA_CACHE_HIT_PERCENTAGE,
ESTD_OVERALLOC_COUNT
FROM V$PGA_TARGET_ADVICE
ORDER BY 1;
How it works...
Let's explain the steps of this recipe.
The query in step 2 shows the statistics collected in the database after sorting. You can
see an example of the output in the following screenshot:
The output is divided between in-memory (the first row) and on-disk (the second row) sorts,
while the number of rows sorted is displayed in the last row. To obtain better performance,
we need to perform sorts in memory and not on disk.
In step 3, the execution plan of a simple query—involving an
ORDER
BY
clause—is elaborated,
and the output can be seen in the following screenshot:
