Stabiliser l’exécution des requêtes SQL avec SQL Plan Baseline (SPM)

 

Une fonctionnalité Oracle à ne pas occulter pour stabiliser les performances et fixer le plan d’exécution des requêtes SQL est l’utilisation des plans de référence ou baseline, fonctionnalité qui a été introduite avec la version 11g d’Oracle Enterprise Edition. Cette option vous affranchit de toute implémentation de type SQL_Profile ou autre stored outline.

Ainsi, lors de la mise en œuvre d’un partitionnement mensuel (INTERVAL) sur certaines tables chez un client, les temps d’exécution des requêtes se comportaient de façon erratique et pouvaient varier de quelques secondes à plusieurs minutes.
Du fait de l’implémentation du partitionnement, les plans d’exécution étaient totalement différents de ceux d’origine et, de plus, les statistiques pas toujours actualisées pour le segment courant, ce qui nuisait grandement à la recherche du meilleur plan d’exécution pour certaines requêtes.

Pour palier ce manque et stabiliser les plans d’exécution, je pris l’option d’utiliser les plans SQL ‘baseline’ qui garantissent l’exécution du plan quelle que soit la fraîcheur des statistiques sur les tables partitionnées.

En scrutant les tables AWR, si vous disposer des options ‘diagnostic et tuning pack’, il est possible d’extraire les différents plans associés à une même requête et sélectionner ensuite le meilleur plan d’exécution.

Ainsi, la requête suivante nous permet d’extraire tous les plans d’exécution pour une période de 31 jours sur les tables qui nous intéressent :

Contrôle des requêtes SQL

Nous allons intégrer cette requête dans une petite procédure Check_SQL_Plan qui nous permettra de visualiser toutes les requêtes  ayant plusieurs plans d’exécution sur un mois, cette même procédure pourra générer le plan de référence (SQL_BASELINE) le plus performant lorsque la variable ‘load’ est positionnée à ‘Y’, ce qui appellera la procédure SQL_ID_STS

Procédure Check_SQL_Plan

L’appel de la procédure Check_SQL_Plan retourne le résultat suivant :

Génération des plans de référence

Pour générer le plan de référence, nous devons appeler la procédure SQL_ID_STS en passant pour paramètres l’identifiant de la requête (sql_id) et celui du plan d’exécution (plan_hash_value)

Procédure SQL_ID_STS

Pour générer le plan de référence, il suffit d’exécuter la procédure SQL_ID_STS puis de contrôler l’identifiant du plan de référence de la manière suivante :

Contrôle des plans de référence

Pour retrouver la requête et son plan d’exécution dans DBA_SQL_PLAN_BASELINES, il faut recalculer l’identifiant de la requête (SQL_ID) à partir de son texte en précisant l’identifiant du plan de référence désiré (SQL_HANDLE).

Ainsi, la procédure suivante permet de contrôler l’identifiant de la requête (SQL_ID) et son plan d’exécution (PLAN_HASH_VALUE) pour un plan de référence donné :

Il suffit ensuite de renseigner l’identifiant du plan de référence pour la procédure which_sql_id afin d’afficher la requête à laquelle le plan d’exécution de référence s’applique :

La vérification du plan d’exécution de référence s’effectue en renseignant les parmètres SQL_HANDLE et PLAN_NAME de la fonction DISPLAY_SQL_PLAN_BASELINE :

 

Jean-michel Seinturier

About Jean-michel Seinturier

Jean-michel Seinturier has written 20 post in this blog.

Architecte Solutions

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

%d blogueurs aiment cette page :