Database Reference
In-Depth Information
I discuss work area configuration (sizing) in Chapter 9. As you might recall from that chapter, there are two sizing
methods. Which one is used depends on the value of the
workarea_size_policy
initialization parameter. The two
methods are as follows:
auto
: The database engine automatically does the sizing of the work areas. The total
amount of PGA dedicated to one instance is controlled by the
pga_aggregate_target
initialization parameter or, as of version 11.1, by the
memory_target
initialization
parameter.
manual
: The
sort_area_size
initialization parameter limits the maximum size of a single
work area. In addition, the
sort_area_retained_size
initialization parameter controls
how the PGA is released when the sort is over.
In-Memory Sorts
The processing of an in-memory sort is straightforward. The data is loaded into a work area, and the sorting takes
place. It's important to stress that all data must be loaded into the work area, not only the columns referenced as
the join condition. Therefore, to avoid wasting a lot of memory, only the columns that are really necessary should
be referenced in the
SELECT
clause. To illustrate this point, let's look at two examples based on the four-table join
discussed in the previous section.
In the following example, all columns in all tables are referenced in the
SELECT
clause. In the execution plan,
the
OMem
and
Used-Mem
columns provide information about the work areas. The former is the estimated amount of
memory needed for an in-memory sort. The latter is the actual amount of memory used by the operation during
execution. The value between brackets (that is, the zero) means that the sorts were fully processed in memory:
SELECT t1.*, t2.*, t3.*, t4.*
FROM t1, t2, t3, t4
WHERE t1.id = t2.t1_id
AND t2.id = t3.t2_id
AND t3.id = t4.t3_id
AND t1.n = 19
-----------------------------------------------------------
| Id | Operation | Name | OMem | Used-Mem |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | MERGE JOIN | | | |
| 2 | SORT JOIN | | 34816 |30720 (0)|
| 3 | MERGE JOIN | | | |
| 4 | SORT JOIN | | 5120 | 4096 (0)|
| 5 | MERGE JOIN | | | |
| 6 | SORT JOIN | | 3072 | 2048 (0)|
|* 7 | TABLE ACCESS FULL| T1 | | |
|* 8 | SORT JOIN | | 21504 |18432 (0)|
| 9 | TABLE ACCESS FULL| T2 | | |
|* 10 | SORT JOIN | | 160K| 142K (0)|
| 11 | TABLE ACCESS FULL | T3 | | |
|* 12 | SORT JOIN | | 1045K| 928K (0)|
| 13 | TABLE ACCESS FULL | T4 | | |
-----------------------------------------------------------
