Oracle Cloud Infrastructureドキュメント

専用Exadataインフラストラクチャ上のAutonomous DatabaseでのSQLトレースの使用

Autonomous Database on Dedicated Exadata InfrastructureでSQLトレースを使用すると、アプリケーションの負荷の高いSQL文など、過剰なデータベース・ワークロードのソースを識別しやすくなります。

ノート

SQLトレースは、Autonomous Databaseバージョン19.18以降でのみ使用できます。

SQLトレースについて

アプリケーション操作に予想以上に時間がかかる場合、解析、実行およびフェッチ・フェーズでそのSQL文に費やされた時間などの詳細とともに、この操作の一部として実行されたすべてのSQL文のトレースを取得すると、パフォーマンス問題の原因を特定して解決するのに役立ちます。Autonomous DatabaseでSQLトレースを使用してこれを実現できます。

Autonomous Databaseでは、SQLトレースはデフォルトで無効になっています。SQLトレース・データの収集を開始するには、これを有効にする必要があります。SQL文をトレースするには、ADMINユーザーとして次のタスクを実装します。
  • SQLトレース・ファイルを保存するためのデータベースの構成から開始します。詳細は、Autonomous DatabaseでのSQLトレースの構成を参照してください。
  • 次に、SQLトレースを有効にします。Autonomous DatabaseでのSQLトレースの有効化を参照してください。
    ノート

    SQLトレースを有効にすると、トレース収集が有効になっている間、セッションのアプリケーション・パフォーマンスが低下する可能性があります。このパフォーマンスへの影響は、トレース・データの収集と保存のオーバーヘッドが原因となっています。
  • SQLトレース・データの収集を停止するには、SQLトレースを無効にする必要があります。SQLトレースの無効化を参照してください。
  • SQLトレースを無効にすると、トレースを有効にしたセッションの実行中に収集されたトレース・データが、セッション内のSESSION_CLOUD_TRACEビューと、SQLトレースの設定中に構成したバケット内のトレース・ファイルに書き込まれます。トレース・データを表示するには、2つのオプションがあります:

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

Autonomous DatabaseをSQLトレース用に構成するには、次を実行します:
  1. トレース・ファイルをクラウド・オブジェクト・ストレージに格納するためのバケットを作成します。
    SQLトレース・ファイルを保存するには、Autonomous Databaseでサポートされているどのクラウド・オブジェクト・ストアにもバケットを配置します。たとえば、Oracle Cloud Infrastructure Object Storageにバケットを作成するには、「バケットの作成」を参照してください。

    ヒント:

    Oracle Cloud Infrastructure Object Storageでバケットを作成する場合は、必ず「標準」をストレージ層として選択してください。SQLトレース・ファイルは、標準ストレージ層で作成されたバケットでのみサポートされるためです。標準オブジェクト・ストレージ層の詳細は、ストレージ層の理解を参照してください。
  2. DBMS_CLOUD.CREATE_CREDENTIALを使用して、クラウド・オブジェクト・ストレージ・アカウントの資格証明を作成します。
    例:
    BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username => 'adb_user@example.com', 
    password => 'password'
);END;
/

    様々なオブジェクト・ストレージ・サービスのusernameおよびpasswordパラメータの引数の詳細は、CREATE_CREDENTIALプロシージャを参照してください。

  3. 初期化パラメータを設定して、SQLトレース・ファイルのバケットのクラウド・オブジェクト・ストレージURLを指定し、Cloud Object Storageにアクセスするための資格証明を指定します。
    1. データベース・プロパティのDEFAULT_LOGGING_BUCKETを設定して、クラウド・オブジェクト・ストレージのロギング・バケットを指定します。
      たとえば、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/';

      ここで、namespace-stringはOCIオブジェクト・ストレージ・ネームスペースで、bucket_nameは前に作成したバケットの名前です。詳細は、オブジェクト・ストレージ・ネームスペースの理解を参照してください。

      リージョンの詳細は、リージョンおよび可用性ドメインを参照してください。

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

    2. データベース・プロパティのDEFAULT_CREDENTIALに、ステップ2で作成した資格証明を設定します。
      例:
      ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.DEF_CRED_NAME';

      資格証明にスキーマ名を含める必要があります。この例では、スキーマはADMINです。

Autonomous DatabaseでのSQLトレースの有効化

ノート

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

データベース・セッションでSQLトレースを有効にするには、次を実行します:

  1. オプションで、アプリケーションのクライアント識別子を設定します。このステップはオプションですが、お薦めします。SQLトレースでは、トレース・ファイルがクラウド・オブジェクト・ストアに書き込まれるときに、クライアント識別子がトレース・ファイル名のコンポーネントとして使用されます。
    例:
    BEGIN
  DBMS_SESSION.SET_IDENTIFIER('sqlt_test');
