Analyze, Rebuild sous Oracle 9.2.0.4 [Résolu]

[Knonix]

Bonjour,

Suite à un problême résolu lors d'une autre discusion lié à une migration d'un oracle 8.1.7 à un oracle 9.2.0.4, je me retrouve avec une base qui lorsque je calcule les stats dessus, provoque des scan full dans les requêtes comme si les index n'existait plus (hors ce n'est pas le cas). LE fait de faire un rebuild des index ne suffit pas et me donne le même résultat, par lorsque je réalise un move de ma table (pour simuler un rebuild) alors mes index passe en statut UNUSABLE et là je les reconstruit en faisant un alter index <name> rebuilt tablespace et par la suite ma requête se remet à fonctionner correctement sans faire de scan full.
Donc 1) je ne comprends pas pourquoi ma requête au départ après le passage des stats ne passe pas par mes index 2) auriez vous une autre maniére de faire.

Tout cela en sachant que je ne pourrais pas patcher mes bases dans une version sup (gros groupe lié à une référence technique fixe).

[13thFloor]

Si les index ne sont plus utilisés c'est que l'optimiseur a préféré faire un FTS qu'il a estimé moins couteux que d'utiliser un index.
Si après un rebuild d'index, le plan d'exécution change (et les index sont à nouveau utilisés), je pense que l'index doit à présent occuper moins de place, d'ou un coût potentiellement moins élévé qu'un FTS.
La présence d'histogramme en viendrait-elle pas provoquer ces changements de plan ?

PS : quelle est la valeur du paramètre OPTIMIZER_MODE ?

[orafrance]

le MOVE supprime les stats et alors tu passes en mode RULE... probablement que les stats ne sont pas assez précises... mais est-ce que ta requête est plus lente avec les FTS ?

[Knonix]

Super réponse et parfaitement Claire, merci.

