Comment l'optimiseur d'Oracle calcule le coût

L'analyse des plans d'exécution des requêtes SQL met en évidence des valeurs numériques affichées dans des colonnes appelées Cost (coût) et Rows (lignes) pour chaque opération effectuée par l'optimiseur. S'il est évident que le meilleur plan devrait être celui qui a le moindre coût, les algorithmes utilisés ne sont pas toujours détaillés dans la documentation d'Oracle. Pourtant ils ont fait depuis des années l'objet des d'analyses minutieuses pour comprendre leur fonctionnement et les hypothèses sur lesquels ils sont basés. ♪

Article lu fois.

L'auteur

Marius NITU

L'article

Publié le 1^er février 2009 - Mis à jour le 14 février 2009

Version PDF Version hors-ligne

ePub, Azw et Mobi

Liens sociaux

I. Introduction▲

SQL est un langage non procédural et cela signifie que l'utilisateur du langage, c'est-à-dire le développeur des applications, spécifie en fait quelles données chercher et non pas l'algorithme exact à appliquer pour ramener ces données. Trouver le plan d'exécution d'une requête, c'est-à-dire la méthode la plus efficace pour exécuter la requête SQL est le rôle d'un composant logiciel appelé optimiseur. Au départ dans les premières versions d'Oracle l'approche prise dans le développement d'un optimiseur a été assez simpliste, mais, remarquablement efficace pour l'état des choses de l'époque, inventorier les méthodes d'accès aux données disponibles : balayage de la table, utilisation d'un index, utilisation d'un pointer (le Rowid), etc., et les classifier par ordre d'efficacité. Ainsi le plus efficace est d'accéder aux données par Rowid (pointer) et le moins par balayage complet de la table. D'autres règles concernent les algorithmes des jointures notamment le choix de la table directrice. Sans entrer dans les détails il est facile à comprendre que cet optimiseur est totalement stable : ses choix ne dépendent pas de la volumétrie des données, mais, plutôt de la disponibilité ou pas de certaines méthodes d'accès, en spécial les index. C'était une solution honorable qui fonctionnait bien dans pas mal de cas, mais, qui était aussi réputée pour générer de mauvais plans d'exécution surtout si plusieurs index composites pouvaient se qualifier pour l'exécution de la requête et de prendre la mauvaise table comme table directrice dans les jointures. À part l'intérêt historique qui permet de comprendre la raison des quelques vieux conseils d'optimisation difficilement justifiable dans l'état actuel des choses cet optimiseur ne présente plus d'intérêt, notamment parce que les nouvelles méthodes d'accès ou de jointures ne lui sont pas disponibles. Dans la suite seulement l'optimiseur basé sur le coût est analysé.

I-A. Composants de l'optimiseur▲

L'optimiseur est constitué de trois composants qui s'enchaînent les entrées- sorties [1]

Le transformateur de requête qui prend en entrée une requête parsée et il essaie de la transformer de telle façon qu'un meilleur plan d'exécution peut être généré, en employant des techniques de transformation de type : fusionner la requête avec une éventuelle vue (view merging), le déplacement des prédicats (predicate pushing), transformation des sous-interrogations en jointures (unnesting sub queries), etc.
L'estimateur qui pour la requête transformée génère trois types de mesure : sélectivité, cardinalité et coût.
Le générateur de plan qui explore plusieurs possibilités d'exécution de la requête et prend celle qui a le meilleur coût.

Le but de cet article est de donner un aperçu des algorithmes employés par l'estimateur pour calculer la sélectivité, les cardinalités et le coût en explorant quelques différences entre les versions 9i, 10g et 11 d'Oracle. L'optimiseur est une composante logicielle extrêmement complexe et qui connaît des améliorations et modifications constantes d'une version à l'autre. Donner les détails précis de son fonctionnement dans diverses situations est une œuvre assez complexe qui dépasse largement le cadre de ce tutoriel. Le lecteur intéressé par ce sujet est invité à étudier [2].

I-B. Sélectivité, cardinalité, coût▲

La sélectivité représente une partie des lignes d'un ensemble des lignes (tables, vue, résultat d'une jointure ou d'un opérateur GROUP BY) [1]. La notion de sélectivité est liée à la notion de prédicat ou de combinaison des prédicats de la requête par exemple dans « colonne = valeur ». La sélectivité d'un prédicat indique combien de lignes d'un ensemble des lignes passent le test de filtrage du prédicat. Les valeurs de la sélectivité sont dans le domaine 0.0 à 1.0 où 0.0 signifie qu'aucune ligne ne sera sélectionnée et 1.0 que toutes les lignes seront sélectionnées. Le calcul de la sélectivité est basé sur les statistiques des objets à accéder. Si ces statistiques ne sont pas disponibles, l'estimateur utilise pour ses calculs des valeurs implicites en Oracle9 et en plus l'échantillonnage dynamique à partir d'Oracle 10g (Le paramètre qui contrôle l'échantillonnage dynamique est présent déjà en Oracle9i, mais sa valeur est 1 par rapport à la valeur 2 en Oracle 10g).

La cardinalité représente le nombre des lignes d'un ensemble des lignes (tables, vue, etc.) [1]

