Documentation Oracle Cloud Infrastructure

Utilisation de 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 des modifications apportées au plan d'exécution SQL.

SPM est un mécanisme préventif qui permet à l'optimiseur Oracle de gérer automatiquement les plans SQL, en veillant à ce que la base de données n'utilise que des plans connus ou vérifiés. Les performances d'une application de base de données dépendent fortement de l'exécution cohérente des instructions SQL. Le plan d'exécution d'une instruction SQL peut changer de façon inattendue pour diverses raisons, telles que la collecte de statistiques destinées à l'optimiseur, la modification des paramètres de l'optimiseur ou la définition de schémas ou de métadonnées. SPM fournit une "stabilité de plan" grâce à une structure qui préserve les plans SQL actuels au milieu des changements d'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é qu'il présente des performances comparables ou supérieures au plan en cours.

SPM utilise un mécanisme proactif appelé ligne de base de plan SQL, qui est un ensemble de plans SQL acceptés que l'optimiseur Oracle est autorisé à utiliser pour une instruction SQL. En utilisant des lignes de base, SPM empêche les régressions de plan dues aux modifications environnementales, tout en permettant à l'optimiseur de repérer et d'utiliser de meilleurs plans.

Les principaux composants de SPM sont les suivants :

  • Capture de plan : techniques de capture et de stockage des informations pertinentes sur les plans dans la base de gestion SQL pour un ensemble d'instructions SQL. La capture d'un plan implique d'informer SPM du plan et cela peut se faire par :
    • Capture automatique de plan : lorsque cette option est activée, la base de données vérifie si les instructions SQL exécutées sont admissibles pour la capture automatique. Pour être admissible à la capture automatique de plan, une instruction exécutée doit pouvoir être répétée et ne doit être exclue par aucun filtre de capture.
    • Capture de plan manuel : chargement en masse lancé par l'utilisateur des plans d'exécution existants pour les instructions SQL dans une ligne de base de plan SQL.
  • Sélection de plan : l'optimiseur Oracle peut détecter les modifications de plan en fonction de l'historique de plan stocké et utiliser des SQL Plan Baselines pour sélectionner des plans afin d'éviter d'éventuelles régressions de performances.
  • Evolution du plan : processus d'ajout de nouveaux plans aux SQL Plan Baselines existantes, manuellement ou automatiquement. L'optimiseur Oracle vérifie les nouveaux plans et les ajoute à un SQL Plan Baseline existant.

Pour plus d'informations sur SPM et ses composants, reportez-vous à Présentation de SQL Plan Management dans le guide de réglage SQL Oracle Database.

Pour utiliser SPM, accédez à la page Détails de la base de données gérée, puis cliquez sur Gestion des plans SQL dans le panneau de gauche sous Ressources. Vous pouvez effectuer les tâches SPM suivantes dans Database Management Diagnostics & Management :

  • Gérer les lignes de base de plan SQL.
  • Soumettez des tâches pour charger des plans SQL dans des lignes de base de plan SQL.
  • Effectuer des tâches de configuration telles que l'activation, la désactivation ou la modification de SQL Plan Baseline, la capture automatique de plan et les paramètres de tâche Automatic SPM Evolve Advisor.

Privilèges requis pour effectuer les tâches SPM

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

Remarque

Tout utilisateur disposant du privilège ADMINISTER SQL MANAGEMENT OBJECT peut exécuter le package DBMS_SPM.
Tâche Droits requis
Modifier un ou plusieurs attributs d'un plan SQL unique ou tous les plans associés à une instruction SQL. Privilège EXECUTE sur le package SYS.DBMS_SPM.
Modifiez la limite d'espace disque de la base de gestion SQL. Privilège EXECUTE sur le package SYS.DBMS_SPM.
Modifiez la période de conservation des plans SQL inutilisés. Privilège EXECUTE sur le package SYS.DBMS_SPM.
Configurez les filtres de capture automatique. Privilège EXECUTE sur le package SYS.DBMS_SPM.
Configurez la tâche Automatic SPM Evolve Advisor. Privilège EXECUTE sur le package SYS.DBMS_SPM.

