Blog

le 15/12/2012
Thomas Bordet
Archives blog-arkzoyd @fr

db_file_multiblock_read_count selon le CBO

Dans sa présentation dédiée aux CBO et Exadata, Maria Colgan indique que, par défaut et depuis la version 8.1.6, l’optimiseur considère que la valeur de db_file_multiblock_read_count est 8. Et on a envie de dire, je le savais puisque, comme tout le monde, je lis la documentation et qu’il est écrit « However, if db_file_multiblock_read_count is not set or is set to 0 (zero), then the optimizer uses a value of 8 for costing. » dans Oracle® Database Performance Tuning Guide 11g Release 2 (11.2) – 13 Managing Optimizer Statistics – 13.4.1.2 Multiblock Read Count.

En fait, c’est le genre de chose que vous savez tout en l’ignorant. Imaginez que l’optimiseur se soit mis à évaluer les coûts avec 128 un beau jour entre la 8i et la 9i ou plutôt en mettant à jour votre système. Vous vous en souviendriez ! Evidemment avec Exadata. Je vous laisse imaginer là où elle vous emmène. Et comme souvent, la réponse est « il y a un patch » à moins que ce soit 12c.

Mais prenons un chemin qui n’a rien à voir avec celui de Maria pour discuter de 2 ou 3 points contre-intuitifs à propos de ce paramètre ou de ce qui tourne autour…

Note:
Les tests réalisés ci-dessous s’appuient sur Oracle 11.2.0.3 sur Oracle Linux 5.8 x64. Les résultats peuvent différer avec les versions ou le paramétrage.

Environnement de test

Pour commencer, il faut un environnement de test ; pour cela, créez une base de données avec les paramètres par défaut ; créez une table T1 et examinez le plan de la requête select count(col2) from t1 :

create table T1(id number, 
                col2 varchar2(4000)) 
                tablespace users;

insert into T1 
   (select rownum, 
          rpad('X',1000) 
      from dual 
    connect by level <=200000);

commit;

exec dbms_stats.gather_table_stats(user, 'T1') 

explain plan for select count(col2) from t1;

set tab off
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  1001 |  8007   (1)| 00:01:37 |
|   1 |  SORT AGGREGATE    |      |     1 |  1001 |            |          |
|   2 |   TABLE ACCESS FULL| T1   |   200K|   190M|  8007   (1)| 00:01:37 |
---------------------------------------------------------------------------

Pour fixer les idées, vous pouvez réaliser une trace 10053, et vous constaterez que Maria a raison :

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using NOWORKLOAD Stats
  CPUSPEEDNW: 928 millions instructions/sec (default is 100)
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM:  10 milliseconds (default is 10)
  MBRC:       NO VALUE blocks (default is 8)

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 200000  #Blks:  29477  AvgRowLen:  1006.00  ChainCnt:  0.00
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T1[T1]
  Table: T1  Alias: T1
    Card: Original: 200000.000000  Rounded: 200000  Computed: 200000.00  Non Adjusted: 200000.00
  Access Path: TableScan
    Cost:  8006.90  Resp: 8006.90  Degree: 0
      Cost_io: 7985.00  Cost_cpu: 243918687
      Resp_io: 7985.00  Resp_cpu: 243918687
  Best:: AccessPath: TableScan
         Cost: 8006.90  Degree: 1  Resp: 8006.90  Card: 200000.00  Bytes: 0

***************************************

Et pourtant, la valeur par défaut du paramètre associé est bien 128 sur la plateforme comme le montre la requête ci-dessous :

show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     128

Paramètre vs valeur par défaut

Modifiez la valeur du paramètre pour lui fixer sa « valeur par défaut », c’est à dire dans ce cas 128. On pourrait s’attendre à ce que rien ne change puisque cette valeur est la valeur par défaut sur la plateforme ; pourtant le coût de l’accès à la table change :

alter session set db_file_multiblock_read_count=128;

explain plan for select count(col2) from t1;

set tab off
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  1001 |  5128   (1)| 00:01:02 |
|   1 |  SORT AGGREGATE    |      |     1 |  1001 |            |          |
|   2 |   TABLE ACCESS FULL| T1   |   200K|   190M|  5128   (1)| 00:01:02 |
---------------------------------------------------------------------------

