Discussion :

[Fabien Celaia]

Oracle 8.1.7.0.0 sur Sun

Soit la table

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE "W4"."CASE_VARIABLE" ("TCID" NUMBER(10) NOT NULL, 
    "VARIABLE" VARCHAR2(64) NOT NULL, "TYPE" NUMBER(5) NOT NULL, 
    "RANK" NUMBER(5), "VALUE" VARCHAR2(2000), "DOC_ID" NUMBER(10),
    "VISIBILITY" NUMBER(5), 
    CONSTRAINT "SYS_C001306" FOREIGN KEY("DOC_ID") 
    REFERENCES "W4"."DOCUMENT"("ID"))  
    TABLESPACE "W4_SCHED" PCTFREE 30 PCTUSED 50 INITRANS 1 
    MAXTRANS 255 
    STORAGE ( INITIAL 1848360K NEXT 102400K MINEXTENTS 1 
    MAXEXTENTS 249 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) 
    LOGGING

et ses indexes

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE INDEX "W4"."INX_CASE_VARIABLE_INDEX" 
    ON "W4"."CASE_VARIABLE"  ("TCID") 
    TABLESPACE "W4_INX" PCTFREE 2 INITRANS 2 MAXTRANS 255 
    STORAGE ( INITIAL 1058920K NEXT 529544K MINEXTENTS 1 
    MAXEXTENTS 249 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1) 
    LOGGING;
CREATE INDEX "W4"."INX_VARIABLE_INDEX" 
    ON "W4"."CASE_VARIABLE"  ("VARIABLE") 
    TABLESPACE "W4_INX" PCTFREE 2 INITRANS 2 MAXTRANS 255 
    STORAGE ( INITIAL 1000000K NEXT 102400K MINEXTENTS 1 
    MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST 
    GROUPS 1) 
    LOGGING;

Soit la requête succinte de repro suivante:

Code :

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

1
2
SELECT * FROM CASE_VARIABLE
WHERE VARIABLE='dossierCanton'

sachant que
1) les statistiques sont à jour
2) la sélectivité de l'index est bonne (450 valeurs distinctes sur 15 millions de tuples)
3) la stratégie de l'optimiseur au niveau instance est "CHOOSE"

Comment expliquer que l'optimiseur choisisse un FULL SCAN sur cette table ? Si on force l'optimiseur en mode RULE, l'index est correctement choisi.

A relever que si l'on choisit un SELECT VARIABLE plutôt qu'un SELECT *, l'index est correctement choisi (encore heureux avec une couverture par index ).

[SheikYerbouti]

La seule chose que je puis dire pour l'instant est qu'un index n'est valable que si la requête ramène moins de 15% des lignes. Hors 450 valeurs distinctes sur 15 millions ne rentre pas dans la plage...

Il serait plus interressant de savoir le temps pris par le select VARIABLE en comparaison avec le select *

[Pomalaix]

Pour être sûr du coup, personnellement je ferais comme suit :
- suppression de tous les index relatifs à la table
- ANALYZE TABLE CASE_VARIABLE DELETE STATISTICS;
- ANALYZE TABLE CASE_VARIABLE ESTIMATE STATISTICS;
- recréation de l'index INX_VARIABLE_INDEX (si c'est une base de test, avec la clause NOLOGGING plutôt que LOGGING, ce qui est plus rapide)
- ANALYZE INDEX INX_VARIABLE_INDEX ESTIMATE STATISTICS;
- étape facultative : faire brûler un cierge

et retester...

[Pomalaix]

Hors 450 valeurs distinctes sur 15 millions ne rentre pas dans la plage...

Salut camarade, tu veux dire quoi par là ?
Si ces valeurs sont équitablement représentées, le select sur une valeur donnée ramènera 1/450 ème des lignes, ce qui devrait bien provoquer l'utilisation de l'index.

[SheikYerbouti]

Je voulais dire très rapidement que la lecture d'un grand nombre de blocks d'index peut être parfois plus lent qu'un FULL TABLE SCAN, et si les clés distinctes sont bien éparpillées dans les blocs, l'avantage de l'index est perdu.
raison pour laquelle, je trouvais plus interressant de comparer le temps pris par chaque requête, car cela est plus interressant que de savoir si l'index est utilisé ou non.

[Fabien Celaia]

