Articles autour des SGBD Oracle, SQL Server & PostgreSQL
28 Mars 2018
Pendant que mon acolyte Laurent profite du soleil et des températures positives en Inde, je me suis dit que rédiger un article allait bien me réchauffer quelque peu les doigts ! Alors pourquoi ne pas en profiter pour parler des statistiques Oracle, un vaste sujet dans lequel nous avons tous mis les main à un moment où à un autre.
Dans un environnement transactionnel et batch, les données d'une base sont constamment ajoutées, modifiées et supprimées. Après un batch qui fait du delete ou des insertions en masse, comment Oracle peut savoir (ou estimer) la quantité de données manipulées et surtout comment adapter les plans d’exécution en fonction ?
Évidemment, ce sont les statistiques objets qui sont collectées par défaut quotidiennement (à travers un job faisant parti d'un groupe de taches automatiquement planifiées dites "autotasks") qui permettent de mettre à jour les compteurs et d'estimer au mieux la quantité et la répartition des données au sein des tables. Ces statistiques sont combinées aux statistiques système (s'il y en a) ce qui va permettre à l'optimiseur oracle CBO (Cost Based Optimizer) de générer un plan d’exécution le plus efficace possible (ça, c'est la théorie...)
Parfois, il peut arriver que les réglages à mettre en œuvre soient plus fins que prévus, comme par exemple modifier certaines options de collecte mais uniquement sur une table, ou un groupe de tables, ou encore fixer les statistiques d'une table pour ne plus qu'elles soit recalculées.
Nous allons voir à travers cet article comment changer le seuil de pourcentage de données modifiées d'une table (STALE_PERCENT) pour obtenir des statistiques considérées comme trop anciennes par l'optimiseur, et comment fixer une méthode de calcul pour les histogrammes sur une colonne.
Ma configuration reste classique, à savoir une VM équipée de 1 CPU & 4Go de RAM et bien sur d'un noyau oracle 12.2.0.1.
Tout d'abord nous allons créer un jeu de test sur le schéma "MRS" avec une table dans laquelle il y aura une bonne quantité de rows.
SQL> CREATE TABLE MRS.REPONSE_1
(
Q1 NUMBER,
Q2 NUMBER,
Q3 NUMBER,
Q4 NUMBER,
Q5 NUMBER,
Q6 NUMBER,
Q7 NUMBER,
Q8 NUMBER,
Q9 NUMBER,
Q10 NUMBER,
ID_PAYS NUMBER,
ID_FORMULAIRE NUMBER,
DATE_QUESTIONNAIRE NUMBER)
NOLOGGING;
Table created.
SQL> BEGIN
FOR i IN 1..25 LOOP
FOR j IN 1..500000 LOOP
INSERT /*+APPEND*/ INTO MRS.Reponse_1 VALUES (
ROUND(DBMS_RANDOM.VALUE(0,10)),
ROUND(DBMS_RANDOM.VALUE(0,10)),
ROUND(DBMS_RANDOM.VALUE(0,10)),
ROUND(DBMS_RANDOM.VALUE(0,10)),
ROUND(DBMS_RANDOM.VALUE(0,10)),
ROUND(DBMS_RANDOM.VALUE(0,10)),
ROUND(DBMS_RANDOM.VALUE(0,10)),
ROUND(DBMS_RANDOM.VALUE(0,10)),
ROUND(DBMS_RANDOM.VALUE(0,10)),
ROUND(DBMS_RANDOM.VALUE(0,10)),
ROUND(DBMS_RANDOM.VALUE(1,25)),
ROUND(DBMS_RANDOM.VALUE(1,100)),
ROUND(DBMS_RANDOM.VALUE(20071201,20071231)));
END LOOP;
COMMIT;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
SQL> select count(*) from MRS.Reponse_1;
COUNT(*)
----------
12500000
Afin d'initialiser les statistiques, je dois les collecter sur ma table (sinon l'optimiseur ne peut pas avoir un "état avant" de la quantité de données et de leur répartition au sein de la table)
SQL> exec dbms_stats.gather_table_stats('MRS','REPONSE_1');
PL/SQL procedure successfully completed.
SQL> select s.TABLE_NAME,
s.NUM_ROWS "rows in stats",
to_char(s.LAST_ANALYZED,'DD-MM-YYYY HH24:MI') "last Analyzed",
s.STALE_STATS as "Stale?",
t.num_rows "rows in table",
from dba_tab_statistics s, dba_tables t
where s.table_name=t.table_name
and t.owner='MRS';
TABLE_NAME rows in stats last Analyzed Stale? rows in table
------------ ------------- ---------------- ---- -------------
REPONSE_1 12500000 27-03-2018 09:29 NO 12500000
Bien, je vais maintenant lancer un DELETE afin de générer un pourcentage de données modifiées, afin par la suite d'adapter la collecte en modifiant le seuil de déclenchement.
SQL> select count(*) from mrs.reponse_1; COUNT(*) ---------- 12500000 SQL> delete from mrs.reponse_1 where id_pays=1; 260435 rows deleted. SQL> delete from mrs.reponse_1 where id_pays=20; 521202 rows deleted. SQL> select count(*) from mrs.reponse_1; COUNT(*) ---------- 11718363 SQL> commit ;
Je vais maintenant préciser à Oracle que les statistiques doivent être calculées sur ma table REPONSE_1 dès lors où 5% des données ont été modifiées, car dans mon exemple de DELETE je n'ai supprimé que 6,2% de son contenu.
En effet, tant que le seuil des 10% de données modifiées n'a pas été atteint, alors les statistiques de ma table REPONSE_1 sont considérées comme à jour, mais 6% de mouvement sur une table de plus de 10 millions de lignes pourrait avoir un impact important, d'où le besoin d'avoir à modifier ce type de paramètre.
Vérifions donc cela :
SQL> select dbms_stats.get_prefs('STALE_PERCENT','MRS','REPONSE_1') "stale percent" from dual;
stale percent
-----------------------
10
Si je modifie le seuil et le passe a 5%, l'optimiseur devra considérer comme trop anciennes les statistiques (STALE) :
SQL> begin
dbms_stats.set_table_prefs (
'MRS',
'REPONSE_1',
'STALE_PERCENT',
'5');
end;
/
PL/SQL procedure successfully completed.
SQL> select dbms_stats.get_prefs('STALE_PERCENT','MRS','REPONSE_1') "stale percent" from dual;
stale percent
------------------------
5
SQL> select s.TABLE_NAME,
s.NUM_ROWS "rows in stats",
to_char(s.LAST_ANALYZED,'DD-MM-YYYY HH24:MI') "last Analyzed",
s.STALE_STATS as "Stale?",
t.num_rows "rows in table",
from dba_tab_statistics s, dba_tables t
where s.table_name=t.table_name
and t.owner='MRS';
TABLE_NAME rows in stats last Analyzed Stale? rows in table
------------ ------------- ---------------- ---- -------------
REPONSE_1 12500000 27-03-2018 09:29 YES 12500000
Parfait, en baissant notre seuil à 5%, les stats deviennent anciennes (STALE) et Oracle indique qu'il est nécessaire de les recalculer. Il suffit par la suite de relancer un simple calcul de stats pour les rafraichir, ce que je fais car cela me sera utile pour la suite de l'article.
SQL>exec dbms_stats.gather_table_stats('MRS','REPONSE_1');
PL/SQL procedure successfully completed.
A présent, intéressons nous aux histogrammes. Par défaut les histogrammes sont collectés avec l'option "FOR ALL COLUMNS SIZE AUTO" et Oracle recommande de conserver ce mode.
Cependant, il peut arriver d'avoir à régler plus finement ce calcul afin d'améliorer les performances des requêtes SQL, notamment en spécifiant des réglages spécifiques sur une ou plusieurs tables.
Pour identifier la bonne configuration à mettre en œuvre il faut plonger dans la table et étudier la répartition des données au sein des différentes colonnes.
Par exemple si je prend la colonne Q1 de ma table REPONSE_1, j'ai une répartition par pays (ID_PAYS) assez homogène, sauf pour Q1=10 et Q1=0 :
SQL> select q1,count(id_pays) from mrs.reponse_1 group by q1 order by 1;
Q1 COUNT(ID_PAYS)
---------- --------------
0 585431
1 1170380
2 1172390
3 1170278
4 1173418
5 1173813
6 1171061
7 1170647
8 1171704
9 1172261
10 586980
Je peux donc indiquer à oracle de calculer les histogrammes pour ma colonne Q1, pour que l'optimiseur puisse être capable d'estimer la cardinalité des données, pour générer le meilleur plan d’exécution possible.
Nous allons le vérifier en utilisant une requête simple pour visualiser les différences entre les plans d’exécutions "avec et sans histogrammes", en ayant pris au préalable soin de flusher la shared pool et le buffer cache :
SQL> alter system flush shared_pool; System altered. SQL> alter system flush buffer_cache; System altered.
Afin de visualiser en détail les ROWS estimées par l'optimiseur et les ROWS réellement parcourues (E-Rows / A-Rows), j'utilise le HINT "GATHER_PLAN_STATISTICS" (disponible depuis la version 10g) qui me permettra de faire afficher tous les détails dans mon plan d’exécution, en utilisant ensuite par la procédure "DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST')".
Attention : Pour avoir toutes les statistiques réelles liée à l’exécution de ma requête, il faut qu'elle soit totalement jouée, je ne peux pas utiliser "explain plan" ou "autotrace traceonly".
SQL> select /*+ GATHER_PLAN_STATISTICS */ id_pays from reponse_1 where q1=10 or q1=0; .. .. <=== je passe sur la sortie écran de mes valeurs .. 1172411 rows selected. SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------- SQL_ID 2pfkxw4pccgrv, child number 0 ------------------------------------- select /*+ GATHER_PLAN_STATISTICS */ id_pays from reponse_1 where q1=10 or q1=0 Plan hash value: 1548463714 | Id | Operation | Name | Starts | E-Rows| A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1172K |00:00:01.65 | 158K| 80478 | |* 1 | TABLE ACCESS FULL| REPONSE_1 | 1 | 2130K| 1172K |00:00:01.65 | 158K| 80478 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("Q1"=0 OR "Q1"=10))
La lecture du plan d'exécution nous indique que l'optimiseur estime avoir plus de 2 millions de lignes à parcourir (E-Rows 2130K), alors qu'en réel il n'y en aurait que la moitié environ (A-Rows à 1172K).
Si je vérifie les compteurs de l'optimiseur sur ma colonne, sans calcul d'histogrammes :
SQL> SELECT COLUMN_NAME,ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_HISTOGRAMS WHERE TABLE_NAME='REPONSE_1' AND COLUMN_NAME = 'Q1'; COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE -------------------- --------------- -------------- Q1 1 10 Q1 0 0
Je n'ai bien sur aucune information sur la répartition des données au sein de ma colonne, alors modifions cela :
SQL> begin dbms_stats.set_table_prefs( 'MRS', 'REPONSE_1', 'method_opt', 'for all columns size 1 for columns size 254 Q1'); PL/SQL procedure successfully completed.
Je lance un calcul de statistiques "classique" :
SQL> exec dbms_stats.gather_table_stats('MRS','REPONSE_1');
PL/SQL procedure successfully completed.
Je verifie maintenant les compteurs que cela à généré afin de voir ce qu'à échantillonné l'optimiseur oracle :
SQL> SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME='REPONSE_1' AND COLUMN_NAME ='Q1'; TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM --------------- ------------------------- ------------ --------------- REPONSE_1 Q1 11 FREQUENCY SQL> SELECT COLUMN_NAME,ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_HISTOGRAMS WHERE TABLE_NAME='REPONSE_1' AND COLUMN_NAME ='Q1'; COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ------------------------- --------------- -------------- Q1 585431 0 Q1 11718363 10 Q1 11131383 9 Q1 9959122 8 Q1 8787418 7 Q1 1755811 1 Q1 6445710 5 Q1 5271897 4 Q1 4098479 3 Q1 2928201 2 Q1 7616771 6
Par exemple, l'optimiseur nous indique pour une valeur de Q1=10 il y a entre 11718363 (endpoint value 10) et 11131383 (endpoint value 9) d'enregistrements, soit 586980 au total, vérifions donc :
SQL> select count(q1) from mrs.reponse_1 where q1=10;
COUNT(Q1)
----------
586980
Bien, et coté SQL cela nous donne quoi de beau ? Relancons donc notre SQL du début :
SQL> alter system flush shared_pool; System altered. SQL> alter system flush buffer_cache; System altered. SQL> select /*+ GATHER_PLAN_STATISTICS */ id_pays from reponse_1 where q1=10 or q1=0; .. .. <=== je passe sur la sortie ecran de mes valeurs .. 1172411 rows selected. SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------- SQL_ID 2pfkxw4pccgrv, child number 0 ------------------------------------- select /*+ GATHER_PLAN_STATISTICS */ id_pays from reponse_1 where q1=10 or q1=0 Plan hash value: 1548463714 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1172K|00:00:01.65 | 158K| 80478 | |* 1 | TABLE ACCESS FULL| REPONSE_1 | 1 | 1172K| 1172K|00:00:01.65 | 158K| 80478 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("Q1"=0 OR "Q1"=10))
La lecture de ce plan d’exécution nous montre que l'optimiseur a bien été mis à jour avec les histogrammes calculés sur ma colonne Q1, puisque nous avons une estimation de lignes à parcourir (E-Rows) égale à ce qui est réellement traité (A-Rows) durant l’exécution de ma requête !
Dans un prochain article, nous verrons comment fixer ces statistiques pour ne plus avoir de calcul sur ma table, pour éviter (par exemple) d'avoir des changements de plan d’exécution qui s’avéreraient moins performants.
Enjoy !
/image%2F1471192%2F20180417%2Fob_b20714_taz01-10.jpg)
/image%2F1471192%2F20180323%2Fob_9ea1b2_lami-23.jpg)
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)