APPEND NOLOGGING : accélérer les insertions

Pour les chargements massifs, il peut être intéressant d'utiliser certaines options qui permettent d'accélérer le temps de traitement. Bien sûr ces options ont des contre-parties.

L'option APPEND pour l'insert force Oracle à passer directement au-delà du HWM (High Water Mark) pour insérer les nouvelles lignes. Elle évite ainsi la recherche des blocks libres et réduit fortement le nombre de blocks à sauvegarder dans les UNDO. Mais elle conduit à une éventuelle perte d'espace disque.

L'option NOLOGGING indique à Oracle qu'il n'est pas nécessaire de logguer dans les REDO les changements qui ont lieu dans la table. Il s'ensuit une écriture moins importante dans les REDO, mais également l'impossibilité de récupérer la table avec les nouvelles lignes en cas de restauration de cette dernière.

Exemple

Préparation :

SQL> CREATE TABLE toto AS
  2  SELECT rownum ID, dbms_random.string('A', 12) STR,
  3  trunc(dbms_random.value(1,1000)) NUM
  4  FROM all_objects
  5  WHERE rownum <= 200000
  6  ;

Table created.

SQL> INSERT INTO toto SELECT * FROM toto;

200000 rows created.
SQL> INSERT INTO toto SELECT * FROM toto;

400000 rows created.
SQL> COMMIT;

Commit complete.

Session 1 :

SQL> SET TIMING ON
SQL> INSERT INTO toto SELECT * FROM toto;

800000 rows created.

Elapsed: 00:00:31.04
SQL> ROLLBACK;

Rollback complete.

Elapsed: 00:00:02.09
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'undo change vector size';

NAME                                                       VALUE
----------------------------------------------------- ----------
undo change vector size                                  2270900
 

SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'rollback changes - undo records applied';

NAME                                                       VALUE
----------------------------------------------------- ----------
rollback changes - undo records applied                     6087

SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'redo size';

NAME                                                       VALUE
----------------------------------------------------- ----------
redo size                                               31320224

Session 2 :

SQL> SET TIMING ON
SQL> INSERT /*+ APPEND */ INTO toto SELECT * FROM toto;

800000 rows created.

Elapsed: 00:00:12.06

SQL> ROLLBACK;

Rollback complete.

Elapsed: 00:00:00.00
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'undo change vector size';

NAME                                                       VALUE
----------------------------------------------------- ----------
undo change vector size                                   258516

SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'rollback changes - undo records applied';

NAME                                                       VALUE
----------------------------------------------------- ----------
rollback changes - undo records applied                        0

SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'redo size';

NAME                                                       VALUE
----------------------------------------------------- ----------
redo size                                               25438660

Session 3 :

SQL> SET TIMING ON
SQL> INSERT /*+ APPEND NOLOGGING */ INTO toto SELECT * FROM toto;

800000 rows created.

Elapsed: 00:00:04.01

SQL> ROLLBACK;

Rollback complete.

Elapsed: 00:00:00.00
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'undo change vector size';

NAME                                                       VALUE
----------------------------------------------------- ----------
undo change vector size                                      208

SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'rollback changes - undo records applied';

NAME                                                       VALUE
----------------------------------------------------------------
rollback changes - undo records applied                        0
 
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'redo size';

NAME                                                       VALUE
----------------------------------------------------- ----------
redo size                                               24488552

en résumé :

APPEND   NOLOGGING  INSERT(sec)  UNDO_VECTOR  REDO_SIZE

     N           N           31      2270900   31320224

     Y           N           12       258516   25438660

     Y           Y            4          208   24488552