Discussion :

[orafrance]

il faut savoir que c'est très couteux et parfois buggé... ce qui peut justifier de le pas les passer

[nuke_y]

Buggé ?? Arf, ça m'arrange pas ça...

[orafrance]

parfois

[remi4444]

il faut savoir que c'est très couteux et parfois buggé... ce qui peut justifier de le pas les passer

Le passer sur toutes les colonnes de toutes les tables, ça serait ennormément de traitement pour pas grand chose. Par contre je serais moins pessimiste que Fred, j'ai souvent vu les histogrammes très bien fonctionner en 8i, alors en 9i on peut légitimement s'attendre à ce que ça marche quand meme...

Mon dba est plutôt compétent et sympa

comme tous les DBA non ?

[nuke_y]

Nan, j'en ai eu qui pensaient que leur travail se résumait à faire des sauvegardes et maintenir les serveurs et d'autres qui refusaient carrément de faire leur boulot...

[nuke_y]

Bon après résolution du 1er problème, je m'attaque au 2e : un certain nombre de requêtes semblent avoir été impactées en mal par le calcul des statistiques.

Pour exemple, les requêtes suivantes (seuls les hints diffèrent) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
SELECT count(1) FROM
(
SELECT
G_T0.ID , 
G_T0.DATE , 
COUNT(DISTINCT G_T1.T1_NO)  
FROM
"TABLE0" G_T0, 
"TABLE1" G_T1 
WHERE
( TO_CHAR(G_T0.T0_NO)  = G_T1.T0_NO AND G_T0.REF_ID = 1  )
AND ( G_T0.DATE  >= '02/10/2006'   AND G_T0.DATE  <= '06/10/2006'   ) 
GROUP BY
G_T0.ID, 
G_T0.DATE
);

coût : 107.236
durée : 17mn 17s
résultat : 212.262 lignes

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
SELECT count(1) FROM
(
SELECT
/*+INDEX(G_T1 I_NO) */
G_T0.ID , 
G_T0.DATE , 
COUNT(DISTINCT G_T1.NO)  
FROM
"TABLE0" G_T0, 
"TABLE1" G_T1 
WHERE
( TO_CHAR(G_T0.T0_NO)  = G_T1.T0_NO AND G_T0.REF_ID = 1  )
AND ( G_T0.DATE  >= '02/10/2006'   AND G_T0.DATE  <= '06/10/2006'   ) 
GROUP BY
G_T0.ID, 
G_T0.DATE
);

coût : 1.949.311
durée : 17mn 17s
résultat : 212.262 lignes

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
SELECT count(1) FROM
(
SELECT
/*+USE_NL(G_T1 G_T0) */
G_T0.ID , 
G_T0.DATE , 
COUNT(DISTINCT G_T1.NO)  
FROM
"TABLE0" G_T0, 
"TABLE1" G_T1 
WHERE
( TO_CHAR(G_T0.T0_NO)  = G_T1.T0_NO AND G_T0.REF_ID = 1  )
AND ( G_T0.DATE  >= '02/10/2006'   AND G_T0.DATE  <= '06/10/2006'   ) 
GROUP BY
G_T0.ID, 
G_T0.DATE
);

coût : 5.757.900
durée : 1mn 46s
résultat : 212.262 lignes

Alors explication de la requête :

Table0 contient beaucoup de lignes et le critère de sélection et sur la DATE.
Table1 contient beaucoup de lignes, environ 5 lignes pour chaque ligne de Table0.
On obtient les lignes de Table1 qui sont rapprochables de celles de Table0 en utilisant la jointure Table0.T0_NO = Table1.T0_NO mais comme ils n'ont pas le même type (sans commentaire), une conversion a lieu (d'ailleurs j'ai fait des tests pour voir, la conversion ne pose pas directement de problème de perfs). De plus on filtre sur le Table0.REF_ID = 1 (il y a 3 domaines : le 1, le 2 et le 3).

Le but de la requête est de ramener le nombre distinct de Table1.No qu'on peut trouver pour chaque Table0.Id et chaque Table0.DATE.

On constate, en regardant les tests, que les coûts s'envolent alors que les performances sont meilleures. De plus le 3e plan d'exécution (celui obtenu avec le NL), qui est le meilleur, est le MEME que celui que j'obtient sur une autre instance où les statistiques n'ont pas été calculées, et c'est le MEME que celui que j'obtenais avant le calcul des statistiques sur le serveur de production.

Alors
1) Il me semble que l'optimiseur choisi le plan d'exécution avec le moins de coûts, donc il a raison de choisir le 1er non ?
2) Mais par contre son calcul des coûts est biaisé non ?
3) Je n'ai pas encore demandé le calcul des histogrammes pour le colonne Table1.T0_NO mais ça va venir (je ne veux pas impacter les traitements de demain, qui seront longs, mais au moins je sais pourquoi et de combien).
4) Comment on fait pour mettre plusieurs hints à une requête ?

Merci de vos commentaires / conseils / critiques / réponses.

[orafrance]

1) oui mais le Hash Join est moins couteux selon l'optimiseur ce qui est loin d'être toujours le cas
2) en effet, les régles de calcul sont complexes et favorise certaines situation... et il dépend d'élement comme les stats, les paramètres, etc...
4) par exemple : /*+ index (tab index) ordered use_nl (tab1 tab2) */

[remi4444]

Remarques:

- Encapsuler la requête dans un COUNT je suppose que c'est pour le test ? parcque sinon faire un gros tri (count(distinct)) pour ensuite le jeter c'est un peu du gaspillage

- Il est effectivement normal que lorsqu'on met des HINT le "cout" soit toujours plus haut que sans. Car le cout n'est qu'un calcul théorique fait avant l'exécution de la requête et oracle choisi le moindre, or il arrive que ces couts théoriques soient éloignés des couts réel, d'ou les 2 methodes pour palier à ça:
* soit faire des stats plus fines (histogramme) pour que le calcul du cout soit meilleur
* soit faire fi de ce calcul en forçant oracle dans ses choix par des HINT meme si les cout calculés ne sont pas les "meilleurs".

- "TO_CHAR(G_T0.T0_NO)" c'est indispensable ? car le fait de faire un calcul disqualifie l'éventuelle utilisation d'un index sur TABLE0 ce qui est peut etre dommage...

- La colonne G_T0.DATE est une candidate typique pour avoir un histogramme parceque la selectivité d'un encadrement est faible à priori pour oracle (1/4 il me semble...)

Si tu trouves que oracle privilégie un peu trop les FULL et HASH par rapport aux boucles / index. Tu peux diminuer le paramètre OPTIMIZER_INDEX_COST_ADJ (cout d'acces à un index). Il est par défaut à 100 ce qui est souvent sur-évalué. C'est un paramètre qui peut se changer au niveau de la session donc pratique pour les tests. Tu peux le tester à 20 puis à 5, (le mettre à 1 ne se fait que pour du débuggage car ça force toutes les utilisations d'index y compris les index-fast-full-scan ce qui peux dégrader fortement les performances)

[orafrance]

- Il est effectivement normal que lorsqu'on met des HINT le "cout" soit toujours plus haut que sans. Car le cout n'est qu'un calcul théorique fait avant l'exécution de la requête et oracle choisi le moindre,

tu confonds l'ajout de hint avec une différence d'exécution ou uniquement parsing... c'est très différent

[remi4444]

tu confonds l'ajout de hint avec une différence d'exécution ou uniquement parsing... c'est très différent

Justement, le HINT sert à forcer une execution différente non ?

[orafrance]

Si tu trouves que oracle privilégie un peu trop les FULL et HASH par rapport aux boucles / index. Tu peux diminuer le paramètre OPTIMIZER_INDEX_COST_ADJ (cout d'acces à un index). Il est par défaut à 100 ce qui est souvent sur-évalué. C'est un paramètre qui peut se changer au niveau de la session donc pratique pour les tests. Tu peux le tester à 20 puis à 5, (le mettre à 1 ne se fait que pour du débuggage car ça force toutes les utilisations d'index y compris les index-fast-full-scan ce qui peux dégrader fortement les performances)

hop hop hop... on se calme avec ce paramètre. Pour réduire (ou supprimer) le hash on fera plutôt un alter session pour changer hash_area_size ou carrément hash_join_enabled.

Il fait faire TRES attention à l'utilisation de OPTIMIZER_INDEX_COST_ADJ qui permet de modifier le cout d'un index (50% = je dis qu'un accés par index est 50% moins couteux que la normale).
En modifiant ce paramètre on risque d'orienter maladroitement l'optimiseur en présumant que les full scan sont moins intéressant alors que l'éradication des full scan est loin d'être raisonnable.

pour info : http://www.lc.leidenuniv.nl/awcourse...mops.htm#47011

SOYEZ TRES PRUDENT

[orafrance]

Justement, le HINT sert à forcer une execution différente non ?

oui mais le hint change le cout rien qu'au parsing. A l'exécution le coût peut encore changer si on fait du estimate sur le calcul de stats

[remi4444]

oui mais le hint change le cout rien qu'au parsing. A l'exécution le coût peut encore changer si on fait du estimate sur le calcul de stats

Ah ben oui, on est bien d'accord, c'est justement ce que j'avais tenté d'exprimer, mais j'ai pas du etre clair désolé...

[nuke_y]

- "TO_CHAR(G_T0.T0_NO)" c'est indispensable ? car le fait de faire un calcul disqualifie l'éventuelle utilisation d'un index sur TABLE0 ce qui est peut etre dommage...

En fait le type qui a designé ça a estimé qu'on avait tord d'utiliser T0_NO en caractère et a décidé que ça serait un nombre, en allant à l'encontre de la continuité avec l'existant... Enfin bon, sans commentaire... Un jour je refondrais ça correctement...

Sinon l'histogramme je l'aurai plutôt vu sur G_T1.T0_NO, parce que la sélectivité est pourrie (9% des données valent "0", le reste c'est des valeurs distinctes).

[remi4444]

SOYEZ TRES PRUDENT

Absoluement, il faut etre prudent...
Pour information, j'ai travaillé sur une des bases oracle les plus importantes de France, c'est oracle qui nous avais préconisé de le mettre à 5. Et les benchmark avaient démontrés que c'était la bonne valeur.
Cepentant je suis d'accord, ce ne sont pas des paramètres qui se changent à la légère. Par contre c'est moins risqué de tester en début de batch un ajustement au niveau session, comme ça on ne perturbe personne d'autre...

[remi4444]

Sinon l'histogramme je l'aurai plutôt vu sur G_T1.T0_NO, parce que la sélectivité est pourrie (9% des données valent "0", le reste c'est des valeurs distinctes).

L'un n'empêche pas l'autre...