A y regarder de près dans la trace 10053, le changement est bien lié au positionnement de votre paramètre qui fait passer la valeur par défaut de 8 à 128 :

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using NOWORKLOAD Stats
  CPUSPEEDNW: 928 millions instructions/sec (default is 100)
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM:  10 milliseconds (default is 10)
  MBRC:       NO VALUE blocks (default is 128)

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 200000  #Blks:  29477  AvgRowLen:  1006.00  ChainCnt:  0.00
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T1[T1]
  Table: T1  Alias: T1
    Card: Original: 200000.000000  Rounded: 200000  Computed: 200000.00  Non Adjusted: 200000.00
  Access Path: TableScan
    Cost:  5127.90  Resp: 5127.90  Degree: 0
      Cost_io: 5106.00  Cost_cpu: 243918687
      Resp_io: 5106.00  Resp_cpu: 243918687
  Best:: AccessPath: TableScan
         Cost: 5127.90  Degree: 1  Resp: 5127.90  Card: 200000.00  Bytes: 0

***************************************

db_multiblock_read_count vs MBRC

Dans sa présentation, Maria recommande de ne pas collecter, en général, les statistiques système ce qui a presque fait tomber de sa chaise mon voisin ;-). L’exemple précédent illustre que l’utilisation du paramèt
re impacte potentiellement les coûts et donc les plans. Ça peut d’ailleurs être un problème dans la mesure où ce paramètre à un impact sur la taille des I/O qui n’a rien avoir avec l’optimiseur. Alors comment jouer indépendamment sur ces 2 critères ? La réponse est évidemment l’utilisation de la statistique système MBRC que le script ci-dessous fixe à 8 :

alter session set db_file_multiblock_read_count=128;

select PNAME, PVAL1 
  from sys.aux_stats$ 
 where pname='MBRC';

PNAME                               PVAL1
------------------------------ ----------
MBRC

exec dbms_stats.set_system_stats('MBRC','8');

select PNAME, PVAL1 
  from sys.aux_stats$ 
 where pname='MBRC';

PNAME                               PVAL1
------------------------------ ----------
MBRC                                    8

explain plan for select count(col2) from t1;

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  1001 |  8007   (1)| 00:01:37 |
|   1 |  SORT AGGREGATE    |      |     1 |  1001 |            |          |
|   2 |   TABLE ACCESS FULL| T1   |   200K|   190M|  8007   (1)| 00:01:37 |
---------------------------------------------------------------------------

En prenant la trace 10053, vous constatez effectivement l’effet de la modification. La valeur de 8 est prise, même si la valeur par défaut est toujours 128 :

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using NOWORKLOAD Stats
  CPUSPEEDNW: 928 millions instructions/sec (default is 100)
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM:  10 milliseconds (default is 10)
  MBRC:       8 blocks (default is 128)

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 200000  #Blks:  29477  AvgRowLen:  1006.00  ChainCnt:  0.00
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T1[T1]
  Table: T1  Alias: T1
    Card: Original: 200000.000000  Rounded: 200000  Computed: 200000.00  Non Adjusted: 200000.00
  Access Path: TableScan
    Cost:  8006.90  Resp: 8006.90  Degree: 0
      Cost_io: 7985.00  Cost_cpu: 243918687
      Resp_io: 7985.00  Resp_cpu: 243918687
  Best:: AccessPath: TableScan
         Cost: 8006.90  Degree: 1  Resp: 8006.90  Card: 200000.00  Bytes: 0

***************************************

Autre effet de db_file_multiblock_read_count

La documentation associée à db_file_multiblock_read_count indique que le paramètre à un impact sur la taille des I/O. Pour vous en persuader, vous pouvez utiliser la commande strace sous Linux et exécuter la requête précédente ; vous repèrerez assez facilement :

si db_file_multiblock_read_count=128 et que votre taille de block est 8k, des appels système comme celui ci-dessous :

io_getevents(140693457436672, 1, 128, {{0x7ff5bdc7fe00, 0x7ff5bdc7fe00, 1048576, 0}}, {600, 0}) = 1

si db_file_multiblock_read_count=8 et que votre taille de block est 8k, des appels système comme celui ci-dessous :

io_getevents(140693457436672, 2, 128, {{0x7ff5bdc6fe00, 0x7ff5bdc6fe00, 65536, 0}}, {0, 0}) = 1

