Les statistiques jouent un rôle crucial dans le choix d’un plan d’exécution, puisqu’ils renseignent le Cost Based Optimizer sur le nombre de lignes et de blocs de données à brasser à chaque étape d’un plan d’exécution.
Les statistiques sur les objets sont globalement les mêmes côté Oracle et côté SQL Server. Le mécanisme manuel de création/mise à jour est quasi similaire, mais leur mise à jour automatique diffère selon les moteurs.
Pour illustrer cet article, j’ai créé une petite table:
ID PRENOM NOM CODE_POSTAL --- --------------- --------------- ----------- 1 Jean Dupont 75009 2 Michel Dumoulin 75012 3 Paul Despres 44000 4 Robert Durand 75009 5 Bernard Duval 33120 6 Bertrand Duval 75009 7 Norbert Duval 75009 8 Jules Duval 72100 9 Paul Duval 75012 10 Michel Duval 75009
Une clé primaire a été créée sur la colonne ID.
Contenu d’une statistique
Partie commune:
Une statistique (sur un index, une colonne ou un ensemble de colonnes) contient des informations sur la table:
- Nombre de lignes dans la table
- Taille moyenne d’une ligne
- Nombre de blocs/pages de la table
Des informations sur la répartition des valeurs pour la ou les colonnes:
- Nombre de valeurs distinctes, nombre de doublons
- Particularité d’une colonne (unicité, existence de valeurs nulles)
- Histogramme de la répartition des valeurs.
Légères différences pour cet histogramme:
Côté SQL Server, il existe systématiquement et contient au maximum 200 valeurs (bornes d’intervalles ou “buckets”)
Côté Oracle, il est optionnel, contient au maximum 254 valeurs et se décline en deux types (Frequency et Height Balanced) selon le nombre de valeurs distinctes dans la ou les colonnes.
Des informations sur la taille de l’index:
- Profondeur (nombre maximum de blocs à parcourir entre la racine et une feuille)
- Nombre de blocs/pages feuilles
Spécificités SQL Server
Une statistique peut porter sur un sous ensemble d’une table. On parle alors de statistique filtrée
Spécificités Oracle
Une statistique peut porter sur une fonction appliquée à une (ou plusieurs) colonne(s). On parle alors de statistique sur Expression
Une statistique sur un index contient une information supplémentaire, le (fameux) Clustering Factor. Il permet de savoir si les feuilles de l’index sont ordonnées “de la même façon” que les blocs de la table ou pas.
Une statistique peut porter sur une requête, grâce au mécanisme SQL_profile (nécessite le Tuning Pack).
Exemples
SELECT TS.TABLE_NAME,TS.NUM_ROWS,TS.BLOCKS,TS.AVG_ROW_LEN,CS.COLUMN_NAME, CS.NUM_DISTINCT,CS.DENSITY,CS.HISTOGRAM, TO_CHAR(CS.LAST_ANALYZED,'YYYY-MM-DD HH:MI') FROM USER_TAB_STATISTICS TS INNER JOIN USER_TAB_COL_STATISTICS CS ON TS.TABLE_NAME = CS.TABLE_NAME WHERE TS.TABLE_NAME='TABLE_TEST'; TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM TO_CHAR(CS.LAST_ ------------ ---------- ---------- ----------- ------------ ------------ ---------- --------------- ---------------- TABLE_TEST 10 1 22 CODE_POSTAL 5 .05 FREQUENCY 2013-02-19 10:57 TABLE_TEST 10 1 22 NOM 5 .2 NONE 2013-02-19 10:57 TABLE_TEST 10 1 22 PRENOM 8 .125 NONE 2013-02-19 10:57 TABLE_TEST 10 1 22 ID 10 .1 NONE 2013-02-19 10:57
select CAST(OBJECT_NAME(S.object_id) as VARCHAR(12)) AS 'TABLE', CAST(S.name AS VARCHAR(12)) AS 'STAT', S.user_created,S. has_filter,CAST( S.filter_definition AS varchar(45)) AS 'FILTRE',I.is_unique, I.is_primary_key, STATS_DATE(S.object_id,S.stats_id) AS 'DATE STAT'
from sys.stats S
left join sys.indexes I ON (I.index_id = S.stats_id AND I.object_id = S.object_id )
where S.object_id=OBJECT_ID('TABLE_TEST')
TABLE STAT user_created has_filter FILTRE is_unique is_primary_key DATE STAT
------------ ------------ ------------ ---------- --------------------------------------------- --------- ------------- -----------------------
TABLE_TEST PK__TABLE_TE 0 0 NULL 1 1 NULL
TABLE_TEST STAT_NOM 1 0 NULL NULL NULL 2013-02-19 17:45:43.380
TABLE_TEST STAT_CP 1 0 NULL NULL NULL 2013-02-19 17:45:43.380
TABLE_TEST STAT_FILTREE 1 1 ([prenom]>'Alain' NULL NULL 2013-02-19 17:45:43.383
AND [prenom]< 'Luc')Oracle
SELECT TABLE_NAME, INDEX_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR FROM USER_IND_STATISTICS WHERE TABLE_NAME='TABLE_TEST'; TABLE_NAME INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR ------------ ------------ ---------- ----------- ------------- ----------------- TABLE_TEST SYS_C006816 0 1 10 1
Histogramme sur la colonne CODE_POSTAL
Oracle
select ENDPOINT_VALUE, ENDPOINT_NUMBER from user_histograms where table_name='TABLE_TEST' and column_name='CODE_POSTAL';
ENDPOINT_VALUE ENDPOINT_NUMBER
-------------- ---------------
33120 1
44000 2
72100 3
75009 8
75012 10Pour la description de la valeur du Endpoint Number, une “fréquence cumulative”, je vous invite à jeter un coup d’oeil à cet article de Jonathan Lewis
SQL Server
DBCC SHOW_STATISTICS('TABLE_TEST','STAT_CP')
Name Updated Rows Rows Sampled Steps Density Average key length String Index Filter Expression Unfiltered Rows
-------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- ------ ------------- ------------------ ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------
STAT_CP Feb 19 2013 5:45PM 10 10 5 0 4 NO NULL 10
All density Average Length Columns
------------- -------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0,2 4 code_postal
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
------------ ------------- ------------- -------------------- --------------
33120 0 1 0 1
44000 0 1 0 1
72100 0 1 0 1
75009 0 5 0 1
75012 0 2 0 1Création d’une statistique
Elle s’effectue de manière identique :
Une statistique est automatiquement créée lors de la création d’un index, sur la même combinaison de colonnes.
Elle peut être créée manuellement sur une ou plusieurs colonnes, en se basant sur toutes les données de la table (FULLSCAN) ou sur un échantillon des données ( SAMPLE pour SQL Server, ESTIMATE pour Oracle).
Une statistique est créée automatiquement par l’Optimizer lorsque celui-ci estime qu’elle manque. Ceci est conditionné par un paramètre (auto_create_statistics pour une base SQL Server, et Dynamic_Sampling pour une instance Oracle), qui est positionné par défaut.
Exemple
SQL Server
CREATE STATISTICS STAT_NOM ON TABLE_TEST(nom) CREATE STATISTICS STAT_CP ON TABLE_TEST(code_postal) CREATE STATISTICS STAT_FILTREE_PRENOM ON TABLE_TEST(prenom) where prenom >'Alain' AND prenom < 'Luc' GO
Oracle
exec dbms_stats.gather_table_stats(sys_context('USERENV', 'CURRENT_SCHEMA'),'TABLE_TEST');
exec dbms_stats.gather_table_stats(sys_context('USERENV', 'CURRENT_SCHEMA'),'TABLE_TEST',METHOD_OPT =>'FOR COLUMNS SIZE 200 code_postal');Mise à jour d’une statistique
La mise à jour manuelle d’une statistique s’effectue de manière similaire à sa création sur les deux moteurs, via la commande UPDATE STATISTICS pour SQL Server et les procédures DBMS_STATS.GATHER_…_STATS pour Oracle.
Lorsqu’un index est reconstruit (Rebuild), sa statistique est automatiquement mise à jour. Attention, les statistiques ne sont pas automatiquement mises à jours lorsqu’un index est réorganisé partiellement (REORGANIZE avec SQL Server et COALESCE avec Oracle).
Le mécanisme de mise à jour automatique des statistiques effectif par défaut , et il est différent sur les deux moteurs:
Spécificité SQL Server
Lorsqu’une colonne a été modifiée à plus de 20%, toute statistique incluant cette colonne est considérée comme obsolète. Une compilation de requête sur une statistique obsolète entraîne la mise à jour de cette statistique.
l’Optimizer disposera donc toujours de statistiques fraîches, mais le temps d’exécution d’une requête pourra être allongé par la mise à jour d’une statistique.
Ce mécanisme peut être désactivé au niveau de la base (option auto_update_statistics à passer à false) ou d’une statistique (option NORECOMPUTE des ordres UPDATE STATISTICS et CREATE STATISTICS).
Spécificité Oracle
Lorsqu’une colonne a été modifiée à plus de 10% (seuil modifiable en 11g), toute statistique incluant cette colonne est marquée obsolète (état Stale), mais l’Optimizer continue de l’utiliser. Une tâche de maintenance quotidienne (planifiée par défaut à 22h) met à jour toute statistique obsolète.
L’optimizer pourra donc se baser sur des statistiques obsolètes, qui seront mises à jour au plus tard 24 heures après avoir été déclarées obsolètes.
Exemple
Oracle
exec dbms_stats.gather_table_stats(sys_context('USERENV', 'CURRENT_SCHEMA'),'TABLE_TEST');
exec dbms_stats.gather_schema_stats(sys_context('USERENV', 'CURRENT_SCHEMA'),OPTIONS =>'GATHER STALE');SQL Server
UPDATE STATISTICS TABLE_TEST(STAT_CP) UPDATE STATISTICS TABLE_TEST WITH ALL UPDATE STATISTICS TABLE_TEST(STAT_NOM) WITH NORECOMPUTE
Manipulations d’une Statistique
Il n’est pas possible (simplement) de créer de toutes pièces une statistique, ni avec Oracle ni avec SQL Server.
Il est cependant possible d’extraire une statistique et de la réimporter, soit sur la même base soit dans une autre base. Cette opération se fait via les procédures EXPORT_<TYPE D’OBJET>_STATS et IMPORT_<TYPE D’OBJET>_STATS sur Oracle et l’option STATS_STREAM des ordres DBCC SHOW_STATISTICS, CREATE STATISTICS et UPDATE STATISTICS de SQL Server.
Spécifité Oracle
Par défaut, les anciennes statistiques sont préservées pendant 31 jours, cette durée étant configurable avec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION, et peuvent être restaurées avec DBMS_STATS.RESTORE_TABLE_STATS
Exemple
SQL Server
DBCC SHOW_STATISTICS('TABLE_TEST','STAT_CP') WITH STATS_STREAM
CREATE STATISTICS STAT_IMPORT ON TABLE_TEST(CODE_POSTAL) WITH STATS_STREAM=0x01000000010000000000000000000000838304...Oracle
EXEC dbms_stats.create_stat_table(sys_context('USERENV', 'CURRENT_SCHEMA'), 'STAT_TABLE_TEST');
EXEC dbms_stats.export_table_stats(sys_context('USERENV', 'CURRENT_SCHEMA'),'TABLE_TEST',stattab=>'STAT_TABLE_TEST',statid=>'BACKUP_STAT_13_FEVRIER');
SELECT STATID,VERSION,TO_CHAR(D1,'YYYY-MM-DD HH24:MI') FROM STAT_TABLE_TEST;
EXEC dbms_stats.import_table_stats(sys_context('USERENV', 'CURRENT_SCHEMA'),'TABLE_TEST',stattab=>'STAT_TABLE_TEST',statid=>'BACKUP_STAT_13_FEVRIER');
EXEC dbms_stats.restore_table_stats(sys_context('USERENV', 'CURRENT_SCHEMA'),'TABLE_TEST', as_of_timestamp=>TO_DATE('2013-02-19 15:00', 'YYYY-MM-DD HH24:MI'));
Continuez votre lecture sur le blog :
- Nouveautés MySQL 8.0 : Les Histogrammes (Capdata team) [MySQL]
- Retrouver les tables dont les stats ne sont plus compilées en automatique (David Baffaleuf) [SQL Server]
- Production SQL Server : Les Statistiques (Benjamin VESAN) [SQL Server]
- Nouveautés MySQL 8.0 : Les indexes invisibles (Capdata team) [MySQL]
- Oracle et SQL Server: L’optimizer (Benjamin VESAN) [OracleSQL Server]
Au sujet du dernier paragraphe, il est assez simple de créer des stats “custom” pour une table ou un index sur une base oracle.
Le package dbms_stats fourni les procédure set_table_stats et set_index_stats qui permettent de manipuler des stats à l’envie avec les informations de stats basique (num_rows,numblocs,avgrlen,…).