9i / Optimiseurs CHOOSE et RULE

Bonjour,
j'ai un petit problème de point de vue avec un de mes fournisseurs...
Ce fournisseur m'a fournit une application qui fonctionne sur une base Oracle 9i (9.2.0.5), essentiellement sur des Proc Stockées en PL (sur lesquelles, bien sûr je n'ai pas la main... ce serait trop facile)...

Comme tout DBA qui se respecte mon premier réflexe est de générer les stats sur la base pour que l'optimiseur CHOOSE puisse trouver son chemin au mieux... mais voilà : ce fournisseur me soutient mordicus que toute son applic et ses Procs sont optimisées pour le mode RULE et que je ne dois surtout pas mettre la base en mode CHOOSE et générer les stats (actuellement j'ai le paramètre optimiser_mode=RULE dans mon init.ora)...

Ce que je n'arrive pas à comprendre, c'est comment du code SQL peut être optimisé pour le mode RULE (plus restrictif et pointilleux) et ne pas donner des performances AU MINIMUM semblables en mode CHOOSE avec des stats correctement générées...

alors voici où j'ai besoin de votre expérience :

- mon fournisseur me mène en bateau ou pas ?

- ou alors y a-t'il quelque chose qui m'échappe dans les optimiseurs ?

tout avis sur la question est le bienvenu...

Il passe peut etre des paramètres à l'optimiseur directement dans les requetes

C'est un peu porc (surtout si l'appli est pas seule sur la base)

Un moyen simple de vérifier ce qu'il se passe (un DBA est tout puissant sur la base)

TU fais une copie de la vue v$sql avant de lancer une commande sur son appli
Tu fais une copie de la vue v$sql apres l'execution de la requete

Puis tu fais une requete sur tes deux tables afin de faire la soustraction des deux et essayer de voir parmis les ordres que cela te laisse la tete des requetes.

Si y a des parametres de tuning (entre /*+ */) ca veux dire qu'il passe tout en hard à l'optimiseur.

Apres pour ce qui est des performances faut pousser un peu plus l'analyse.

Sans vouloir confirmer ni infirmer les déclarations de votre fournisseur, il est tout à fait possible que leurs requêtes soient optimisées volontairement.

Ce n'est d'ailleurs pas pour rien qu'il est possible avec Oracle de stocker des plans précis d'exécution (OUTLINE) afin de pouvoir les rejouer tels-quels, même si les données (et leur volume) évoluent avec le temps.

Bonjour,

Ton fournisseur ne te mène pas forcément en bateau. Il fut un temps où les requêtes SQL étaient optimisées en mode RULE. Pour cela, il y a 2 grandes techniques :

1 ) l'ordre des tables : lorsque tu fais des jointures entre des tables, l'ordre d'apparition des tables derrière le FROM a une importance : on les range selon le nombre d'occurrences remontées, clause WHERE incluse.

Par exemple, si tu fais une jointure entre 2 tables A et B respectivement de 1000 et de 10000 lignes, avec une clause WHERE telle que l'on remonte 100 lignes sur A et 10 lignes sur B, alors tu dois mettre B en premier, juste derrière le FROM (à moins que ce ne soit l'inverse. Désolé, mais je fais cela de tête, et il y a bien longtemps que j'ai abandonné le mode RULE).

De toute manière, si tu lances les statistiques pour travailler en mode COST, dis toi alors que l'ordre des tables n'a plus aucune importance. C'est l'optimiseur statistique qui va générer plusieurs plans d'éxecution, et prendre le moins couteux.

2 ) l'utilisation des index : le mode RULE utilise systématiquement les index dès qu'il peut. Par exemple, même sur une table n'ayant que quelques occurrences tenant dans un bloc Oracle, le fait de chercher une donnée par sa PK provoque en mode RULE l'utilisation de l'index (lecture du bloc d'index) puis recherche de la ligne dans la table (lecture du bloc de table). On a donc un Unique Scan Index avec lecture de 2 blocs.

En mode COST, l'optimiseur est plus intelligent : il fera un Full Scan Table, avec au final lecture d'un seul bloc. L'index n'a donc pas été utilisé.

Pour éviter l'utilisation des index en RULE, la technique consiste à utiliser une fonction ou une opération. Par exemple, si tu as une colonne de type NUMBER qui est indexée, tu lui ajoutes 0. Au lieu de faire WHERE COL = 10, tu fais WHERE COL + 0 = 10.

Idem avec les chaînes de caractères où tu peux concatener avec une chaîne vide, du genre WHERE colonne || '' = 'ma valeur'.

Cette fois-ci, si tu génères les statistiques, cela ne changera pas le plan d'exécution, à cause de l'utilisation de ces fonctions (à moins de créer dans certains cas un index basé sur une fonction, mais cela c'est une autre histoire).

Pour finir, puisque tu as généré les stats, as-tu noté sur ton appli un changement des temps de réponse (que ce soit en bien ou en mal) ???

De toute manière, il faut se dire que le mode RULE a bien vécu, qu'il n'existe plus en 10g, et qu'il est plus que temps de passer en mode COST.

J'ajouterai à tout ça que le très sérieux Oracle Application 10.0 (donc écrit par Oracle ;)) est aussi optimisé pour le mode RULE et que le passage en CHOOSE n'est pas supporté par Oracle (j'en ai fait la triste expérience :?).

Il faut dire que le mode RULE, si il n'est plus optimisé par Oracle reste probablement le plus simple pour garantir la stabilité des plans d'exécutions... voila pourquoi le fournisseur a probablement choisi ce mode. En plus, il faut rappeler que le mode CHOOSE s'est grandement amélioré avec les versions et que ce serait dommage pour le fournisseur de ne pas profiter des clients qui sont encore en 7.3 ou 8.0 ;)

merci à tous pour vos avis et conseils...
je vais essayer de voir dans les requêtes via v$sql pour voir si des optimisation explicites (hints ou "fonction" de type colA + 0 = 10) existent dans son code...

sinon pour ce qui est des performances, je n'ai pas voulu finaliser les stats et les ai donc effacé puis ai laissé ma base en mode RULE pour une raison très simple : au moindre pépin avec l'applic, le fournisseur aurait beau jeux de se dédouaner en disant que la base n'est pas dans le mode qu'il préconise... alors je vais monter une base test et faire quelques essais...

par contre je vais essayer de "négocier" avec lui pour lui faire comprendre que le mode CHOOSE a fortement évolué et qu'il est dommage de se priver de ces améliorations... de plus l'argument massue sera là pour le jour de la migration vers 10g :)