Le coût représente des unités de travail ou des ressources utilisées pour exécuter la requête [1]. Le nombre des opérations d'entrée/sortie de disque, l'utilisation du CPU et de la mémoire représentent des unités de travail. Le chemin d'accès détermine le nombre des unités de travail nécessaires à l'obtention des données de la table. De cette façon le coût d'accès d'un balayage de la table dépend du nombre des blocs à lire et de la valeur d'un paramètre qui indique le nombre des blocs lus dans une opération de lecture E/S multiblocs. Le coût d'accès via un index dépend du nombre de niveaux d'index, de nombre des blocs feuille qu'il faut balayer et du nombre de lignes ramenées en utilisant le pointer (rowid) associé aux clefs d'index. Le coût d'une jointure est une combinaison des coûts d'accès individuels plus le coût de l'opération de jointure : nested loop, sort merge joint ou hash join.

I-C. Le paramètre OPTIMIZER_MODE▲

Il y a en fait trois variants de l'optimiseur basés sur le coût [2] identifiés par la valeur du paramètre OPTIMIZER_MODE :

ALL_ROWS : l'optimiseur essaie de trouver le plan d'exécution qui a le meilleur temps de réponse global
FIRST_ROWS_N : l'optimiseur essaie de trouver le plan d'exécution qui permet de ramener le plus rapidement possible les N premières lignes où N prend les valeurs 1, 10, 100 ou 1000.
FIRST_ROWS : l'optimiseur essaie de trouver le plan d'exécution qui permet de ramener le plus rapidement possible le premier enregistrement, mais, en utilisant un mélange de coût et heuristique.

Le but de chaque variante est toujours le même : trouver le meilleur plan d'exécution, mais dû aux règles différentes implémentées dans l'optimiseur le résultat n'est pas identique. Par exemple en mode FIRST_ROWS_N l'optimiseur va favoriser l'utilisation d'un index pour une même requête qui en mode ALL_ROWS pourrait être exécutée avec un balayage de la table.

Les autres valeurs possibles : CHOOSE, choix de l'optimiseur coût ou basée sur les règles en fonction de la disponibilité des statistiques pour les tables et index utilisés et RULE, optimiseur basée sur les règles sont disponibles pour compatibilité antérieure et ne doivent pas être utilisées normalement à partir d'Oracle 9. Par défaut en Oracle10g, le but de l'optimiseur est le meilleur temps de réponse global.

II. Les relations entre prédicats et sélectivité▲

Dans les plans d'exécution, Oracle fait apparaître les filtres utilisés pour calculer le nombre des lignes ramenées. La théorie de probabilités nous enseigne que si nous jetons un dé et nous ne posons la question de savoir combien de chances il y en a d'obtenir la valeur 6, la réponse est de 1/6 ce qui représente 1 divisé par le nombre des valeurs distinctes que le dé peut avoir. D'une manière analogue, on peut supposer que le nombre des lignes ramenées par une requête qu'utilise un prédicat de type « col = 6 » pour une table où il y a seulement six valeurs distinctes pour cette colonne, sera de 1/6 multiplié par le nombre des lignes de la table. Cette fraction : 1 divisé par le nombre des valeurs distinctes de la colonne, est précisément la valeur du facteur de filtrage pour une requête utilisant un prédicat qui implique une seule colonne avec condition d'égalité :

Sélectionnez

FF = 1/NDV = densité

où NDV est le nombre des valeurs distinctes de la colonne.

Les relations entre le Facteur de Filtrage et les prédicats sont [4] :

Sans variable de liaison, sans histogrammes et si les colonnes sont déclarées non nulles (T1)

Prédicat	Facteur de Filtrage
C1 = valeur	c1.density
C1 like valeur	c1.density ^(*)
C1 > valeur	(Hi - valeur) / (Hi - Low)
C1 >= valeur	(Hi - valeur) / (Hi - Low) + 1 / c1.num_distinct
C1 < valeur	(Valeur - Low) / (Hi - Low)
C1 <= valeur	(Valeur - Low) / (Hi - Low) + 1 / c1.num_distinct
C1 between val1 and val2	(Val2 - Val1) / (Hi - Low) + 2 * 1 / c.num_distinct

Avec variables de liaison (T2)

Prédicat	Facteur de Filtrage
C1 = :b1	c1.density
C1 {like \| < \| <= \|> \| >=} :b1	{ 5.0e-02 \|c1.density }
C1 between :b1 and :b2	5.0e-02 * 5.0e-0.2

La combinaison des prédicats (T3)

Prédicat	Facteur de Filtrage
prédicat1 and prédicat2	FF1 * FF2
prédicat1 or prédicat2	FF1 + FF2 - FF1 * FF2

où density, high_value (Hi), low_value (Low), num_distinct sont des colonnes de la vue (dba|all|user)_tab_columns.

(*) En fait plusieurs formules ont été observées pour ce cas. Col like '%' signifie toutes les lignes et l'optimiseur utilisent la statistique NumRows. Col like 'A%' est souvent interprété comme col >= 'A' and col < 'B', etc. Avec l'augmentation des caractères de la chaîne littérale les deux formules donnent le même résultat.

III. L'équation de calcul du coût▲

L'équation utilisée pour estimer le coût est [2], [3] :

Sélectionnez

