Documentation sur Oracle Cloud Infrastructure

Utiliser SPM pour gérer les plans d'exécution SQL

Vous pouvez utiliser SQL Plan Management (SPM) pour vous assurer que les performances d'exécution d'une instruction SQL ne se dégradent pas en raison de modifications du plan d'exécution SQL (plan SQL).

SPM est un mécanisme préventif qui permet à l'optimiseur Oracle de gérer automatiquement les plans SQL, en s'assurant que la base de données n'utilise que des plans connus ou vérifiés. La performance de toute application de base de données repose largement sur une exécution cohérente d'énoncés SQL. Le plan d'exécution d'une instruction SQL peut changer de manière inattendue pour diverses raisons, telles que la collecte des statistiques destinées à l'optimiseur, les modifications apportées aux paramètres de l'optimiseur ou les définitions de schéma ou de métadonnées. SPM assure la "stabilité du plan" au moyen d'une structure qui préserve les plans SQL courants au milieu des modifications de l'environnement, tout en n'autorisant les modifications que pour de meilleurs plans. Lorsqu'un nouveau plan SQL est trouvé pour une instruction SQL, il n'est pas utilisé tant qu'il n'a pas été vérifié pour avoir des performances comparables ou supérieures à celles du plan courant.

SPM utilise un mécanisme proactif appelé Approbation de plan SQL, qui est un ensemble de plans SQL acceptés que l'optimiseur Oracle est autorisé à utiliser pour un énoncé SQL. En utilisant des lignes de base, SPM empêche les régressions de plan dues aux changements environnementaux, tout en permettant à l'optimiseur de détecter et d'utiliser de meilleurs plans.

Les principaux composants de SPM sont les suivants :

  • Saisie de plan : Techniques permettant de saisir et de stocker des informations pertinentes sur les plans dans la base de gestion SQL pour un jeu d'énoncés SQL. La capture d'un plan implique de sensibiliser SPM au plan et cela peut se faire par :
    • Saisie automatique de plan : Lorsque cette option est activée, la base de données vérifie si les énoncés SQL exécutés sont admissibles à la saisie automatique. Pour être admissible à la saisie automatique de plan, une instruction exécutée doit pouvoir être répétée et ne doit pas être exclue par aucun filtre de saisie.
    • Saisie manuelle de plan : Chargement en masse lancé par l'utilisateur des plans d'exécution existants pour les énoncés SQL dans une référence de plan SQL.
  • Sélection de plan : Capacité de l'optimiseur Oracle à détecter les modifications de plan en fonction de l'historique de plan stocké et de l'utilisation de références de plan SQL pour sélectionner des plans afin d'éviter les régressions de performance potentielles.
  • Évolution du plan : Processus qui consiste à ajouter de nouveaux plans aux références de plan SQL existantes, manuellement ou automatiquement. L'optimiseur Oracle vérifie les nouveaux plans et les ajoute à une ligne de base de plan SQL existante.

Pour plus d'informations sur SPM et ses composants, voir Aperçu de la gestion des plans SQL dans le guide de réglage SQL pour Oracle Database.

Pour utiliser SPM, allez à la page Détails de la base de données gérée et cliquez sur Gestion des plans SQL dans le volet de gauche sous Ressources. Vous pouvez effectuer les tâches SPM suivantes dans Diagnostics & Management du service de gestion de bases de données :

  • Gérer les références de plan SQL.
  • Soumettre des tâches pour charger des plans SQL dans des SQL Plan Baselines.
  • Effectuez des tâches de configuration telles que l'activation, la désactivation ou la modification du SQL Plan Baseline, la capture automatique de plans et les paramètres de tâche Automatic SPM Evolve Advisor.

Privilèges requis pour exécuter les tâches SPM

Le tableau suivant répertorie les tâches SPM et les privilèges requis pour les effectuer.

Note

