Databases Reference
In-Depth Information
If we are using a dedicated server connection, the User Global Area is located inside the
Program Global Area (PGA). If we use an Oracle Shared Server Connection, the User Global
Area is not inside the PGA, but it's in the Shared Pool. Hence, an application which often
performs sorting should not use Oracle Shared Server Connections.
If the space needed for sorting is greater than the space reserved for the sort area, the data
to be sorted is split into smaller pieces, called sort runs. The sort occurs on every single byte,
which is stored in temporary segments on-disk.
The data of sort runs are finally merged together to obtain the final result. If there is enough
space for this merge operation in the sort area, we have a single-pass (on-disk) sort,
otherwise the merge operation is executed in more steps, merging two subsets of sort runs
in each step; in this case, we have a multi-pass (on-disk) sort.
When the I/O operation from and to disk is involved, an optimal sort cannot take place, so it
is better to have a single-pass sort than the multi-pass.
The initialization parameter
STATISTICS_LEVEL
should be set
to
TYPICAL
(the default value) or
ALL
; setting this parameter to
BASIC
turns off the generation of PGA advice performance views.
The data in these views is reset at instance start-up or when the
value of the
PGA_AGGREGATE_TARGET
parameter is altered.
Please note that the
PGA_AGGREGATE_TARGET
parameter value
can change automatically over time, starting with Oracle Database
11
g
as part of the Automatic Memory Management enhancements
available at 11
g
. For more details check the note 443746.1 at the
following URL:
https://support.oracle.com/CSP/main/article?
cmd=show&type=NOT&id=443746.1
See also
F
More information on memory management in
Chapter
9
,
Tuning Memory
in the
recipe
Tuning the
Program
Global
Area
and
the
User
Global
Area
F
In this chapter, the recipe
Troubleshooting
temporary
tablespace
gives more
information about temporary segments and temporary tablespaces
Sorting and indexing
We have seen various aspects of indexing in
Chapter
3
,
Optimizing Storage Structures.
In
this recipe, we will focus on how to use indexes to avoid sort operations.
