vendredi 26 août 2011

Restaurer des statistiques antérieures

Depuis la 10g, à chaque fois que vous calculez des statistiques dans Oracle via le package DBMS_STATS les statistiques précédentes sont sauvegardées dans le dictionnaire de données pendant une durée par défaut de 31 jours. L’intérêt de cette historisation des statistiques c’est qu’il est alors possible de les restaurer facilement.

La restauration de statistiques antérieures peut être utile lorsqu’après un calcul de stats sur une ou plusieurs tables on se retrouve avec des nouveaux plans moins performants. Le fait de restaurer les statistiques précédentes peut nous permettre de revenir aux performances correctes observées précédemment en attendant d’analyser le problème de performances lié à ces nouvelles stats.

Voici un exemple très simple pour comprendre le fonctionnement de l’historisation des statistiques.
SQL> select table_name,last_analyzed from user_tables where table_name='T1';
 
TABLE_NAME                     LAST_ANAL
------------------------------ ---------
T1                             25-JUL-11
 
SQL> select TABLE_NAME,NUM_ROWS
  2  from user_tab_statistics where table_name='T1';
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                  72742

Je vois que les dernières stats sur ma table T1 datent du 25/07/2011 et que le nombre de lignes dans la table est estimé à 72742.
Maintenant je vide la table et je recalcule des stats :
SQL> truncate table T1;
 
Table truncated.
 
SQL> exec dbms_stats.gather_table_stats(USER,'T1');
 
PL/SQL procedure successfully completed.
 
SQL> select TABLE_NAME,NUM_ROWS
  2  from user_tab_statistics where table_name='T1';
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                      0

J’ai bien maintenant 0 lignes dans ma table et les stats reflètent bien cet état de fait.

L’historique des statistiques est visualisable dans la vue USER_TAB_STATS_HISTORY :
SQL> select STATS_UPDATE_TIME
  2  from user_tab_stats_history where TABLE_NAME='T1';
 
STATS_UPDATE_TIME
---------------------------------------------------------------------------
25-JUL-11 02.40.18.819000 PM +02:00
25-JUL-11 02.45.31.305000 PM +02:00
25-JUL-11 02.45.47.274000 PM +02:00
25-AUG-11 01.44.58.074000 PM +02:00

En requêtant la vue USER_TAB_STATS_HISTORY on voit que lors des 31 derniers jours les statistiques ont été collectées 4 fois sur la table T1.

Ces stats sont sauvegardées et sont donc restaurables en utilisant la procédure RESTORE_TABLE_STATS du package DBMS_STATS.
SQL> BEGIN
  2  DBMS_STATS.RESTORE_TABLE_STATS(    OWNNAME=>USER, TABNAME=>'T1', as_of_timestamp=>sysdate-1);
  3  END;
  4  /
 
PL/SQL procedure successfully completed.

La requête ci-dessus restaure l’état des statistiques de la table T1 de la veille.
D’ailleurs si j’interroge la vue USER_TAB_STATISTICS je vois que les statistiques courante de T1 reflètent l’existence de 72742 lignes alors que ma table est vide.
SQL> select TABLE_NAME,NUM_ROWS
  2  from user_tab_statistics where table_name='T1';
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                  72742

Il existe des procédures équivalentes à RESTORE_TABLE_STATS pour restaurer les statistiques au niveau de toute la base ou bien au niveau schéma, dictionnaire de données etc. (voir la doc Oracle).

jeudi 11 août 2011

Les statistiques étendues

Statistiques étendues sur des colonnes dépendantes

Ceux qui ont un peu d’expérience en matière de performance savent que les plans d’exécution non optimaux que peut être amené à choisir le CBO sont souvent liés à des erreurs dans l'estimation des cardinalités. La cardinalité d’une opération (par exemple un Full Table Scan) correspond au nombre de lignes retournées par cette opération après application des filtres. Le CBO estime cette cardinalité en multipliant le nombre de lignes de la table par la sélectivité de l’opération.