END;
/
  2. オプションで、アプリケーションのモジュール名を設定します。このステップはオプションですが、お薦めします。SQLトレースでは、トレース・ファイルがクラウド・オブジェクト・ストアに書き込まれるときに、モジュール名がトレース・ファイル名のコンポーネントとして使用されます。

    例:

    BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('modname', null);
END;
/
  3. SQLトレース機能を有効にします。
    ALTER SESSION SET SQL_TRACE = TRUE;
  4. ワークロードを実行します。
    このステップには、アプリケーション全体の実行またはアプリケーションの特定の部分の実行が含まれます。データベース・セッションでのワークロードの実行中に、SQLトレース・データが収集されます。
  5. SQLトレース無効化
    SQLトレースを無効にすると、セッションで収集されたデータがセッションの表と、SQLトレースの設定時に構成したバケット内のトレース・ファイルに書き込まれます。

SQLトレース無効化

SQLトレースを無効にするには、次を実行します:
  1. SQLトレース機能を無効にします。
    ALTER SESSION SET SQL_TRACE = FALSE;
  2. 必要に応じて、環境の必要に応じて、データベース・プロパティのDEFAULT_LOGGING_BUCKETをリセットして、Cloud Object Storageのロギング・バケットの値をクリアできます。
    例:
    ALTER DATABASE PROPERTY SET DEFAULT_LOGGING_BUCKET = '';
SQLトレースを無効にすると、トレースを有効にしたセッションの実行中に収集されたトレース・データが表にコピーされ、クラウド・オブジェクト・ストアのトレース・ファイルに送信されます。

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

SQLトレース・ファイル・データを使用して、Autonomous Databaseでのアプリケーション・パフォーマンスを分析します。データベース・セッションでSQLトレースを無効にすると、データはDEFAULT_LOGGING_BUCKETで構成されたクラウド・オブジェクト・ストア・バケットに書き込まれます。

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

default_logging_bucket/sqltrace/clientID/moduleName/sqltrace_numID1_numID2.trc

ファイル名の構成要素は:

  • default_logging_bucket: DEFAULT_LOGGING_BUCKETデータベース・プロパティの値です。詳細は、Autonomous DatabaseでのSQLトレースの構成を参照してください。

  • clientID: クライアント識別子です。詳細は、Autonomous DatabaseでのSQLトレースの有効化を参照してください。

  • moduleName: モジュール名です。詳細は、Autonomous DatabaseでのSQLトレースの有効化を参照してください。

  • numID1_numID2: SQLトレース機能で提供される2つの識別子です。numID1およびnumID2の数値によって、クラウド・オブジェクト・ストレージの同じバケットでトレースを使用したり、トレース・ファイルを作成したりする他のセッションから各トレース・ファイル名が一意に区別されます。

    データベース・サービスがパラレル化をサポートし、セッションがパラレル問合せを実行する場合、SQLトレース機能では、異なるnumID1およびnumID2の値を持つ複数のトレース・ファイルが生成されることがあります。

ノート

同じセッション内でSQLトレースを複数回有効化および無効化すると、トレースの繰返しごとにクラウド・オブジェクト・ストアに個別のトレース・ファイルが生成されます。セッションで生成された以前のトレースが上書きされないように、その後生成されるファイルは同じ命名規則に従い、トレース・ファイル名に数値の接尾辞が追加されます。この数値のサフィックスは数値1から始まり、その後トレースが繰り返されるたびに1増分されます。

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

sqltrace/sqlt_test/modname/sqltrace_5415_56432.trc

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

  1. オブジェクト・ストアからローカル・システムにトレース・ファイルをコピーします。
  2. トレース・ファイルが保存されているディレクトリにナビゲートします。
  3. 次の構文を使用して、オペレーティング・システムのプロンプトで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]

    必要な引数は、入力ファイルと出力ファイルのみです。

  4. オンライン・ヘルプを表示するには、引数なしでTKPROFを起動します。
    TKPROFユーティリティの使用の詳細は、『Oracle Database SQLチューニング・ガイド』の「エンドツーエンド・アプリケーションのトレースのツール」を参照してください。

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

SQLトレースを有効にすると、クラウド・オブジェクト・ストアのトレース・ファイルに保存されたものと同じトレース情報が、トレースが有効になったセッションのSESSION_CLOUD_TRACEビューに表示されます。
データベース・セッション中は、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 ORDERBY row_number;

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

同じセッション内でSQLトレースを複数回有効化および無効化すると、SESSION_CLOUD_TRACEにはすべての繰返しのトレース・データが累積的に表示されます。したがって、前にトレースを無効にしてからセッションでトレースを再度有効にしても、前の繰返しで生成されたトレース・データは削除されません。