Documentation Oracle Cloud Infrastructure

Analyse d'instructions SQL avec SQL Tuning Advisor

SQL Tuning Advisor permet d'analyser et de régler des instructions SQL.

Le réglage SQL est un aspect important du réglage des performances de système de base de données. SQL Tuning Advisor est un mécanisme permettant de résoudre les problèmes liés aux instructions SQL dont les performances ne sont pas optimales. Il utilise des instructions SQL ou un ensemble de réglages SQL en guise d'entrée et appelle l'optimiseur de réglage automatique pour analyser les instructions. La sortie se présente sous la forme de résultats et de recommandations, avec une justification et le gain attendu pour chaque recommandation. Les recommandations de réglage incluent celles mentionnées ci-après. Vous pouvez choisir d'accepter les recommandations pour réaliser le réglage des instructions SQL.

  • Collecte de statistiques d'objets
  • Création d'index
  • Réécriture d'instructions SQL
  • Création de profils SQL
  • Création de valeurs de référence de plan SQL

Pour plus d'informations sur les sujets suivants, reportez-vous aux rubriques indiquées :

Dans Database Management Diagnostics & Management, vous pouvez utiliser SQL Tuning Advisor pour analyser les instructions SQL d'une base de données gérée.

Remarque

SQL Tuning Advisor est uniquement disponible pour Oracle Database Enterprise Edition versions 12.2 et ultérieures. Si le paramètre d'initialisation COMPATIBLE est défini pour la base de données, il doit correspondre aux versions 12.2.0 et ultérieures. Pour plus d'informations sur la compatibilité de base de données, reportez-vous à Qu'est-ce que la compatibilité Oracle Database ? dans le guide de mise à niveau Oracle Database.

Voici les principales étapes de l'utilisation de SQL Tuning Advisor dans Diagnostics & Management :

  1. Sélection de l'entrée et exécution de SQL Tuning Advisor. vous pouvez sélectionner l'entrée (instructions SQL ou ensemble de réglages SQL) pour SQL Tuning Advisor à l'aide de l'une des méthodes suivantes, puis la soumettre en tant que tâche de réglage SQL :

    • Accédez au hub de performances, sélectionnez des ID SQL dans l'onglet ASH Analytics, puis cliquez sur Régler le code SQL.

    • Cliquez sur Régler le code SQL dans la section Tâches SQL Tuning Advisor, puis sélectionnez des instructions SQL ou un ensemble de patches. Pour pouvoir sélectionner des instructions SQL individuelles en tant qu'entrée, vous devez d'abord les sélectionner dans l'onglet ASH Analytics du hub de performances.

    • Activez le réglage SQL automatique sur la base de données. Diagnostics & Management ne prend pas en charge la configuration automatique de SQL Tuning Advisor. Toutefois, si SQL Tuning Advisor est configuré pour s'exécuter automatiquement sur la base de données, la tâche de réglage SQL automatique ainsi que ses résultats et ses recommandations sont également affichés dans Diagnostics & Management. Pour savoir comment configurer SQL Tuning Advisor en tant que tâche automatisée, reportez-vous à la section Managing the Automatic SQL Tuning Task dans le guide Oracle Database SQL Tuning Guide.

  2. Visualiser les résultats : les résultats de SQL Tuning Advisor sont affichés dans la section Tâches SQL Tuning Advisor de la page Détails de la base de données gérée.
  3. Implémentation des recommandations : les résultats et les recommandations de SQL Tuning Advisor sont disponibles sur la page Détails de la tâche SQL Tuning Advisor. Vous pouvez consulter ces informations et choisir d'implémenter les recommandations.

Rôle et privilèges requis pour utiliser SQL Tuning Advisor

Vous devez disposer de privilèges d'administration Oracle Database pour effectuer les tâches dans le workflow SQL Tuning Advisor. En outre, le rôle et les privilèges suivants doivent être affectés :