La seule chose que je puis dire pour l'instant est qu'un index n'est valable que si la requête ramène moins de 15% des lignes. Hors 450 valeurs distinctes sur 15 millions ne rentre pas dans la plage...

Il serait plus interressant de savoir le temps pris par le select VARIABLE en comparaison avec le select *

Il y a 453 valeurs distinctes sur 19'491'649. Notre requête en particuliers filtre 98345 enregistrements, soit 0.5% de la table, ce qui est inférieur au seuil des 15%.

L'exécution via RULE (=> index) prend moins de 100ms; l'exécution via CHOOSE (FULL SCAN) prend plus de 15 sec.

[rouardg]

Bonjour,

L'optimiseur a décidé de faire un FULL SCAN sur la table plutôt que d'y accèder via l'index.

En mode CBO, et sur ce type de requête, l'optimiseur se décide suivant 4 critères :

1 ) la selectivé de l'index. Ici, elle est correcte puisque avec 450 valeurs distinctes, cela signifie que pour une valeur donnée, on ramène 1 / 450 soit 0,22 % de la table.

2 ) la valeur du paramètre db_file_multiblock_read_count. Plus il est élevé, et plus on lit de blocs à chaque lecture, ce qui favorise le full scan. De mémoire, il me semble aussi qu'il y ait 2 autres paramètres (optimizer_index_cost_adj et optimizer_index_caching) qui influent sur la décision entre full scan de table et passage par index,

3 ) le clustering factor de l'index,

4 ) dans les cas extrêmes, la génération des statistiques avec les histogrammes indiquant la fréquence des données. En effet, dans le point 1), on supposait que les 450 valeurs distinctes étaient réparties de manière homogène. Or ce n'est peut-être pas le cas. Par exemple, quand FADACE exécute un :

SELECT * FROM CASE_VARIABLE
WHERE VARIABLE='dossierCanton'

est-on sur que dossierCanton représente 15000000 / 450, soit 33333 lignes ???

Pour avoir justement une idée de la distribution des données, voici une requête utilisant une fonction analytique (testée sous 9i) :

Code :

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

1
2
3
select variable, compte, round (100 * ratio_to_report (compte) over (), 2) as "%"
from (select variable, count (*) compte from case_variable
group by variable)

[Fabien Celaia]

1) Sélectivité: mes dernières valeurs semblent confirmer une bonne sélectivité.

2) rouardg, ta requête ne passe pas en v 8.1.7 => ORA-00439

3) Ce problème est apparu depuis que j'ai lancé un index rebuild sur cette table.

4) db_file_multiblock_read_count est à 8 sur les 2 serveurs (ah oui, j'ai oublié de vous dire que sur le serveur de Prod, tout marche bien, avec -parait-il - "presque" les mêmes paramètres)

5) Je suis en train de suivre la procédur de Pomalaix, mais suis relativement pessimiste quant à sa réussite compte tenu que la suppression de l'index et des stats a déjà été testée (mas pas la suppression de tous les indexes)

6) optimizer_index_cost_adj=100 et optimizer_index_caching=0 sur les 2 serveurs. On va essayer de les changer, car c'est sans doute pas bon, mais ça n'explique pas pourquoi tout se passe bien sur un des serveurs.

[rouardg]

2) rouardg, ta requête ne passe pas en v 8.1.7 => ORA-00439

Ok Fadace. Effectivement, les fonctions analytiques n'existent pas toutes en 8i. Alors utilise ceci STP :

Code :

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

1
2
3
select variable, count (*), round (100 * (count (*) / (select count (*) from case_variable)), 2) as "%"
from case_variable
group by rollup (variable)

Par contre, cela risque d'être long, car si il y a un full scan sur la table à chaque fois.

Sinon, tu peux y aller en 2 temps :

Code :

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

select count (*) from case_variable ;

et puis tu mets la valeur obtenue dans la seconde requête :

Code :

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

1
2
3
select variable, count (*), round (100 * count (*) / LA_VALEUR, 2) as "%"
from case_variable
group by rollup (variable)

6) optimizer_index_cost_adj=100 et optimizer_index_caching=0 sur les 2 serveurs. On va essayer de les changer, car c'est sans doute pas bon, mais ça n'explique pas pourquoi tout se passe bien sur un des serveurs.

Moi à ta place, j'éviterais d'y toucher car cela risque de modifier pas mal de plan d'exécution de requêtes. l'important pour toi, c'est que ces valeurs sont identiques entre les 2 valeurs.

