Documentation sur Oracle Cloud Infrastructure

Analyser des énoncés SQL à l'aide de SQL Tuning Advisor

Vous pouvez utiliser SQL Tuning Advisor pour analyser et régler des énoncés SQL.

Le réglage SQL est un aspect important du réglage de la performance du système de base de données. SQL Tuning Advisor est un mécanisme qui permet de résoudre les problèmes liés aux énoncés SQL dont la performance n'est pas optimale. Il prend un ou plusieurs énoncés SQL ou un ensemble de réglages SQL (STS) en entrée et appelle l'optimiseur de réglage automatique pour analyser les énoncés. Les résultats sont présentés sous la forme de conclusions et de recommandations, ainsi que d'une explication de chaque recommandation et des avantages attendus. Les recommandations de réglage sont les suivantes et vous pouvez choisir d'accepter les recommandations pour terminer le réglage des énoncés SQL.

  • Collecte de statistiques sur les objets
  • Création d'index
  • Réécriture des énoncés SQL
  • Création de profils SQL
  • Création de références de plan SQL

Pour plus d'informations sur les sujets suivants :

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

Note

SQL Tuning Advisor est disponible uniquement pour Oracle Database Édition Enterprise versions 12.2 et ultérieures, et 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, voir Qu'est-ce que la compatibilité Oracle Database? dans le Guide de mise à niveau d'Oracle Database.

Voici les principales étapes à suivre pour utiliser SQL Tuning Advisor dans Diagnostics & Management :

  1. Sélectionner l'entrée et exécuter SQL Tuning Advisor : L'entrée (énoncés SQL ou ensemble de réglages SQL) pour SQL Tuning Advisor peut être sélectionnée à l'aide de l'une des options suivantes et soumise en tant que tâche de réglage SQL :

    • Allez au centre de performance, sélectionnez un ou plusieurs ID SQL dans l'onglet Analyse ASH, puis cliquez sur Régler SQL.

    • Cliquez sur Régler SQL dans la section Tâches SQL Tuning Advisor et sélectionnez des énoncés SQL ou un ensemble de réglages SQL. Notez que pour pouvoir sélectionner des énoncés SQL individuels comme entrée, vous devez d'abord les sélectionner dans l'onglet Analyse ASH du centre de performance.

    • Activez le réglage SQL automatique sur la base de données. Notez que 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 les conclusions et les recommandations s'affichent également dans Diagnostics & Management. Pour plus d'informations sur la configuration de SQL Tuning Advisor en tant que tâche automatisée, voir Gestion de la tâche automatique de réglage SQL dans le Guide de réglage SQL pour Oracle Database.

  2. Voir les conclusions : Les conclusions de SQL Tuning Advisor sont affichées dans la section Tâches SQL Tuning Advisor de la page Détails de la base de données gérée.
  3. Mettre en oeuvre les recommandations : Les résultats et recommandations de SQL Tuning Advisor sont disponibles dans la page Détails de la tâche SQL Tuning Advisor. Vous pouvez consulter ces informations et choisir de mettre en oeuvre les recommandations.

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

Vous devez disposer des privilèges d'administration d'Oracle Database pour effectuer les tâches du flux de travail 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, voir Configuration des privilèges et autorisations de rôle dans le Guide sur la sécurité d'Oracle Database.

Exécuter SQL Tuning Advisor

Dans un premier temps, vous devez exécuter SQL Tuning Advisor sur des énoncés SQL sélectionnés ou un ensemble de réglages SQL.

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

  • Cliquez sur Centre de performance et, dans l'onglet Analyse ASH, faites défiler l'affichage 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 un ou plusieurs énoncés SQL et cliquez sur Régler SQL.

    Note

    Le centre de performance pour les bases de données gérées prend uniquement en charge Oracle Database Édition Enterprise et la disponibilité des fonctions du centre de performance dépend du type et de la version de la base de données Oracle et nécessite certains privilèges supplémentaires. Pour plus d'informations sur toutes les conditions ayant une incidence sur l'utilisation du centre de performance pour les bases de données gérées, voir OCI : Conditions préalables pour le centre de performance (KB59684) dans My Oracle Support.

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