GRANT SELECT_CATALOG_ROLE <following privileges> TO <admin user>
  • ADVISOR
  • CREATE JOB
  • CREATE SESSION
  • SELECT ANY DICTIONARY
  • INHERIT ANY PRIVILEGES
  • ADMINISTER SQL TUNING SET TO <SQLTUNEUSER>

Pour plus d'informations sur les rôles et les privilèges d'Oracle Database, reportez-vous à Configuration d'autorisation de privilège et de rôle dans le guide de sécurité Oracle Database.

Exécution de SQL Tuning Advisor

Pour commencer, vous devez exécuter SQL Tuning Advisor sur des instructions SQL sélectionnées ou sur un ensemble de réglages SQL.

Vous pouvez utiliser l'une des options suivantes pour accéder au panneau Exécuter SQL Tuning Advisor sur la page Détails de la base de données gérée de la base de données gérée de la base de données gérée :

  • Cliquez sur Hub de performances, accédez à l'onglet ASH Analytics, faites défiler l'écran vers le bas jusqu'aux ID SQL répertoriés dans la section ID SQL par classe d'attente (vue par défaut), sélectionnez des instructions SQL, puis cliquez sur Régler le code SQL.

    Remarque

    Le hub de performances pour les bases de données gérées ne prend en charge qu'Oracle Database Enterprise Edition. La disponibilité des fonctionnalités du hub de performances dépend du type et de la version de la base de données Oracle, et nécessite certains privilèges supplémentaires. Afin d'obtenir des informations sur toutes les conditions ayant une incidence sur l'utilisation du hub de performances pour les bases de données gérées, reportez-vous à OCI : Prerequisite Conditions for Performance Hub (KB59684) dans My Oracle Support.

  • Cliquez sur SQL Tuning Advisor dans le panneau de gauche sous Ressources, puis cliquez sur Régler le code SQL dans la section Tâches SQL Tuning Advisor.

Dans le panneau Exécution de SQL Tuning Advisor, procédez comme suit :

  1. Saisissez les détails suivants dans la section Définition de tâche :
    1. Nom : saisissez le nom de la tâche de réglage SQL.
    2. Description : saisissez éventuellement une description de la tâche.
  2. Sélectionnez l'une des options suivantes dans la section SQL :
    • Instructions SQL sélectionnées : servez-vous de cette option pour sélectionner et analyser les instructions SQL sélectionnées dans le hub de performances. Cette option est activée uniquement si vous avez préalablement sélectionné des instructions SQL dans l'onglet ASH Analytics du hub de performances.
    • Ensemble de réglages SQL : servez-vous de cette option afin de sélectionner un ensemble de réglages SQL en tant qu'entrée pour SQL Tuning Advisor. Les ensembles de réglages SQL ne sont répertoriés dans cette section que s'ils ont été préalablement créés dans la base de données. Pour obtenir des informations sur les ensembles de réglages SQL, reportez-vous à Capture de charges globales dans des ensembles de réglages SQL dans le guide de réglage SQL Oracle Database.
  3. Indiquez la portée de la tâche dans la section Paramètres de la tâche :
    1. Durée limite totale (minutes) : indiquez la durée totale de l'analyse de l'instruction par SQL Tuning Advisor.
    2. Portée de l'analyse : sélectionnez l'option appropriée pour définir la portée de l'analyse.
      • Limité : SQL Tuning Advisor génère des recommandations basées sur les vérifications statistiques, l'analyse des chemins d'accès et l'analyse de la structure SQL. Aucune recommandation de profil SQL n'est générée.
      • Complet : SQL Tuning Advisor effectue toutes les analyses qu'il réalise dans le cadre de la portée Limité, plus le profilage SQL.
  4. 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.
  5. Cliquez sur Exécuter.
La tâche de réglage SQL est soumise à SQL Tuning Advisor.

Affichage des résultats de SQL Tuning Advisor

Lors de l'exécution de SQL Tuning Advisor, vous pouvez consulter ses résultats et recommandations dans Diagnostics & Management.

