Plan d'éxécution sur vue matérialisée [Résolu]

[startout]

Bonjour à tous,

Je suis actuellement en stage dans une équipe de DBA Etude.
Dans le cadre de mon stage, je suis amené à optimiser des requêtes.
L'équipe de dev nous a fournit une requête qui effectue une projection sur des champs d'une vue matérialisée. Cette requête contient juste une sélection (dans le sens restriction) sur un champ numérique.

Quand on fait le plan d'éxécution de la requête, l'optimiseur effectue un full table scan.
L'ensemble des valeurs est relativement bien réparti et la vue matérialisée est en refresh complete uniquement 1 seule fois dans la nuit.

Nous avons donc décidé de mettre un index sur la colonne de restriction.
Lancement du plan d'éxécution et nous obtenons toujours un full table scan même en pécisant des valeurs peu courante dans la restriction.

Nous avons crée une nouvelle vue matérialisée à partir de la vue existant. Puis création de l'index sur la nouvelle vue. Lancement du plan d'éxécution, l'optimiseur cette fois effectue un scan range index.

Je cherche à comprendre pour sur l'une des vue, l'optimiseur choisit de faire un full scan de la vue et sur la même vue, l'optim choisit de passer par l'index ?

En sachant que la base est sous oracle 10 et que les stats sont à jour.

Une idée ?

Merci beaucoup pour votre aide.

[mnitu]

Il y plusieurs explications à ce phénomène mais sans un exemple de ce vous fait ou un jeu d’essai il est difficile de se prononcer : statistiques à jour, hard parsing de la requêt, peek variable binding, paramètrage des sessions, etc.
Tracez l’optimiseur via l’événement 10053 et vous aurez l’explication.

[startout]

Bonjour Mnitu,

Je vous remercie pour votre retour.
Je suis désolé pour le peu de données sur cette requête. J'ai écrit le post de tête sans avoir accès à la base de données.
Les voies d'exploration que vous me fournissez me suffiront amplement.
Même si je ne sais pas ce qu'est le hard parsing, peek variable binding et l'evenement 10053.
Mais nous avons un outil génial qu'est internet et il ne fait pas de doute qu'en cherchant un peu, je puisse trouver.
Merci encore.

Cordialement,
Thierry

[mnitu]

Hard parsing – pour chaque requête que vous envoyés Oracle cherche d'abord si la requête existe déjà dans le shared pool (texte identique y compris les blancs, tabs, commentaires, etc.) et si les droits d'exécution et l'environnement permet sa réutilisation. Si c'est le cas Oracle réutilise le plan d'exécution déjà élaboré pour la requête en question, c'est le soft parsing. Si non, c'est à dire la requête n'existe pas dans le shared pool où l'environnement n'est pas le même Oracle est obligé de faire les étapes des calcul et optimisation du plan d'exécution. Dans votre cas, si vous comparez les requêtes exécutées dans des conditions qui ne sont pas identiques ça peut expliquer une différence telle que vous l'avez constaté.

Bind variable peeking – les requêtes qui utilisent des variable à la place des valeurs en dur posent un problème à l'optimiseur. L'optimiseur à besoin de la valeur pour calculer les cardinalités et choisir le meilleur plan de l'exécution mais cette valeur n'est pas directement disponible. A partir de la version 9 Oracle pratique l'introspection de la valeur des variables, c'est à dire qu'il regarde la valeur de la variable et l'utilise dans l'élaboration du plan d'exécution. Mais ça se passe seulement au moment du hard parsing. Dans votre cas ça peut signifier qu'une requête à été optimisée avec une valeur qui implique le balayage de la table et une autre avec une autre valeur mais qui implique l'utilisation d'un index. C'est peut être, ce que vous appelez des valeurs "peu courante".

Événement 10053 – Oracle c'est un logiciel qui permet toute sorte de traces pour diagnostiquer ce qui se passe. Ces traces sont activée via une instruction alter session en indiquant l'événement à tracer et le niveau de la trace. 10053 permet la trace de l'optimiseur, vous allez retrouver dedans tous les paramètres de la session ainsi que les informations qui ont conduit l'optimiseur à faire son choix. L'interprétation de cet fichier n'est pas simple mais avec de la patience vous pouvez trouver ce qui détermine le choix différente entre les deux requêtes.

Comme vous ne fournissez pas ni d'exemple de ce que vous faite, ni de jeux d'essai il est impossible de vous dire ce qui se passe précisément. La seule chose possible est de vous fournir des hypothèse et des indications sur comment vous pouvez obtenir plus d'informations par vous même. Et il y a encore bien des phénomènes qui peuvent expliquer le comportement que vous avez décrit.

[Rams7s]

Une autre reponse possible:

Ou alors vous avez des donnees reparties un peu partout.

Code :

1
2
3
4
5
6
7
8
 
SELECT 
table_name,
blevel,
	avg_data_blocks_per_key,
	avg_leaf_blocks_per_key, 
	clustering_factor
FROM	user_indexes;

Si le clustering factor est tres eleve, ca veut dire que meme si Oracle utilise l'index pour savoir ou trouver les donnes, il va devoir naviguer de bloc de donnees en bloc de donnees sans cesse sur la table. Faisant par la meme occasion un grand nombre d'entree/sorties (logique/physique). Tant et si bien, que ca peut lui couter plus que de faire un scan complet de la table.

Mais comme le dit mnitu, c'est le 10053 qui vous dira ce que fait le CBO et pourquoi il choisit ce plan et pas un autre.

www.jlcomp.demon.co.uk/12_using_index.doc
Il est vraiment genial ce mec.