Reduzieren von Disk I/O durch optimieren der Oracle Sort Areas

Operationen wie ORDER BY, DISTINCT, GROUP BY, CREATE INDEX, INTERSECT, MINUS, UNION lösen Sortoperationen aus. Daten können von Oracle auf verschiedene Arten sortiert werden, unter Verwendeung eines Index, im Memory oder als Disk-Sort. Das richtige Setzen der INIT.ORA Parameter SORT_AREA_SIZE und SORT_AREA_RETAINED_SIZE sind für In-Memory Sorts wichtig, ein korrekt definierter TEMPORARY Tablespace für Disk-Sorts.

In-Memory Sorts

In-Memory Sorts werden bis zu einer Grösse von SORT_AREA_RETAINED_SIZE vollständig im Memory Bereich des User Prozesses (PGA) durchgeführt. SORT_AREA_RETAINED_SIZE ist ein statisch allozierter Memory Bereich, der jedem User Prozess zur Verfügung steht. Der Wert darf nicht zu gross gewählt werden, als Faustregel etwa 1/2 von SORT_AREA_SIZE. Wird mehr Memory für die Sortoperation benötigt, so wird maximal bis zu SORT_AREA_SIZE im User-Prozess-Memory sortiert. Dieser Memory Bereich wird dynamisch alloziert und nach der Sortoperation wieder freigegeben. Grössere Sorts werden als Disk-Sort in einem temporären Segment durchgeführt. Sollen grössere Imports beschleunigt durchgeführt werden, so kann der Parameter SORT_AREA_RETAINED_SIZE auf einen grösseren Wert gesetzt werden um die Indexe schneller zu erstellen. Beide Parameter werden im INIT.ORA Parameterfile definiert und können mit folgendem Query kontrolliert werden:

SELECT name,value
FROM v$parameter
WHERE name LIKE '%sort_area%';

NAME                        VALUE
--------------------------- -------
sort_area_size              512000
sort_area_retained_size     128000

Kontrolle der Sort Operationen

Anzahl der Sorts im Memory und auf der Disk

SELECT  name, value
  FROM  v$sysstat
WHERE  name IN ('sorts (memory)',
        'sorts (disk)',
        'sorts (rows)')
ORDER BY statistic#;

Durschnittliche Anzahl sortierter Rows pro Sort Operation

SELECT TO_CHAR(ROUND(tot.value/(mem.value+dsk.value)))
  FROM v$sysstat tot, v$sysstat mem, v$sysstat dsk
WHERE tot.name = 'sorts (rows)'
   AND mem.name = 'sorts (memory)'
   AND dsk.name = 'sorts (disk)';

Disks Sorts

Disk Sorts werden im TEMPORARY Tablespace durchgeführt. Es ist wichtig, dass dieser Tablespace optimal ausgelegt wird, dazu ist folgendes zu beachten.

1. TEMORARY Klausel angeben beim Erstellen des Tablespaces

2. INITIAL = NEXT = DB_BLOCK_SIZE + (multiple of SORT_AREA_SIZE)

3. PCTINCREASE = 0

Bei einem Blocksize von 4K und einer SORT_AREA_SIZE von 512K ergeben sich folgende Werte für INITIAL und NEXT des Temporary Tablespace:

4K + (1 * 512K) = 516K
4K + (2 * 512K) = 1028K
4K + (3 * 512K) = 1540K

CREATE TABLESPACE temp
  DATAFILE '/u01/db/tmp/SOL1_temp1.dbf' SIZE 100M REUSE
  AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
  DEFAULT STORAGE (INITIAL     1028K
                   NEXT        1028K
                   MINEXTENTS  2
                   MAXEXTENTS  UNLIMITED
                   PCTINCREASE 0)
 TEMPORARY
  ONLINE;