Remarque : seul l'utilisateur SYS peut configurer la tâche Automatic SPM Evolve Advisor, SYS_AUTO_SPM_EVOLVE_TASK.

Désactivez la capture de plan automatique. ALTER SYSTEM privilege
Désactivez la tâche Automatic SPM Evolve Advisor. Privilège EXECUTE sur le package SYS.DBMS_AUTO_TASK_ADMIN.
Désactivez la tâche de la fonction de conseil d'évolution automatique SPM à fréquence élevée. Privilège EXECUTE sur le package SYS.DBMS_SPM.
Désactiver l'utilisation des lignes de base de plan SQL stockées dans la base de gestion SQL. ALTER SYSTEM privilege
Supprimez un seul plan SQL ou tous les plans associés à une instruction SQL. Privilège EXECUTE sur le package SYS.DBMS_SPM.
Activer la capture de plan automatique. ALTER SYSTEM privilege
Activez la tâche Automatic SPM Evolve Advisor. Privilège EXECUTE sur le package SYS.DBMS_AUTO_TASK_ADMIN.
Activez la tâche de la fonction de conseil d'évolution automatique SPM à fréquence élevée. Privilège EXECUTE sur le package SYS.DBMS_SPM.
Activer l'utilisation des lignes de base de plan SQL stockées dans la base de gestion SQL. ALTER SYSTEM privilege
Chargez des plans SQL à partir de clichés AWR. Privilège EXECUTE sur les packages SYS.DBMS_SPM et SYS.DBMS_SCHEDULER.
Chargez des plans SQL à partir du cache de curseur. Privilège EXECUTE sur les packages SYS.DBMS_SPM et SYS.DBMS_SCHEDULER.
Afficher les détails de configuration de la ligne de base 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
Afficher les détails de la ligne de base 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 le package SYS.DBMS_XPLAN.
Affichez les lignes de base de plan SQL. Privilège SELECT ou READ sur la vue SYS.DBA_SQL_PLAN_BASELINES.
Affichez les travaux soumis pour charger des lignes de base de plan SQL. Privilège SELECT ou READ sur la vue SYS.DBA_SCHEDULER_JOBS.
Affichez le nombre de SQL Plan Baselines agrégées par leurs attributs. Privilège SELECT ou READ sur la vue SYS.DBA_SQL_PLAN_BASELINES.
Afficher le nombre de SQL Plan Baselines agrégés par 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 lignes de base de plan SQL

Vous pouvez gérer les lignes de base de plan SQL dans l'onglet Lignes de base de plan SQL.

