Discussion :

[totoche]

Bonjour,
Est-ce un 'mauvais parmetrage ' du DB_FILE_MULTIBLOCK_READ_COUNT peut entrainer un full acess sur une attribut indexé mais à faible sélectivité (peu de cardinalité)?
Merci de vos réponses

[mnitu]

Comment l'optimiseur d'Oracle calcule le coût

[totoche]

Si un attribut possede 10 cardinalités, mais qu'un seul attribut retourne 70% des lignes de la table.
Que le db_file_multiblock_read_count est calé sur la taille du cache OS.

Sachant que le temps lecture en mode multiblocs intervient dans le calcul du cout, il peut-y avoir un risque que l'optimiseur fasse un full table scan, même si la requete dans son prédicat porte sur l'attribut à faible sélectivité (70%)

Seule la lecture du plan d'execution, indiquera s'il faut faire un hint pour forcer l'usage de l'index, au détriment des cardinalités a forte séléctivité.

Face a ce constat, ne vaut-il mieux ne pas faire de hint afin de bénéficier de la lecture multibmock pour les cardinalité a forte sélectivité, et joindre une vue materializée pour l'attribut a faible sélectivité ?


Merci de vos suggestions

[Waldar]

Je ne suis pas sûr d'avoir compris l'intégralité de votre discours, mais si une requête doit retourner 70% des lignes de la table, un full scan sera nettement plus rapide qu'un index scan + table scan by rowid.

[mnitu]

...
Seule la lecture du plan d'execution, indiquera s'il faut faire un hint pour forcer l'usage de l'index, au détriment des cardinalités a forte séléctivité.

Face a ce constat, ne vaut-il mieux ne pas faire de hint afin de bénéficier de la lecture multibmock pour les cardinalité a forte sélectivité, et joindre une vue materializée pour l'attribut a faible sélectivité ?

...

Rules for Hinting

Mais, vos propos sont un peu confuses.

[totoche]

Pardon, je ne me suis pas relu, et j'ai fais une inversion

Sachant que le temps lecture en mode multiblocs intervient dans le calcul du cout, il peut-y avoir un risque que l'optimiseur fasse un full table scan, alors que la requete porte sur une cardinalité a forte sélectivité (peu de lignes retournés)

Face a ce constat, ne vaut-il mieux une vue materializée pour l'attribut a faible sélectivité ?

[skuatamad]

alors que la requete porte sur une cardinalité a forte sélectivité (peu de lignes retournés)

Face a ce constat, ne vaut-il mieux une vue materializée pour l'attribut a faible sélectivité ?

Il y a les histogrammes pour aider l'optimiseur, mais le phénomène de bind variable peeking (cf l'article de mnitu) impacte fortement l'intérêt des histogrammes.

Plutôt qu'une vue materialisée tu peux peut être tester la valeur pour la renseigner en dur et pour le reste utiliser les binds variables, du style :

Code :

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

1
2
3
4
5
if (p_var1 == 1) then 
  select * from t where c = 1 and c2 = p_var2;
else 
  select * from t where c = p_var1 and c2 = p_var2;
end;

Ou peut être juste ne pas bind la variable si tu es dans un contexte type DW.

[mnitu]

Les choses changent constamment avec les versions d’Oracle et donc, les solutions peuvent être totalement différentes en fonction de la version.

A partir de la version 11g Oracle à introduit le mécanisme de « bind variable peeking aware » c’est-à-dire Oracle va surveiller les requêtes q’ utilisent des variables de liaison, il s’apercevra quand ces requêtes partirons dans les choux à cause des distributions de données non uniforme et il réévaluera les plan d’exécution.

Je suis bien d’accord qu’il existe une classe de problèmes qui pourrait être résolues avec des vue matérialisées mais, je reste assez dubitatif quand à leur utilité dans ces cas.

[Edit]
Je trouve votre formulation

requete porte sur une cardinalité a forte sélectivité (peu de lignes retournés

un peu confuse. Regardez aussi les définitions dans la doc d'Oracle.
[/Edit]

[Ahmed AANGOUR]

Juste quelques remarques sur le sujet:
- le paramètre db_file_multiblock_read_count est pris en compte lors du cost IO lorsque les workload system statistics ne sont pas calculés.
- Lorsque les workload system statistics sont disponibles, le paramètres db_file_multiblock_read_count n'est plus utilisé. c'est remplacé par le MBRC.

Donc la valeur donnée à ce paramètre a un impact directe sur le coût des lectures multiblocks uniquement lorsque les stats system ne sont pas disponibles.

De plus la valeur de ce paramètre est limité aux capacités de l'OS.

Il y'a une fonctionnalité à partir de la 10g r2 qui permet de laisser le soin à oracle de configurer la valeur de ce paramètre. il suffit pour cela de ne rien mettre dans ce paramètre.

[totoche]

Ok merci,
donc dans le cas ou les stats sont calculées, et l'optimiseur en mode choose(par ex), on a tout intérêt a calibrer le DB_FILE_MULTIBLOCK_READ_COUNT sur la capacité de l'OS non ?

[Ahmed AANGOUR]

C'est l'inverse, si les stats systemes workload sont calculés, le paramètre n'est pas pris en compte par le CBO.

[totoche]

Ok et merci Ahmed,
je suis en 8.1.6 :

J'ai les stats qui sont jouées, les requêtes se fondent la plupart de temps sur l'indexation INDEX RANGE SCAN, cependant je souhaite optimiser les TABLE ACCESS FULL que parfois les requêtes intègrent.
C'est pourquoi je souhaité jouer sur le paramètre db_file_multiblock_read_count..

Ahmed AANGOUR

le paramètres db_file_multiblock_read_count n'est plus utilisé. c'est remplacé par le MBRC.

Je n'ai pas trouvé trace de 'littérature' indiquant qu'en présence de stats le db_file_multiblock_read_count n'est plus utilisé.
Si tu peux m'indiquer un lien, merci

http://www.tafora.fr/wp/db_file_mult...count.doc.html

[Ahmed AANGOUR]

Si t'es en 8i, oublie ce que je t'ai dit sur les stats systemes. il n' y'en a pas en 8i.

Si t'as des stats objets à jour et que des fois dans les plans t'as des Full scan, cela ne veut pas dire que le Full Scan est mauvais en soit. Au contraire, quand tu récupères un gros pourcentage de ta table, le full scan est plus performant qu'un accès via un index.

Si t'as des doutes sur certaines requêtes tu peux nous les envoyer avec le plan associé.

[totoche]

Ok, merci,
tu ne vois aucun inconvénient a ce que je calque mon DB_FILE_MULTIBLOCK_READ_COUNT sur la capacité de l'OS ?