Effectuer une trace SQL sur une base de données Autonomous AI

Utilisez la fonction de trace SQL pour identifier la source d'une charge globale de base de données excessive, telle qu'une instruction SQL à forte consommation de ressources dans votre application. Autonomous AI Database prend en charge la trace basée sur les sessions avec ALTER SESSION et la trace ciblée avec le package DBMS_USERDIAG.

La fonction de trace SQL est désactivée par défaut. Pour collecter des données de trace, activez une règle de trace, exécutez la charge globale à examiner, puis désactivez la règle à l'aide des mêmes attributs d'identification. Lorsque la fonction de trace est désactivée, la base de données télécharge les données collectées vers Cloud Object Storage. Vous pouvez vérifier les données dans le fichier trace généré ou dans la vue SESSION_CLOUD_TRACE.

La méthode de trace détermine la manière dont la base de données identifie les sessions à tracer. La fonction de trace de service, de module et d'action du client peut identifier les sessions correspondantes dans les instances de base de données, tandis que la fonction de trace basée sur les sessions cible une session spécifique sur une instance spécifique. L'identificateur client et les informations de service, de module et de suivi des actions prennent en charge les variables attachées et les événements Wait. Etant donné que la fonction de trace de base n'est pas disponible pour ces méthodes de trace, la fonction de trace d'attente est activée par défaut lorsque vous n'indiquez pas explicitement la fonction de trace de liaison ou d'attente. La fonction de trace basée sur les sessions prend en charge les niveaux de trace de base, de liaison et d'attente.

Configurer la fonction de trace SQL sur la base de données Autonomous AI

Configurez la base de données pour enregistrer les fichiers trace SQL dans Cloud Object Storage avant d'activer la fonction de trace SQL.

Remarque

Remarque : si vous activez la fonction de trace SQL, les performances de l'application pour la session risquent d'être dégradées lorsque la collecte de trace est activée. L'impact attendu est dû à la surcharge liée à la collecte et à l'enregistrement des données de trace.

Prérequis pour la fonction de trace SQL

La configuration suivante est requise pour la trace ALTER SESSION et la trace DBMS_USERDIAG. Effectuez ces étapes avant d'activer l'une ou l'autre des méthodes de trace.

Pour configurer la base de données pour la fonction de trace SQL, procédez comme suit :

1. Créez un bucket pour stocker les fichiers trace dans Cloud Object Storage.

   Pour enregistrer des fichiers trace SQL, vous pouvez utiliser n'importe quel service Cloud Object Storage pris en charge par Autonomous AI Database.

   Par exemple, pour créer un bucket dans Oracle Cloud Infrastructure Object Storage, procédez comme suit :

   1. Ouvrez la console Oracle Cloud Infrastructure.

   2. Sélectionnez Stockage dans le menu.

   3. Sous Stockage, sélectionnez Object Storage et Archive Storage.

   4. Cliquez sur Créer un bucket.

   5. Sur la page Créer un bucket, entrez le nom du bucket et cliquez sur Créer.

   Si vous utilisez Oracle Cloud Infrastructure Object Storage, les fichiers de trace SQL sont pris en charge uniquement dans les buckets créés dans le niveau de stockage standard. Sélectionnez Standard comme niveau de stockage. Pour plus d'informations sur le niveau Object Storage standard, reportez-vous à Présentation d'Object Storage.

2. Créez des informations d'identification pour votre compte Cloud Object Storage à l'aide de DBMS_CLOUD.CREATE_CREDENTIAL.

   Par exemple :

   BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL(
       credential_name => 'DEF_CRED_NAME',
       username        => 'adb_user@example.com',
       password        => 'password');
   END;
   /

   Remarque
   
   Remarque : si votre compte utilisateur se trouve dans le domaine d'identité par défaut, la spécification du seul nom utilisateur suffit. Si votre compte utilisateur appartient à un domaine d'identité autre que celui par défaut, ajoutez un préfixe au nom de domaine d'identité suivi de /. Par exemple, identity_domain_name/adb_user@example.com.

   Pour Oracle Cloud Infrastructure, indiquez le nom utilisateur Oracle Cloud Infrastructure en tant que username et le jeton d'authentification Oracle Cloud Infrastructure en tant que password. Pour plus d'informations, reportez-vous à Utilisation des jetons d'authentification.

   Pour plus d'informations sur les paramètres et leurs valeurs du service Cloud Object Storage que vous utilisez, reportez-vous à Procédure CREATE_CREDENTIAL.

