SQL Amer #3

Faîtes toujours confiance au Cost Based Optimizer (CBO) ! (Français - Anglais - Index en Français - English Index)

Ce titre me rend nerveux ! En fait, le sujet de cet article n'est pas l'optimiseur Oracle mais l'impact de ne pas lui donner assez d'informations pour lui permettre de trouver le plan d'exécution adéquat  en regard des performances attendues. Je prendrai les statistiques comme un exemple d'informations qui peuvent manquer à l'optimiseur. En fait, c'est loin d'être les seules informations utiles à l'optimiseur : les contraintes, les équivalences (hierarchies, rewrite equivalences...) ou les ordres SQL différenciés (cf SQL Amer #4 : Utilisez toujours des variables de type "Bind" !) sont des informations précieuses pour que l'optimiseur trouve le plan idéal. Alors, avant de "virer" l'optimiseur en qui vous avez toujours eu confiance, demandez-vous : "Auriez-vous fait mieux que lui en connaissant ce qu'il connaissait ?"

Avant que vous ne répondiez à cette question, je vais vous raconter une histoire qui m'est arrivée il y a déjà plusieurs années ! Un de mes clients avait eu une semaine extrêmement difficile après avoir passé une nouvelle version de son application de la phase de test en production. Après la migration, l'application était pratiquement inutilisable du fait de très mauvaises performances et la société ne pu que constater de graves retards à cause de l'informatique. Pour s'en sortir, cela nécessitat l'intervention de plusieurs développeurs et une semaine complète. Ils finirent par ré-écrire une requête de 4 pages en plusieurs requêtes plus petites. Oracle8i et l'optimiseur furent jugés coupables et condamnés ! En tant qu'employé d'Oracle et en vrai défenseur de la société, je plongeais dans la bagarre pour "laver" l'honneur d'Oracle8i : mon comportement était guidé par la fascination de Larry pour les traditions japonaises. Parfois, je suis un âne, cette fois là, je ne le fus pas ! J'emmenais avec moi l'une des personnes les plus expertes, sinon la plus experte, en tuning SQL d'Oracle France. C'est une vraie professionnelle et, sans doute à cause de son humilité, elle ne semblait pas enchantée par mon attitude kamikaze. Après 4 heures de tests, de réflexions et je ne sais pas trop quoi, elle l'avait ! Comme une sorcière, elle jeta son sort de magie noire (i.e. DBMS_STATS) et la requête qui durait 50 minutes s'exécutat en moins d'une demi seconde. Sa propre surprise était encore plus grande que ma joie ! Il y a plusieurs morales à cette histoire :
1) Il est parfois mieux de connaître (ou de travailler) avec quelqu'un en qui vous avez confiance plutôt que de résoudre les problèmes vous-même.
2) Lorsque vous faites des tests, assurez-vous que ces tests sont représentatifs de la réalité. Par exemple, si vous utilisez une base de données, utilisez des copies physiques entre les environnements de test et de production ; utilisez les mêmes stratégies de collecte de statistiques ; Assurez vous que la charge est représentative d'une vraie activité.
3) Découper une requête en requêtes plus petites ne devrait pas être la meilleure idée.
4) Je ne sais pas comment ma propre histoire se terminera... Parfois, je souhaiterais ne pas être allé aussi loin.

Dans les sections qui suivent, je vais vous montrer un exemple de SQL dont les plans sont impactés par la stratégie de collecte de statistiques. Ensuite je vous expliquerai comment Oracle10g peut aider ceux qui n'ont pas d'amis dans une équipe d'experts Oracle.

Lorsque les plans sont impactés par la stratégie de collecte de statistiques