Dans le panneau Exécuter SQL Tuning Advisor :

  1. Entrez les détails suivants dans la section Définition de la tâche :
    1. Nom : Entrez un nom pour la tâche de réglage SQL.
    2. Description : Facultativement, entrez une description de la tâche.
  2. Sélectionnez l'une des options suivantes dans la section SQL :
    • énoncés SQL sélectionnés : Utilisez cette option pour sélectionner et analyser les énoncés SQL sélectionnés dans le centre de performance. Notez que cette option n'est activée que si des énoncés SQL sont d'abord sélectionnés dans l'onglet Analyse ASH du centre de performance.
    • Ensemble de réglages SQL : Utilisez cette option pour sélectionner un ensemble de réglages SQL comme entrée de SQL Tuning Advisor. Notez que des ensembles de réglages SQL sont listés dans cette section uniquement s'ils ont été créés pour la première fois dans la base de données. Pour plus d'informations sur les ensembles de réglages SQL, voir Saisie de charges de travail dans des ensembles de réglages SQL dans le Guide de réglage SQL pour Oracle Database.
  3. Indiquez la portée de la tâche dans la section Paramètres de la tâche :
    1. Limite de temps total (minutes) : Spécifiez le temps total que SQL Tuning Advisor doit consacrer à l'analyse de l'énoncé.
    2. Portée de l'analyse : Sélectionnez l'option appropriée pour définir la portée de l'analyse.
      • Limitée : SQL Tuning Advisor émet des recommandations basées sur des vérifications statistiques, l'analyse des chemins d'accès et l'analyse de la structure SQL. Les recommandations de profil SQL ne sont pas générées.
      • Complète : SQL Tuning Advisor effectue toutes les analyses dans le cadre de la portée Limitée plus le profilage SQL.
  4. 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.
  5. Cliquez sur Exécuter.
La tâche de réglage SQL est soumise à SQL Tuning Advisor.

Voir les conclusions de SQL Tuning Advisor

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

Après avoir exécuté SQL Tuning Advisor sur des énoncés SQL sélectionnés ou un STS, la tâche de réglage SQL est affichée dans la section Tâches SQL Tuning Advisor. Notez que si SQL Tuning Advisor est configuré pour s'exécuter automatiquement sur la base de données, les tâches de réglage SQL automatiques sont également affichées. Pour chacune des tâches de réglage SQL, vous pouvez cliquer sur l'icône Actions (Actions) et cliquer sur l'option Réexécuter la tâche de réglage SQL pour l'exécuter de nouveau, 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 conclusions et les recommandations de SQL Tuning Advisor sont affichées dans les onglets suivants de la page Détails de la tâche SQL Tuning Advisor :

  • Onglet Sommaire : Afficher un sommaire graphique de la tâche et les conclusions fournies par SQL Tuning Advisor :
    • énoncés SQL distincts examinés : Les énoncés SQL examinés par SQL Tuning Advisor sont regroupés dans les catégories SQL examiné avec des conclusions, SQL ignoré en raison d'erreurs et SQL examiné sans conclusions et affichés dans un graphique torique. Notez que si l'énoncé SQL a été exécuté plusieurs fois, une seule instance d'exécution (distincte) est prise en compte au cours de la période d'analyse.
    • Avantage des recommandations de profil SQL : L'avantage des recommandations de profil SQL en temps de base de données (en secondes) est affiché dans des graphiques à barres. Les graphiques à barres présentent des barres Avant et Après, indiquant le temps de base de données réel et le temps de base de données amélioré, respectivement. Si les recommandations de profil SQL sont fournies mais ne sont pas mises en oeuvre, un seul graphique d'avantages Potentiel s'affiche. Si les recommandations de profil SQL sont mises en oeuvre partiellement, les graphiques d'avantages Potentiel et Mise en oeuvre s'affichent. Si les recommandations de profil SQL sont entièrement mises en oeuvre, seul le graphique d'avantages Mise en oeuvre s'affiche.
    • Conclusions avec des recommandations par type : Les conclusions de SQL Tuning Advisor avec des recommandations sont classées par type et affichées dans un graphique à barres.
  • Onglet Conclusions SQL : Afficher les conclusions et mettre en oeuvre les recommandations de SQL Tuning Advisor. Vous pouvez afficher la liste des énoncés SQL et les informations correspondantes en fonction de l'analyse effectuée par SQL Tuning Advisor. Ces informations présentent les avantages estimatifs en termes de performance et les conclusions avec des recommandations. Vous pouvez sélectionner un seul énoncé SQL pour afficher les recommandations spécifiques pour chaque conclusion, ainsi que l'explication et les avantages attendus si une recommandation est mise en oeuvre. Dans cet onglet, vous pouvez utiliser les options disponibles pour mettre en oeuvre toutes les recommandations de profil SQL, mettre en oeuvre une seule recommandation et comparer les plans d'exécution.
  • onglet SQL sans conclusions : Afficher les énoncés SQL qui ont été ignorés en raison de l'absence de conclusions ou de recommandations, d'erreurs ou de temporisation de la tâche.

