Plan de requête dégradé (soudainement lent)
Une requête rapide hier est soudainement lente aujourd'hui. En général, l'optimiseur est passé à un plan moins bon. Nous trouvons la cause racine parmi statistiques obsolètes, sensibilité aux paramètres, croissance du volume de données ou changement brutal de plan, et stabilisons le plan.
Le plan a-t-il vraiment changé, et quand ?
Existe-t-il plusieurs plans (plan_hash/plan_generation) pour la même requête, et quand le ralentissement a-t-il commencé ? Coïncide-t-il avec un déploiement, une montée de version, un changement de paramètre ou un gros chargement de données récent ?
SELECT sql_id, plan_hash_value, executions, ROUND(elapsed_time/GREATEST(executions,1)/1e6,3) AS sec_per_exec, first_load_time
FROM v$sql WHERE sql_id='&sqlid' ORDER BY first_load_time;- Les statistiques sont peut-être obsolètes
- La même requête se comporte différemment selon le paramètre (sniffing/peeking)
- Le volume de données a fortement augmenté
- Le plan a changé brutalement après une montée de version/un flush
Les statistiques sont-elles à jour ?
L'optimiseur fonde ses décisions sur les statistiques. Si la date du dernier analyze est ancienne ou si le compteur de modifications est élevé, les estimations ont peut-être réellement dérivé, ce qui mène à un mauvais plan.
SELECT table_name, num_rows, last_analyzed, stale_stats
FROM dba_tab_statistics WHERE table_name='T';- Statistiques obsolètes/absentes
- Les statistiques sont à jour mais le plan reste sensible aux paramètres
Y a-t-il une sensibilité aux paramètres ?
La requête choisit et met en cache un plan selon la valeur de paramètre de sa première exécution (parameter sniffing MSSQL / bind peeking Oracle). Ce plan peut être mauvais pour d'autres valeurs à distribution asymétrique.
SELECT sql_id, child_number, plan_hash_value, is_bind_sensitive, is_bind_aware
FROM v$sql WHERE sql_id='&sqlid';- Oui, le plan alterne entre bon et mauvais selon le paramètre
- Le bon plan est connu, je veux le figer définitivement
Cause racine : statistiques obsolètes / absentes
Sur la base d'anciennes statistiques ne correspondant pas à la réalité, l'optimiseur a mal estimé le nombre de lignes et choisi un mauvais plan. C'est la cause la plus fréquente de régression de plan.
Cause racine : sensibilité aux paramètres (sniffing / bind peeking)
Le plan en cache a été choisi pour la première valeur de paramètre reçue et est mauvais pour d'autres valeurs à distribution asymétrique. L'objectif est de garantir le choix d'un plan adapté à la valeur.
Solution : figer le bon plan (baseline / force plan)
S'il existe un bon plan connu, le figer pour que l'optimiseur ne s'en écarte pas met fin durablement à la régression.
Cause racine : le volume de données a augmenté, le plan n'est plus adapté
À mesure que la table grossit, un plan autrefois idéal (par ex. nested loop) passe désormais mal à l'échelle ; l'optimiseur a besoin de statistiques et de chemins d'accès reflétant correctement la croissance.
Cause racine : changement brutal de plan (montée de version / vidage du cache)
Le plan a changé brutalement après une montée de version, un changement de paramètre ou le vidage du cache de plans. Le comportement de l'optimiseur ou les conditions d'environnement ont évolué.