3. Définissez les propriétés de base de données afin d'indiquer l'emplacement Cloud Object Storage pour les fichiers trace SQL et les informations d'identification utilisées pour y accéder.

   1. Définissez la propriété de base de données DEFAULT_LOGGING_BUCKET pour indiquer le bucket de journalisation.

      Par exemple, si vous avez créé le bucket dans Oracle Cloud Infrastructure Object Storage :

      SET DEFINE OFF;
      ALTER DATABASE PROPERTY SET
         DEFAULT_LOGGING_BUCKET = 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/bucket_name/o/';

      Dans cet exemple, namespace-string est l'espace de noms Oracle Cloud Infrastructure Object Storage et bucket_name est le nom du bucket que vous avez créé. Pour plus d'informations, reportez-vous à Présentation des espaces de noms Object Storage.

      Pour obtenir la liste des régions, reportez-vous à Régions et domaines de disponibilité.

   2. Définissez la propriété de base de données DEFAULT_CREDENTIAL sur les informations d'identification que vous avez créées à l'étape 2. Incluez le nom du schéma avec les informations d'identification. Dans cet exemple, le schéma est ADMIN.

      ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.DEF_CRED_NAME';

DBMS_USERDIAG-Prérequis propres

Les prérequis courants ci-dessus s'appliquent à la trace DBMS_USERDIAG. Avant d'utiliser ce package, vous devez également remplir les prérequis suivants :

• Vous devez être connecté en tant qu'utilisateur ADMIN ou disposer du privilège EXECUTE sur DBMS_USERDIAG.
• Pour la fonction de trace basée sur une session, obtenez l'ID et le numéro de série de la session cible. Vous pouvez interroger V$SESSION pour identifier ces valeurs.
• Pour la fonction de trace d'identificateur client, assurez-vous que l'application définit l'identificateur client, par exemple SALES_APP.
• Pour la fonction de trace de service, de module et d'action, identifiez les attributs d'application utilisés pour correspondre aux sessions cible.
• Utilisez la fonction de trace attachée et d'attente uniquement lorsque vous avez besoin de ces informations de diagnostic supplémentaires. Ces options peuvent générer des fichiers trace volumineux, affecter les performances des applications et augmenter la mémoire PGA utilisée par la session lors de la collecte des données de trace.

Une fois que vous avez terminé les prérequis communs et spécifiques à DBMS_USERDIAG, choisissez la méthode de trace qui correspond le mieux à votre objectif de diagnostic. Utilisez ALTER SESSION pour suivre l'activité dans la session en cours ou DBMS_USERDIAG pour cibler une charge globale ou un groupe de sessions spécifique.

Sessions trace avec ALTER SESSION

Utilisez ALTER SESSION pour activer la fonction de trace SQL pour la session de base de données en cours. Cette méthode capture les informations de trace pour le travail effectué au cours de cette session.

Avant d'activer la fonction de trace SQL, configurez la base de données pour enregistrer les fichiers trace SQL. Pour plus d'informations, reportez-vous à Configuration de la trace SQL sur la base de données Autonomous AI.

Pour activer la fonction de trace SQL pour la session en cours :

1. (Facultatif) Définissez un identificateur client pour l'application. Cet identificateur est utilisé comme composant du nom du fichier trace.

   Par exemple :

   BEGIN
     DBMS_SESSION.SET_IDENTIFIER('sqlt_test');
   END;
   /

   DBMS_SESSION.SET_IDENTIFIER définit l'identificateur client de la session en cours.

2. (Facultatif) Définissez un nom de module pour l'application. Le nom du module est également utilisé comme composant du nom du fichier trace.

   Par exemple :

   BEGIN
     DBMS_APPLICATION_INFO.SET_MODULE('modname', NULL);
   END;
   /

3. Activez la fonction de trace SQL.

   Par exemple :

   ALTER SESSION SET SQL_TRACE = TRUE;

   ALTER SESSION SET SQL_TRACE = TRUE active la trace pour la session en cours.