[Fabien Celaia]

Pomalaix, comme prévu : TABLE ACCESS FULL, mode CHOOSE

[orafrance]

Déjà, il y a quand même une chose importante c'est le nombre de permutations possibles (OPTIMIZER_MAX_PERMUTATIONS) et la chance

Effectivement, Oracle calcule un certain nombre de plans d'exécution possibles pour la requête et, en fonction de formules savantes, choisis le moins pire ... sauf que si on n'a pas de chance, il peut arriver que le bon plan ne soit pas trouvé (et ceux en fonction de paramétres aussi divers qu'inconnus... de moi en tout cas ), c'est pourquoi Oracle a mis en place les hints qui permettent d'aider le CBO dans le choix du plan d'exécution.

Donc, pour ta requête, je t'invite à rajouter un hint :

Code :

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

1
2
3
SELECT /*+ index(CASE_VARIABLE ,INX_VARIABLE_INDEX) */ * 
FROM CASE_VARIABLE 
WHERE VARIABLE='dossierCanton'

Voili voilou... moi des fois je préfére ne pas trop me poser de questions sur la manière dont le CBO fonctionne parce que par moment c'est très... comment dire... spéciale

PS : il faut éviter de trop jouer avec OPTIMIZER_MAX_PERMUTATIONS parce qu'en mettant une trop grande valeur on risque fort d'augmenter la durée de la phase de parsing.

PS 2 : t'aurais pas exécuter la requête avant de créer l'index ? Si c'est le cas, le plan d'exéction sans index était peut-être en cache

[Fabien Celaia]

Eh oui ! C'est ça la beauté des optimiseurs statistiques

Bon ! la requête ne peut être modifiée car elle est générée par un outil W4 dont on n'a pas les "clefs". le forçage de l'index a déjà été testé comme tu me l'as spécifié, mais aussi en forçant la stratégie RULE: les 2 méthodes forcent correctement l'index mais, comme je viens de le dire, ne sont pas applicable dans mon cas.

L'optimizer_max_permutation, je ne pense pas qu'il soit prédominant sur une requête ne touchant que 4 tables. je me trompe peut-être, mais avec 4, il devrait tester les 24 permutations possibles...

On a essayé de modifier le paramêtre optimizer_index_cost_adj à 20, et là, les résultats sur cette requête sont probants. Le problème, c'est que l'on ne connait pas l'impact sur le reste des requêtes, cette option modifiant le comportement de l'instance.

N'y a-t-il pas moyen, sous Oracle, de visualiser/modifier l'histogramme statistique sur un index ou une colonne donnée ? De modifier puis figer un plan d'execution pour une requête donnée ?

Mais surtout comment expliquer la différence de comportement entre 2 serveurs de même configuration ? Comment un rebuid index peut-il "pourrir" les performances ?

[orafrance]

un REBUILD supprime les stats, donc tu les recalcules et voila, les stats ne sont plus les mêmes
En revanche, ce qui est peut-être probable c'est qu'après le rebuild, la requête soit passé et le plan d'exécution mis en cache... après t'as beau recalculer les stats, rien n'y fait, l'explain plan est en cache

Ce que tu peux faire aussi c'est monter le plan d'exécution en mémoire volontairement, ainsi tu garantis sa pérennité quelque soit les événements sur les objets de la requête. Malheureusement, je ne sais pas comment on fait, je vais chercher

Pour les permutations, il y a bien 4 tables mais combien d'indexes ? Ca aussi ça rentre en ligne de compte

[SheikYerbouti]

N'y a-t-il pas moyen, sous Oracle, de visualiser/modifier l'histogramme statistique sur un index ou une colonne donnée ? De modifier puis figer un plan d'execution pour une requête donnée ?

Si, les OUTLINES sont prévus pour cela.

[rouardg]

Fadace, tu ne nous as pas dit comment évoluer les données de ta table ?

Y a t'il des insertions, suppressions ou mises à jour quotidiennes ?

En tout cas, vu la simplicité de la requête (un select sur une seule table portant sur une seule colonne qui est indexée), à chaque fois qu'Oracle m'a fait un Full Scan de table au lieu d'un Range Scan d'index, c'était du au "clustering factor" de l'index qui était mauvais.

Le clustering factor (colonne CLUSTERING_FACTOR de la table USER_INDEXES) est un nombre qui est calculé lors des statistiques. Ce facteur mesure le nombre de blocs de la table qu'il faut lire lorsque l'on scanne l'index.

En clair : on a une table de 15 millions de lignes, avec 450 valeurs distinctes pour la colonne VARIABLE (soit en gros 33000 tuples pour une valeur). En supposant qu'un tuple fasse 100 octets, et que DB_BLOCK_SIZE=9182, la table comporte en gros 183000 blocs (15000000 * 100 / 8192), avec à peu près 82 tuples par bloc.

Maintenant, il y a 2 cas de figure extêmes :
1 ) les données de la table sont bien ordonnées suivant la colonne VARIABLE. A ce moment-là, nos 33000 tuples pour la valeur 'dossierCanton' vont être côte à côte et tenir dans 400 blocs de la table (33000 tuples / 82 tuples par bloc).