Tout utilisateur disposant du privilège ADMINISTER SQL MANAGEMENT OBJECT peut exécuter l'ensemble DBMS_SPM.
Tâche Privilèges requis
Modifiez un ou plusieurs attributs d'un seul plan SQL ou de tous les plans associés à une instruction SQL. Privilège EXECUTE sur l'ensemble SYS.DBMS_SPM.
Modifiez la limite d'espace disque pour la base de gestion SQL. Privilège EXECUTE sur l'ensemble SYS.DBMS_SPM.
Modifiez la période de conservation des plans SQL non utilisés. Privilège EXECUTE sur l'ensemble SYS.DBMS_SPM.
Configurer les filtres de saisie automatique. Privilège EXECUTE sur l'ensemble SYS.DBMS_SPM.
Configurez la tâche du service SPM de conseil d'évolution automatique. Privilège EXECUTE sur l'ensemble SYS.DBMS_SPM.

Note : Seul l'utilisateur SYS peut configurer la tâche du service de conseils d'évolution SPM automatique, SYS_AUTO_SPM_EVOLVE_TASK.

Désactiver la saisie de plan automatique. ALTER SYSTEM privilege
Désactivez la tâche du service SPM de conseil d'évolution automatique. Privilège EXECUTE sur l'ensemble SYS.DBMS_AUTO_TASK_ADMIN.
Désactiver la tâche du service SPM de conseil d'évolution automatique à fréquence élevée. Privilège EXECUTE sur l'ensemble SYS.DBMS_SPM.
Désactivez l'utilisation des SQL Plan Baselines stockés dans la base de gestion SQL. ALTER SYSTEM privilege
Supprimez un seul plan SQL ou tous les plans associés à un énoncé SQL. Privilège EXECUTE sur l'ensemble SYS.DBMS_SPM.
Activer la saisie de plan automatique. ALTER SYSTEM privilege
Activer la tâche du service SPM de conseil d'évolution automatique. Privilège EXECUTE sur l'ensemble SYS.DBMS_AUTO_TASK_ADMIN.
Activer la tâche du service SPM de conseil d'évolution automatique à fréquence élevée. Privilège EXECUTE sur l'ensemble SYS.DBMS_SPM.
Activer l'utilisation des SQL Plan Baselines stockés dans la base de gestion SQL. ALTER SYSTEM privilege
Charger les plans SQL à partir d'instantanés AWR. Privilège EXECUTE sur les ensembles SYS.DBMS_SPM et SYS.DBMS_SCHEDULER.
Chargez les plans SQL à partir du cache de curseur. Privilège EXECUTE sur les ensembles SYS.DBMS_SPM et SYS.DBMS_SCHEDULER.
Voir les détails de configuration de référence de plan SQL. Privilège SELECT ou READ sur les vues suivantes :
  • SYS.DBA_SQL_MANAGEMENT_CONFIG
  • SYS.V_$SYSAUX_OCCUPANTS
  • SYS.V_$SYSTEM_PARAMETER2
  • SYS.DBA_ADVISOR_PARAMETERS
  • SYS.DBA_AUTOTASK_CLIENT
Voir les détails de la référence de plan SQL.
  • Privilège SELECT ou READ sur la vue SYS.DBA_SQL_PLAN_BASELINES.
  • Privilèges requis pour exécuter l'instruction SQL pour laquelle vous souhaitez obtenir le plan.
  • Privilège EXECUTE sur l'ensemble SYS.DBMS_XPLAN.
Voir les références de plan SQL. Privilège SELECT ou READ sur la vue SYS.DBA_SQL_PLAN_BASELINES.
Affichez les travaux soumis pour charger les SQL Plan Baselines. Privilège SELECT ou READ sur la vue SYS.DBA_SCHEDULER_JOBS.
Affichez le nombre de SQL Plan Baselines agrégés par leurs attributs. Privilège SELECT ou READ sur la vue SYS.DBA_SQL_PLAN_BASELINES.
Affichez le nombre de SQL Plan Baselines agrégés en fonction de leur dernière exécution. Privilège SELECT ou READ sur la vue SYS.DBA_SQL_PLAN_BASELINES.
Affichez les instructions SQL à partir du cache de curseur. Privilège SELECT ou READ sur la vue SYS.V_$SQL.

Gérer les références de plan SQL

Vous pouvez gérer les références de plan SQL dans l'onglet Références de plan SQL.

