Utilisation de la fonction de trace SQL sur Autonomous Database on Dedicated Exadata Infrastructure

Vous pouvez utiliser la fonction de trace SQL avec Autonomous Database on Dedicated Exadata Infrastructure pour vous aider à identifier la source d'une charge globale de base de données excessive, telle qu'une instruction SQL à charge élevée dans votre application.

A propos de la fonction de trace SQL

Lorsqu'une opération d'application prend plus de temps que prévu, obtenir une trace de toutes les instructions SQL exécutées dans le cadre de cette opération avec des détails tels que le temps passé par cette instruction SQL dans les phases d'analyse, d'exécution et d'extraction vous aidera à identifier et à résoudre la cause du problème de performances. Pour ce faire, vous pouvez utiliser la fonction de trace SQL sur une instance Autonomous Database.

La fonction de trace SQL est désactivée par défaut dans Autonomous Database. Vous devez l'activer pour commencer à collecter les données de trace SQL. Pour générer le suivi des instructions SQL, implémentez les tâches suivantes en tant qu'utilisateur ADMIN :

Configuration de la fonction de trace SQL sur Autonomous Database

Afin de configurer votre instance Autonomous Database pour la fonction de trace SQL, procédez comme suit :
  1. Créez un bucket pour stocker les fichiers trace dans le stockage Cloud Object Storage.
    Pour enregistrer les fichiers de la fonction de trace SQL, le bucket peut se trouver dans n'importe quel stockage d'objet cloud pris en charge par Autonomous Database. Par exemple, pour créer un bucket dans Oracle Cloud Infrastructure Object Storage, reportez-vous à Création d'un bucket.

    Conseil :

    Veillez à sélectionner Standard comme niveau de stockage lors de la création du bucket dans Oracle Cloud Infrastructure Object Storage, car les fichiers de trace SQL sont uniquement pris en charge avec les buckets créés dans le niveau de stockage standard. Pour plus d'informations sur le niveau Object Storage standard, reportez-vous à Présentation des niveaux de stockage.
  2. Créez des informations d'identification pour votre compte de stockage d'objet cloud à l'aide de DBMS_CLOUD.CREATE_CREDENTIAL.
    Exemple :
    BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username => 'adb_user@example.com', 
    password => 'password'
);END;
/

    Reportez-vous à Procédure CREATE_CREDENTIAL pour plus de détails sur les arguments des paramètres username et password des différents services de stockage d'objet.

  3. Définissez des paramètres d'initialisation afin d'indiquer l'URL Cloud Object Storage d'un bucket pour les fichiers trace SQL et d'indiquer les informations d'identification permettant d'accéder au cloud Object Storage.
    1. Définissez la propriété de base de données DEFAULT_LOGGING_BUCKET pour indiquer le bucket de journalisation sur le stockage d'objet cloud.
      Par exemple, si vous créez le bucket avec Oracle Cloud Infrastructure (OCI) 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/';

      Où namespace-string est l'espace de noms OCI Object Storage et bucket_name est le nom du bucket que vous avez créé précédemment. 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é.

      Le stockage d'objet cloud que vous utilisez pour les fichiers de la fonction de trace SQL peut être n'importe quel stockage d'objet cloud pris en charge par Autonomous Database.

    2. Définissez la propriété de base de données DEFAULT_CREDENTIAL sur les informations d'identification créées à l'étape 2.
      Exemple :
      ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.DEF_CRED_NAME';

      Vous devez inclure le nom de schéma avec les informations d'identification. Dans cet exemple, le schéma est ADMIN.

Activation de la fonction de trace SQL sur Autonomous Database

Remarques :

L'activation de la fonction de trace SQL peut dégrader les performances de l'application pour la session pendant que la collecte de trace est activée. Cette incidence sur les performances est attendue en raison du temps système lié à la collecte et à l'enregistrement des données de trace.

Afin d'activer la trace SQL pour une session de base de données, procédez comme suit :

  1. Vous pouvez éventuellement définir un identificateur client pour l'application. Cette étape est facultative mais recommandée. La trace SQL utilise l'identificateur client comme composant du nom de fichier trace lorsque ce dernier est écrit dans le stockage d'objet cloud.
    Exemple :
    BEGIN
  DBMS_SESSION.SET_IDENTIFIER('sqlt_test');