Dans les 2 cas capturés, les valeurs en rouge indiquent la taille des I/O, soit dans le premier cas, 1Mo et 64Ko dans le second cas. Il s’agit ici d’appels asynchrones mais vous pourrez facilement faire le parallèle avec des I/O synchrones.

Conclusion

Pour finir avec votre test, vous aurez surement envie de remettre vos paramètres à leur valeur par défaut, en supposant qu’ils n’avaient pas été modifiés précédemment :

exec dbms_stats.delete_system_stats
select pname, pval1 
  from sys.aux_stats$
 where pname='MBRC';

PNAME                               PVAL1
------------------------------ ----------
MBRC

exit;

Et si vous cherchez à retenir quelque chose de cet article, dites-vous juste qu’Il vaut mieux y réfléchir à 2 fois… et quand bien même !

Thomas Bordet

Voir tous ses articles

5 réflexions sur “db_file_multiblock_read_count selon le CBO”

Michel Stevelinck
06/03/2013 à 09:42

Bonjour. Merci pour votre blog.

Je suppose que placer le paramètre système de la DB _db_file_optimizer_read_count =8 est (presque) la même chose que d’écrire :
exec dbms_stats.set_system_stats(‘MBRC’,’8′);
C’est personnellement ce que je fais. Je laisse croire à l’Optimizer qu’il est à 8 alors qu’il est à 128 (sous Linux) pour le db_file_multiblock_read_count

Sinon l’Optimizer a l’air tellement content des performances des IOs qu’il favorise un peu trop le Full Table Scan. C’est un peu logique mais ça ne donne pas toujours des performances au top.

Pour en rajouter une couche Oracle propose également un autre paramètre « to confuse the enemy » :
_db_file_exec_read_count (MBRC pour l’execution)

Voir la petite Storyteller ici : http://dioncho.wordpress.com/tag/_db_file_optimizer_read_count/
Grégory Guillou
19/12/2012 à 08:38

Oui 1M, par défaut et si on met les redo de côté, c’est sans doute après 8K, la taille la plus utilisée ; dans ASM ou dans les extents des tables allouées automatiquement par le système.
Ahmed AANGOUR
18/12/2012 à 22:26

Laisse tomber je crois que mon cerveau n’était pas réveillé 😉

Je ne sais pas pourquoi des extents de 1Mo me semblaient gros alors que pas du tout.

En créant la même table que la tienne sur ma base avec un tablespace en AUTOALLOCATE j’obtiens même des extents de 8Mo.
Grégory Guillou
18/12/2012 à 08:13

C’est un maximum. Pourquoi théorique ? Ici, le tablespace utilisé est alloué par le système. La table fait plus de 200Mo. les extents sont donc en immense majorité des multiples de 1Mo.

C’est vrai, j’ai raccourci cette section. Dans le cas que tu décris, d’un extend de 4Mo et avec mon paramétrage, Oracle ferait en effet 4 appels système de 1M. Selon la perspective, il se peut que ce soit 4 I/O ou pas. Ca dépend pas notamment du type d’attachement (cf max_sectors_kb), des caches ou du type de syscall. Sans oublier l’impact du système ou du stockage. Et puis, si c’était juste un maximum, pourquoi pas mettre 512 après tout ?

Ce qui m’intéressait, c’est l’impact de db_file_multiblock_read_count et/ou de la statistique système MBRC sur le cost t’un table access full et, par lien, sur le choix d’un plan plutôt qu’un autre. Cela dit, le sujet des I/O est loin d’être sans intérêt. Ca pourrait être le bon sujet d’un très bon livre
Ahmed AANGOUR
17/12/2012 à 23:01

Il me semblaite que le MBRC ne constituait qu’un maximum théorique. Et qu’en pratique, le moteur oracle ne pouvait pas récupérer en un mullti-block read count plus de blocks qu’il n’y en a dans un extent.

Donc pour moi si par exemple l’extent fait 32 blocks et que le MBRC est à 128 il faudrait 4 I/Os (128/32) pour récupérer 128 blocks d’une table.

Du coup je suis troublé de voir que ta commande strace montre une taille d’I/O de 1MB lorsque le MBRC est à 128.
Quelle est la taille de ton extent?

Les commentaires sont fermés.