Les vignettes suivantes sont affichées en haut de l'onglet Lignes de référence du plan SQL :

  • Sommaire : Affiche le nombre total de lignes de base de plan SQL et indique si la référence de plan SQL, la saisie automatique de plan et les tâches de la fonction de conseil d'évolution SPM automatique sont activées. Dans la vignette Sommaire, vous pouvez activer ou désactiver les tâches SQL Plan Baseline, Saisie automatique de plans et Automatic SPM Evolve Advisor en cliquant sur les boutons Activer ou Désactiver et en fournissant des données d'identification de base de données.
  • Dernières exécutions de référence : Affiche le nombre de lignes de base de plan SQL en fonction de leur dernière exécution. Dans la vignette Dernières exécutions de référence, pointez la souris sur le graphique à secteurs pour voir des détails supplémentaires et filtrez les données affichées dans le graphique en cliquant sur les options de période répertoriées dans la légende.
  • Statistiques de plan SQL : Affiche les plans SQL décomposés par les statistiques suivantes :
    • Activé : Plans SQL pouvant être utilisés par l'optimiseur Oracle.
    • Accepté : Plans SQL qui sont dans des lignes de base de plan SQL et donc disponibles pour utilisation par l'optimiseur Oracle.
    • Reproduit : Plans SQL reproduits par l'optimiseur Oracle.
    • Fixe : Plans SQL acceptés marqués comme privilégiés, de sorte que l'optimiseur Oracle ne considère que ces plans dans la référence de plan SQL.
    • Épuration automatique : Plans SQL configurés pour être épurés automatiquement après la période de conservation par défaut.

    Dans la vignette Statistiques du plan SQL, pointez la souris sur le graphique à barres horizontales pour voir des détails supplémentaires et filtrez les données affichées dans le graphique en cliquant sur les options listées dans la légende.

La section Plans SQL répertorie les plans SQL exécutés avec des détails supplémentaires tels que la date de la dernière exécution d'un plan SQL, qu'il soit activé, accepté, reproduit, etc., et son origine. Pour filtrer la liste :

  • Cliquez sur une section du graphique à secteurs dans la vignette Référencer les dernières exécutions pour filtrer par heure de dernière exécution.
  • Cliquez sur une barre dans la vignette Statistiques du plan SQL pour filtrer les plans selon qu'ils sont activés, acceptés, reproduits, corrigés ou configurés pour l'épuration automatique.

Vous pouvez également utiliser le champ de recherche pour effectuer une recherche par texte SQL, nom de plan ou origine.

Dans la section Plans SQL, vous pouvez :

  • Cliquez sur le lien du plan SQL dans la colonne Texte SQL pour voir le plan SQL.
  • Cliquez sur l'icône Actions (Actions) d'un énoncé SQL et utilisez les options suivantes dans le menu :
    • Modifier les attributs de l'énoncé SQL : Cliquez pour modifier les attributs de l'énoncé SQL. Notez que toute modification apportée aux attributs de l'énoncé SQL aura une incidence sur tous les plans SQL associés.
    • Supprimer l'énoncé SQL : Cliquez pour supprimer l'énoncé SQL. Notez que la suppression de l'énoncé SQL supprime les plans SQL associés.
  • Cliquez sur l'icône Actions (Actions) pour un plan SQL et utilisez les options suivantes dans le menu :
    • Voir les détails : Cliquez sur ce bouton pour voir le plan SQL.
    • Modifier les attributs : Cliquez pour définir ou modifier les attributs suivants du plan SQL.
      • Épuration automatique : Cochez cette case pour épurer (supprimer) automatiquement le plan SQL après la période de conservation spécifiée.
      • Activé : Cochez cette case pour indiquer que le plan SQL est un plan activé.
      • Fixe : Cochez cette case pour indiquer que le plan SQL est un plan fixe.
    • Supprimer : Cliquez sur ce bouton pour supprimer le plan SQL de la référence de plan SQL.

Charger les plans SQL

Vous pouvez soumettre une tâche pour charger des plans SQL dans des références de plan SQL dans l'onglet Load SQL plans (Charger des plans SQL).

Vous pouvez charger des plans SQL à partir des sources suivantes :

