The intermediate runs are written to the temporary tablespace of the user, which
reduces sort performance because it involves disk I/O.
Sizing the sort area of the PGA was a critical tuning point in Oracle database releases
prior to Oracle Database 10g. A sort area that was too small for the typical amount
of data requiring sorting would result in temporary tablespace disk I/O and reduced
performance. A sort area that was significantly larger than necessary would waste
memory.
As of Oracle Database 10g, the database provides automatic sizing for the PGA. By
default, this memory management is enabled, and sizing for PGA work areas is based
on 20 percent of the SGA memory size. By using automatic sizing for the PGA, you
eliminate the need to size individual portions of the PGA, such as SORT_AREA_
SIZE.
Oracle Database 11g introduced automatic memory management that spans both the
SGA and the PGA. By setting a single MEMORY_TARGET initialization parameter
(given that the PGA size can be based on a percentage of the SGA memory size), the
PGA and SGA will be automatically set to appropriate initial values.
Pages:
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405