Il n'y a pas de magie, s'il manque une information à l'optimiseur, il peut, soit la deviner, soit avec plus ou moins de précision, l'estimer. Selon la version d'Oracle, l'optimiseur a des comportements différents et est de plus en plus intelligent (je devrais dire évolué). Ci-dessous, vous trouverez l'exemple d'une requête SQL exécutée avec deux stratégies de prise de statistiques différentes. Cet exemple présente un cas concret de statistiques qui impactent un plan. Certains peuvent penser que dans le premier cas l'optimiseur se trompe. En fait, il n'en est rien ! Sachant ce qu'il sait, l'optimiseur choisit le meilleur plan d'exécution. Parce que, l'exemple a été choisi à dessein, les valeurs des données sont contraires à l'estimation qu'effectue l'optimiseur. En revanche, il est vrai qu'il existe un plan plus efficace que le premier plan pour exécuter la requête. C'est d'ailleurs ce qui ce passe lorsque vous fournissez plus d'informations en changeant les options de prises de statistiques. 

Créons une table nommée DEMO avec 2 colonnes : COL1 est un NUMBER et est indexée ; elle ne contient pas des valeurs uniques ; COL2 est un VARCHAR2 et est utilisée pour augmenter la taille de la table et le coût d'une opération de type "TABLE ACCESS FULL". Il y a 100000 lignes dans la table ; pour 50000 d'entre-elles, la valeur de la colonne COL1 est unique et comprise entre 1 et 50000 ; pour 50000 autres, la valeur de COL1 est égale à 50001. Vous trouverez le script utilisé pour créer la table, l'index et les données ci-dessous :

CREATE TABLE DEMO (COL1 NUMBER,
                   COL2 VARCHAR2(500));
CREATE INDEX DEMO_COL1_IDX ON DEMO(COL1);
BEGIN
  FOR I IN 1..50000 LOOP
    INSERT INTO DEMO(COL1, COL2) VALUES (i,
       'This string is used to raise the high water mark and the cost of a TABLE SCAN FULL');
    INSERT INTO DEMO(COL1, COL2) VALUES (50001,
       'This string is used to raise the high water mark and the cost of a TABLE SCAN FULL');
  END LOOP;
END;
/
COMMIT; 

D'abord, prenons les statistiques avec DBMS_STATS et les options ci-dessous : 
- cascade=>true signifie que les statistiques sont prises sur la table et sur ses index
- method_opt=>'FOR ALL COLUMNS SIZE 1' signifie que seules les valeurs minimum et maximum de chaque colonne sont prises
- no_validate=>false signifie que dans le dans le cas où il y a déjà un plan d'exécution calculé dans la shared pool pour une requête qui accède à cette table, il sera invalidé de sorte que la prochaine requête fera l'objet d'un "hard parse" (Le plan sera recalculé). Cette option est utilisée pour s'assurer que les nouvelles statistiques soient prises en compte par les requêtes passées précédemment.

exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEMO', cascade=>true, -
   method_opt=>'FOR ALL COLUMNS SIZE 1', -
   no_invalidate=>false)

Une fois les statistiques prises, exécutons la requête. Les statistiques d'exécution sont affichées seulement à la seconde exécution, une fois le plan calculé et les données dans le buffer cache :

set autotrace off

exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEMO', cascade=>true, -
   method_opt=>'FOR ALL COLUMNS SIZE 1', -
   no_invalidate=>false) 

select count(b.col2)
    from demo a, demo b
    where a.col1-1=b.col1
      and a.col1=50001;

set autotrace on

/

COUNT(B.COL2)
-------------
        50000

Plan d'exécution
----------------------------------------------------------
Plan hash value: 756120225

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |     1 |    92 |    57   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |               |     1 |    92 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEMO          |    28 |  2436 |     2   (0)| 00:00:01 |
|   3 |    NESTED LOOPS              |               |   794 | 73048 |    57   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN         | DEMO_COL1_IDX |    28 |   140 |     1   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN         | DEMO_COL1_IDX |    28 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("A"."COL1"=50001)
   5 - access("B"."COL1"="A"."COL1"-1)


Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      50105  consistent gets
          0  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Comme vous pouvez le voir en rouge, l'optimiseur estime le nombre de lignes retournées par la condition "A.COL1=50001" à 28. C'est une estimation et c'est loin de la réalité (50000 lignes) mais du fait de la manière dont les statistiques ont été prises, l'optimiseur ne dispose d'aucune information pour deviner combien d'occurences de la valeur 50001 sont présentes dans la colonne COL1. Dans cet exemple, le plan serait excellent pour n'importe quelle valeur... excepté 50001 ! Le nombre de blocs utilisés pour exécuter la requête est de 50105 (en rouge) et, comme nous le verrons ensuite, il y a un meilleur plan.