Exemple :
Soit une table CLIENT de 1 millions de lignes avec 90% d’hommes et 10% de femmes. Si j’exécute une requête me retournant la liste des clients hommes, le CBO va estimer le nombre de lignes retournées par cette requête afin de choisir un plan d’exécution adapté. Pour ce faire (en partant du principe qu’un histogramme a été calculé pour la colonne SEXE de la table CLIENT) il va multiplier la sélectivité (les 90%) par le nombre de lignes de la table. Il obtiendra une cardinalité de 900 000 et décidera surement d’opter pour un FULL TABLE SCAN pour récupérer ces lignes. Si à l’inverse, on voulait lister les clients femmes la cardinalité estimée aurait été de 100 000, et peut-être que le CBO aurait alors choisi un accès indexé.

Lorsqu’il y’a plusieurs colonnes impliquées dans la clause WHERE d’une requête le CBO va multiplier les sélectivités de chaque colonne pour calculer la cardinalité. Par exemple, si dans ma table j’ai 30% des clients qui vivent à Paris, on pourrait dire statistiquement parlant que j’ai 27% de clients hommes qui vivent à Paris (30% multiplié par 90%). Ce calcul est tout à fait raisonnable car les colonnes SEXE et VILLE ne sont pas directement liées.
Le problème se présente lorsque les filtres sont effectués sur des colonnes corrélées comme la VILLE et la REGION. Un client qui habite à Paris, habite forcément en Ile De France. C’est évident pour nous tous mais ça ne l’est pas pour l’optimiseur car il n’a aucune idée de ce que signifient les colonnes. Donc si on a dans nos stats 30% des clients qui vivent à Paris et 50% des clients qui vivent en Ile De France, et qu’on exécute une requête avec la clause WHERE suivante « WHERE VILLE = ’PARIS’ AND REGION = ‘IDF’ » l’optimiseur va calculer la cardinalité en utilisant une sélectivité de 15% (50% multiplié par 30%) au lieu de 30%.

Avant la 11g, pour obtenir une bonne cardinalité lorsqu’une requête impliquait des colonnes corrélées, il fallait qu’on ait un index composite sur ces deux colonnes ou bien qu’on force le Dynamic Sampling pour cette requête. Avec la 11g il est désormais possible de calculer des statistiques étendues sur ces colonnes corrélées (Multicolumn Extended Statistics) permettant à l’optimiseur de reconnaitre ces dépendances lors du parsing de la requête.

TEST CASE :
L’exemple ci-dessous montre comment les stats étendues permettent d’obtenir une cardinalité plus juste lorsque 2 colonnes dépendantes sont utilisées dans une requête.

Je vais d’abord créer ma table CLIENT en me basant sur la table CUSTOMER du schéma SH d’Oracle :
SQL> create table CLIENT(ID,PRENOM,NOM,SEXE,PAYS,VILLE,REGION)
  2  as
  3  select cu.CUST_ID, cu.CUST_FIRST_NAME, cu.CUST_LAST_NAME,
  4  cu.CUST_GENDER, co.COUNTRY_NAME, cu.CUST_CITY, cu.CUST_STATE_PROVINCE
  5  from customers cu, countries co
  6  where cu.COUNTRY_ID = co.COUNTRY_ID;
 
Table created.
 
SQL> select count(1) from CLIENT;
 
  COUNT(1)
----------
     55500
 
SQL> exec dbms_stats.gather_table_stats(user,'CLIENT');
 
PL/SQL procedure successfully completed.

Comme les colonnes VILLE et REGION sont skewed je vais calculer des histogrammes pour ces 2 colonnes:
SQL> exec dbms_stats.gather_table_stats(user,'CLIENT',method_opt => 'for columns size 254 VILLE, size 145 REGION');
 
PL/SQL procedure successfully completed.
 