(1) cost = (#SRDS * sreadtm + 
            #MRDS * mreadtm +
            #CPUCycles/cpuspeed) / sreadtm

où

#SRDS est le nombre des lectures E/S en mode mono bloc ;
sreadtm est le temps nécessaire à la lecture d'un bloc ;
#MRDS est le nombre des lectures en mode multibloc ;
mreadtm est le temps de lecture en mode multibloc ;
CPUCycles est le nombre de cycles CPU ;
cpuspped est le nombre de cycles CPU par seconde.

Cette équation peut être réarrangée pour donner

Sélectionnez

(1a) cost = #SRDS  + 
            #MRDS * mreadtm / sreadtm +
            #CPUCycles / (cpuspeed * sreadtm)

Le modelé de coût impliqué par l'équation (1) a été introduite en Oracle 9 et il reste valable pour Oracle 10g et 11 quoi que des différences subtiles existent entre les versions. Avant Oracle 9, l'optimiseur ne faisait pas de distinction entre la lecture en mode monobloc et la lecture en mode multibloc et ne prenait pas en compte le fait que la lecture des données consomme du temps CPU. En Oracle 8 et avant l'équation se réduisait à sa forme la plus simple [3]

Sélectionnez

(1b) cost = #RDS

où RDS inclut les lectures en mode monobloc et multibloc sans distinction.

En dépit du fait que l'équation est restée la même le comportement de l'optimiseur a changé d'une version à l'autre. Ainsi, en Oracle8 ou Oracle9i sans le calcul des statistiques système c'est l'ancienne équation (1b) qui est employée. À partir d'Oracle9i une fois que les statistiques système ont été calculées et à partir d'Oracle 10g c'est toujours l'équation (1a) qui est utilisée. En fait en Oracle 10g et 11 les statistiques suivantes sont initialisées du démarrage de la base [1] :

ioseektime : temps de recherche d'un bloc sur le disque en ms, valeur par défaut 10 ;
iotfrspeed : la vitesse de transfert de données en Bytes par ms, valeur par défaut 4096 ;
cpuspeedNW : le nombre moyen de cycles CPU par seconde, valeur par défaut qui dépend du système ;
MBRC : le nombre des blocs lus en moyenne dans une opération E/S en mode multibloc.

Les valeurs de ces statistiques sont ensuite utilisées pour calculer les composants nécessaires à l'équation de coût [2] :

Sélectionnez

    sreadtm  = ioseektm + db_block_size / iotfrspeed
(2) mreadtm  = ioseektm + MBRC * db_block_size / iotfrspeed
    cpuspeed = cpuspeedNW

IV. Étude de cas : balayage complet de la table▲

IV-A. Calcul du coût▲

Le jeu d'essai proposé est le suivant :

Sélectionnez

DROP TABLE bigemp
/
DROP TABLE bigdept
/
CREATE TABLE bigemp (
  empno PRIMARY KEY, ename, job, mgr, hiredate, sal, comm, deptno
) 
pctfree 99
pctused 1
AS
SELECT empno+x AS empno, ename, job, mgr, hiredate, sal, comm, deptno+x AS     deptno
  FROM emp
CROSS JOIN (SELECT level*1000 AS x FROM dual CONNECT BY level <= 1000)
/
CREATE TABLE bigdept (
  deptno PRIMARY KEY, dname, loc
) 
pctfree 99
pctused 1
AS
SELECT deptno+x AS deptno, dname, loc
  FROM dept
CROSS JOIN (SELECT level*1000 AS x FROM dual CONNECT BY level <= 1000)
/

les statistiques étant calculées de la manière suivante :

Sélectionnez

exec dbms_stats.gather_table_stats(user, 'BIGEMP', cascade=>true)
exec dbms_stats.gather_table_stats(user, 'BIGDEPT', cascade=>true)

Quelques remarques à faire : comme prérequis le jeu d'essai se base sur des tables et les données d'un ancien jeu d'essai d'Oracle créé par le script demobld.sql. Il y a au départ 14 enregistrements dans la table emp et 4 dans la table dept. Les tables BIGEMP et BIGDEPT contienznt donc 1000 fois plus d'enregistrements. Les tables du jeu d'essai déterminent l'allocation d'un nombre significatif des blocs dû aux valeurs inhabituelles des paramètres pctfree et pctused employés. [2]

Les autres paramètres sont :

optimizer_mode=ALL_ROWS ;
optimiser_dynamic sampling = 1 (Oracle9i) et 2 (Oracle 10g et 11) ;
optimizer_index_caching = 0 ;
optimizer_index_cost_adj = 100 ;
db_block_size = 8 ;
db_file_multiblock_read_count = 32 (Oracle9i) et 128 (Oracle 10 et 11).

Au départ les statistiques système ne sont pas calculées.

Nous allons commencer avec une base Oracle 9.2 et les requêtes suivantes :

Sélectionnez

SQL> truncate table plan_table;
 
Table tronquée.
 
SQL> explain plan set statement_id='BIGEEMPaSsF' for Select * From bigemp;
 
Explicité.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             | 14000 |   546K|   864 |
|   1 |  TABLE ACCESS FULL   | BIGEMP      | 14000 |   546K|   864 |
--------------------------------------------------------------------
 
Note: cpu costing is off
 
9 ligne(s) sélectionnée(s).
SQL> explain plan set statement_id='BIGDEPTaSsF' for Select * From bigdept;
 
Explicité.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  4000 | 84000 |   126 |
|   1 |  TABLE ACCESS FULL   | BIGDEPT     |  4000 | 84000 |   126 |
--------------------------------------------------------------------
 
Note: cpu costing is off
 
9 ligne(s) sélectionnée(s).
 
SQL> Select table_name, num_rows, blocks, avg_row_len
  From user_tables
 Where table_name in ('BIGEMP','BIGDEPT');
  2    3
TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
BIGDEPT                              4000       2040          21
BIGEMP                              14000      14159          40

Première chose à remarquer est la note d'en bas de l'affichage du plan d'exécution, concernant la non-prise en compte du modèle de coût associé à l'équation (1). Donc dans le calcul du coût c'est l'équation (1b) qui est utilisée.

Sélectionnez

(1b) cost = #RDS

Il est évident que la valeur affichée dans la zone Rows du plan d'exécution provient de la colonne Num_Rows de la vue user_tables. En examinant les colonnes Blocks de la vue user_tables et Cost du plan d'exécution, on retrouve le fait que le coût dépend du nombre de blocs lus. En fait la formule employée est :

Sélectionnez

(1c) cost = #RDS = Blocs / K + 1

où K dépend du paramètre db_file_multiblock_read_count. Dans ce cas db_file_multiblock_read_count a la valeur 32 et donc K prend la valeur 16,41 [3], la valeur du facteur K étant calculée d'une manière empirique.

Faisons les calculs.

Pour BIGEMP le nombre des blocs est 14159

Sélectionnez

cost = 14159 / 16.41 = 862,82

Si on arrondit à l'entier immédiatement supérieur : 863 et on ajoute 1 on retrouve le coût 864. La valeur 1 correspond au paramètre caché _table_scan_cost_plus_one qui a la valeur vraie en Oracle9 et faux en Oracle 8 (en conséquence la même requête en Oracle8 aura comme coût 863).

La table BIGDEPT

Sélectionnez

cost = 2040 / 16,41 = 124,31 donc arrondi 125 + 1 = 126

Pour une valeur de paramètre db_file_multiblock_read_count égal à 16, K prend la valeur 10.40 [3] et donc le coût de la requête sur la table BIGEMP devrait être :

Sélectionnez

cost = 14159 / 10,40 = 1361,44 donc arrondi 1362 + 1 = 1363

Faisons la vérification :

Sélectionnez

SQL> alter session set db_file_multiblock_read_count=16;
 
Session modifiée.
 
SQL> explain plan set statement_id='BIGEMP16' for select * from bigemp;
 
Explicité.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             | 14000 |   546K|  1363 |
|   1 |  TABLE ACCESS FULL   | BIGEMP      | 14000 |   546K|  1363 |
--------------------------------------------------------------------
 
Note: cpu costing is off
 
9 ligne(s) sélectionnée(s).

Il est intéressant d'examiner le contenu de la table plan_table à ce moment :

Sélectionnez

SQL> select statement_id, id, cost, cpu_cost, io_cost
  2  from plan_table;
 
STATEMENT_ID                           ID       COST   CPU_COST    IO_COST
------------------------------ ---------- ---------- ---------- ----------
BIGEEMPaSsF                             0        864                   864
BIGEEMPaSsF                             1        864                   864
BIGDEPTaSsF                             0        126                   126
BIGDEPTaSsF                             1        126                   126
BIGEMP16                                0       1363                  1363
BIGEMP16                                1       1363                  1363
 
6 ligne(s) sélectionnée(s).

Remarquons que la colonne CPU_COST est vide ce qui met en évidence que seulement le nombre des lectures E/S, colonne IO_COST, contribue à la constitution du coût.

Il est également utile d'examiner cette même requête avant le calcul des statistiques sur les deux tables :

Sélectionnez

SQL> explain plan set statement_id='BIGEMPsansStat' for Select * From bigemp;
 
Explicité.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  1156K|    95M|   864 |
|   1 |  TABLE ACCESS FULL   | BIGEMP      |  1156K|    95M|   864 |
--------------------------------------------------------------------
 
Note: cpu costing is off
 
9 ligne(s) sélectionnée(s).

Le coût est le même en dépit du fait que la colonne Blocks de la vue user_tables est nulle à ce moment, mais le mystère s'explique peut-être en interrogeant la colonne Blocks de la vue user_segments. Paradoxalement c'est en absence des statistiques que l'estimation du nombre des blocs est la plus proche de la réalité si on prend en compte que les statistiques peuvent être périmées. Par contre, le nombre des lignes ramenées, c'est-à-dire la cardinalité, est bien loin de la réalité, étant presque 100 fois plus importante, sans doute dû au nombre important des blocs qui composent les 2 tables. Et c'est bien ce type d'erreur d'estimation qui conduit très souvent à un mauvais plan d'exécution. C'est donc la raison principale d'un conseil d'optimisation qui demande de s'assurer avant toute action que les statistiques pour les objets accédés sont à jour, surtout pour une base Oracle9.

À partir de la version 10g, les choses se sont nettement améliorées :

Sélectionnez

SQL> explain plan set statement_id='BIGEEMPsSsF' for Select * From bigemp;
 
Explicité.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
Plan hash value: 2368838273
 
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 13934 |  1183K|  3853   (1)| 00:00:47 |
|   1 |  TABLE ACCESS FULL| BIGEMP | 13934 |  1183K|  3853   (1)| 00:00:47 |
----------------------------------------------------------------------------
 
Note
-----
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
   - dynamic sampling used for this statement
 
12 ligne(s) sélectionnée(s).

l'explication étant donnée par la remarque d'en bas du plan d'exécution : l'échantillonnage dynamique. En fait le paramètre optimizer_dynamic_sampling a la valeur 1 comme valeur par défaut en Oracle 9, mais, 2 en Oracle 10 [1] ce qui signifie en effet l'application d'une politique d'échantillonnage plus agressive. Remarquez comme le nombre de lignes estimées 13934 est assez proche de la réalité 14000. Mais bien sûr, cette amélioration implique des temps plus longs d'élaboration des plans d'exécutions, donc la recommandation de s'assurer que les statistiques des segments sont à jour reste toujours valable.

Nous allons maintenant activer les statistiques système en utilisant les procédures du package dbms_stat. Au lieu de collecter ces statistiques nous allons mettre à jour ces statistiques avec les valeurs suivantes :

cpuspeed, le nombre des cycles CPU par seconde : 500 ;
sreadtim, le temps de lecture en mode monobloc : 5 ms ;
mreadtim, le temps de lecture en mode multibloc : 30 ms ;
MBRC, le nombre des blocs lus en mode multibloc : 16.

Sélectionnez

SQL> Begin
  DBMS_STATS.SET_SYSTEM_STATS('cpuspeed',500);
  DBMS_STATS.SET_SYSTEM_STATS('sreadtim',5.0);
  DBMS_STATS.SET_SYSTEM_STATS('mreadtim',30.0);
  DBMS_STATS.SET_SYSTEM_STATS('mbrc',16);
end;
/
 
Procédure PL/SQL terminée avec succès.

Affichons ensuite le contenu de la table de statistiques système (Oracle recommande l'utilisation de la procédure dbms_stats.get_system_stats) :

Sélectionnez

SQL> r
  1  select sname, pname, pval1
  2  from sys.aux_stats$
  3* where sname = 'SYSSTATS_MAIN'
 
SNAME           PNAME                PVAL1
--------------- --------------- ----------
SYSSTATS_MAIN   CPUSPEED               500
SYSSTATS_MAIN   MAXTHR                  -1
SYSSTATS_MAIN   MBRC                    16
SYSSTATS_MAIN   MREADTIM                30
SYSSTATS_MAIN   SLAVETHR                -1
SYSSTATS_MAIN   SREADTIM                 5
 
6 ligne(s) sélectionnée(s).

Et nous allons ignorer dans la suite MAXTHR et SLAVETHR. (Pour supprimer ces statistiques vous pouvez utiliser la méthode brutale et probablement non recommandée qui consiste à supprimer le contenu de la table sys.aux_stats$. À partir d'Oracle10g il est possible de restaurer les valeurs antérieures stockées dans la table d'historique SYS.WRI_OPTSTAT_AUX_HISTORY via le package dbms_stats.)

Examinons actuellement la même requête sur la table BIGEMP

Sélectionnez

SQL> explain plan set statement_id='BIGEMPsysStat' for select * from bigemp;
 
Explicité.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             | 14000 |   546K|  5353   (1)|
|   1 |  TABLE ACCESS FULL   | BIGEMP      | 14000 |   546K|  5353   (1)|
-------------------------------------------------------------------------
 
7 ligne(s) sélectionnée(s).
 
SQL> select id, cost, cpu_cost, io_cost
  2  from plan_table
  3  where statement_id = 'BIGEMPsysStat';
 
        ID       COST   CPU_COST    IO_COST
---------- ---------- ---------- ----------
         0       5353  104892469       5311
         1       5353  104892469       5311

Naturellement la note indiquant que le modelé de coût CPU n'est pas actif a disparu et le coût a changé d'une manière radicale. En fait c'est l'équation (1a)

Sélectionnez

(1a) cost = #SRDS  + 
            #MRDS * mreadtm / sreadtm +
            #CPUCycles / (cpuspeed * sreadtm)

qui est utilisée pour le calculer de la manière suivante :

#SRDS est égal à 0 (balayage complet de la table) ;
#MRDS = Blocks / MBRC ;
mreadtm = 30 ;
sreadtm = 5 ;
cpuspeed = 500 ;
#CPUCycles = colonne cpu_cost dans plan_table.

Et donc pour la partie E/S

Sélectionnez

    io_cost = #MRDS * mreadtm / sreadtm 
    io_cost = (14159/ 16) * 30/5 = 884,9375 * 6 = 5309, 625

et après arrondi à l'entier supérieur et ajout de 1 (_table_scan_cost_plus_one égal true)

Sélectionnez

    io_cost = 5311

Pour la partie cpu_cost

Sélectionnez

    cpu_cost = #CPUCycles / (cpuspeed * sreadtm) 
    cpu_cost = 104892469/(500 000 * 5) = 41.95 = 42 après arrondi

Et finalement le coût est

Sélectionnez

    cost = cpu_cost + io_cost = 5311 + 42 = 5353

À partir d'Oracle 9 une fois que les statistiques système sont calculées le coût d'exécution de la requête fait la différence entre les lectures en mode monobloc et multibloc et démontre que l'utilisation du CPU a une importance dans le calcul du coût.

Avant de passer à la version 10g examinons encore une requête :

Sélectionnez

SQL> explain plan set statement_id='BIGEMPsysStatFF' for select * from bigemp where deptno = 1010;
 
Explicité.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     5 |   200 |  5354   (1)|
|*  1 |  TABLE ACCESS FULL   | BIGEMP      |     5 |   200 |  5354   (1)|
-------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - filter("BIGEMP"."DEPTNO"=1010)
 
12 ligne(s) sélectionnée(s).
 
SQL> select id, cost, cpu_cost, io_cost
  2  from plan_table
  3   where statement_id = 'BIGEMPsysStatFF';
 
        ID       COST   CPU_COST    IO_COST
---------- ---------- ---------- ----------
         0       5354  105592469       5311
         1       5354  105592469       5311

ce qui met en évidence via la colonne CPU_COST que par rapport à l'ancien modèle décrit par l'équation (1b), le nouveau modèle intègre le coût impliqué par la comparaison de la valeur de la colonne deptno avec la constante 1010 pour les 14000 lignes existantes dans la table BIGEMP. En fait il est possible d'en estimer le coût d'une opération de comparaison en cycles CPU :

Sélectionnez

    Coût_FF = (105592469 &#150; 104892469) / 14000 = 700 000 / 14000 = 50

Si on modifie la valeur du paramètre db_file_multibloc_read_count pour la passer de 32 à 16 on constate que le coût ne change plus comme avant et cela s'explique par l'emploi de la statistique MBRC à la place de ce paramètre.

À partir de la version Oracle10g le modèle d'estimation de coût de basé sur utilisation CPU est le modèle implicite. Affichons d'abord le contenu de la table des statistiques système :

Sélectionnez

SQL> select sname, pname, pval1
  2    from sys.aux_stats$
  3   where sname = 'SYSSTATS_MAIN';
 
SNAME                          PNAME                               PVAL1
------------------------------ ------------------------------ ----------
SYSSTATS_MAIN                  CPUSPEED
SYSSTATS_MAIN                  CPUSPEEDNW                     500,790116
SYSSTATS_MAIN                  IOSEEKTIM                              10
SYSSTATS_MAIN                  IOTFRSPEED                           4096
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  MBRC
SYSSTATS_MAIN                  MREADTIM
SYSSTATS_MAIN                  SLAVETHR
SYSSTATS_MAIN                  SREADTIM

On constate que les statistiques ioseektim, et iotfrspeed ont les valeurs par défaut et on retrouve la valeur de la statistique CPUSPEEDNW (cpuspeed noworkload) initialisée au démarrage de la base (rappelez-vous que cette valeur dépend du système). On peut donc calculer (2)

Sélectionnez

    sreadtm = ioseek_tim + db_block_size / iotfrspeed
    sreadtm = 10 + 8192 / 4096 = 12
    mreadtm = ioseek_tim + mbrc * db_block_size / iotfrspeed
    mreadtm = 10 + 8 * 8192 / 4096 = 26

Et donc

Sélectionnez

    io_cost = #MRDS * mreadtm / sreadtm +
    io_cost = (14159 / 8) * (26 / 12) = 3834,729&#8230; +1 (_table_scan_cost_plus_one) =
    io_cost = 3836

Sélectionnez

    cpu_cost = #CPUCycles / (cpuspeed * sreadtm) 
    cpu_cost = 104892469 / (500 790,116 * 12) = 17,45.. = 17 (les règles d'arrondi ayant changé en Oracle 10g [2]).

pour un coût total

Sélectionnez

    cost = io_cost + cpu_cost = 3836 + 17 = 3853

Faisons la vérification :

Sélectionnez

SQL> explain plan set statement_id='BIGEMPsansSysStat' for select * from bigemp;
 
 
Explicité.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
Plan hash value: 2368838273
 
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 14000 |   546K|  3853   (1)| 00:00:47 |
|   1 |  TABLE ACCESS FULL| BIGEMP | 14000 |   546K|  3853   (1)| 00:00:47 |
----------------------------------------------------------------------------
 
8 ligne(s) sélectionnée(s).
 
SQL> select id, cost, cpu_cost, io_cost
  2   from plan_table
  3  where statement_id = 'BIGEMPsansSysStat'
  4  ;
 
        ID       COST   CPU_COST    IO_COST
---------- ---------- ---------- ----------
         0       3853  104892469       3836
         1       3853  104892469       3836

IV-B. L'étude des sélectivités et des cardinalités▲

On recommence avec une base Oracle 9 et on utilise la requête suivante :

Sélectionnez

SQL> explain plan set statement_id = 'BIGEMPavecFF' for select * from bigemp where deptno = 1010;
 
Explicité.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     5 |   200 |   864 |
|*  1 |  TABLE ACCESS FULL   | BIGEMP      |     5 |   200 |   864 |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - filter("BIGEMP"."DEPTNO"=1010)
 
Note: cpu costing is off

Remarquez la présence du filtre utilisé par la requête en bas du plan. Le plan d'exécution et le coût n'ont pas changé et cela signifie que dans ce contexte (Oracle 9 sans modelé de coût CPU) le travail fait pour filtrer les lignes est ignoré. Mais dans ce cas l'information la plus importante est donnée par la colonne Rows qui indique le nombre de lignes dont l'optimiseur estime qu'elles passeront le test du filtre : 5.

Pour calculer comment cette valeur est obtenue, on va interroger la vue des statistiques des colonnes :

Sélectionnez

SQL> r
  1  Select column_name, num_distinct, low_value, high_value, density, num_nulls
  2   from user_tab_col_statistics
  3  where table_name = 'BIGEMP'
  4*   and column_name = 'DEPTNO'
 
COLUMN_NAM NUM_DISTINCT LOW_VALUE  HIGH_VALUE    DENSITY  NUM_NULLS
---------- ------------ ---------- ---------- ---------- ----------
DEPTNO             3000 C20B0B     C40201011F .000333333          0

ou mieux encore pour afficher les valeurs limites

Sélectionnez

SQL> r
  1  Select column_name, num_distinct, utl_raw.cast_to_number(low_value) low,
  2         utl_raw.cast_to_number(high_value) high, density, num_nulls
  3   from user_tab_col_statistics
  4  where table_name = 'BIGEMP'
  5*   and column_name = 'DEPTNO'
 
COLUMN_NAM NUM_DISTINCT        LOW       HIGH    DENSITY  NUM_NULLS
---------- ------------ ---------- ---------- ---------- ----------
DEPTNO             3000       1010    1000030 .000333333          0

La valeur de la colonne Rows peut être retrouvée en appliquant les formules du tableau (T1) pour le Facteur de Filtrage dans le cas d'un prédicat d'égalité sans variable de liaison.

Sélectionnez

    Rows = Num_Rows (dans la vue user_tables pour la table BIGEMP) * density

Et donc:

Sélectionnez

    Rows = 14000 * 0,00033333 = 4,66662, arrondi = 5

Mais combien de lignes correspondent vraiment au filtre deptno = 1010

Sélectionnez

SQL> Select count(*)
  2    from bigemp
  3   Where deptno = 1010;
 
  COUNT(*)
----------
         3

Encore une différence : 5 à la place de 3 mais, il n'y pas de raison à s'inquiéter parce que les valeurs sont du même ordre de grandeur. Par contre, ce cas met bien en évidence que la zone Rows du plan d'exécution affichée par la commande explain plan, représente une estimation faite par l'optimiseur et que le nombre réel des lignes véhiculées peut être retrouvé en examinant le plan d'exécution produit par une trace SQL par exemple.

Une autre situation assez riche en enseignements est impliquée par la requête suivante (Oracle9) :

Sélectionnez

SQL> explain plan set statement_id='BIGDEPTavec2FF' for
  2  select * from bigdept where dname='ACCOUNTING' and loc='DALLAS';
 
Explicité.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   250 |  5250 |   774   (1)|
|*  1 |  TABLE ACCESS FULL   | BIGDEPT     |   250 |  5250 |   774   (1)|
-------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - filter("BIGDEPT"."DNAME"='ACCOUNTING' AND
              "BIGDEPT"."LOC"='DALLAS')
 
13 ligne(s) sélectionnée(s).
 
SQL> Select column_name, num_distinct, density, num_nulls
  2    from user_tab_col_statistics
  3   where table_name = 'BIGDEPT'
  4  /
 
COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS
------------------------------ ------------ ---------- ----------
DEPTNO                                 4000     .00025          0
DNAME                                     4        .25          0
LOC                                       4        .25          0

La valeur affichée dans la colonne Rows est calculée ainsi (T3)

Sélectionnez

    FF = FF1 * FF2 = dname.density * loc.density = 0,25 * 0,25 
    Rows = Num_Rows * FF
    Rows = 4000 * 0,25 * 0,25 = 250

Mais si on interroge pour voir combien d'enregistrements correspondent à cet ensemble de prédicats on trouve exactement 0 ce qui s'explique aisément par le fait que pour tous les enregistrements le service ACCOUNTING est associé à la localisation NEW YORK. En fait conformément à la théorie des probabilités l'équation de composition des prédicats FF1 * FF2 est valable dans l'hypothèse des prédicats indépendants ce qui n'est pas le cas pour la table BIGDEPT ; la localisation du service dépend en fait du service ! Mais remarquez l'écart important entre le nombre des lignes estimées : 250 et celles réellement ramenées : 0. La manière la plus simple de corriger ce résultat est d'employer l'échantillonnage dynamique en utilisant le hint SQL dynamic_sampling

Sélectionnez

SQL> explain plan set statement_id='BIGDEPTavec2FFdynSampling' for
  2  select /*+ dynamic_sampling(bigdept 10) */ * from bigdept where dname='ACCOUNTING' and loc='DALLAS';
 
Explicité.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    21 |   774   (1)|
|*  1 |  TABLE ACCESS FULL   | BIGDEPT     |     1 |    21 |   774   (1)|
-------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - filter("BIGDEPT"."DNAME"='ACCOUNTING' AND
              "BIGDEPT"."LOC"='DALLAS')
 
13 ligne(s) sélectionnée(s).

Mais utilisez cette technique plutôt pour explorer si ce n'est pas ce type de problème qui provoque le choix d'un mauvais plan d'exécution et non pas comme remède pérenne aux mauvais plans.

Examinons un autre cas en utilisant la requête suivante :

Sélectionnez

SQL> explain plan set statement_id 'BIGEMPavecFF2' for select * from bigemp where deptno >= 1000030;
 
Explicité.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     5 |   200 |   864 |
|*  1 |  TABLE ACCESS FULL   | BIGEMP      |     5 |   200 |   864 |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - filter("BIGEMP"."DEPTNO">=1000030)
 
Note: cpu costing is off
 
14 ligne(s) sélectionnée(s).

Et maintenant cette même requête en utilisant cette fois une variable de liaison :

Sélectionnez

SQL> variable b number
SQL> exec :b := 1000030;
 
Procédure PL/SQL terminée avec succès.
 
SQL> explain plan set statement_id 'BIGEMPavecFFetVl' for select * from bigemp where deptno >= :b;
 
Explicité.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   700 | 28000 |   864 |
|*  1 |  TABLE ACCESS FULL   | BIGEMP      |   700 | 28000 |   864 |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - filter("BIGEMP"."DEPTNO">=TO_NUMBER(:Z))
 
Note: cpu costing is off

Examinez la colonne Rows dans ces deux cas et voilà une belle différence : 5 lignes sans la variable de liaison et bien 700 si une variable de liaison est utilisée. L'explication est simple : sans variable la formule est

Sélectionnez

    Rows = 14000 * [(Hi - valeur) / (Hi - Low) + 1 / c1.num_distinct]
    Rows = 14000 *[(1000030 - 1000030) / (1000030 - 1010) + 1 / 3000] 
    Rows = 14000 * 1/3000 = 4.666... donc arrondi 5

mais avec les variables de liaison (T2)

Sélectionnez

    Rows = 14000 * 5e-2 = 700

Il y a plusieurs enseignements à tirer de cet exemple. D'abord, il est complètement erroné d'examiner le plan d'exécution d'une requête qui à l'origine utilise des variables de liaison en remplaçant les variables de liaison par des valeurs en dur sauf pour vérifier un problème lié au mécanisme de « bind variable peeking » c'est-à-dire l'introspection de la valeur des variables de liaison. Ensuite le conseil qui indique qu'il faut toujours utiliser des variables de liaison pour toutes les requêtes doit être réinterprété. S'il est vrai qu'une application qui n'utilise pas de variables de liaison ajoute inutilement une charge supplémentaire au serveur qui peut souvent être vraiment pénalisante, il est clair que certaines requêtes profitent en fait des meilleures estimations obtenues via les valeurs littérales remplaçant des variables de liaison des requêtes.

V. Conclusions▲

À partir de la version 9i deux classes des statistiques ont un impact direct sur l'élaboration des plans d'exécution : les statistiques système et les statistiques des segments (tables, index, etc.). Collecter ces statistiques et les tenir à jour par rapport aux données est vital pour permettre le calcul des meilleurs plans d'exécution. Sans ces informations il ne nous reste qu'à espérer que les objets accédés par la requête cadrent avec les diverses hypothèses sur lesquelles l'optimiseur se base. Activer les statistiques système est tout aussi important que les statistiques des segments.

Sauf quelques cas très simples à chaque différence de plusieurs ordres de grandeur entre le nombre des lignes estimées par l'optimiseur lors de l'élaboration du plan et celles effectivement ramenées à l'exécution de la requête corresponde un plan d'exécution sous-optimal et doit constituer un signal d'alerte.

L'arithmétique des cardinalités et du coût présentée dans ce document donne une version un peu simpliste des choses. En réalité les algorithmes sont de loin plus compliqués, prenant en compte parfois des valeurs plafond ou plancher, des règles d'arrondi plus complexes ou de branche des décisions conduisant à des équations différentes. Mais ces équations, déduites parfois d'une manière empirique et ayant fait l'objet de procédures de « piratage informatique » fonctionnent avec une bonne précision. La multitude des paramètres, le changement des valeurs par défaut des paramètres ou des règles d'arrondi, les bugs présents dans le code de l'optimiseur et corrigés d'une version à l'autre apportent un plus de difficultés dans l'obtention d'une précision infaillible.

VI. Remerciements▲

Un grand merci à SheikYerboutiSheikYerbouti pour la relecture et ses corrections orthographiques ainsi qu'à SQLProSQLPro et Vincent RogierVincent Rogier pour leurs corrections et encouragements. Une mention spéciale est faite à PomalaixPomalaix pour sa critique contradictoire et son insistance sur la précision de certains termes utilisés.

VII. Bibliographie▲

[1] Oracle® Database Performance Tuning GuidePerformance Tunning Guide, 10g Release 2 (10.2), Part Number B14211-01
[2] Jonathan Lewis, Cost-Based Oracle Fundamentals, ISBN 1-59059-636-6
[3] Wolfgang Bretling, What is new in the Oracle9i CBO, http://www.centrexcc.com
[4] Wolfgang Breitling, A Look under the Hood of CBO-10053 Event, http://www.centrexcc.com

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants :

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2009 Marius NITU. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.