Maintenant, prenons les statistiques une fois encore mais avec une information complémentaire : la distribution des données dans la colonne COL1. La syntaxe de DBMS_STATS peut être la suivante :
- method_opt=>'FOR ALL COLUMNS SIZE 254' est une option qui signifie qu'un histogramme de 254 buckets pour aider l'optimiseur à estimer la distribution de données dans la colonne COL1.

set autotrace off

exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEMO', cascade=>true, -
   method_opt=>'FOR ALL COLUMNS SIZE 254', -
   no_invalidate=>false) 

Une fois les statistiques prises, relançons la requête. Comme dans l'exécution précédente, les statistiques d'exécution de la requête ne sont affichées que pour la seconde exécution une fois le plan calculé et toutes les données dans le buffer cache :

select count(b.col2)
    from demo a, demo b
    where a.col1-1=b.col1
      and a.col1=50001;

set autotrace on

/

COUNT(B.COL2)
-------------
        50000

Plan d'exécution
----------------------------------------------------------
Plan hash value: 2918810667

----------------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |               |     1 |    92 |   445   (4)| 00:00:06 |
|   1 |  SORT AGGREGATE        |               |     1 |    92 |            |          |
|*  2 |   HASH JOIN            |               |   100K|  9019K|   445   (4)| 00:00:06 |
|*  3 |    INDEX FAST FULL SCAN| DEMO_COL1_IDX | 50197 |   245K|    85   (5)| 00:00:02 |
|   4 |    TABLE ACCESS FULL   | DEMO          |   100K|  8496K|   356   (2)| 00:00:05 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."COL1"="A"."COL1"-1)
   3 - filter("A"."COL1"=50001)

Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1590  consistent gets
          0  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Si vous comparez ce plan avec le précédent, vous voyez qu'il est différent. Vous pouvez également voir que l'estimation du nombre de lignes renvoyés par la première étape de l'exécution est très précise. Le nombre en rouge montre que l'optimiseur estime ce nombre à 50197 contre 50000 en réalité et contre 28 estimées avec le même filtre dans les statistiques précédentes (A.COL1=50001). Comme une conséquence, le nombre de blocs utilisés pendant l'exécution de la requête est de 1590 et donc 30 fois plus faible qu'avec le plan précédent et est donc meilleur.

Dans cette section, nous avons montré qu'il est important de fournir les bonnes informations à l'optimiseur pour améliorer ses décisions. Afin de mieux comprendre les contraintes relatives à cette pièce de code centrale pour Oracle, vous devez vous souvenir que :
- L'optimiseur doit décider et ne peut pas prendre trop de temps pour le faire. Vous, vous pouvez ! En fait, avec 10g, ce n'est plus vrai et je devrais dire le CBO au lieu d'optimiseur ! Avec l'ATO (Automatic Tuning Optimizer), il est désormais possible de fournir du temps à l'optimiseur pour qu'il apprenne de ses décisions et regarde plus en détail de nombreux aspects, y compris la pertinence des statistiques.
- Les statistiques sont des statistiques et pas la réalité : connaître la moyenne et la variance des poids de personnes en France vous aidera à estimer mon poids et estimer l'erreur que vous pourriez faire. Mais vous ne devinerez pas mon poids ainsi
- Calculer (ou estimer) les statistiques coûte de la CPU, des IO et de la mémoire... Etre efficae, signifie trouver les meilleurs compromis. Cela ne signifie pas toujours fournir le meilleur plan d'exécution possible !
- Les données changent et les statistiques peuvent ne pas refléter la réalité...
- Les statistiques peuvent être très utiles ou complètement inutiles... Cela dépend de votre SQL ! C'est dommage de payer du temps processeur, des IO ou de la mémoire pour quelquechose qui ne sera pas utilisé.