4. Exécutez la charge globale à analyser. Les données de trace SQL sont collectées pendant l'exécution de la charge globale dans la session.

5. Désactivez la fonction de trace SQL.

Désactivation de la fonction de trace SQL sur la base de données Autonomous AI

Désactivez la fonction de trace SQL une fois la charge globale terminée. La désactivation de la fonction de trace écrit les données collectées dans la session et dans un fichier trace dans le bucket Cloud Object Storage configuré pour la fonction de trace SQL.

Pour désactiver la fonction de trace SQL pour la session en cours :

1. Désactivez la fonction de trace SQL.

   Par exemple :

   ALTER SESSION SET SQL_TRACE = FALSE;

   ALTER SESSION SET SQL_TRACE = FALSE désactive la trace pour la session en cours.

2. (Facultatif) Réinitialisez la propriété de base de données DEFAULT_LOGGING_BUCKET pour effacer la valeur du bucket de journalisation.

   Par exemple :

   ALTER DATABASE PROPERTY SET DEFAULT_LOGGING_BUCKET = '';

   Cette instruction efface le bucket de journalisation configuré pour la trace SQL.

Une fois la fonction de trace SQL désactivée, vous pouvez afficher les données de trace de l'une des manières suivantes :

• Affichez et analysez les données de trace SQL dans le fichier trace enregistré dans Cloud Object Storage. Reportez-vous à Affichage du fichier trace enregistré dans le stockage d'objets cloud sur la base de données Autonomous AI.
• Affichez et analysez les données de trace SQL dans la vue SESSION_CLOUD_TRACE. Reportez-vous à Visualisation des données de trace dans la vue SESSION_CLOUD_TRACE sur la base de données Autonomous AI.

Trace des charges de travail avec DBMS_USERDIAG

Utilisez le package DBMS_USERDIAG pour activer et désactiver la fonction de trace SQL pour une instruction SQL, une session, un identificateur client ou un service applicatif, un module et une action spécifiques. Cette approche vous aide à collecter des informations de diagnostic ciblées pour la charge globale SQL concernée, tout en minimisant l'activité de base de données non liée dans l'analyse.

Avantages de la fonction de trace SQL DBMS_USERDIAG

DBMS_USERDIAG offre les fonctionnalités suivantes :

• Portée de la fonction de trace vers une instruction SQL spécifique à l'aide de SQL_ID.
• Tracez une autre session active en indiquant son ID de session et son numéro de série.
• Suivre l'activité de l'application par identificateur client.
• Tracez l'activité de l'application par service, module et action.
• Incluez des valeurs de variable attachée et des détails d'événement Wait lorsque des diagnostics plus approfondis sont requis.
• Collectez des statistiques de plan d'exécution pour la première exécution, chaque exécution ou un ensemble adaptatif d'exécutions en fonction des besoins de l'enquête.

Activer la fonction de trace SQL pour un ID SQL spécifique

Utilisez une valeur SQL_ID pour tracer uniquement l'instruction SQL correspondante. Dans cet exemple, les informations relatives aux variables attachées et aux événements Wait sont collectées et les statistiques relatives aux plans adaptatifs sont activées.

Le paramètre sys n'est pas opérationnel dans la base de données Autonomous AI et n'est pas utilisé dans les exemples de cette rubrique.

Par exemple :

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_ENABLE(
    sql_id    => '5yka4rvxpbjc6',
    binds     => 1,
    waits     => 1,
    plan_stat => 'ADAPTIVE');
END;
/

Le paramètre sql_id limite la trace à l'instruction SQL indiquée. Les paramètres binds et waits incluent les informations relatives aux variables attachées et aux événements Wait, et plan_stat indique l'option de collecte des statistiques de plan.

Exécutez la charge globale qui utilise l'ID SQL 5yka4rvxpbjc6, puis désactivez la trace :

Par exemple :

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_DISABLE(
    sql_id => '5yka4rvxpbjc6');
END;
/

Le paramètre sql_id identifie la règle de trace à désactiver. Utilisez la même valeur d'identification que celle indiquée lors de l'activation de la fonction de trace.

La valeur sql_id doit identifier l'instruction à suivre. Les valeurs plan_stat valides sont FIRST_EXECUTION, NEVER, ALL_EXECUTIONS et ADAPTIVE.