SQL>  select column_name,histogram from user_tab_col_statistics where table_name='CLIENT';
 
COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
ID                             NONE
PRENOM                         NONE
NOM                            NONE
SEXE                           NONE
PAYS                           NONE
VILLE                          HEIGHT BALANCED
REGION                         FREQUENCY

Voyons ce qu’estime le CBO pour les clients qui vivent à Paris :
SQL> select count(*) from client where ville='Paris';
 
  COUNT(*)
----------
        77
 
1 row selected.
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
SQL_ID  2stvn1str5n8g, child number 0
-------------------------------------
select count(*) from client where ville='Paris'
 
Plan hash value: 3543756331
 
---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.01 |     470 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |     470 |
|*  2 |   TABLE ACCESS FULL| CLIENT |      1 |     82 |     77 |00:00:00.01 |     470 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("VILLE"='Paris')

Il y’a 77 clients qui vivent à Paris et le CBO en estime 82. On peut dire que l’estimation est juste.

Voyons ensuite les clients qui vivent en région Ile De France :
SQL> select count(*) from client where region='Ile-de-France';
 
  COUNT(*)
----------
      1034
 
1 row selected.
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
SQL_ID  bd28djabdux6b, child number 0
-------------------------------------
select count(*) from client where region='Ile-de-France'
 
Plan hash value: 3543756331
 
---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.01 |     470 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |     470 |
|*  2 |   TABLE ACCESS FULL| CLIENT |      1 |    892 |   1034 |00:00:00.01 |     470 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("REGION"='Ile-de-France')

Le nombre de clients franciliens est de 1034 et le CBO en estime 892. Là aussi on a une estimation plutôt raisonnable.

Voyons maintenant ce que donne la requête lorsqu’on combine les 2 critères :
SQL> select count(*) from client where ville='Paris' and region='Ile-de-France';
 
  COUNT(*)
----------
        77
 
1 row selected.
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
SQL_ID  0y19xgnav3auh, child number 0
-------------------------------------
select count(*) from client where ville='Paris' and
region='Ile-de-France'
 
Plan hash value: 3543756331
 
---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.01 |     470 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |     470 |
|*  2 |   TABLE ACCESS FULL| CLIENT |      1 |      1 |     77 |00:00:00.01 |     470 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("VILLE"='Paris' AND "REGION"='Ile-de-France'))

Là le CBO se trompe complètement car en multipliant les 2 sélectivités il en arrive à estimer que la requête ne retournera qu’une seule ligne au lieu de 77. Cette erreur vient du fait qu’il n’est pas informé de la corrélation qui existe entre la VILLE et la REGION.

Calculons des statistiques étendues pour ces 2 colonnes :
SQL> BEGIN
  2     DBMS_STATS.gather_table_stats(user,'CLIENT',
  3     method_opt => 'FOR ALL COLUMNS FOR COLUMNS (VILLE,REGION)',
  4     NO_INVALIDATE => FALSE
  5     );
  6  END;
  7  /
 
PL/SQL procedure successfully completed.

Le fait de calculer des stats étendues sur ces 2 colonnes va engendrer la création d’une colonne virtuelle appelée EXTENSION. La vue USER_STAT_EXTENSIONS permet d’avoir des informations sur ces extensions :
SQL> SELECT extension_name, extension
  2   FROM user_stat_extensions
  3   WHERE table_name = 'CLIENT';
 
EXTENSION_NAME                 EXTENSION
------------------------------ --------------------------------------------------------------------------------
SYS_STUE_OO2MD$OBR5EQSB7NBV$RO ("VILLE","REGION")

Le nom donné à l’extension correspond au nom de la colonne virtuelle qu’Oracle a automatiquement définie et qu’on peut également voir dans la liste des colonnes de la table :
SQL> select column_name,histogram from user_tab_col_statistics where table_name='CLIENT';
 
COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
ID                             HEIGHT BALANCED
PRENOM                         HEIGHT BALANCED
NOM                            HEIGHT BALANCED
SEXE                           FREQUENCY
PAYS                           FREQUENCY
VILLE                          HEIGHT BALANCED
REGION                         HEIGHT BALANCED
SYS_STUE_OO2MD$OBR5EQSB7NBV$RO HEIGHT BALANCED

Maintenant que le CBO a des informations sur la corrélation existante ente les colonnes VILLE et REGION, voyons ce que donne ses estimations pour notre requête précédente :
SQL> select count(*) from client where ville='Paris' and region='Ile-de-France';
 
  COUNT(*)
----------
        77
 
1 row selected.
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
SQL_ID  0y19xgnav3auh, child number 0
-------------------------------------
select count(*) from client where ville='Paris' and
region='Ile-de-France'
 
Plan hash value: 3543756331
 
---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.01 |     470 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |     470 |
|*  2 |   TABLE ACCESS FULL| CLIENT |      1 |     87 |     77 |00:00:00.01 |     470 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("VILLE"='Paris' AND "REGION"='Ile-de-France'))

La cardinalité estimée est 87, ce qui est bien mieux que le 1 obtenu sans les stats étendues.
Bien sûr pour cette requête, la cardinalité estimée ne change rien au plan d’exécution mais imaginons qu’on ait des jointures en plus dans la requête avec des tables volumineuses: avec une cardinalité fausse on peut se retrouver facilement avec un plan complètement inappropriée avec par exemple un MERGE JOIN CARTESIAN entre 2 tables retournant chacune plusieurs milliers de lignes.

Au lieu de créer les extensions au moment du calcul de stats via la procédure GATHER_TABLE_STATS, on pourrait créer directement ces extensions en utilisant la nouvelle fonction CREATE_EXTENDED_STATS du package DBMS_STATS :
SQL> exec dbms_stats.drop_extended_stats(ownname => user,tabname => 'CLIENT',extension => '(VILLE,REGION)');
 
PL/SQL procedure successfully completed.
 
SQL> SELECT
  2  dbms_stats.create_extended_stats(ownname => user,tabname => 'CLIENT',extension => '(VILLE,REGION)') EXTENSION_NAME
  3  FROM dual;
 
EXTENSION_NAME
-----------------------------------------------------------
SYS_STUE_OO2MD$OBR5EQSB7NBV$RO

La fonction retourne le nom de la colonne virtuelle (extension).
A cette étape les stats ne sont pas calculées pour l’extension. Elle le seront lors du prochain calcul de stats effectués sur la table.

Si vous êtes face à un problème lié à la dépendance de colonnes et que vous êtes en 10g il ne vous sera pas possible de créer des statistiques étendues. La solution consistera à forcer le dynamic sampling via un hint ou bien en ne calculant pas de stats pour la table.

Exemple avec dynamic sampling :
SQL> exec dbms_stats.drop_extended_stats(ownname => user,tabname => 'CLIENT',extension => '(VILLE,REGION)');
 
PL/SQL procedure successfully completed.
 
SQL> exec dbms_stats.delete_table_stats(user,'CLIENT');
 
PL/SQL procedure successfully completed.
 
SQL> select count(*) from client where ville='Paris' and region='Ile-de-France';
 
  COUNT(*)
----------
        77
 
1 row selected.
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
SQL_ID  0y19xgnav3auh, child number 0
-------------------------------------
select count(*) from client where ville='Paris' and
region='Ile-de-France'
 
Plan hash value: 3543756331
 
---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.01 |     470 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |     470 |
|*  2 |   TABLE ACCESS FULL| CLIENT |      1 |     77 |     77 |00:00:00.01 |     470 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("VILLE"='Paris' AND "REGION"='Ile-de-France'))
 
Note
-----
   - dynamic sampling used for this statement (level=2)

