Autonomous AI DatabaseでのSQLトレースの実行

SQLトレースを使用すると、アプリケーションの高負荷SQL文など、過剰なデータベース・ワークロードのソースを識別しやすくなります。Autonomous AI Databaseでは、ALTER SESSIONを使用したセッションベースのトレースと、DBMS_USERDIAGパッケージを使用したターゲット・トレースがサポートされています。

SQLトレースは、デフォルトで無効になっています。トレース・データを収集するには、トレース・ルールを有効化し、調査するワークロードを実行し、同じ識別属性を使用してルールを無効化します。トレースが無効になっている場合、データベースは収集されたデータをCloud Object Storageにアップロードします。生成されたトレース・ファイルまたはSESSION_CLOUD_TRACEビューでデータを確認できます。

トレース方法によって、データベースがトレースするセッションを識別する方法が決まります。クライアント識別子とサービス、モジュールおよびアクションのトレースでは、データベース・インスタンス間の一致するセッションを識別できますが、セッションベースのトレースでは、特定のインスタンス上の特定のセッションをターゲットとします。クライアント識別子、サービス、モジュールおよびアクション・トレースでは、バインド変数および待機イベント情報がサポートされます。これらのトレース方法では基本的なトレースを使用できないため、バインドまたは待機トレースを明示的に指定しないと、待機トレースがデフォルトで有効になります。セッションベースのトレースでは、基本、バインドおよび待機のトレース・レベルがサポートされます。

Autonomous AI DatabaseでのSQLトレースの構成

SQLトレースを有効にする前に、SQLトレース・ファイルをCloud Object Storageに保存するようにデータベースを構成します。

ノート

ノート: SQLトレースを有効にすると、トレース収集が有効になっている間、セッションのアプリケーションのパフォーマンスが低下する可能性があります。この予想される影響は、トレース・データの収集および保存のオーバーヘッドが原因です。

SQLトレースの前提条件

ALTER SESSIONトレースとDBMS_USERDIAGトレースの両方に、次の構成が必要です。トレース方法を有効にする前に、次のステップを実行します。

SQLトレース用にデータベースを構成するには、次の手順を実行します。

トレース・ファイルをクラウド・オブジェクト・ストレージに格納するバケットを作成します。

SQLトレース・ファイルを保存するには、Autonomous AI Databaseでサポートされている任意のクラウド・オブジェクト・ストレージ・サービスを使用できます。

たとえば、Oracle Cloud Infrastructure Object Storageにバケットを作成するには:
1. Oracle Cloud Infrastructure Consoleを開きます。
2. メニューから「記憶域」を選択します。
3. 「ストレージ」で、「オブジェクト・ストレージおよびアーカイブ・ストレージ」を選択します。
4. 「バケットの作成」をクリックします。
5. 「バケットの作成」ページで、バケット名を入力して「作成」をクリックします。
Oracle Cloud Infrastructure Object Storageを使用する場合、SQLトレース・ファイルは、標準ストレージ層で作成されたバケットでのみサポートされます。「標準」をストレージ層として選択します。標準オブジェクト・ストレージ層については、オブジェクト・ストレージの概要に関する項を参照してください。
DBMS_CLOUD.CREATE_CREDENTIALを使用して、クラウド・オブジェクト・ストレージ・アカウントの資格証明を作成します。

たとえば:
```
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username        => 'adb_user@example.com',
    password        => 'password');
END;
/
```
ノート

ノート:ユーザー・アカウントがデフォルトのアイデンティティ・ドメインにある場合は、ユーザー名のみを指定すれば十分です。ユーザー・アカウントがデフォルト以外のアイデンティティ・ドメインに属している場合は、アイデンティティ・ドメイン名の前にユーザー名を付け、その後に/を続けます。たとえば、identity_domain_name/adb_user@example.comです。

Oracle Cloud Infrastructureの場合、Oracle Cloud Infrastructureユーザー名をusernameに、Oracle Cloud Infrastructure認証トークンをpasswordに指定します。詳細は、認証トークンの操作を参照してください。

使用するクラウド・オブジェクト・ストレージ・サービスのパラメータとその値の詳細は、CREATE_CREDENTIALプロシージャを参照してください。
データベース・プロパティを設定して、SQLトレース・ファイルのクラウド・オブジェクト・ストレージの場所と、そのアクセスに使用される資格証明を指定します。
1. DEFAULT_LOGGING_BUCKETデータベース・プロパティを設定して、ロギング・バケットを指定します。
  
  たとえば、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/';
