Discussion :

[jelefevre2003]

Bonjour,

J'ai une requête SQL assez simple qui utilise une jointure entre deux tables.

Select * from table1, table2 where table1.colonne1 = table2.colonne1 and table2.colonne2 = ?

colonne1 est la clef primaire de table1 (pk)
colonne1 et colonne2 sont indexées dans table2 (idx1, idx2)


Quand je regarde le schéma d'exécution de cette requête à l'aide de la console "Enterprise Manager 10G", dans la rubrique "Instance de base de données > Taux d'activité les plus élevés", je vois que le comportement d'Oracle dépend de la requête. En sélectionnant différentes "valeur de hachage de plan" de la liste déroulante, je vois que

parfois, il passe par pk et idx2
parfois, il passe seulement par idx2
parfois, il n'utilise aucun index, il fait un fiull scan sur chacune des tables


Je ne comprends pas pourquoi je ne trouve pas tout le temps le même schéma d'exécution avec pk et idx2, d'autant qu'il m'affiche dans les 2 derniers cas des temps d'exécution de 3 et de 4 secondes.


J'aurais deux questions

1)
Est ce que le schéma d'exécution d'une requête dépend du schéma de la base de données (table + index) ou est ce qu'il peut dépendre de la valeur que je donne à "table2.colonne2 = ?" dans ma requête ?

2)
Est ce que le schéma d'exécution de la console corerspond à ce qui s'est réellement passé sur le serveur à un instant donné, ou est ce que Oracle stocke simplement la requête et au moment où j'utilise la console, il simule ce qui a dû se passer ?


Merci de votre aide.

[pifor]

Réponse question 1:

Le plan d'exécution d'une requête dépend forcément du schéma auquel appartiennent la table et les index concernés puisque vous pouvez avoir des objets (tables, index) différents qui portent le même nom dans des schémas différents.

Dans certains cas, le plan d'exécution d'une requête qui utilise une bind variable peut dépendre de la valeur affectée à la bind variable. C'est ce qu'on appelle le bind variable peeking. Voir par exemple cet article de Tom Kyte:
http://www.oracle.com/technology/ora...o18asktom.html


Réponse question 2:

Je ne connais pas en détail la fonctionnalité d'OEM que vous utilisez mais je doute fort que OEM simule quelque chose: il y a assez de vues dynamiques et d'outils pour afficher des plans d'exécutions sans avoir besoin de simuler quoi que ce soit.

Remarque: qu'entendez-vous exactement par "schéma d'exécution" ?

Une requête a un plan d'exécution qui s'exécute par défaut dans le schéma de l'utilisateur connecté mais une requête peut parfaitement référencer des objets d'autres schémas avec

• la règle de nommage <schéma>.<objet>
• l'utilisation de synonymes
• l'instruction ALTER SESSION SET CURRENT_SCHEMA=xxx

Précisez votre version oracle à 4 chiffres SVP.

[mongolic]

Bonjour,

Pour les plans de trois à quatre secondes, des histogrammes imprécis peuvent conduire l'optimiseur à choisir un mauvais plan. Egalement le clusterig_factor de l'indexe.

[jelefevre2003]

Bonjour,
Merci pour vos réponses.
J'utilise la version 10.2.0.3.0
Par "schéma d'exécution", je voulais dire en fait "plan d'exécution". désolé pour la confusion.

Je précise que j'ai créé ma base de donnée en exportant une ancienne base avec la commande exp sans les statistiques puis en réimportant les données avec la commande imp et l'option statistics=recalculate.

Est ce qu'il ne pourrait pas y avoir un problème de statistiques qui fait que les plans d'exécution affichés dans la console d'administration soient faussés ?

J'ai un doute parce que quand j'exécute directement la requête avec SQL+, le temps de réponse est inférieur à 1s, alors que sur la console, il est de l'ordre de 3 à 4 secondes.

Merci de votre aide

[jelefevre2003]

Bonjour,

Pour les plans de trois à quatre secondes, des histogrammes imprécis peuvent conduire l'optimiseur à choisir un mauvais plan. Egalement le clusterig_factor de l'indexe.

Comment savoir si les histogrammes sont imprécis ou pas, et s'ils le sont comment les corriger ?

Merci beaucoup

[jkofr]

Hello,

Régénère les stats des 2 tables.

Maintenant, il est possible que le plan d'exec change en fonction de ta bind variable dans la clause where.

Mais bon, si t'as un index sur une colonne d'un cote et deux de l'autre, ya pas vraiment de raison que le plan d'exec change.

Jko

[mongolic]

tu peux interroger la vue DBA_TAB_HISTOGRAMS en filtrant sur ta table et les colonnes concernées. Mais il faut avoir une notion de ce que représente les histogrammes dans oracle. Ce sera long à expliquer. Sur le lien suivant tu auras des infos :
http://www.dba-oracle.com/art_otn_cbo_p4.htmhttp://www.dba-oracle.com/art_otn_cbo_p4.htm.

J'essaye quelques explications : un histogramme donne à oracle des indications sur la fréquence d'une valeur : pour une colonne donnée de 1000000 occurrences, tu peux avoir la valeur A qui a une occurrence et la valeur B qui en a 999999 . Du coup avec la clause where col=A il faut passer par l'index et pour where col=B il vaut mieux faire un full scan. Dans les histogrammes, oracle stocke cette information.
Si tu n'as que deux valeurs, il stockera pour chaque valeur le nombre d'occurrences (dans ce cas là il y a peu de chance qu'oracle choisisse un mauvais plan), mais si tu as 100 000 valeurs distinctes, il fonctionne différement ( et il y a plus d'approximation ) et dans ce cas le plan n'est pas toujours pertinent.

En résumé :
Si tu n'as aucun histogramme pour tes colonnes, foireux ou pas, le plan sera toujours le même.
Si tu as des histogrammes, le plan peut varier en fonction de la valeur de la bind variable. Et dans ce cas si ta colonne possède un nombre de valeurs qui dépasse un seuil (pour ta version c'est 250 mais je ne suis pas sur), oracle va faire une approximation de la fréquence de ta valeur -> le plan peut être foireux (dans ce cas la clause dynamic_sampling peut-être utilisée pour éviter ce plan foireux ou tu peux encore supprimer les histogrammes sur ta colonne). En dessous du seuil de 250, les fréquences sont précises, dans ce cas les histogrammes conduisent à un bon plan d'exécution.

Si tu veux tu peux poster les différents plan pour ta requête ainsi que les histogrammes, je pourrai te dire dans quel cas de figure tu es.

Sinon les histogrammes se génèrent lors du calcul de stats avec l'option method_opt qui peut prendre différentes valeurs selon les colonnes sur lesquelles tu souhaites des histos.