Dans ce cas de figure, le clustering factor est bon. Il est de l'ordre de 18300, c'est-à-dire proche du nombre de blocs de la table, et cet index sera utilisé.

L'optimiseur fera donc un Range Scan de l'index (donc lecture de X blocs de l'index), puis il ira chercher les données dans les 400 blocs de la table.

Au total, cela fait 400 + X blocs lus.

2 ) les données ne sont pas du tout ordonnées. Le pire des cas, c'est que nos 33000 tuples figurent chacun dans un bloc de la table (cela fait donc 33000 blocs de la table à lire).

Le clustering factor est alors mauvais. Sa valeur est proche du nombre de lignes de la table, au lieu d'être proche du nombre de blocs de la table.

Avec ce facteur, Oracle sait que le Range Scan d'index va lui coûter 33000 + X blocs à lire. Ce processus est coûteux, puisque pour chaque ROWID extrait de l'index, il faut aller lire un bloc de la table.
A ce moment-là, Oracle préfère faire un Full Scan de la table, surtout si le paramètre DB_FILE_MULTIBLOCK_READ_COUNT est élevé.



Pour finir, tu peux faire cette manip simplement Fadace, par un :

Code :

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

1
2
3
4
5
create table essai as select * from case_variable order by variable ;
 
create index essai_index on essai (variable) ;
 
analyze table essai compute statistics ;

Il ne te reste plus quà regarder le plan d'exécution pour un :

Code :

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

1
2
SELECT * FROM CASE_VARIABLE 
WHERE VARIABLE='dossierCanton' ;

Et au passage, rien ne t'empêche d'aller voir le clustering factor des index entre ces 2 tables.

Code :

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

select table_name, index_name, clustering_factor from user_indexes where index_name in ('INX_VARIABLE_INDEX', 'ESSAI_INDEX') ;

Si cette fois-ci, l'index est utilisé, cela signifie que les données de ta table CASE_VARIABLE ont besoin d'être réorganisées. Ou alors utilise un Hint (mais cela m'a l'air impossible pour toi), ou bien un Outline comme te l'a dit SheikYerbouti.

[Fabien Celaia]

Sur le serveur posant problème, le clustering_factor est de 13'991'005
Sur le serveur sans soucis, il est de 21'000'395

Ceci doit être, je pense, mis en parallèle avec le nombre de tupe des tables

[rouardg]

Ceci doit être, je pense, mis en parallèle avec le nombre de tupe des tables

Pourquoi, tu n'as pas 15 millions de tuples pour les 2 serveurs ???

[Fabien Celaia]

En Préproduction où le problème est apparu après un index rebuild
enregistrements=12'899'364
valeurs distinctes=453
valeur pour notre filtre=99'770
sélectivité=0.77%

En Production où le problème n'apparait pas (encore)
enregistrements=12'887'529
valeurs distinctes=426
valeur pour notre filtre=53'209
sélectivité=0.41%

La sélectivité n'est pas la même, mais elle est loin des 15% dans tous les cas. Je suis parti sur une comparaison pre-prod pure compte tenu que sans changement de données, il y avait eu changement de comportement de l'optimiseur après un simple alter index... rebuild.

[orafrance]

comme je te l'ai dis le REBUILD supprime les stats sur l'index ce qui explique une partie du changement de comportement

[SheikYerbouti]

Dans ce cas il faut comparer tous les paramètres dont peux tenir compte l'optimiseur (espace mémoire compris)... A version Oracle strictement identique, il doit forcément exister des différences entre les 2 machines.