Oracle et SQL Server: Les Statistiques

Lundi, février 25, 2013
By Benjamin VESAN in Oracle (bvesan@capdata-osmozium.com) [41 article(s)]

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

 Statistiques sur colonnes
Oracle
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
SQL Server
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')
Statistique sur index

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              10

Pour 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                    1

Cré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 :




Cliquer pour partager cet article sur Viadeo
Cliquer sur "CAPTURER" pour sauvegarder cet article dans Evernote Clip to Evernote

Tags: , , ,

2 Responses to “Oracle et SQL Server: Les Statistiques”

  1. Florian Meyrueis

    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,…).

    #55814
  2. Benjamin VESAN

    Bonjour Florian,
    Je suis d’accord avec cette remarque, mais l’utilisation des procédures DBMS_STATS.SET_*_STATS ne permet pas (à ma connaissance) de créer l’histogramme, qui reste pour moi le cœur d’une statistique. C’est pourquoi il reste important à mon sens de savoir extraire une statistique complète et et la réimporter.

    #55827

Leave a Reply