Les mosaïques suivantes sont affichées en haut de l'onglet Lignes de base de plan SQL :

  • Récapitulatif : affiche le nombre total de lignes de base de plan SQL et indique si les tâches SQL Plan Baseline, de capture de plan automatique et Automatic SPM Evolve Advisor sont activées. Dans la mosaïque Récapitulatif, vous pouvez activer ou désactiver les tâches SQL Plan Baseline, de capture de plan automatique et Automatic SPM Evolve Advisor en cliquant sur les boutons Activer ou Désactiver et en fournissant des informations 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 la date de leur dernière exécution. Dans la mosaïque Dernières exécutions de la référence, placez le pointeur de la souris sur le graphique à secteurs pour afficher 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 ventilés selon les statistiques suivantes :
    • Activé : plans SQL pouvant être utilisés par l'optimiseur Oracle.
    • Accepté : plans SQL qui figurent dans des SQL Plan Baselines et peuvent donc être utilisés par l'optimiseur Oracle.
    • Reproduit : plans SQL reproduits par l'optimiseur Oracle.
    • Fixe : plans SQL acceptés marqués comme préférés, de sorte que l'optimiseur Oracle ne prend en compte que ces plans dans le SQL Plan Baseline.
    • Purge automatique : plans SQL configurés pour être purgés automatiquement après la période de conservation par défaut.

    Dans la mosaïque Statistiques de plan SQL, placez le pointeur de la souris sur le graphique à barres horizontales pour afficher des détails supplémentaires et filtrez les données affichées dans le graphique en cliquant sur les options répertorié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, son activation, son acceptation, sa reproduction, etc., ainsi que son origine. Pour filtrer la liste, procédez comme suit :

  • Cliquez sur une section du graphique à secteurs dans la mosaïque Dernières exécutions de référence pour filtrer par heure de dernière exécution.
  • Cliquez sur une barre de la mosaïque Statistiques de plan SQL pour filtrer les plans selon qu'ils sont activés, acceptés, reproduits, corrigés ou configurés pour la purge 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 effectuer les tâches suivantes :

  • Cliquez sur le lien du plan SQL dans la colonne Texte SQL pour visualiser le plan SQL.
  • Cliquez sur l'icône Actions (Actions) d'une instruction SQL et utilisez les options suivantes dans le menu :
    • Modifier les attributs d'instruction SQL : cliquez sur cette option pour modifier les attributs d'instruction SQL. Les modifications apportées aux attributs d'instruction SQL auront une incidence sur tous les plans SQL associés.
    • Supprimer l'instruction SQL : cliquez sur cette option pour supprimer l'instruction SQL. La suppression de l'instruction SQL supprimera les plans SQL associés.
  • Cliquez sur l'icône Actions (Actions) d'un plan SQL et utilisez les options suivantes dans le menu :
    • Visualiser les détails : cliquez sur cette option pour visualiser le plan SQL.
    • Modifier les attributs : cliquez sur cette option pour définir ou modifier les attributs suivants du plan SQL.
      • Purger automatiquement : cochez cette case pour purger (supprimer) automatiquement le plan SQL après la période de conservation indiqué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 cette option pour supprimer le plan SQL de la ligne de base de plan SQL.

Charger les plans SQL

Vous pouvez soumettre une tâche pour charger des plans SQL dans des SQL Plan Baselines dans l'onglet 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 les plans SQL.
  2. Dans la liste déroulante Charger le 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, procédez comme suit :
    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 renseigné automatiquement de la tâche et apportez des modifications, si nécessaire.
      2. Description : saisissez éventuellement une description de la tâche.
      3. Cliché de début : entrez le numéro du cliché de début dans la plage et sélectionnez-le dans la liste déroulante.
      4. Cliché de fin : entrez le numéro du cliché de fin dans la plage et sélectionnez-le dans la liste déroulante.
      5. Filtre de texte SQL : vous pouvez éventuellement saisir 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 compris dans la plage de clichés indiquée dans le référentiel AWR sont sélectionnés.
      6. Attributs de plan : cochez les cases suivantes pour indiquer 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 d'informations d'identification de la section Informations d'identification pour indiquer les informations d'identification de base de données à connecter à la base de données gérée. Pour plus d'informations sur les types d'informations d'identification, reportez-vous à Utilisation des informations d'identification pour effectuer des tâches de diagnostic et de gestion.
    3. Cliquez sur Enregistrer les modifications.

Charger des plans SQL à partir du cache de curseurs

  1. Dans la section Gestion des plans SQL, cliquez sur l'onglet Charger les plans SQL.
  2. Dans la liste déroulante Charger le plan SQL à partir de, sélectionnez Cache de curseur et cliquez sur Charger.
  3. Dans le panneau Charger des plans SQL à partir du cache de curseur, procédez comme suit :
    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 renseigné automatiquement de la tâche et apportez des modifications, si nécessaire.
      2. Description : saisissez éventuellement une description de la tâche.
      3. Extraire la ligne de base à l'aide de : utilisez l'une des options suivantes pour charger le plan SQL :
        • ID SQL : sélectionnez ce bouton radio pour identifier l'instruction SQL dans le cache de curseur dont vous souhaitez charger les plans SQL. Lorsque vous sélectionnez ce bouton radio, les champs suivants s'affichent :
          • ID SQL : entrez l'ID d'instruction SQL.
          • Valeur de hachage de plan : entrez éventuellement 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 : vous pouvez éventuellement sélectionner les boutons radio Texte SQL ou Gestionnaire SQL et saisir 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 utilisé pour identifier le SQL Plan Baseline dans lequel les plans sont chargés. Si le SQL Plan Baseline n'existe pas, il est créé.
        • Nom du filtre : sélectionnez ce bouton radio pour indiquer le filtre permettant d'identifier une instruction SQL ou un ensemble d'instructions 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 indiquer 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 préféré, de sorte que l'optimiseur ne prend en compte que les plans fixes de la référence.
        • Activé : cochez cette case pour indiquer que les plans chargés sont des plans activés. Un plan activé est admissible à l'utilisation par l'optimiseur Oracle.
    2. Sélectionnez l'une des options disponibles dans la liste déroulante Type d'informations d'identification de la section Informations d'identification pour indiquer les informations d'identification de base de données à connecter à la base de données gérée. Pour plus d'informations sur les types d'informations d'identification, reportez-vous à Utilisation des informations d'identification pour effectuer des tâches de diagnostic et de gestion.
    3. Cliquez sur Enregistrer les modifications.

