Utiliser le traçage SQL pour une base de données autonome sur une infrastructure Exadata dédiée

Vous pouvez utiliser le traçage SQL avec l'infrastructure Autonomous Database sur une base Exadata dédiée pour vous aider à identifier la source d'une charge de travail de base de données excessive, telle qu'un énoncé SQL à charge élevée dans votre application.

À propos de la trace SQL

Lorsqu'une opération d'application prend plus de temps que prévu, l'obtention d'une trace de tous les énoncés SQL exécutés dans le cadre de cette opération avec des détails tels que le temps passé par cet énoncé SQL dans les phases d'analyse, d'exécution et d'extraction vous aidera à identifier et à résoudre la cause du problème de performance. Pour ce faire, vous pouvez utiliser le traçage SQL sur une base de données Autonomous Database.

Le traçage SQL est désactivé par défaut dans Autonomous Database. Vous devez l'activer pour commencer à collecter les données de trace SQL. Pour effectuer le suivi des instructions SQL, implémentez les tâches suivantes en tant qu'utilisateur ADMIN :

Configurer le traçage SQL dans la base de données autonome

Pour configurer votre base de données Autonomous Database pour le traçage SQL, effectuez les opérations suivantes :
  1. Créez un seau pour stocker les fichiers de trace dans le stockage d'objets en nuage.
    Le seau peut se trouver dans n'importe quel magasin d'objets en nuage pris en charge par la base de données autonome. Par exemple, pour créer un seau dans Oracle Cloud Infrastructure Object Storage, voir Création d'un seau.

    Conseil :

    Veillez à sélectionner Standard comme niveau de stockage lors de la création de votre seau dans Oracle Cloud Infrastructure Object Storage, car les fichiers de suivi SQL ne sont pris en charge que par des seaux créés au niveau de stockage standard. Pour plus d'informations sur le niveau de stockage d'objets standard, voir Présentation des niveaux de stockage.
  2. Créez des données d'identification pour votre compte de stockage d'objets en nuage à 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;
/

    Voir Procédure CREATE_CREDENTIAL pour plus de détails sur les arguments des paramètres username et password pour différents services de stockage d'objets.

  3. Définissez les paramètres d'initialisation pour spécifier l'URL du stockage d'objets en nuage d'un seau pour les fichiers de trace SQL et pour spécifier les données d'identification permettant d'accéder au stockage d'objets en nuage.
    1. Définissez la propriété de base de données DEFAULT_LOGGING_BUCKET pour spécifier le seau de connexion dans le stockage d'objets en nuage.
      Par exemple, si vous créez le seau avec le service de stockage d'objets pour Oracle Cloud Infrastructure (OCI) :
      SET DEFINE OFF;
ALTER DATABASE PROPERTY SET 
   DEFAULT_LOGGING_BUCKET = 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/bucket_name/o/';

      Où namespace-string est l'espace de noms du stockage d'objets OCI et bucket_name est le nom du seau que vous avez créé précédemment. Pour plus d'informations, voir Présentation des espaces de noms du stockage d'objets.

      Voir Régions et domaines de disponibilité pour obtenir la liste des régions.

      Le magasin d'objets en nuage que vous utilisez pour les fichiers de trace SQL peut être n'importe quel magasin d'objets en nuage pris en charge par Autonomous Database.

    2. Affectez à la propriété de base de données DEFAULT_CREDENTIAL les données d'identification que vous avez créées à l'étape 2.
      Par exemple :
      ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.DEF_CRED_NAME';

      L'inclusion du nom de schéma avec les données d'identification est requise. Dans cet exemple, le schéma est ADMIN.

Activer le traçage SQL dans la base de données autonome

Note :

L'activation du traçage SQL peut dégrader les performances de l'application pour la session pendant la collecte. Cet impact sur la performance est attendu en raison de la surcharge liée à la collecte et à l'enregistrement des données de trace.

Pour activer le traçage SQL pour une session de base de données, effectuez les opérations suivantes :

  1. Facultativement, définissez un identificateur de client pour l'application. Cette étape est facultative mais recommandée. Le traçage SQL utilise l'identificateur de client comme composant du nom du fichier de suivi lors de l'écriture de celui-ci dans le magasin d'objets en nuage.
    Par exemple :
    BEGIN
  DBMS_SESSION.SET_IDENTIFIER('sqlt_test');
END;
/
  2. Facultativement, définissez un nom de module pour l'application. Cette étape est facultative mais recommandée. Le traçage SQL utilise le nom de module comme composant du nom du fichier de suivi lors de l'écriture de celui-ci dans le magasin d'objets en nuage.

    Par exemple :

    BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('modname', null);
