Overblog
Editer l'article Suivre ce blog Administration + Créer mon blog

LAMI DBA

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 !

commentaires

Tchoukouaha 08/02/2020 09:32

Bonjour Monsieur,
Votre article est d'une très grande qualité.
Et je vous remercie pour votre travail.
Cordialement.
Georges Tchoukouaha

Articles autour des SGBD Oracle, SQL Server & PostgreSQL

A propos de LAMI-DBA

Le Blog LAMI-DBA est la fusion de deux blogs existants, celui de LAurent (laodba) et celui de MIckael (dbafaq), deux DBA passionnés des sgbd, et particulièrement d'Oracle.

 

Laurent, 47 ans, Expert Oracle & MS SQL Server, Team Leader, dit "Le Taz", Certifié Expert RAC 11G, Exadata Implementation Specialist, OCA 11G, 

Profil Linkdin

 

 

 

Mickael, 37 ans, Expert Oracle, dit "Batman", Certifié Expert RAC 11G, OCP 11G, OCP 10G,

Profil Linkdin      

 

Hébergé par Overblog