Charger les plans SQL à partir du référentiel AWR

  1. Dans la section Gestion des plans SQL, cliquez sur l'onglet Charger des plans SQL.
  2. Dans la liste déroulante Charger un plan SQL à partir de, sélectionnez AWR et cliquez sur Charger.
  3. Dans le panneau Charger les plans SQL à partir du référentiel AWR :
    1. Fournissez les informations suivantes pour soumettre la tâche dans la section Général :
      1. Nom de la tâche : Vérifiez le nom alimenté automatiquement de la tâche et apportez-y des modifications, si nécessaire.
      2. Description : Facultativement, entrez une description de la tâche.
      3. Instantané de début : Entrez le numéro de l'instantané de début dans l'intervalle et sélectionnez-le dans la liste déroulante.
      4. Instantané de fin : Entrez le numéro de l'instantané de fin dans l'intervalle et sélectionnez-le dans la liste déroulante.
      5. Filtre de texte SQL : Facultativement, entrez du texte SQL pour charger uniquement les plans qui répondent aux critères de filtrage. Si aucune valeur n'est fournie, tous les plans de l'intervalle de clichés spécifié dans le référentiel AWR sont sélectionnés.
      6. Attributs de plan : Cochez les cases suivantes pour spécifier les attributs de plan :
        • Fixe : Cochez cette case pour indiquer que les plans chargés sont des plans fixes.
        • Activé : Cochez cette case pour indiquer que les plans chargés sont des plans activés.
    2. Sélectionnez l'une des options disponibles dans la liste déroulante Type de données d'identification de la section Données d'identification pour spécifier les données d'identification de base de données à connecter à la base de données gérée. Pour plus d'informations sur les types de données d'identification, voir Utiliser les données d'identification pour effectuer des tâches de diagnostic et de gestion.
    3. Cliquez sur enregistrer les modifications.

Charger les plans SQL à partir de la mémoire cache de curseur

  1. Dans la section Gestion des plans SQL, cliquez sur l'onglet Charger des plans SQL.
  2. Dans la liste déroulante Charger le plan SQL à partir de, sélectionnez Mémoire cache de curseur et cliquez sur Charger.
  3. Dans le panneau Charger les plans SQL à partir de la mémoire cache de curseur :
    1. Fournissez les informations suivantes pour soumettre la tâche dans la section Général :
      1. Nom de la tâche : Vérifiez le nom alimenté automatiquement de la tâche et apportez-y des modifications, si nécessaire.
      2. Description : Facultativement, entrez une description de la tâche.
      3. Extraire la référence à l'aide de : Utilisez l'une des options suivantes pour charger le plan SQL :
        • ID SQL : Sélectionnez ce bouton radio pour identifier l'énoncé SQL dans la mémoire cache de curseur dont vous voulez charger les plans SQL. Lorsque vous sélectionnez ce bouton radio, les champs suivants s'affichent :
          • ID SQL : Entrez l'ID énoncé SQL.
          • Valeur de hachage de plan : Facultativement, entrez la valeur de hachage de plan du plan SQL. Si aucune valeur n'est fournie, tous les plans présents dans le cache de curseur pour l'instruction SQL sont chargés.
          • Paramètre utilisé pour identifier la référence de plan SQL dans laquelle les plans sont chargés : Facultativement, sélectionnez les boutons radio Texte SQL ou Traitement SQL et entrez la valeur du paramètre dans le champ Valeur de paramètre. Si aucune valeur n'est fournie, le texte de l'instruction SQL identifiée est extrait du cache de curseur et sert à identifier la référence de plan SQL dans laquelle les plans sont chargés. Si la référence de plan SQL n'existe pas, elle est créée.
        • Nom du filtre : Sélectionnez ce bouton radio pour spécifier le filtre permettant d'identifier un énoncé SQL ou un jeu d'énoncés SQL. Lorsque vous sélectionnez ce bouton radio, les champs suivants s'affichent :
          • Nom du filtre : Sélectionnez un nom de filtre dans la liste déroulante.
          • Valeur de filtre : Entrez la valeur de filtre correspondante.
      4. Attributs de plan : Cochez les cases suivantes pour spécifier les attributs de plan :
        • Fixe : Cochez cette case pour indiquer que les plans chargés sont des plans fixes. Un plan fixe est un plan accepté qui est marqué comme privilégié, de sorte que l'optimiseur considère uniquement les plans fixes dans la ligne de base.
        • Activé : Cochez cette case pour indiquer que les plans chargés sont des plans activés. Un plan activé peut être utilisé par l'optimiseur Oracle.
    2. Sélectionnez l'une des options disponibles dans la liste déroulante Type de données d'identification de la section Données d'identification pour spécifier les données d'identification de base de données à connecter à la base de données gérée. Pour plus d'informations sur les types de données d'identification, voir Utiliser les données d'identification pour effectuer des tâches de diagnostic et de gestion.
    3. Cliquez sur enregistrer les modifications.

