Discussion :

[tropiko]

bonjour
Notre application batch sous 11gr2 exécute plusieurs centaines de requêtes DML
et sont susceptibles d"évoluer (en codage) par ajout de tables , de colonnes ou de critères de sélection ou jointure à chaque livraison mensuelle.
Est-ce que l'utilisation des SPM(Sql Plan Management) ou SQL Profiles est
NON recommandée dans ce cas vu l'évolution continue des requêtes ...

En effet , vu que les SPM et les Sql Profiles se basent sur l'écriture"figée" d'une requête donnée , ces 2 techniques ne sont peut être pas la plus judicieuse pour stabiliser les plans d'exécution ? Sinon il faut accepter de regénérer les SPM ou les SQL Profiles à chaque modification ?
Thanks

[Pomalaix]

Si le texte de la requête change en quoi que ce soit, alors c'est une nouvelle requête (SQLID différent).
Donc, pour SPM comme pour les profils, c'est une requête qu'ils ne connaissent pas encore, et qu'ils vont traiter comme telle.
Je ne vois donc aucune sorte "d'incompatibilité" de ces mécanismes avec votre situation.

Par ailleurs, "stabiliser les plans d'exécution" ne doit pas davantage être le saint graal que "défragmenter la base". Il faut déjà s'assurer d'être face à un problème effectif.
Si l'optimiseur basé sur le coût d'exécution a supplanté son ancêtre, c'est que son essence-même est d'être dynamique, adaptatif, fortement contextuel. Les changements de plan d'exécution censément illégitimes sont en temps normal peu nombreux, et doivent être examinés au cas par cas.

[Mohamed.Houri]

L'utilisation du Sql Plan Management(SPM) permet la stabilité d'un plan d'exécution et également son évolution. Pour qu'une requête puisse utiliser un Plan d’exécution stockée dans le SQL Plan Baseline(SPB), il faut que

(a) le force_matching_signature de la requête corresponde à celui de la requête ayant servi pour stocker le plan d'exécution dans le SQL Plan Baseline

(b) que le plan stocké dans la SPB soit reproductible (pas d'index supprimé entre temps par exemple)

Si vos requêtes changent en y ajoutant des tables ou des colonnes, leur nouvelle force_matching_signature(et à fortiori le sql_id) cesse de correspondre avec celle enregistrée dans le SPB et, conduisant donc, le plan figé à ne plus être utilisable.

J’aurai laissé le SPM en place, et si mes requêtes changent elles utiliseront leur propre plan. J’analyserai par la suite la nouvelle situation et verrai s’il faut faire évoluer ces nouveaux plans en les injectant dans le SPB ou pas.

Sachez que lorsque vous aurez plusieurs plans du même sql (force_matching_signature) dans le SPB, il y aura forcément un surcoût lorsque le CBO choisira le plan à utiliser. Pour cela il doit s'assurer que les plans sont reproductibles et lorsqu'ils le sont il évaluera le moins couteux pour l'utiliser. Tout cela doit être pris en compte lors de la décision d'utiliser le SPM ou pas. J'ai quelques articles à ce sujet ici

[pachot]

Bonjour,

A mon avis, SPM est une bonne solution pour assurer la stabilité des plans d'exécution d'une application assez figée, sur laquelle on n'a pas trop la main.
Mais ça demande un travail de revue régulière des plans susceptibles d'évoluer.

Ca peut être très utile lors d'un upgrade Oracle, ou changement majeur de configuration ou d'infrastructure si on veut être sûr qu'il n'y a pas de régression.

Sur une appli qui évolue, il serait préférable de faire le travail en amont: comprendre pourquoi un plan d'exécution n'est pas optimal, et corriger la root cause (statistiques, indexes, etc).
D'après mon expérience, les plans qui changent, c'est souvent parce qu'il n'y a pas de plan optimal. L'optimiseur 'hésite' entre plusieurs accès car aucun n'est parfait. Quand il y a un accès qui sort du lot (parce que les stats sont justes, que les requêtes sont bien écrites, que les index sont bien choisis,...), qu'il ramène plusieurs lignes en lisant seulement quelques blocs, il a de grande chance d'être stable. Et il sera probablement toujours optimal sur des requêtes un peu différentes.

Cordialement,
Franck.

[tropiko]

Bonjour ,
Je reviens à cette discussion ...
Quand est ce que il faut previligier les SQL profiles sachant que ce batch n'utilise pas des variables bind (execute immediate ...) ?
Enfin que faut-il prévoir pour une migration 11g vers 12c dans mon cas :
* Statistiques utilisateurs figées.
* Seule la technique des hints est utilisée pour stabiliser les plans.
Thanks