[9i][Conseils] Statistiques et index

**nuke_y** · 20/10/2006, 11h43

Bonjour à tous (ça faisait longtemps).

j'ai rencontré récemment un problème assez épineux sur mon datawarehouse.

J'ai deux énooOOOooormes tables (disons 300 000 lignes / jour depuis 1 an pour la 1ere et 50 000 lignes / jour depuis 5 ans pour la 2e ) de la structure suivante :
[QUOTE]
Table 1
Id_table1
Id_table2
info1_table1
info2_table1
info3_table1
info4_table1
flag_table1

300 000 lignes/jour -> 70 000 000 lignes

Table 2
Id_table2
...

50 000 lignes/jour -> 60 000 000 lignes

Evidemment ces tables ne ressemblent à rien, le but étant de ramener toutes les informations sans restriction, puis d'en extraire des informations cohérentes pour les stocker proprement.

Bon bref, j'avais une clé unique sur la table1, et donc un index :

Id_table1
info1_table1
info2_table1
info3_table1
info4_table1

Pourquoi Id_table2 n'est pas présent dans la clé ? Et bien au moment de la première analyse ça ne paraissait pas nécessaire (d'ailleurs d'un point de vue fonctionnel ça ne l'est pas).

Tous les jours, je sélectionne les données exploitables pour les copier dans une autre table et je flag la table1 à 1 (Table1.flag_table1=1). Je fais ça ligne à ligne (ça prend environ 1h, donc c'est acceptable) donc je peux facilement repérer la ligne que je viens de copier et aller mettre à jour son flag.
Le process ressemble à peu près à ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SELECT ma liste de lignes
POUR CHAQUE LIGNE
INSERT ma ligne dans ma table
SI l'insert est ok ALORS
UPDATE Table1.flag_table1=1 WHERE Table1.id_table1=xxx AND Table1.id_info1_table1=xxx AND... AND Table1.id_table2=xxx
SINON
UPDATE Table1.flag_table1=-1 WHERE Table1.id_table1=xxx AND Table1.id_info1_table1=xxx AND... AND Table1.id_table2=xxx

Et ça marchait très bien.

Depuis un moment, on essaye de rapprocher les données de la Table1 de celles de la Table2. En gros on essaye de trouver pour chaque ligne de la Table2 les informations qui peuvent exister dans la Table1.

Pour accélerer un peu la jointure : Table2.id_table2 = Table1.id_table2, j'ai créé un index sur la Table1 :

id_table2

et ça marchait très bien.

Mais récemment, en essayant d'optimiser d'autres process, je me suis rendu compte que les statistiques n'étaient jamais calculées. J'ai donc demandé aux dba de calculer les statistiques pour toutes les tables qui étaient inclues dans un process particulier.
Ca a été fait, et tout marchait bien.

Mais avant-hier, le dba a décidé de calculer les statistiques pour toutes les tables (initiative que je loue). Et là c'est le drame : les délais de mon process expliqué plus haut explosent ! Ils passent de 1000 lignes / s à 1 ligne toutes les 4mn.

Après plusieurs heures de recherche j'ai finit par comprendre : suite au recalcul des statistiques, l'optimiseur d'Oracle a considéré qu'il valait mieux utiliser le 2e index basé uniquement sur id_table2 pour identifier la ligne de Table1 à mettre à jour.

En effet si vous regardez dans le process, j'utilise effectivement Table1.id_table2 dans ma requête de mise à jour.

Là où se pose un vrai problème c'est que la sélectivité de mon 2e index (celui qui est basé seulement sur le champ Table1.id_table2) est un peu particulière :

20% des données ont la valeur 0 (ce sont les données qu'on ne peut pas rapprocher)
80 % des données ont une valeur de bonne sélectivité (en gros 5 valeurs id_table1 <-> 1 valeur id_table 2).

Par conséquent, si id_table2 vaut une valeur quelconque, l'utilisation de l'index 1 ou de l'index 2 donne quasiment les même résultats (quoique sûrement un peu plus rapide avec l'index 1).

Par contre si id_table2 vaut 0, l'utilisation de l'index 2 ramène 20% des données de la table, ce qui donne une durée de 4mn pour chaque ligne.

J'ai vérifié en modifiant mon process comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SELECT ma liste de lignes
POUR CHAQUE LIGNE
INSERT ma ligne dans ma table
SI l'insert est ok ALORS
UPDATE Table1.flag_table1=1 WHERE Table1.id_table1=... AND Table1.id_info1_table1=... AND... AND Table1.id_table2=...
SINON
UPDATE Table1.flag_table1=-1 WHERE Table1.id_table1=xxx AND Table1.id_info1_table1=xxx AND... AND Table1.id_info4_table1=xxx

Et je retrouve des délais corrects.

Donc maintenant arrive la question : est-ce que c'est l'optimiseur qui fait n'importe quoi en utilisant l'index 2 qui présente une sélectivité pourrie alors qu'il pourrait utiliser l'index 1, ou est-ce qu'il y a une vraie erreur d'analyse ? Est-ce que je devrais créer un index 3 qui serait le même que l'index 1 + id_table2 ?

Merci de vos remarques, questions, conseils.

**remi4444** · 20/10/2006, 12h34

C'est typiquement le problème qui est résolu par les histograme, c'est un niveau de stat détaillé sur une colonne. Ca sert à avoir un plan différent selon la valeur du critère (0 ou pas 0 dans ton cas...). Donc avant de bidouiller la requêtes avec de HINT, il faut tenter ça.

exemple:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
ANALYZE TABLE TABLE1
   ESTIMATE STATISTICS FOR COLUMNS id_table2 
 SIZE 50;

**orafrance** · 20/10/2006, 14h08

ça marche aussi avec DBMS_STATS qu'il vaut mieux utiliser désormais

Sinon, rien d'étonnant à voir les plans d'exécution changer et les hint et/ou OUTLINES sont là pour corriger le quelques problèmes que ça génére

**remi4444** · 20/10/2006, 14h33

Envoyé par Fred_D

ça marche aussi avec DBMS_STATS qu'il vaut mieux utiliser désormais

Oui mais j'ai jamais compris exactement la commande à passer pour mettre un histogramme sur une colonne en particulier, tu as un exemple ? (ok je suis un peu "vielle école" parfois...

)