```
  この例では、namespace-stringはOracle Cloud Infrastructure Object Storageネームスペースで、bucket_nameは作成したバケットの名前です。詳細は、オブジェクト・ストレージ・ネームスペースの理解を参照してください。
  
  リージョンのリストは、リージョンおよび可用性ドメインを参照してください。
2. DEFAULT_CREDENTIALデータベース・プロパティを、ステップ2で作成した資格証明に設定します。スキーマ名を資格証明に含めます。この例では、スキーマはADMINです。
```
ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.DEF_CRED_NAME';
```

DBMS_USERDIAG固有の前提条件

前述の一般的な前提条件は、DBMS_USERDIAGトレースに適用されます。また、このパッケージを使用する前に、次の前提条件を満たしてください。

ADMINユーザーとしてログインしているか、DBMS_USERDIAGに対するEXECUTE権限を持っている必要があります。
セッションベースのトレースの場合は、ターゲットのセッションIDとシリアル番号を取得します。V$SESSIONを問い合せて、これらの値を識別できます。
クライアント識別子トレースの場合、アプリケーションでクライアント識別子(SALES_APPなど)が設定されていることを確認します。
サービス、モジュールおよびアクションのトレースでは、ターゲット・セッションと一致させるために使用されるアプリケーション属性を識別します。
この追加の診断情報が必要な場合にのみ、バインドおよび待機トレースを使用します。これらのオプションは、大規模なトレース・ファイルを生成し、アプリケーションのパフォーマンスに影響を与え、トレース・データの収集中にセッションで使用されるPGAメモリーを増やすことができます。

共通およびDBMS_USERDIAG固有の前提条件を完了した後、診断目標に最も適したトレース方法を選択します。ALTER SESSIONを使用して現在のセッションのアクティビティをトレースするか、DBMS_USERDIAGを使用して特定のワークロードまたはセッション・グループをターゲット指定します。

ALTER SESSIONを使用したセッションのトレース

ALTER SESSIONを使用して、現在のデータベース・セッションのSQLトレースを有効にします。このメソッドは、そのセッションで実行された作業のトレース情報を取得します。

SQLトレースを有効にする前に、SQLトレース・ファイルを保存するようにデータベースを構成します。詳細は、Autonomous AI DatabaseでのSQLトレースの構成を参照してください。

現在のセッションでSQLトレースを有効にするには:

(オプション)アプリケーションのクライアント識別子を設定します。この識別子は、トレース・ファイル名のコンポーネントとして使用されます。

たとえば:
```
BEGIN
  DBMS_SESSION.SET_IDENTIFIER('sqlt_test');