Mettre en oeuvre les recommandations de SQL Tuning Advisor

Vous pouvez créer des travaux pour implémenter des recommandations SQL Tuning Advisor.

Pour ce faire, allez à la page Détails de la tâche SQL Tuning Advisor et consultez les conclusions et les recommandations de l'analyse SQL Tuning Advisor dans l'onglet Conclusions SQL. Dans cet onglet, vous pouvez créer un travail permettant d'implémenter toutes les recommandations de profil SQL ou d'implémenter une recommandation de profil, d'index ou de statistiques SQL unique, et de comparer les plans d'exécution. Vous pouvez également afficher les conclusions diverses, de restructuration d'énoncé SQL et de plan de remplacement. En revanche, l'option de mise en oeuvre des recommandations de restructuration d'énoncé SQL et de plan de remplacement n'est pas disponible. Pour plus d'informations sur la restructuration d'énoncé SQL et les plans de remplacement, voir Analyse structurelle SQL et Analyse de plan de remplacement dans le Guide de réglage SQL pour Oracle Database.


Onglet Conclusions SQL

Rubriques :

Mettre en oeuvre 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 Conclusions SQL.
  2. Cliquez sur Mettre en oeuvre toutes les recommandations de profil SQL.
  3. Dans le panneau Mettre en oeuvre toutes les recommandations :
    1. Fournissez les informations suivantes pour créer une tâche afin de mettre en oeuvre les recommandations :
      1. Nom : Entrez un nom unique pour la tâche.
      2. Description : Facultativement, entrez une description de la tâche.
    2. Spécifiez les paramètres de la tâche :
      1. Mettre en oeuvre le nouveau profil avec la correspondance forcée : Sélectionnez cette option pour cibler tous les énoncés SQL qui ont le même texte après avoir normalisé toutes les valeurs littérales en variables de liaison. Notez que si une combinaison de valeurs littérales et de valeurs de liaison est utilisée dans l'énoncé SQL, aucune transformation de liaison n'a lieu.
      2. Définir une catégorie de profil SQL : Sélectionnez cette option pour indiquer la catégorie dans laquelle créer le profil SQL et entrez le nom de la catégorie dans le champ Créer un profil dans la catégorie.
    3. Spécifiez les paramètres de la tâche :
      1. Type de données d'identification : Sélectionnez l'une des options disponibles 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.
      2. Seau pour sortie de tâche : Sélectionnez le seau du service de stockage d'objets Oracle dans lequel la sortie de tâche sera stockée. Le seau du service de stockage d'objets Oracle peut se trouver dans le même compartiment que la tâche ou dans un autre compartiment, et vous devez disposer des autorisations requises du service de stockage d'objets pour Oracle Cloud Infrastructure pour sélectionner le seau. Pour plus d'informations, voir Autorisations supplémentaires requises pour utiliser les diagnostics et la gestion.
    4. Facultativement, cliquez sur Afficher l'énoncé SQL pour voir l'énoncé SQL qui sera exécuté.
    5. Cliquez sur Mettre en oeuvre les recommandations.
Toutes les recommandations de profil SQL seront maintenant mises en oeuvre et vous pouvez surveiller cette tâche dans la section Tâches de la base de données gérée.

Pour mettre en oeuvre une recommandation de profil SQL unique, sélectionnez un seul énoncé SQL, faites défiler l'affichage vers le bas jusqu'à la section Mettre en oeuvre une recommandation pour l'ID SQL : <ID SQL> et cliquez sur l'icône Actions (Actions) dans la rangée Profil SQL, puis cliquez sur Mettre en oeuvre la recommandation.

Pour plus d'informations sur les profils SQL, voir À propos des profils SQL dans le Guide de réglage SQL pour Oracle Database.

Mettre en oeuvre la recommandation d'index

  1. Dans l'onglet Résultats SQL, sélectionnez un seul énoncé SQL, faites glisser vers le bas jusqu'à la section Mettre en oeuvre une recommandation pour l'ID SQL : <ID SQL> et cliquez sur l'icône Actions (Actions) dans la rangée Index, puis cliquez sur Mettre en oeuvre la recommandation.
  2. Dans le panneau Mettre en oeuvre la recommandation :
    1. Fournissez les informations suivantes pour créer une tâche afin de mettre en oeuvre les recommandations :
      1. Nom : Entrez un nom unique pour la tâche.
      2. Description : Facultativement, entrez une description de la tâche.
    2. Dans la section Paramètres de tâche, sélectionnez l'espace-table qui sera utilisé pour mettre en oeuvre la recommandation d'index.
    3. Spécifiez les paramètres de la tâche :
      1. Type de données d'identification : Sélectionnez l'une des options disponibles 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.
      2. Seau pour sortie de tâche : Sélectionnez le seau du service de stockage d'objets Oracle dans lequel la sortie de tâche sera stockée. Le seau du service de stockage d'objets Oracle peut se trouver dans le même compartiment que la tâche ou dans un autre compartiment, et vous devez disposer des autorisations requises du service de stockage d'objets pour Oracle Cloud Infrastructure pour sélectionner le seau. Pour plus d'informations, voir Autorisations supplémentaires requises pour utiliser les diagnostics et la gestion.
    4. Facultativement, cliquez sur Afficher l'énoncé SQL pour voir l'énoncé SQL qui sera exécuté.
    5. Cliquez sur Mettre en oeuvre la recommandation.