Activer la fonction de trace SQL pour une autre session

Utilisez l'ID et le numéro de série de la session cible pour tracer une autre session. Dans cet exemple, 39 est l'ID de session et 15593 est le numéro de série.

Par exemple :

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_ENABLE(
    sid   => 39,
    ser   => 15593,
    binds => 1,
    waits => 1);
END;
/

Les paramètres sid et ser identifient la session cible. Les paramètres binds et waits permettent de collecter des informations sur les variables attachées et les événements Wait.

Exécutez la charge globale dans la session cible, puis désactivez la fonction de trace à partir d'une session disposant des privilèges requis :

Par exemple :

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_DISABLE(
    sid => 39,
    ser => 15593);
END;
/

Les paramètres sid et ser identifient la règle de trace associée à la session cible.

Si la session cible se ferme avant que la fonction de trace ne soit désactivée, la base de données télécharge le fichier trace lorsque la session se termine.

Activer la fonction de trace SQL par identificateur client

Utilisez un identificateur client pour tracer les sessions correspondantes entre les instances de base de données. Dans cet exemple, l'application définit l'identificateur client sur SALES_APP.

Par exemple :

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_ENABLE(
    client_id => 'SALES_APP',
    binds     => 1,
    waits     => 1);
END;
/

Le paramètre client_id identifie les sessions d'application à suivre. Les paramètres binds et waits permettent de collecter des informations de diagnostic supplémentaires.

Exécutez la charge globale de l'application, puis désactivez la fonction de trace en utilisant le même identificateur client :

Par exemple :

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_DISABLE(
    client_id => 'SALES_APP');
END;
/

Le paramètre client_id identifie la règle de trace à désactiver. Utilisez le même identificateur client que celui que vous avez spécifié lors de l'activation de la fonction de trace.

Activer la fonction de trace SQL par service, module et action

Utilisez les attributs d'application pour tracer les sessions qui utilisent un service, un module et une action spécifiés. Dans cet exemple, la fonction de trace est limitée au service HIGH, au module ORDER_PROCESSING et à l'action CREATE_ORDER.

Par exemple :

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_ENABLE(
    service => 'HIGH',
    module  => 'ORDER_PROCESSING',
    action  => 'CREATE_ORDER',
    binds   => 1,
    waits   => 1);
END;
/

Les paramètres service, module et action identifient les sessions d'application à suivre. Les paramètres binds et waits permettent de collecter des informations de diagnostic supplémentaires.

Exécutez la charge globale de l'application, puis désactivez la fonction de trace en utilisant les mêmes valeurs de service, de module et d'action :

Par exemple :

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_DISABLE(
    service => 'HIGH',
    module  => 'ORDER_PROCESSING',
    action  => 'CREATE_ORDER');
END;
/

Les paramètres service, module et action identifient la règle de trace à désactiver. Utilisez les mêmes valeurs que celles que vous avez indiquées lors de l'activation de la fonction de trace.

L'application doit définir les attributs de service, de module et d'action pour ses sessions. Vous pouvez utiliser DBMS_APPLICATION_INFO.SET_MODULE pour définir le module et l'action. La mise en correspondance est effectuée entre les instances de base de données lorsque les attributs d'application sont identiques.

Pour plus d'informations, reportez-vous à :

• Procédure SESSION_SQL_TRACE_ENABLE
• Procédure SESSION_SQL_TRACE_DISABLE

Vérifier la trace SQL

Vous pouvez interroger les vues suivantes pour vérifier que la fonction de trace est activée et pour vérifier les règles de trace :

SELECT sid, serial#, sql_trace
FROM   V$SESSION
WHERE  sid = 39;

SELECT *
FROM   DBA_ENABLED_TRACES;

La colonne TRACE_TYPE dans DBA_ENABLED_TRACES identifie la méthode de trace, telle que l'ID SQL, la session, l'identificateur client ou le service, le module et l'action.

Afficher le fichier trace enregistré dans Cloud Object Storage sur la base de données Autonomous AI

Décrit le nom des fichiers de sortie pour les fichiers trace SQL et affiche les commandes permettant d'utiliser TKPROF pour organiser et afficher les données des fichiers trace.

