Optimiser le DB_FILE_MULTIBLOCK_READ_COUNT

Dimanche 29 août 2010 7 29 /08 /Août /2010 12:19

Optimiser le DB_FILE_MULTIBLOCK_READ_COUNT

Optimiser le paramètre DB_FILE_MULTIBLOCK_READ_COUNT

La valeur de db_file_multiblock_read_count a un impact sur les performances de l'instance. L'optimiseur s'appuit sur ce paramètre qui controle le nombre de block pré-chargés dans le buffer cache durant des FULL TABLE SCAN et INDEX FAST FULL SCAN.

A noter qu'à partir de la version 10G R2, ce paramètre peut être gérée automatiquement par Oracle. Il choisira la valeur en fonction de la taille des I/O et du cache buffer OS et des disques. Ne définissez pas ce paramètre explicitement si vous voulez qu' Oracle le gère pour vous.

Voici une petite démonstration sur comment définir, optimiser le paramètre db_file_multiblock_read_count manuellement à partir de la version 9.2 d'Oracle.

Voir la valeur de notre paramètre initial.

SQL> show parameter db_file_multiblock_read_count

NAME	TYPE	VALUE
db_file_multiblock_read_count	integer	16

Nous activons le traçage de l'Events Level 8.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

Un fichier trace sera généré dans le dossier UDUMP.(show parameter user_dump_dest)

Effectuons une requête sur une grande table de préférence.

SQL> SELECT COUNT(*) FROM MaTable ;

Maintenant nous allons voir le fichier trace dans UDUMP.

WAIT #27: nam='db file scattered read' ela= 2009 file#=5 block#=492349 blocks=16 obj#=52822
WAIT #27: nam='db file scattered read' ela= 1841 file#=5 block#=492365 blocks=16 obj#=52822
WAIT #27: nam='db file scattered read' ela= 3650 file#=5 block#=492381 blocks=16 obj#=52822
WAIT #27: nam='db file scattered read' ela= 1945 file#=5 block#=492397 blocks=16 obj#=52822

Sous Oracle 9.2 le nombre de block lus sera identifié par p3=16, sous Oracle 10G c'est blocks=16 pour l'évènement 'db file scattered read'. On s'aperçoit ici que le nombre de block lus est bien de 16 (comme notre paramètre initial), mais est-ce que ce nombre est le paramètre optimal pour notre instance ?

Nous allons changer le paramètre db_file_multiblock_read_count.

SQL> ALTER SYSTEM SET db_file_multiblock_read_count=512;

Nous mettons volontairement une valeur haute.
Nous relançons notre requête sur une grande table.
Faite un ALTER SYSTEM FLUSH BUFFER_CACHE si vous interrogez toujours la même table.

Retournons voir le fichier trace dans UDUMP.

WAIT #25: nam='db file scattered read' ela= 28381 file#=5 block#=492301 blocks=124 obj#=52822
WAIT #25: nam='db file scattered read' ela= 30666 file#=5 block#=492427 blocks=126 obj#=52822
WAIT #25: nam='db file scattered read' ela= 26819 file#=5 block#=492555 blocks=126 obj#=52822
WAIT #25: nam='db file scattered read' ela= 29500 file#=5 block#=492683 blocks=126 obj#=52822

On peut voir ici que le nombre optimal de blocks lus est 126. Nous allons donc initialiser le paramètre db_file_multiblock_read_count à 128 et arrêter la trace Events.

SQL> ALTER SYSTEM SET db_file_multiblock_read_count=128;
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser

• Tracer une session avec DBMS_SUPPORT
• Tracer une session avec DBMS_MONITOR