**nuke_y** · 20/10/2006, 14h56

Envoyé par Fred_D

ça marche aussi avec DBMS_STATS qu'il vaut mieux utiliser désormais

Sinon, rien d'étonnant à voir les plans d'exécution changer et les hint et/ou OUTLINES sont là pour corriger le quelques problèmes que ça génére

Ok, déjà vous me rassurez.

Quelques liens / infos / tutoriels à me donner pour que j'ai de quoi discuter avec mon dba.

Bon sinon je cherchais sur notre ami google ne vous en faites pas.

Pour les hint je ne peux pas en utiliser malheureusement : les requêtes sont générées par un outil (d'où l'intérêt d'avoir l'architecture la plus propre possible).

Merci des infos.

**remi4444** · 20/10/2006, 15h05

Un truc tout bete si tu veux éviter le passage par un index sur ta colonne id_table2, au lieu de faire une simple égalité Table1.id_table2 = 'machin' tu fais Table1.id_table2||'' = 'machin' . Comme oracle va voir non pas la colonne id_table2 mais un "calcul" ça lui interdira de passer par l'index

Là j'ai supposé que ton identifiant était une chaine de caractère, si c'est un entier tu fait par exemple: (Table1.id_table2+0) = 2456

C'est très laid mais ça dépanne parfois

**nuke_y** · 20/10/2006, 15h16

Alors effectivement... c'est génial !

Je me le garde dans un coin du ciboulot ce truc là... Ou alors je vais ptet même le mettre en production dès lundi. Un truc du genre, qui empêche que l'index soit utilisé.

Merci de l'idée.

Enfin je vais quand même essayer de faire en sorte que le bon index soit utilisé de manière propre.