Vous pouvez regretter l'inefficacité du monde précédent, lorsque le CBO n'était pas et quand les choses étaient prédictibles !  A cette époque, pratiquement toutes les requêtes étaient sous-optimales et cela prennait des heures pour tester et optimiser nombre d'entre elles. N'ayez pas de regrets, dans la section ci-après, je vais vous montrer à quel point Oracle est intelligent (Une fois de plus, je devrais dire évolué) et que la stratégie de collecte par défaut vient facilement à bout de l'exemple précédent.

Si vous n'avez pas d'amis dans une équipe d'expert Oracle

**Faites ce test avec Oracle 10g** Prenez juste les statistiques avec les options par défaut qui sont, dans le cas de l'option method_opt, la suivante :
- method_opt=>'FOR ALL COLUMNS SIZE AUTO'
Cela signifie que la base Oracle décide de prendre les histogrammes sur les colonnes automatiquement et, si c'est le cas, il décide de quelle est leur taille.

Pour démontrer cette fonctionnalité, nous devons d'abord supprimer la table DEMO puisqu'Oracle a déjà monitoré l'activité sur cette table. Son fonctionnement n'est déjà plus le comportement par défaut. Supprimer simplement la table DEMO et recréez la avec le script ci-dessous : 

DROP TABLE DEMO PURGE;
CREATE TABLE DEMO (COL1 NUMBER,
                   COL2 VARCHAR2(500));
CREATE INDEX DEMO_COL1_IDX ON DEMO(COL1);
BEGIN
  FOR I IN 1..50000 LOOP
    INSERT INTO DEMO(COL1, COL2) VALUES (i,
       'This string is used to raise the high water mark and the cost of a TABLE SCAN FULL');
    INSERT INTO DEMO(COL1, COL2) VALUES (50001,
       'This string is used to raise the high water mark and the cost of a TABLE SCAN FULL');
  END LOOP;
END;
/
COMMIT;

Prenons les statistiques avec la stratégie automatique par défaut :

exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEMO', cascade=>true, -
   method_opt=>'FOR ALL COLUMNS SIZE AUTO',
   no_invalidate=>false) 

Vous pouvez voir grâce à la requête ci-dessous qu'aucun histogramme n'a été pris par la stratégie de collecte de statistique utilisée :

set pages 1000
column COLUMN_NAME format a30
select column_name, max(endpoint_number) num_buckets from user_tab_histograms
 where table_name='DEMO'
 group by column_name;

COLUMN_NAME                    NUM_BUCKETS
------------------------------ -----------
COL1                                     1
COL2                                     1

Maintenant, lançons une requête sur la table DEMO avec un prédicat qui filtre la colonne COL1. 

select count(distinct COL2) from demo
   where COL1=1;

La prochaine fois que vous prendrez les statistiques avec une méthode d'optimisation automatique :

exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEMO', cascade=>true, -
   method_opt=>'FOR ALL COLUMNS SIZE AUTO')

Vous verrez en requêtant la vue statique des histogrammes de tables que, parce que la requête SQL a été supervisée les statistiques sont désormais plus précises.

set pages 1000
column COLUMN_NAME format a30
select column_name, max(endpoint_number) num_buckets from user_tab_histograms
 where table_name='DEMO'
 group by column_name;

COLUMN_NAME                    NUM_BUCKETS
------------------------------ -----------
COL1                                   254
COL2                                     1

Moralité, avec Oracle 10g, je n'aurais pas eu besoin de ma précieuse experte si j'avais utilisé la méthode automatique d'optimisation des statistiques. Je garde quand même son téléphone, on ne sait jamais !

GarK!