END;
/
  2. Vous pouvez éventuellement définir un nom de module pour l'application. Cette étape est facultative mais recommandée. La fonction de trace SQL utilise le nom de module comme composant du nom de fichier trace lorsque ce dernier est écrit dans le stockage d'objet cloud.

    Exemple :

    BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('modname', null);
END;
/
  3. Activez la fonction SQL Trace.
    ALTER SESSION SET SQL_TRACE = TRUE;
  4. Exécutez votre charge globale.
    Cette étape implique l'exécution de l'intégralité de l'application ou de certaines parties spécifiques de l'application. Pendant que vous exécutez la charge globale dans la session de base de données, les données de la fonction de trace SQL sont collectées.
  5. Désactiver la fonction de trace SQL.
    Lorsque vous désactivez la trace SQL, les données collectées pour la session sont écrites dans une table de la session et dans un fichier trace du bucket que vous configurez lors de la définition de la trace SQL.

Désactiver la fonction de trace SQL

Pour désactiver la trace SQL, procédez comme suit :
  1. Désactivez la fonction SQL Trace.
    ALTER SESSION SET SQL_TRACE = FALSE;
  2. Si nécessaire pour votre environnement, vous pouvez réinitialiser la propriété de base de données DEFAULT_LOGGING_BUCKET afin d'effacer la valeur du bucket de journalisation sur le stockage d'objet cloud.
    Exemple :
    ALTER DATABASE PROPERTY SET DEFAULT_LOGGING_BUCKET = '';
Lorsque vous désactivez la fonction de trace SQL, les données de trace collectées pendant l'exécution de la session avec la fonction de trace activée sont copiées dans une table et envoyées vers un fichier trace sur le stockage d'objet cloud.

Visualisation du fichier trace enregistré dans le stockage d'objet cloud sur Autonomous Database

Les données du fichier trace SQL permettent d'analyser les performances des applications sur Autonomous Database. Lorsque vous désactivez la trace SQL dans la session de base de données, les données sont écrites dans le bucket de stockage d'objet cloud configuré avec DEFAULT_LOGGING_BUCKET.

La fonction de trace SQL écrit les données de trace collectées dans la session dans le stockage d'objet cloud 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. Pour plus d'informations, reportez-vous à Configuration de la fonction de trace SQL sur Autonomous Database.

  • clientID : identificateur client. Pour plus d'informations, reportez-vous à Activation de la fonction de trace SQL sur Autonomous Database.

  • moduleName : nom du module. Pour plus d'informations, reportez-vous à Activation de la fonction de trace SQL sur Autonomous Database.

  • numID1_numID2 : deux identificateurs fournis par la fonction de trace SQL. Les valeurs numériques numID1 et numID2 différencient de manière unique chaque nom de fichier trace d'autres sessions utilisant la fonction de trace et créant des fichiers trace dans le même bucket de stockage d'objet cloud.

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

Remarques :

Lorsque la 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 dans le stockage d'objet cloud. Pour éviter d'écraser les traces précédentes générées dans la session, les fichiers générés ultérieurement suivent la même convention de dénomination et un suffixe numérique est ajouté au nom de fichier trace. Ce suffixe numérique commence par le nombre 1 et est ensuite incrémenté de 1 pour chaque itération de traçage.

Par exemple, voici un exemple de nom de fichier trace généré lorsque vous définissez l'identificateur client sur "sql_test" et le nom de module sur "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 de la banque 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 à 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 à Outils de trace d'application de bout en bout dans le guide de réglage SQL d'Oracle Database 19c ou le guide de réglage SQL d'Oracle Database 23ai.

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

Lorsque vous activez la fonction de trace SQL, les informations de trace enregistrées dans le fichier trace dans le stockage d'objet cloud sont également disponibles dans la vue SESSION_CLOUD_TRACE de la session où la fonction de trace a été activée.
Pendant que vous êtes encore dans la session de base de données, vous pouvez visualiser les données de la fonction de trace SQL dans la vue SESSION_CLOUD_TRACE. La vue SESSION_CLOUD_TRACE comprend les colonnes ROW_NUMBER et TRACE.
DESC SESSION_CLOUD_TRACE

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

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

Après avoir désactivé la fonction de trace SQL pour la session, vous pouvez exécuter des requêtes sur la vue SESSION_CLOUD_TRACE.

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 de toutes les itérations cumulées. Par conséquent, la réactivation de la fonction de trace dans une session après sa désactivation n'enlève pas les données de trace générées par l'itération précédente.