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_NC00008
quot;='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.