Lorsque vous désactivez la fonction de trace SQL, les données de trace collectées dans la session sont écrites dans le bucket Cloud Object Storage configuré avec DEFAULT_LOGGING_BUCKET.

La fonction SQL Trace écrit les données de trace collectées dans la session dans Cloud Object Storage au format suivant :

default_logging_bucket/sqltrace/clientID/moduleName/sqltrace_numID1_numID2.trc

Les composants du nom de fichier sont les suivants :

• default_logging_bucket : valeur de la propriété de base de données DEFAULT_LOGGING_BUCKET. Reportez-vous à Configuration de la fonction de trace SQL sur Autonomous AI Database.
• clientID : identificateur client. Reportez-vous à Suivi des sessions avec ALTER SESSION.
• moduleName : nom du module. Reportez-vous à Suivi des sessions avec ALTER SESSION.
• numID1_numID2 : deux identificateurs qui distinguent de manière unique le fichier trace des fichiers trace générés par d'autres sessions utilisant le même bucket. Lorsqu'une session exécute une interrogation en parallèle, la fonction SQL Trace peut générer plusieurs fichiers trace avec des valeurs différentes.

Remarque

Remarque : lorsque la fonction de trace SQL est activée et désactivée plusieurs fois dans la même session, chaque itération de trace génère un fichier trace distinct. Les fichiers suivants incluent un suffixe numérique pour éviter d'écraser les fichiers trace précédents. Le suffixe commence à 1 et est incrémenté pour chaque itération de trace suivante.

Par exemple, voici un exemple de nom de fichier trace généré lorsque l'identificateur du client est sqlt_test et que le nom du module est modname :

sqltrace/sqlt_test/modname/sqltrace_5415_56432.trc

Vous pouvez exécuter TKPROF pour convertir le fichier trace en fichier de sortie lisible :

1. Copiez le fichier trace d'Object Storage vers votre système local.

2. Accédez au répertoire dans lequel le fichier trace est enregistré.

3. Exécutez l'utilitaire TKPROF à partir de l'invite du système d'exploitation à l'aide de la syntaxe suivante :

   tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n]
       [aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table]
       [explain=user/password] [record=filename4] [width=n]

   Les fichiers d'entrée et de sortie sont les seuls arguments requis.

4. Pour afficher l'aide en ligne, appelez TKPROF sans arguments.

Pour plus d'informations sur l'utilisation de l'utilitaire TKPROF, reportez-vous à la section "Outils de trace d'application de bout en bout" du Guide de réglage SQL d'Oracle AI Database.

Visualisation des données de trace dans la vue SESSION_CLOUD_TRACE sur la base de données Autonomous AI

Lorsque vous activez la fonction de trace SQL, les mêmes informations de trace enregistrées dans le fichier trace Cloud Object Storage sont disponibles dans la vue SESSION_CLOUD_TRACE de la session dans laquelle la fonction de trace a été activée.

Tant que vous êtes dans la session de base de données, vous pouvez visualiser les données de trace SQL dans la vue SESSION_CLOUD_TRACE. La vue comprend deux colonnes : ROW_NUMBER et TRACE.

DESC SESSION_CLOUD_TRACE

Name       Null? Type
---------- ----- ------------------------------
ROW_NUMBER       NUMBER
TRACE            VARCHAR2(32767)

ROW_NUMBER indique l'ordre des données de trace dans la colonne TRACE. Chaque ligne de sortie de trace écrite dans un fichier trace devient une ligne de la vue et est disponible dans la colonne TRACE.

Une fois la fonction de trace SQL désactivée, interrogez la vue SESSION_CLOUD_TRACE. Par exemple :

SELECT trace
FROM   SESSION_CLOUD_TRACE
ORDER BY row_number;

Les données dans SESSION_CLOUD_TRACE persistent pendant toute la durée de la session. Une fois la session déconnectée ou fermée, les données ne sont plus disponibles.

Si la fonction de trace SQL est activée et désactivée plusieurs fois dans la même session, SESSION_CLOUD_TRACE affiche les données de trace de toutes les itérations de manière cumulée. La réactivation de la fonction de trace dans une session après sa désactivation ne supprime pas les données de trace générées par une itération antérieure.