Une fois que vous avez exécuté SQL Tuning Advisor sur des instructions SQL ou un service STS sélectionnés, la tâche de réglage SQL est affichée dans la section Tâches SQL Tuning Advisor. Si SQL Tuning Advisor est configuré pour s'exécuter automatiquement sur la base de données, les tâches de réglage SQL automatique sont également affichées. Pour chacune d'entre elles, vous pouvez cliquer sur l'icône Actions (Actions), puis sur l'option Réexécuter la tâche de réglage SQL afin de réexécuter la tâche de réglage SQL, si nécessaire.

Cliquez sur le nom de la tâche de réglage SQL pour accéder à la page Détails de la tâche SQL Tuning Advisor. Les résultats et les recommandations de SQL Tuning Advisor sont affichés dans les onglets suivants de la page Détails de la tâche SQL Tuning Advisor :

  • Onglet Récapitulatif : consultez un récapitulatif de la tâche et des résultats fournis par SQL Tuning Advisor sous forme de graphique.
    • Instructions SQL distinctes examinées : les instructions SQL examinées par SQL Tuning Advisor sont regroupées dans les catégories Examen de l'instruction SQL avec résultats, Instruction SQL ignorée en raison d'erreurs et Examen de l'instruction SQL sans résultats, et affichées dans un graphique en anneau. Si l'instruction SQL a été exécutée plusieurs fois, une seule exécution (distincte) au cours de la période d'analyse est prise en compte.
    • Gain pour les recommandations de profil SQL : le gain des recommandations de profil SQL en temps de base de données (en secondes) est affiché dans des graphiques à barres. Les graphiques à barres comportent des barres avant et après, qui indiquent respectivement le temps de base de données réel et le temps de base de données amélioré. Si les recommandations de profil SQL sont fournies mais pas implémentées, un seul graphique de gain Potentiel est affiché. Si les recommandations de profil SQL sont partiellement implémentées, les graphiques de gain Potentiel et Implémenté sont affichés. Si les recommandations de profil SQL sont toutes implémentées, seul le graphique de gain Implémenté est affiché.
    • Résultats avec recommandations par type : les résultats avec recommandations de SQL Tuning Advisor sont classés par type et affichés dans un graphique à barres.
  • Onglet Résultats SQL : consultez les résultats et implémentez les recommandations de SQL Tuning Advisor. Vous pouvez afficher la liste des instructions SQL et les informations correspondantes issues de l'analyse effectuée par SQL Tuning Advisor. Cela inclut l'estimation du gain de performances et les résultats avec recommandations. Vous pouvez sélectionner une instruction SQL unique afin d'afficher les recommandations spécifiques pour chaque résultat, la justification et le gain attendu si une recommandation est implémentée. Dans cet onglet, vous pouvez utiliser les options disponibles pour implémenter toutes les recommandations de profil SQL, implémenter une seule instruction et comparer des plans d'explication.
  • Onglet Instruction SQL sans résultats : visualisez les instructions SQL qui ont été ignorées en raison d'erreurs, de l'expiration de la tâche ou de l'absence de résultats ou de recommandations.

Implémentation des recommandations de SQL Tuning Advisor

Vous pouvez créer des travaux pour implémenter les recommandations de la fonction de conseil STA (SQL Tuning Advisor).

Pour ce faire, accédez à la page Détails de la tâche SQL Tuning Advisor et affichez les résultats et les recommandations de l'analyse SQL Tuning Advisor dans l'onglet Résultats SQL. Dans cet onglet, vous pouvez créer un travail pour implémenter toutes les recommandations de profil SQL ou implémenter une seule recommandation de profil SQL, d'index ou de statistiques, et comparer des plans d'explication. Vous pouvez également consulter les résultats de restructuration SQL, de plan de remplacement et autres. Toutefois, l'option d'implémentation des recommandations de restructuration SQL et de plan de remplacement n'est pas disponible. Pour plus d'informations sur les restructurations SQL et les plans de remplacement, reportez-vous à Analyse structurelle d'instruction SQL et à Analyse de plan de remplacement dans le guide de réglage SQL Oracle Database.