END;
/
```
DBMS_SESSION.SET_IDENTIFIERは、現在のセッションのクライアント識別子を設定します。
(オプション)アプリケーションのモジュール名を設定します。モジュール名は、トレース・ファイル名のコンポーネントとしても使用されます。

たとえば:
```
BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('modname', NULL);
END;
/
```
SQLトレース機能を有効にします。

たとえば:
```
ALTER SESSION SET SQL_TRACE = TRUE;
```
ALTER SESSION SET SQL_TRACE = TRUEは、現在のセッションのトレースを有効にします。
分析するワークロードを実行します。SQLトレース・データは、セッションでのワークロードの実行中に収集されます。
SQLトレースを無効にします。

Autonomous AI DatabaseでのSQLトレースの無効化

ワークロードの終了後にSQLトレースを無効にします。トレースを無効にすると、収集されたデータがセッションおよびSQLトレース用に構成されたクラウド・オブジェクト・ストレージ・バケット内のトレース・ファイルに書き込まれます。

現在のセッションのSQLトレースを無効にするには:

SQLトレース機能を無効にします。

たとえば:
```
ALTER SESSION SET SQL_TRACE = FALSE;
```
ALTER SESSION SET SQL_TRACE = FALSEは、現在のセッションのトレースを無効にします。
(オプション)DEFAULT_LOGGING_BUCKETデータベース・プロパティをリセットして、ロギング・バケット値をクリアします。

たとえば:
```
ALTER DATABASE PROPERTY SET DEFAULT_LOGGING_BUCKET = '';
```
この文は、SQLトレース用に構成されたロギング・バケットをクリアします。

SQLトレースを無効にすると、次のいずれかの方法でトレース・データを表示できます。

クラウド・オブジェクト・ストレージに保存されたトレース・ファイル内のSQLトレース・データを表示および分析します。Autonomous AI Database上のクラウド・オブジェクト・ストレージに保存されたトレース・ファイルの表示を参照してください。
SESSION_CLOUD_TRACEビューでSQLトレース・データを表示および分析します。Autonomous AI DatabaseでのSESSION_CLOUD_TRACEビューのトレース・データを表示を参照してください。

DBMS_USERDIAGを使用したワークロードのトレース

DBMS_USERDIAGパッケージを使用して、特定のSQL文、セッション、クライアント識別子、またはアプリケーション・サービス、モジュールおよびアクションのSQLトレースを有効化および無効化します。このアプローチは、調査中の特定のSQLワークロードを対象とした診断情報を収集し、分析内の無関係なデータベース・アクティビティを最小限に抑えるのに役立ちます。

DBMS_USERDIAG SQLトレースの利点

DBMS_USERDIAGは、次の機能を提供します。

SQL_IDを使用して、特定のSQL文に対するトレースの範囲を指定します。
セッションIDとシリアル番号を指定して、別のアクティブ・セッションをトレースします。
クライアント識別子別にアプリケーション・アクティビティをトレースします。
サービス、モジュールおよびアクション別にアプリケーション・アクティビティをトレースします。
より深い診断が必要な場合は、バインド変数値および待機イベントの詳細を含めます。
調査の必要性に基づいて、最初の実行、すべての実行、または適応的な実行セットの実行計画統計を収集します。

特定のSQL IDに対するSQLトレースの有効化

SQL_IDを使用して、一致するSQL文のみをトレースします。この例では、バインド変数および待機イベント情報が収集され、適応計画統計が有効になります。

sysパラメータはAutonomous AI Databaseでは動作せず、このトピックの例では使用されません。

次に例を示します:

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

sql_idパラメータは、トレースを指定されたSQL文に制限します。bindsおよびwaitsパラメータにはバインド変数および待機イベント情報が含まれ、plan_statにはプラン統計収集オプションを指定します。

SQL ID 5yka4rvxpbjc6を使用するワークロードを実行し、トレースを無効にします。

次に例を示します:

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

sql_idパラメータは、無効にするトレース・ルールを識別します。トレースを有効にするときに指定したものと同じ識別値を使用します。

sql_id値は、トレースする文を識別する必要があります。有効なplan_stat値は、FIRST_EXECUTION、NEVER、ALL_EXECUTIONSおよびADAPTIVEです。

別のセッションでのSQLトレースの有効化

ターゲット・セッションIDとシリアル番号を使用して、別のセッションをトレースします。この例では、39がセッションIDで、15593がシリアル番号です。

次に例を示します:

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

sidおよびserパラメータは、ターゲット・セッションを識別します。bindsおよびwaitsパラメータを使用すると、バインド変数および待機イベント情報の収集が可能になります。

ターゲット・セッションでワークロードを実行し、必要な権限を持つセッションからのトレースを無効にします。

次に例を示します:

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

sidおよびserパラメータは、ターゲット・セッションに関連付けられたトレース・ルールを識別します。

トレースが無効になる前にターゲット・セッションが終了すると、セッションの終了時にトレース・ファイルがアップロードされます。

クライアント識別子によるSQLトレースの有効化

クライアント識別子を使用して、データベース・インスタンス間で一致するセッションをトレースします。この例では、アプリケーションはクライアント識別子をSALES_APPに設定します。

次に例を示します:

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

client_idパラメータは、トレースするアプリケーション・セッションを識別します。bindsおよびwaitsパラメータを使用すると、追加の診断情報を収集できます。

アプリケーション・ワークロードを実行し、同じクライアント識別子を使用してトレースを無効にします。

次に例を示します:

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

client_idパラメータは、無効にするトレース・ルールを識別します。トレースを有効にするときに指定したものと同じクライアント識別子を使用します。

サービス、モジュールおよびアクションによるSQLトレースの有効化

アプリケーション属性を使用して、指定したサービス、モジュールおよびアクションを使用するセッションをトレースします。この例では、トレースはHIGHサービス、ORDER_PROCESSINGモジュールおよびCREATE_ORDERアクションに制限されています。

次に例を示します:

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

service、moduleおよびactionパラメータは、トレースするアプリケーション・セッションを識別します。bindsおよびwaitsパラメータを使用すると、追加の診断情報を収集できます。

アプリケーション・ワークロードを実行し、同じサービス、モジュールおよびアクションの値を使用してトレースを無効にします。

次に例を示します:

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

service、moduleおよびactionパラメータは、無効にするトレース・ルールを識別します。トレースを有効にするときに指定した値と同じ値を使用します。

アプリケーションは、そのセッションのサービス、モジュールおよびアクションの属性を設定する必要があります。DBMS_APPLICATION_INFO.SET_MODULEを使用して、モジュールおよびアクションを設定できます。アプリケーション属性が同じ場合は、データベース・インスタンス間で照合が実行されます。

詳細は、次を参照してください:

SQLトレースの検証

次のビューを問い合せて、トレースが有効であることを確認し、トレース・ルールを確認できます。

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

SELECT *
FROM   DBA_ENABLED_TRACES;

DBA_ENABLED_TRACESのTRACE_TYPE列は、トレース・メソッド(SQL ID、セッション、クライアント識別子、サービス、モジュール、アクションなど)を識別します。

Autonomous AI Databaseのクラウド・オブジェクト・ストレージに保存されたトレース・ファイルの表示

SQLトレース・ファイルの出力ファイル・ネーミングについて説明し、TKPROFを使用してトレース・ファイル・データを編成および表示するコマンドを示します。

SQLトレースを無効にすると、セッションで収集されたトレース・データは、DEFAULT_LOGGING_BUCKETで構成されたクラウド・オブジェクト・ストレージ・バケットに書き込まれます。

SQLトレース機能は、セッションで収集されたトレース・データを次の形式でクラウド・オブジェクト・ストレージに書き込みます。

default_logging_bucket/sqltrace/clientID/moduleName/sqltrace_numID1_numID2.trc

ファイル名のコンポーネントは次のとおりです。

default_logging_bucket: DEFAULT_LOGGING_BUCKETデータベース・プロパティの値。Autonomous AI DatabaseでのSQLトレースの構成を参照してください。
clientID: クライアント識別子。「ALTER SESSIONを使用したセッションのトレース」を参照してください。
moduleName: モジュール名。「ALTER SESSIONを使用したセッションのトレース」を参照してください。
numID1_numID2: トレース・ファイルと、同じバケットを使用して他のセッションによって生成されたトレース・ファイルを一意に区別する2つの識別子。セッションでパラレル問合せを実行すると、SQLトレース機能により、異なる値を持つ複数のトレース・ファイルを生成できます。

ノート

ノート: SQLトレースを同じセッションで複数回有効にして無効にすると、各トレースの反復によって個別のトレース・ファイルが生成されます。後続のファイルには、以前のトレース・ファイルを上書きしないように、数字の接尾辞が含まれています。接尾辞は1から始まり、後続のトレース反復ごとに増分されます。

たとえば、クライアント識別子がsqlt_testでモジュール名がmodnameの場合に生成されるトレース・ファイル名の例を次に示します。

sqltrace/sqlt_test/modname/sqltrace_5415_56432.trc

TKPROFを実行して、トレース・ファイルを読取り可能な出力ファイルに変換できます。

トレース・ファイルをObject Storageからローカル・システムにコピーします。
トレース・ファイルの保存先ディレクトリに移動します。
次の構文を使用して、オペレーティング・システムのプロンプトでTKPROFユーティリティを実行します。
```
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]
```
必要な引数は、入力ファイルと出力ファイルのみです。
オンライン・ヘルプを表示するには、引数なしでTKPROFを起動します。

TKPROFユーティリティの使用の詳細は、『Oracle AI Database SQLチューニング・ガイド』の「エンドツーエンド・アプリケーションのトレース用ツール」を参照してください。

Autonomous AI DatabaseのSESSION_CLOUD_TRACEビューでのトレース・データを表示

SQLトレースを有効にすると、クラウド・オブジェクト・ストレージのトレース・ファイルに保存されたものと同じトレース情報が、トレースを有効にしたセッションのSESSION_CLOUD_TRACEビューに表示されます。

データベース・セッション中に、SQLトレース・データをSESSION_CLOUD_TRACEビューで表示できます。このビューには、ROW_NUMBERとTRACEの2つの列が含まれています。

DESC SESSION_CLOUD_TRACE

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

ROW_NUMBERは、TRACE列のトレース・データの順序を指定します。トレース・ファイルに書き込まれるトレース出力の各行は、ビュー内の行になり、TRACE列で使用できます。

SQLトレースを無効にした後、SESSION_CLOUD_TRACEビューを問い合せます。次に例を示します:

SELECT trace
FROM   SESSION_CLOUD_TRACE
ORDER BY row_number;

SESSION_CLOUD_TRACEのデータは、セッション中に保持されます。ログアウトまたはセッションをクローズすると、データは使用できなくなります。

SQLトレースが有効で、同じセッションで複数回無効になっている場合、SESSION_CLOUD_TRACEはすべての反復のトレース・データを累積的に表示します。以前に無効にした後のセッションでトレースを再度有効にしても、以前の反復によって生成されたトレース・データは削除されません。

Oracle Cloud Infrastructureドキュメント

Autonomous AI DatabaseでのSQLトレースの実行

Autonomous AI DatabaseでのSQLトレースの構成

SQLトレースの前提条件

DBMS_USERDIAG固有の前提条件

ALTER SESSIONを使用したセッションのトレース

Autonomous AI DatabaseでのSQLトレースの無効化

DBMS_USERDIAGを使用したワークロードのトレース

DBMS_USERDIAG SQLトレースの利点

特定のSQL IDに対するSQLトレースの有効化

別のセッションでのSQLトレースの有効化

クライアント識別子によるSQLトレースの有効化

サービス、モジュールおよびアクションによるSQLトレースの有効化

SQLトレースの検証

Autonomous AI Databaseのクラウド・オブジェクト・ストレージに保存されたトレース・ファイルの表示

Autonomous AI DatabaseのSESSION_CLOUD_TRACEビューでのトレース・データを表示