Oracle : Comment optimiser la taille de db_file_multiblock_read_count

2 Octobre 2017

Dans des environnements Oracle, mais surtout dans des environnements Datawarehouse et Décisionnel, la valeur de db_file_multiblock_read_count peut avoir impact fort sur les performances d'une instance.

Mais à quoi sert donc ce paramètre ?

Dans sa définition la plus simple, on pourrait dire que pour une valeur de db_file_multiblock_read_count positionnée à 64 (par exemple), l'optimiseur oracle (CBO) va partir du fait qu'une opération de FULL SCAN / INDEX FAST FULL SCAN sur une table va rapatrier 64 blocs en une passe au sein de son BUFFER CACHE.

L'objectif de ce paramètre est donc bel et bien de minimiser les I/Os réalisés sur les datafiles, mais cette valeur n'est pas une valeur à fixer arbitrairement.

Une mauvaise valeur affectée à ce paramètre peut avoir des conséquences importantes sur le fonctionnement d'une base oracle et en particulier sur les périodes de BATCH, ou encore de reporting décisionnel sur un Datawarehouse.

Note : A partir de la version 10GR2, ce paramètre peut être géré automatiquement par Oracle. La valeur sera fixée en fonction de la taille des I/Os et de la taille du bloc oracle, "DB_BLOCK_SIZE".

Ce n'est pas que je ne fais pas confiance à Oracle, mais il peut s'avérer qu'un réglage soit possible en fonction du contexte technique...

Voici donc une petite démonstration pour optimiser ce paramètre manuellement, et c'est valable depuis les versions oracle 9i.

Tout d'abord, on identifie la valeur fixée à ce paramètre :

SQL> select NAME,VALUE from v$parameter where NAME='db_file_multiblock_read_count';

NAME                           VALUE      
------------------------------ -----------
db_file_multiblock_read_count  64

J'ai donc un paramètre fixé à 64. Voyons voir ce que cela nous donne lors d'une opération de Full Scan sur une table, en prenant soin d'activer la trace oracle qui va bien :

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

Cela va permettre de générer un fichier trace qui sera stocké dans le répertoire diagnostic_dest de l'instance, ou bien dans le répertoire UDUMP pour les versions Oracle antérieures à 11G.

Je vais donc lancer une requête de count sur une big table (de préférence, car si la table est petite cela ne sera pas probant)

SQL> select count(*) from micka.bigtable;

  COUNT(*)
----------
 523155417

Allons donc récupérer le fichier trace qui résulte de cette requête afin de visualiser en détail les opérations qui ont été réalisées.

Dans mon cas ce fichier est dans le répertoire TRACE du repository ADRCI de mon instance (../diag/rdbms/mybase/MYBASE/trace).

En filtrant sur le wait "db file scattered read" cela nous donne :

WAIT #140202478319312: nam='db file scattered read' ela= 5711 file#=33 block#=77889 blocks=63 obj#=75252 tim=941939498531
WAIT #140202478319312: nam='db file scattered read' ela= 4089 file#=32 block#=73921 blocks=63 obj#=75252 tim=941939509683
WAIT #140202478319312: nam='db file scattered read' ela= 6730 file#=34 block#=73473 blocks=63 obj#=75252 tim=941939523327
WAIT #140202478319312: nam='db file scattered read' ela= 3468 file#=33 block#=77953 blocks=63 obj#=75252 tim=941939533399
WAIT #140202478319312: nam='db file scattered read' ela= 4066 file#=32 block#=73985 blocks=63 obj#=75252 tim=941939544505
WAIT #140202478319312: nam='db file scattered read' ela= 2628 file#=33 block#=80194 blocks=62 obj#=75252 tim=941940345424
WAIT #140202478319312: nam='db file scattered read' ela= 1997 file#=32 block#=75778 blocks=64 obj#=75252 tim=941940352292
WAIT #140202478319312: nam='db file scattered read' ela= 1894 file#=32 block#=75842 blocks=64 obj#=75252 tim=941940359686
WAIT #140202478319312: nam='db file scattered read' ela= 1912 file#=32 block#=75906 blocks=64 obj#=75252 tim=941940367583
WAIT #140202478319312: nam='db file scattered read' ela= 1903 file#=32 block#=75970 blocks=64 obj#=75252 tim=941940374978

On identifie bien que le maximum de blocs parcourus est bien égal à la valeur de db_file_multiblock_read_count soit 64.

Afin de voir quelle est la valeur maximale possible, je vais positionner ce paramètre à une valeur plus importante (512), et recommencer le test afin de vérifier combien de blocs au maximum peuvent être parcourus en une passe :

SQL> alter system set db_file_multiblock_read_count=512 scope=both;
System altered.

un petit coup de flush buffer cache car je vais interroger la même table :

SQL> alter system flush buffer_cache;
System altered.

Je relance le count et vais ensuite récupérer la trace comme précédemment :

SQL> select count(*) from micka.bigtable;
  COUNT(*)
----------
 523155417

WAIT #140037889094560: nam='db file scattered read' ela= 31392 file#=33 block#=79234 blocks=128 obj#=75252 tim=943742604157
WAIT #140037889094560: nam='db file scattered read' ela= 29142 file#=33 block#=79362 blocks=128 obj#=75252 tim=943742643740
WAIT #140037889094560: nam='db file scattered read' ela= 30992 file#=33 block#=79490 blocks=128 obj#=75252 tim=943742684455
WAIT #140037889094560: nam='db file scattered read' ela= 60983 file#=32 block#=80386 blocks=128 obj#=75252 tim=943749310624
WAIT #140037889094560: nam='db file scattered read' ela= 58949 file#=32 block#=80514 blocks=128 obj#=75252 tim=943749377942
WAIT #140037889094560: nam='db file scattered read' ela= 55458 file#=32 block#=80642 blocks=128 obj#=75252 tim=943749442444

Dans cette trace, on visualise bien que 128 blocs maximum sont parcourus en une passe. Sachant que nous avions fixé db_file_multiblock_read_count à 512, on peut désormais le fixer à la valeur de 128 qui correspond au maximum possible.

SQL> alter system set db_file_multiblock_read_count=128 scope=both ;
System altered.

SQL> sho parameter multi
NAME                              TYPE                         VALUE
--------------------------        ---------------------------- ---------------------
db_file_multiblock_read_count        integer                   128

Et on oublie pas de désactiver notre trace ;-)

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.

Attention cependant au sizing du BUFFER_CACHE, qui pourrait être amené à être lui aussi revu en fonction des modifications réalisées sur DB_FILE_MULTIBLOCK_READ_COUNT, soit en augmentant la MEMORY_TARGET / MEMORY_MAX_TARGET, soit la SGA_TARGET si AMM n'est pas configuré.

Mais bien sur, en fonction de la taille mémoire allouée à l'instance, il se peut qu'aucun réglage ne soit à faire.

Enjoy ;-)

Mickael