Onglet Résultats SQL

Rubriques :

Implémentation de toutes les recommandations de profil SQL

  1. Accédez à la page Détails de la tâche SQL Tuning Advisor et cliquez sur l'onglet Résultats SQL.
  2. Cliquez sur implémenter toutes les recommandations de profil SQL.
  3. Dans le panneau Implémenter toutes les recommandations, procédez comme suit :
    1. Fournissez les informations suivantes afin de créer un travail pour implémenter les recommandations :
      1. Nom : saisissez le nom unique du travail.
      2. Description : saisissez éventuellement une description du travail.
    2. Indiquez les paramètres de la tâche :
      1. Implémenter le nouveau profil avec une mise en correspondance forcée : sélectionnez cette option pour cibler toutes les instructions SQL qui ont le même texte après avoir normalisé toutes les valeurs littérales en valeurs attachées. Si vous utilisez une combinaison de valeurs littérales et de valeurs attachées dans l'instruction SQL, aucune transformation en valeurs attachées n'est effectuée.
      2. Définir la catégorie du profil SQL : sélectionnez cette option pour indiquer la catégorie dans laquelle créer le profil SQL et saisissez le nom de la catégorie dans le champ Créer un profil dans la catégorie.
    3. Indiquez les paramètres du travail :
      1. Type d'informations d'identification : sélectionnez l'une des options disponibles 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.
      2. Bucket pour la sortie du travail : sélectionnez le bucket Oracle Object Storage dans lequel stocker la sortie du travail. Le bucket Oracle Object Storage peut se trouver dans le même compartiment que le travail ou dans un autre compartiment. Vous devez disposer des droits d'accès requis du service Oracle Cloud Infrastructure Object Storage pour sélectionner le bucket. Pour plus d'informations, reportez-vous à Autorisations supplémentaires requises pour utiliser Diagnostics & Management.
    4. (Facultatif) Cliquez sur Afficher le code SQL pour visualiser l'instruction SQL qui sera exécutée.
    5. Cliquez sur Implémenter les recommandations.
Toutes les recommandations de profil SQL vont à présent être implémentées. Vous pouvez surveiller ce travail dans la section Travaux de la base de données gérée.

Pour implémenter une seule recommandation de profil SQL, cliquez sur la section Mettre en oeuvre une recommandation pour l'ID SQL : <ID SQL>, cliquez sur l'icône Actions (Actions) dans la ligne Profil SQL, puis sur Mettre en oeuvre la recommandation.

Pour plus d'informations sur les profils SQL, reportez-vous à A propos des profils SQL dans le guide de réglage SQL Oracle Database.

Implémentation d'une recommandation d'index

  1. Dans l'onglet Résultats SQL, sélectionnez une instruction SQL, accédez à la section Mettre en oeuvre une recommandation pour l'ID SQL : <ID SQL, cliquez sur l'icône Actions (Actions) dans la ligne Index, puis sur Mettre en oeuvre la recommandation.
  2. Dans le panneau Implémenter la recommandation :
    1. Fournissez les informations suivantes afin de créer un travail pour implémenter les recommandations :
      1. Nom : saisissez le nom unique du travail.
      2. Description : saisissez éventuellement une description du travail.
    2. Dans la section Paramètres de la tâche, sélectionnez le tablespace qui sera utilisé pour implémenter la recommandation d'index.
    3. Indiquez les paramètres du travail :
      1. Type d'informations d'identification : sélectionnez l'une des options disponibles 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.
      2. Bucket pour la sortie du travail : sélectionnez le bucket Oracle Object Storage dans lequel stocker la sortie du travail. Le bucket Oracle Object Storage peut se trouver dans le même compartiment que le travail ou dans un autre compartiment. Vous devez disposer des droits d'accès requis du service Oracle Cloud Infrastructure Object Storage pour sélectionner le bucket. Pour plus d'informations, reportez-vous à Autorisations supplémentaires requises pour utiliser Diagnostics & Management.
    4. (Facultatif) Cliquez sur Afficher le code SQL pour visualiser l'instruction SQL qui sera exécutée.
    5. Cliquez sur Implémenter la recommandation.