L'optimizer_mode est à la valeur Choose, mais si il me fait passer les requêtes en dehors des index (d'où un temps d'exécution passant de 13s (avec les index) à 45mn (dans l'autre cas)). Dois je le passer alors en RULE (c'est une base infocentre avec requetage par Bsussiness Objects

De plus qu'est qu'un histogramme ?? désolé

[orafrance]

je viens de voir le titre... tu calcules pas les stats avec ANALYSE j'espère

Si c'est le cas, utilise DBMS_STATS

Les histogrammes sont des stats sur la répartition des valeurs dans les colonnes -> cf doc

[Knonix]

Non pour les stats je ne passe pas par un analyze mais par un :

Code :

1
2
 
dbms_stats.gather_database_stats();

[orafrance]

ce serait pas mal de voir l'explain plan et la requête

[Knonix]

En fait je me retrouve avec les problêmes pour la même requête que dans le sujet suivant que j'avais lançé :
http://www.developpez.net/forums/sho...d.php?t=573410
J'avais marqué le sujet comme résolu car j'avais trouvé une solution de contournement qui me permettais de faire fonctionner enfin les requêtes, mais à présent je suis à la recherche de la mise en place dune solution pérenne qui me permettrait de recalculer mes stats sans perdre mes performances (ce qui n'est pas le but recherché).

[Knonix]

Si je passe mon optimizer_mode à RULE, alors en effet même si je passe le package DBMS_STATS, je conserve mes performances et ma requête passe bien par mes index

Donc ma question est la suivante :
Quel sont les risques à passer ma base en RULE plutôt qu'en CHOOSE ? mis à part le fait que je le force à passer par mes index.

[orafrance]

tu abandonnes ce qui fait la force de l'optimiseur Oracle depuis la 8i à savoir le CBO. C'est bien dommage pour une requête qui pose problème

[Knonix]

Ok en effet je me suis renseigné sur les CBO et il pourrait être dangereux de l'abandonner car cela me résout le problème pour une requête mais qu'en sera t'il des 3000 autres ????

Je pense que c'est réellement mon import de oracle 8i (fait en from user to user) qui est l'unique responsable de ce bordel, aussi nous allons je pense faire une création de schéma puis un import table par table des données, une reconstruction des index et finir par une analyse, cela devrait normalement me stabilisé l'ensemble.

Encore une dernière question, juste pour mon information, le fait de passer en mode RULE, implique que les stats ne sont jamais utilisé et donc inutile à recalculer ou j'ai encore rien compris ?

[13thFloor]

13" vs 45' !! Impresionnant !
Ta PGA n'est pas un peu juste ? La totalité des jointures hash se fait en mémoire ou sur disque ?
J'en connais un qui va râler : que valent les paramètres db_file_multiblock_read_count et optimizer_index_cost_adj ?
Si le premier est élevé, ça favorise les full table scan au détriment des index.
Tu peux essayer de le positionner à 8 ou 4 au niveau session et vérifier le plan d'exécution et/ou la trace d'exécution de ta requête.

Sujet plus sensible pour le second (par défaut à 100). Même démarche que précédemment.

Sinon, si l'optimiseur ne choisit pas le chemin optimal et que toi tu l'as trouvé, place un hint dans la requête afin qu'elle utilise l'index adéquat.

Mais n'y aurait-il pas quelques bugs coté optim en 9204 ?

[13thFloor]

Envoyé par Knonix

Encore une dernière question, juste pour mon information, le fait de passer en mode RULE, implique que les stats ne sont jamais utilisé et donc inutile à recalculer ou j'ai encore rien compris ?

Les stats ne seront utiles que pour celui qui voudra avoir des infos sur le nb de blocs d'une table, son chainage, la longieur moyenen d'une ligne etc.
L'optimiseur sera syntaxique, donc il les ignore royalement.

[orafrance]

Envoyé par Knonix

Je pense que c'est réellement mon import de oracle 8i (fait en from user to user) qui est l'unique responsable de ce bordel, aussi nous allons je pense faire une création de schéma puis un import table par table des données, une reconstruction des index et finir par une analyse, cela devrait normalement me stabilisé l'ensemble.

Il n'y a aucune raison que ça ne change quoi que ce soit... Essaye de mettre un échantillon plus grand pour les stats si tu fais un ESTIMATE

Envoyé par Knonix

Encore une dernière question, juste pour mon information, le fait de passer en mode RULE, implique que les stats ne sont jamais utilisé et donc inutile à recalculer ou j'ai encore rien compris ?

c'est juste

[orafrance]

Envoyé par 13thFloor

Ta PGA n'est pas un peu juste ? La totalité des jointures hash se fait en mémoire ou sur disque ?
J'en connais un qui va râler : que valent les paramètres db_file_multiblock_read_count et optimizer_index_cost_adj ?

Bah oui je râle

l'explain plan est pourri, j'vois pas ce que la PGA a à voir là-dedans... quand aux paramètres db_file_multiblock_read_count et optimizer_index_cost_adj, j'aime autant voir un hint ou, mieux, un outline

Envoyé par 13thFloor

Si le premier est élevé, ça favorise les full table scan au détriment des index.
Tu peux essayer de le positionner à 8 ou 4 au niveau session et vérifier le plan d'exécution et/ou la trace d'exécution de ta requête.

et tromper le CBO qui prend pas des décisions au hasard

Envoyé par 13thFloor

Sinon, si l'optimiseur ne choisit pas le chemin optimal et que toi tu l'as trouvé, place un hint dans la requête afin qu'elle utilise l'index adéquat.

Ca parait mieux déjà même si je préfèrerais qu'il trouve la raison de ce gourrage du CBO... t'as même pas parler du poucentage d'estimate pour le calcul des stats

Envoyé par 13thFloor

Mais n'y aurait-il pas quelques bugs coté optim en 9204 ?

je dirais que ça a été long à venir La 9.2.0.5 apporte des améliorations importantes au CBO notamment... comme par hasard

Ca y est j'suis faché

[Knonix]

Merci pour l'ensemble de ces réponses :
1) en effet mon db_file_multiblock_read_count est elevé, pour le déterminer j'avais utilisé une méthode à partir d'une requête qui provoquait le FTS afin de déterminer au mieux ce paramêtre qui semblait être 128. Je vais le descendre à 4 et vérifier.
2) Oui la 9.2.0.4 a des problèmes sur la CBO semble t'il. Mais il m'est impossible de modifier cette version et passer en 9.2.0.8 qui est accepté chez nous, pour les raisons suivantes :
- les bases sont sur des serveurs mutualisés (corrigé une base implique corrigé les autres)
- C'est un gros groupe, international, le patch de serveur est interdit tout doit passer par des masters validé par les architectes, puis le projet doit voir sa MOA, prévénir les autres MOA, envoyer un dossier aux archi , validation, retour, test, pr-intégration, intégration, exploitation, grosso-modo il faut compter 6 mois
3) je vais vérifier pour cette histoire de hint, le problèmes c'est que c'est une requête envooyé par des rapports Bussiness Objects (on peut la modifier manuellement mais cela est toujours plus lourd en matiére de maintien de code) Requête généré par l'outil de création de rapport.

Merci pour ces pistes je vais les tester.

[orafrance]

Envoyé par Knonix

1) en effet mon db_file_multiblock_read_count est elevé, pour le déterminer j'avais utilisé une méthode à partir d'une requête qui provoquait le FTS afin de déterminer au mieux ce paramêtre qui semblait être 128. Je vais le descendre à 4 et vérifier.

je ne peux pas croire ce que je lis... t'as une requête qui merde et tu passes un paramètre fondamental de l'optimizer de 128 à... 4 Est-ce que tu connais exactement l'effet de ce paramètre au moins ? En connais-tu les impacts ?