**orafrance** · 20/10/2006, 15h17

Envoyé par remi4444

Oui mais j'ai jamais compris exactement la commande à passer pour mettre un histogramme sur une colonne en particulier, tu as un exemple ? (ok je suis un peu "vielle école" parfois...

)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

dbms_stats.gather_table_stats ('OWNER','TABLE1', estimate => 20,method_opt => 'FOR COLUMNS id_table2  SIZE 50');

**remi4444** · 20/10/2006, 17h59

Envoyé par Fred_D

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

dbms_stats.gather_table_stats ('OWNER','TABLE1', estimate => 20,method_opt => 'FOR COLUMNS id_table2  SIZE 50');

Ah oui, en fait, on écrit les même choses dans une fonction et dans un ordre différent, mais là au moins c'est moderne

**nuke_y** · 20/10/2006, 18h48

Ben c'est surtout que si les résultats sont meilleurs...

**orafrance** · 23/10/2006, 07h58

en effet, DBMS_STATS profitent de pas mal de correction alors que ANALYZE n'évolue plus

**remi4444** · 23/10/2006, 10h42

Envoyé par Fred_D

en effet, DBMS_STATS profitent de pas mal de correction alors que ANALYZE n'évolue plus

Moi j'avais l'impression que c'était surtout une question de pratique d'utiliser un seul package normalisé, il me semble que pour le cas précis, c'est exactement la même chose qui est faite... mais effectivement autant utiliser ce qui est préconisé

**orafrance** · 23/10/2006, 10h47

Ha non... les histogrammes sont mal calculés (voir pas calculé

) avec ANALYZE.

**remi4444** · 23/10/2006, 11h38

Envoyé par Fred_D

Ha non... les histogrammes sont mal calculés (voir pas calculé

) avec ANALYZE.

non là quand même je te trouve très pessimiste, on peux quand meme constaté aprés avoir précisé:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
ANALYZE TABLE TABLE1
   ESTIMATE STATISTICS FOR COLUMNS id_table2 
 SIZE 50;

qu'il y a 49 lignes de plus dans la table SYS.HISTGRM$ j'ose espérer que ça veux dire que les histogrammes sont bien calculés...

**orafrance** · 23/10/2006, 11h45

d'après Oracle, ils sont mal interprétés.

**remi4444** · 23/10/2006, 11h48

Envoyé par Fred_D

d'après Oracle, ils sont mal interprétés.

génial... faisez en vous des beaux histogrammes

**orafrance** · 23/10/2006, 11h55

pour info :

Using DBMS_STATS is the only way to access the new sampling functionality
provided in later releases since ANALYZE is not being enhanced.

- Note 236935.1 Global statistics - DBMS_STATS versus ANALYZE
- estimate_percent => dbms_stats.auto_sample_size
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', -
- degree => DBMS_STATS.DEFAULT_DEGREE, -
- options => 'GATHER AUTO', -

en fait, c'est surtout pour l'autosampling

**nuke_y** · 23/10/2006, 14h19

Mouaih, je vais voir avec mon dba quelle solution il a utilisé, et si on peut utiliser une autre méthode.

**remi4444** · 23/10/2006, 14h28

Envoyé par nuke_y

Mouaih, je vais voir avec mon dba quelle solution il a utilisé, et si on peut utiliser une autre méthode.

Souvent l'histogramme n'est pas une stat que les dba vont faire à priori, c'est après une reflexion et un premier retour d'expérience qu'on se rend compte qu'il peut y avoir des problèmes sur une colonne ou la répartition des données est très hétérogène, seulement à ce moment là on cible des stats avec histogramme sur ces colonnes particulières.

**nuke_y** · 23/10/2006, 15h04

hum ok, ça expliquerait pas mal de choses. Mon dba est plutôt compétent et sympa alors je me demandais pourquoi il n'avait pas utilisé cette solution tout de suite.

[9i][Conseils] Statistiques et index

Oracle

Discussions similaires

Partager

Partager