La recommandation d'index va à présent être implémentée. Vous pouvez surveiller ce travail dans la section Travaux de la base de données gérée.

Implémentation d'une recommandation de statistiques

  1. Dans l'onglet Résultats SQL, sélectionnez une instruction SQL, accédez à la section Implémenter une recommandation pour l'ID SQL : <ID SQL>, cliquez sur l'icône Actions (Actions) dans la ligne Statistiques, puis sur Implémenter la recommandation.
  2. Dans le panneau Implémenter la recommandation :
    1. Fournissez les informations suivantes afin de créer un travail pour implémenter les recommandations :
      1. Nom : saisissez le nom unique du travail.
      2. Description : saisissez éventuellement une description du travail.
    2. Dans la section Paramètres de la tâche, vérifiez les statistiques prises en compte pour l'implémentation.
    3. Indiquez les paramètres du travail :
      1. Type d'informations d'identification : sélectionnez l'une des options disponibles 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.
      2. Bucket pour la sortie du travail : sélectionnez le bucket Oracle Object Storage dans lequel stocker la sortie du travail. Le bucket Oracle Object Storage peut se trouver dans le même compartiment que le travail ou dans un autre compartiment. Vous devez disposer des droits d'accès requis du service Oracle Cloud Infrastructure Object Storage pour sélectionner le bucket. Pour plus d'informations, reportez-vous à Autorisations supplémentaires requises pour utiliser Diagnostics & Management.
    4. (Facultatif) Cliquez sur Afficher le code SQL pour visualiser l'instruction SQL qui sera exécutée.
    5. Cliquez sur Implémenter la recommandation.
La recommandation de statistiques va à présent être implémentée. Vous pouvez surveiller ce travail dans la section Travaux de la base de données gérée.

Comparaison de plans d'explication

Le plan d'explication est une instruction qui affiche les plans d'exécution choisis par l'optimiseur Oracle pour les instructions SELECT, UPDATE, INSERT et DELETE. Le plan d'exécution d'une instruction est la séquence d'opérations qu'Oracle effectue pour exécuter cette instruction.

Pour afficher et comparer des plans d'explication, procédez comme suit :

  1. Dans l'onglet Résultats SQL, sélectionnez une instruction SQL, accédez à la section Implémenter une recommandation pour l'ID SQL : <ID SQL>, cliquez sur l'icône Actions (Actions) Index ou Profil SQL, cliquez sur Comparer les plans d'explication.
  2. Sur la page Comparer les plans d'exécution, procédez comme suit :
    • Comparez le temps de base de données et le nombre d'E/S du plan d'explication d'origine et du nouveau, dans l'onglet Résultats des tests de profil. Cette comparaison est présentée dans une vue graphique et compare des mesures telles que le temps UC, les buffers gets et les lectures sur disque. L'onglet Résultats des tests de profil n'apparaît pas si le rapport de comparaison n'est pas généré.
    • Comparez les étapes du plan d'exécution d'origine et du nouveau plan d'exécution de l'instruction SQL dans l'onglet Plan d'explication. Cette comparaison est présentée dans une vue tabulaire, mais vous pouvez sélectionner Plan d'explication graphique dans la liste déroulante Option d'affichage pour afficher une représentation graphique du plan d'explication. Dans la vue Plan d'explication graphique, cliquez sur Faire pivoter ou utilisez la souris et le pavé tactile pour faire défiler le plan d'explication et afficher la séquence d'opérations.
  3. Cliquez sur Fermer pour revenir à la page Détails de la tâche SQL Tuning Advisor.