Exécuter des tâches de configuration SPM

Vous pouvez effectuer des tâches de configuration SPM telles que l'activation ou la désactivation des paramètres de référence de plan SQL et de saisie automatique de plan dans l'onglet Configuration.

L'onglet Configuration comporte les sections principales suivantes :

  • Référence de plan SQL : Fournit les options permettant d'activer ou de désactiver la référence de plan SQL. Si la référence de plan SQL est activée, les paramètres suivants sont affichés dans cette section et pour y apporter des modifications, cliquez sur Modifier :
    • Conservation du plan (semaines) : Nombre de semaines pendant lesquelles conserver les plans SQL non utilisés avant leur épuration. La période peut être comprise entre 5 et 523 semaines et la valeur par défaut est 53 semaines.
    • Budget de l'espace (%) : Pourcentage maximal d'espace SYSAUX que la base de gestion SQL peut utiliser. L'intervalle autorisé pour cette limite est compris entre 1 % et 50 % et la valeur par défaut est 10 %.
  • Saisie de plan automatique : Fournit les options permettant d'activer ou de désactiver la saisie de plan automatique. Un filtre automatique vous permet de capturer uniquement les instructions souhaitées et d'exclure les instructions non critiques. Cette technique permet d'économiser de l'espace dans l'espace-table SYSAUX.

    Si la saisie automatique de plan est activée, les filtres suivants sont affichés dans cette section et pour y apporter des modifications, cliquez sur Modifier :

    • Actions à inclure ou Actions à exclure : Actions à inclure ou à exclure de la saisie automatique.
    • Modules à inclure ou Modules à exclure : Modules à inclure ou à exclure de la saisie automatique.
    • Noms de schéma d'analyse à inclure ou Noms de schéma d'analyse à exclure : Noms de schéma d'analyse à inclure ou à exclure de la saisie automatique.
    • Texte SQL à inclure ou Texte SQL à exclure : Texte SQL à inclure ou à exclure de la saisie automatique.
    Note

    La saisie sélective de plans (filtres) n'est disponible que pour les bases de données Oracle version 12.2 et ultérieure.
  • Tâche du service SPM de conseil d'évolution automatique : Fournit les options permettant d'activer ou de désactiver la tâche du service SPM de conseil d'évolution automatique. Si la tâche du service de conseils d'évolution SPM automatique est activée, les paramètres suivants sont affichés dans cette section et pour y apporter des modifications, cliquez sur Modifier :
    • Tâche automatique du service SPM de conseil d'évolution à fréquence élevée : Affiche si la tâche automatique du service SPM de conseil d'évolution est activée pour se produire plus fréquemment.
    • Sources de plan de remplacement : Sources pour rechercher des plans supplémentaires.
    • Lignes de référence de plan de remplacement : Plans de remplacement qui doivent être chargés. La valeur par défaut est Existant.
    • Limite de plan de remplacement : Nombre maximal de plans à charger au total. La valeur par défaut est Illimité.
    • Accepter automatiquement les plans : Affiche si les plans recommandés doivent être acceptés automatiquement.
    • Limite de temps autorisée (secondes) : Limite de temps globale en secondes. Il s'agit du temps total autorisé pour la tâche.
    Note

    • Pour les bases de données autonomes, il n'est pas possible d'effectuer des tâches de configuration de gestion de plan SQL.
    • Les privilèges SYSDBA sont requis pour modifier les paramètres de la tâche du service de conseils d'évolution SPM automatique.
    • La tâche SPM automatique du service de conseils d'évolution n'est disponible que pour les bases de données Oracle version 12.2 et ultérieure.
    • La tâche SPM automatique haute fréquence du service de conseil d'évolution n'est disponible que pour les bases de données Oracle 19c et ultérieures s'exécutant sur la plate-forme Oracle Exadata.