Pour info : http://download.oracle.com/docs/cd/B...htm#REFRN10037

déjà une valeur supérieur à 16 c'est plus qu'étrange... en revanche, 4 c'est vraiment très faible, j'espère que tu as des disques rapides

Après si l'augmentation de se paramètre ne donne rien ça veut souvent dire que les blocs contigus ne sont pas intéressant dans les requêtes, donc on peut craindre du chainage ou une grosse fragmentation

Envoyé par Knonix

2) Oui la 9.2.0.4 a des problèmes sur la CBO semble t'il. Mais il m'est impossible de modifier cette version et passer en 9.2.0.8 qui est accepté chez nous, pour les raisons suivantes :
- les bases sont sur des serveurs mutualisés (corrigé une base implique corrigé les autres)
- C'est un gros groupe, international, le patch de serveur est interdit tout doit passer par des masters validé par les architectes, puis le projet doit voir sa MOA, prévénir les autres MOA, envoyer un dossier aux archi , validation, retour, test, pr-intégration, intégration, exploitation, grosso-modo il faut compter 6 mois

Oui, et plus tu t'y prends tard plus ce sera long... faut au moins passé en 9.2.0.5, les versions suivantes ne sont "que" des corrections de bugs.

Envoyé par Knonix

Merci pour ces pistes je vais les tester.

Comme dirait un collégue : "Et tu vas traiter les symptômes sans guérir la maladie"... c'est reculé pour mieux sauter

[Knonix]

Oui mais je ne suis qu'un pion parmi d'autres et je sait parfaitement que le passage de la 9.2.0.5 ne sera pas validé, par contre j'ai déjà lancé le sujet pour la mise en place du patch 9.2.08 (qui est validé par nos instances) sur nos serveurs.

Les disques sont rapides, pas de soucis, ensuite le test de modification du paramètre était uniquement là pour véritablement voir si cela pouvait avoir un impact, hors ce n'est pas le cas. Cependant je me suis renseigné sur ce paramètre et je vais tenté de la recalculé au mieux pour ma base. Sans doute que ma méthode pour voir le nombre de bloc lus n'était pas la meilleure.

Mon soucis majeur c'est, que je le veuille ou pas, nous allons rester sur cette version d'Oracle pour le moment et que le projet est lançé sur les rails de la production sans possibilité de s'arrêter (actuellement en pré intégration).

Donc le but ici est de mettre un pansement en attendant de pouvoir inoculer le vaccin définitif.

Ne pas croire que cela m'amuse bien au contraire, mais lorsque l'on est au sein d'un gros groupe il faut faire avec le carcan que l'on vous donne.

[orafrance]

Envoyé par Knonix

Mon soucis majeur c'est, que je le veuille ou pas, nous allons rester sur cette version d'Oracle pour le moment et que le projet est lançé sur les rails de la production sans possibilité de s'arrêter (actuellement en pré intégration).

je ne comprends pas... l'intégration n'est pas là justement pour mettre en avant les défauts d'une appli et les corriger avant la mise en prod

J'vois pas l'intérêt de faire des phases de tests si on se fout royalement des résultats

Envoyé par Knonix

Donc le but ici est de mettre un pansement en attendant de pouvoir inoculer le vaccin définitif.

Et changer un paramètre aussi important pour toi c'est moins risqué qu'un patch ?

Ca fait 3 fois que je le dis mais avant tout, concentre toi sur la méthode de calcul des stats

Envoyé par Knonix

Ne pas croire que cela m'amuse bien au contraire, mais lorsque l'on est au sein d'un gros groupe il faut faire avec le carcan que l'on vous donne.

J'ai connu ça... et il y a une phrase qui fonctionne bien en général : "si on reste dans cette version, je ne peux pas garantir l'intégrité technique de la base"

En plus, 9.2.0.4 -> 9.2.0.5 c'est une mise à jour mineure... tu le ferais dans ton coin personne ne s'en rendrait compte. Un bon DBA c'est pas seulement un mec qui connait la base sur le bout des doigts, c'est aussi une référence technique qui a un devoir de conseil et une bonne aptitude à convaincre

[Knonix]

Je pense que j'ai omis de dire une chose importante, ici je ne gère que des serveurs de DEV et d'intégration, donc le changement de paramètre et les tests sont effectué sans aucunes gênes par rapport aux utilisateurs (car il n'y en a pas), la base dont l'on parle est ici en DEV. Il est évident qu'avant une livraison finale l'ensemble de ces paramètres est contrôlé et vérifié, alors oui ici je pourrais faire une modification de mon niveau de patch, personne n'y verrait rien, mais cela ne changerait pas le problême de la production sur laquelle je ne maitrise rien.