END;
/
  3. Activez la fonction de trace SQL.
    ALTER SESSION SET SQL_TRACE = TRUE;
  4. Exécutez votre charge de travail.
    Cette étape consiste à exécuter l'ensemble de l'application ou des parties spécifiques de celle-ci. Lorsque vous exécutez votre charge de travail dans la session de base de données, les données de traçage SQL sont collectées.
  5. Désactivez le traçage SQL.
    Lorsque vous désactivez le suivi SQL, les données collectées pour la session sont écrites dans une table de celle-ci et dans un fichier de suivi dans le seau configuré lors de la configuration du suivi SQL.

Désactiver le traçage SQL

Pour désactiver le traçage SQL, effectuez les opérations suivantes :
  1. Désactivez la fonction de trace SQL.
    ALTER SESSION SET SQL_TRACE = FALSE;
  2. Facultativement, selon les besoins de votre environnement, vous pouvez réinitialiser la propriété de base de données DEFAULT_LOGGING_BUCKET pour effacer la valeur du seau de journalisation dans le stockage d'objets en nuage.
    Par exemple :
    ALTER DATABASE PROPERTY SET DEFAULT_LOGGING_BUCKET = '';
Lorsque vous désactivez le traçage SQL, les données de traçage collectées pendant l'exécution de la session avec le traçage activé sont copiées dans une table et envoyées dans un fichier de suivi dans le magasin d'objets en nuage.

Voir le fichier de suivi enregistré dans le magasin d'objets en nuage pour une base de données autonome

Vous utilisez les données du fichier de suivi SQL pour analyser la performance de l'application dans la base de données autonome. Lorsque vous désactivez la trace SQL dans votre session de base de données, les données sont écrites dans le seau du magasin d'objets en nuage configuré avec DEFAULT_LOGGING_BUCKET.

L'outil de trace SQL écrit les données de trace collectées pour la session dans le magasin d'objets en nuage dans le 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. Pour plus d'informations, voir Configurer le traçage SQL dans la base de données autonome.

  • clientID : Identificateur du client. Pour plus d'informations, voir Activer le traçage SQL dans la base de données autonome.

  • moduleName : Nom du module. Pour plus d'informations, voir Activer le traçage SQL dans la base de données autonome.

  • numID1_numID2 : Deux identificateurs fournis par l'outil de trace SQL. Les valeurs numériques numID1 et numID2 distinguent de manière unique chaque nom de fichier de suivi des autres sessions qui utilisent le traçage et créent des fichiers de suivi dans le même seau du stockage d'objets en nuage.

    Lorsque le service de base de données prend en charge le parallélisme et qu'une session exécute une interrogation parallèle, la fonction de trace SQL peut produire plusieurs fichiers de suivi avec des valeurs numID1 et numID2 différentes.

Note :

Lorsque le suivi SQL est activé et désactivé plusieurs fois dans la même session, chaque itération de trace génère un fichier de suivi distinct dans le magasin d'objets Cloud. Pour éviter de remplacer les traces précédentes générées au cours de la session, les fichiers suivants générés respectent la même convention d'attribution de nom et un suffixe numérique est ajouté au nom du fichier de suivi. Ce suffixe numérique commence par le numéro 1 et est incrémenté de 1 pour chaque itération suivante du traçage.

Par exemple, voici un exemple de nom de fichier de suivi généré lorsque vous réglez l'identificateur de client à "sql_test" et le nom de module à "modname" :

sqltrace/sqlt_test/modname/sqltrace_5415_56432.trc

Vous pouvez exécuter TKPROF pour traduire le fichier de suivi en un fichier de sortie lisible.

  1. Copiez le fichier de suivi du magasin d'objets 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 en utilisant 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 voir l'aide en ligne, exécutez TKPROF sans arguments.
    Pour plus d'informations sur l'utilisation de l'utilitaire TKPROF, voir Outils de suivi de bout en bout des applications dans le guide de réglage SQL pour Oracle Database 19c ou le guide de réglage SQL pour Oracle Database 23ai.

Voir les données de trace dans la vue SESSION_CLOUD_TRACE pour une base de données autonome

Lorsque vous activez le traçage SQL, les informations de trace enregistrées dans le fichier de suivi dans le magasin d'objets en nuage sont disponibles dans la vue SESSION_CLOUD_TRACE de la session où le traçage a été activé.
Pendant que vous êtes encore dans la session de base de données, vous pouvez voir les données de trace SQL dans la vue SESSION_CLOUD_TRACE. La vue SESSION_CLOUD_TRACE 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 situées dans la colonne TRACE. Chaque ligne de sortie de traçage écrite dans un fichier de suivi devient une rangée dans la table et est disponible dans la colonne TRACE.

Après avoir désactivé le traçage SQL pour la session, vous pouvez exécuter des interrogations sur la vue SESSION_CLOUD_TRACE.

Par exemple :
SELECT trace FROM SESSION_CLOUD_TRACE ORDERBY row_number;

Les données de SESSION_CLOUD_TRACE sont conservées 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 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 pour toutes les itérations de manière cumulative. Ainsi, la réactivation du traçage dans une session après l'avoir désactivé ne supprime pas les données de trace produites par l'itération précédente.