Exécution des tâches de configuration du SPM

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

L'onglet Configuration contient les sections principales suivantes :

  • Valeur de référence de plan SQL : fournit les options permettant d'activer ou de désactiver la valeur de référence de plan SQL. Si la ligne de base de plan SQL est activée, les paramètres suivants sont affichés dans cette section et, pour les modifier, cliquez sur Modifier :
    • Conservation de plan (semaines) : nombre de semaines pendant lesquelles les plans SQL non utilisés doivent être conservés avant d'être purgés. La période peut être comprise entre 5 et 523 semaines et la valeur par défaut est 53 semaines.
    • Budget d'espace (%) : pourcentage maximal d'espace SYSAUX que la base de gestion SQL peut utiliser. La plage autorisée pour cette limite est comprise entre 1 % et 50 % et la valeur par défaut est 10 %.
  • Capture de plan automatique : fournit les options permettant d'activer ou de désactiver la capture 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 le tablespace SYSAUX.

    Si la capture automatique de plan est activée, les filtres suivants sont affichés dans cette section et, pour les modifier, cliquez sur Modifier :

    • Actions à inclure ou Actions à exclure : actions à inclure ou à exclure de la capture automatique.
    • Modules à inclure : ou Modules à exclure : modules à inclure ou à exclure de la capture 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 capture automatique.
    • Texte SQL à inclure : ou Texte SQL à exclure : texte SQL à inclure ou à exclure de la capture automatique.
    Remarque

    La capture sélective de plans (filtres) est uniquement disponible pour les bases de données Oracle version 12.2 et ultérieure.
  • Tâche de la fonction de conseil d'évolution automatique SPM : fournit les options permettant d'activer ou de désactiver la tâche de la fonction de conseil d'évolution automatique SPM. Si la tâche Automatic SPM Evolve Advisor est activée, les paramètres suivants sont affichés dans cette section et, pour les modifier, cliquez sur Modifier :
    • Tâche de la fonction de conseil d'évolution automatique SPM à fréquence élevée : indique si la tâche de la fonction de conseil d'évolution automatique SPM est activée pour se produire plus fréquemment.
    • Autres sources de plan : sources de recherche de plans supplémentaires.
    • Autres plan de référence : plans alternatifs à charger. La valeur par défaut est Existant.
    • Autre limite de plan : nombre maximum de plans à charger au total. La valeur par défaut est Illimité.
    • Accepter automatiquement les plans : indique si les plans recommandés doivent être acceptés automatiquement.
    • Limite de temps autorisée (s) : limite de temps globale en secondes. Il s'agit du temps total autorisé pour la tâche.
    Remarque

    • Pour les bases de données autonomes, les tâches de configuration de gestion de plan SQL ne peuvent pas être effectuées.
    • Les privilèges SYSDBA sont requis pour modifier les paramètres de la tâche Automatic SPM Evolve Advisor.
    • La tâche Automatic SPM Evolve Advisor n'est disponible que pour les bases de données Oracle version 12.2 et ultérieure.
    • La tâche Automatic SPM Evolve Advisor haute fréquence est uniquement disponible pour les bases de données Oracle 19c et versions ultérieures exécutées sur la plate-forme Oracle Exadata.