Databases Reference
In-Depth Information
In fact, the
DB_BLOCK_SIZE
for our database is 8 KB, which reserves 8192 KB of memory
for sort operations, but actually needs 24 MB of memory to complete the sort in-memory.
When we restore the
WORKAREA_SIZE_POLICY
to
AUTO
, the sorts occur in memory and
not on-disk.
The query in step 6 indicates the percentage of optimal, single-pass, and multi-pass sorts
depending on the memory used for the
WORKAREA
. The output of this operation is as shown
in the following screenshot:
The first two columns show the range in KB that is used to evaluate the optimal
WORKAREA
size. The last three columns show the percentage of optimal executions (that is the sort
operations occurred totally in memory), the percentage of one-pass sorts (that is the sort
operations with a final merge completely in memory), and the percentage of multi-pass sorts
respectively, when the merge operation is executed using more steps with partial results
stored on-disk.
In the last step, we execute a query which returns three columns:
PGA
Target
,
Estimated
PGA
Cache
Hit
Percentage
, and
Estimated
Overallocation
Count
.