La section NOTE à la fin du plan indique bien que le dynamic sampling a été utilisé pour cette requête et surtout on voit que la cardinalité estimée est parfaite.


Statistiques étendues sur une expression :

Un autre cas où le CBO est incapable d'estimer une bonne cardinalité c’est lorsqu’on applique une fonction à une colonne. Le CBO n’a alors aucune idée de l’impact de cette fonction sur la sélectivité de la colonne. Dans ce cas et d’après le livre de Jonathan LEWIS, le CBO applique une séléctivité de 1% c'est-à-dire que pour une table de 1000 lignes il va estimer qu’une requête avec une fonction appliquée sur une colonne va retourner 10 lignes.
Reprenons notre table CLIENT et appliquons dans notre requête la fonction UPPER à la colonne VILLE :
SQL> select count(*) from client where UPPER(ville)='PARIS';
 
  COUNT(*)
----------
        77
 
1 row selected.
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
SQL_ID  59bf7bswaqxvh, child number 0
-------------------------------------
select count(*) from client where UPPER(ville)='PARIS'
 
Plan hash value: 3543756331
 
---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.01 |     470 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |     470 |
|*  2 |   TABLE ACCESS FULL| CLIENT |      1 |    555 |     77 |00:00:00.01 |     470 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CLIENT"."SYS_NC00008quot;='PARIS')

On voit bien que la séléctivité de 1% a été appliquée puisque la table CLIENT contient 55 500 lignes et que le CBO a estimé une cardinalité de 555 (55500*0.01=555).

Calculons maintenant des stats étendues sur la fonction UPPER appliquée à la colonne VILLE :
SQL> BEGIN
  2  DBMS_STATS.gather_table_stats
  3  (ownname => USER,
  4  tabname => 'CLIENT',
  5  method_opt => 'FOR ALL COLUMNS FOR COLUMNS (UPPER(VILLE))'
  6  );
  7  END;
  8  /
 
PL/SQL procedure successfully completed.
 
SQL> SELECT extension_name, extension
  2   FROM user_stat_extensions
  3   WHERE table_name = 'CLIENT';
 
EXTENSION_NAME                 EXTENSION
------------------------------ ----------------------------------------------------------
SYS_STUV9T3PPLW$4FMKHYOB4#YU$D (UPPER("VILLE"))
 

Une colonne virtuelle a été crée correspondant à la fonction « UPPER(VILLE)».
A noter qu’on aurait pu là aussi utiliser la fonction CREATE_EXTENDED_STATS pour créer l’extension correspondant à la fonction UPPER.
Si on exécute de nouveau la requête on verra que la cardinalité estimée est bien plus proche de la réalité :
SQL> select count(*) from client where UPPER(ville)='PARIS';
 
  COUNT(*)
----------
        77
 
1 row selected.
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
SQL_ID  59bf7bswaqxvh, child number 1
-------------------------------------
select count(*) from client where UPPER(ville)='PARIS'
 
Plan hash value: 3543756331
 
---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.01 |     470 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |     470 |
|*  2 |   TABLE ACCESS FULL| CLIENT |      1 |     87 |     77 |00:00:00.01 |     470 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(UPPER("VILLE")='PARIS')

Ces stats étendues peuvent être très utiles pour les applications où on applique des fonctions « maison » dans les clauses WHERE des requêtes. Toutefois, pour que ces stats étendues soient applicables ces fonctions doivent absolument être de type DETERMINISTIC.

CONCLUSION :
Lorsque vous êtes face à un problème de performance lié à l’utilisation d’un plan non-optimal pour une requête donnée, avant d’accuser le CBO de tous les maux, demandez-vous si le CBO dispose bien de toutes les informations nécessaires pour accomplir son travail. La 11g avec la possibilité de calculer des stats étendues sur un groupe de colonnes ou sur une expression peut aider dans certains cas le CBO à estimer des cardinalités plus justes et donc proposer un plan plus optimal.