La recommandation d'index sera maintenant mise en oeuvre et vous pouvez surveiller cette tâche dans la section Tâches de la base de données gérée.

Mettre en oeuvre la recommandation de statistiques

  1. Dans l'onglet Résultats SQL, sélectionnez un seul énoncé SQL, faites défiler l'affichage jusqu'à la section Mettre en oeuvre une recommandation pour l'ID SQL : <ID SQL> et cliquez sur l'icône Actions (Actions) dans la rangée Statistiques, puis cliquez sur Mettre en oeuvre la recommandation.
  2. Dans le panneau Mettre en oeuvre la recommandation :
    1. Fournissez les informations suivantes pour créer une tâche afin de mettre en oeuvre les recommandations :
      1. Nom : Entrez un nom unique pour la tâche.
      2. Description : Facultativement, entrez une description de la tâche.
    2. Dans la section Paramètres de tâche, vérifiez les statistiques prises en compte pour la mise en oeuvre.
    3. Spécifiez les paramètres de la tâche :
      1. Type de données d'identification : Sélectionnez l'une des options disponibles 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.
      2. Seau pour sortie de tâche : Sélectionnez le seau du service de stockage d'objets Oracle dans lequel la sortie de tâche sera stockée. Le seau du service de stockage d'objets Oracle peut se trouver dans le même compartiment que la tâche ou dans un autre compartiment, et vous devez disposer des autorisations requises du service de stockage d'objets pour Oracle Cloud Infrastructure pour sélectionner le seau. Pour plus d'informations, voir Autorisations supplémentaires requises pour utiliser les diagnostics et la gestion.
    4. Facultativement, cliquez sur Afficher l'énoncé SQL pour voir l'énoncé SQL qui sera exécuté.
    5. Cliquez sur Mettre en oeuvre la recommandation.
La recommandation de statistiques va maintenant être mise en oeuvre et vous pouvez surveiller cette tâche dans la section Tâches de la base de données gérée.

Comparer les plans d'exécution

Le plan d'exécution est un énoncé qui affiche les plans d'exécution choisis par l'optimiseur Oracle pour les énoncés SELECT, UPDATE, INSERT et DELETE. Le plan d'exécution d'un énoncé est la séquence des opérations qu'Oracle effectue pour exécuter cet énoncé.

Pour voir et comparer les plans d'exécution :

  1. Dans l'onglet Résultats SQL, sélectionnez un seul énoncé SQL, faites glisser vers le bas jusqu'à la section Mettre en oeuvre une recommandation pour l'ID SQL : <ID SQL> et cliquez sur l'icône Actions (Actions) dans la rangée Index ou Profil SQL, puis cliquez sur Comparer les plans d'exécution.
  2. Dans la page Comparer les plans d'exécution :
    • Comparez le temps de base de données et le nombre d'E/S du plan d'exécution initial et du nouveau plan d'exécution dans l'onglet Résultats de test de profil. Cette comparaison est fournie dans une vue graphique et permet de comparer des mesures telles que CPU time, Buffer gets et Disk read. Notez que l'onglet Résultats de test de profil ne s'affiche pas si le rapport de comparaison n'est pas généré.
    • Comparez les étapes du plan d'exécution initial et du nouveau plan d'exécution de l'énoncé SQL, dans l'onglet plans d'exécution. Cette comparaison est fournie dans une vue tabulaire. Vous pouvez toutefois sélectionner Plan d'exécution graphique dans la liste déroulante Option de vue pour afficher une représentation graphique du plan d'exécution. Dans la vue Plan d'exécution graphique, cliquez sur Effectuer une rotation ou utilisez la souris et le pavé tactile pour faire défiler le plan d'exécution et voir la séquence des opérations.
  3. Cliquez sur Fermer pour retourner à la page Détails de la tâche SQL Tuning Advisor.