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

SQLトレースを使用して、アプリケーションの負荷の高いSQL文など、過剰なデータベース・ワークロードのソースを識別しやすくします。

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

Autonomous DatabaseでSQLトレースを構成するステップを示します。

ノート

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

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

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

    SQLトレース・ファイルを保存するには、Autonomous Databaseでサポートされているどのクラウド・オブジェクト・ストアにもバケットを配置します。

    たとえば、Oracle Cloud Infrastructure Object Storageにバケットを作成するには、次を実行します

    1. Oracle Cloud Infrastructure Consoleを開きます。
    2. メニューから「ストレージ」を選択します。
    3. 「Storage」で、「Object StorageとArchive Storage」を選択します。
    4. 「バケットの作成」をクリックします
    5. 「バケットの作成」ページで、バケット名を入力し、「作成」をクリックします。

    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;
    /

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

    クラウド・オブジェクト・ストレージに応じたパラメータとその値の詳細は、CREATE_CREDENTIALプロシージャを参照してください。

  3. 初期化パラメータを設定して、SQLトレース・ファイルのバケットのクラウド・オブジェクト・ストレージURLを指定し、Cloud Object Storageにアクセスするための資格証明を指定します。
    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は前に作成したバケットの名前です。詳細は、オブジェクト・ストレージ・ネームスペースの理解を参照してください。

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

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

    2. データベース・プロパティのDEFAULT_CREDENTIALに、ステップ2で作成した資格証明を設定します。

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

      たとえば:

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

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

データベース・セッションのSQLトレースを有効にするステップを示します。

ノート

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

SQLトレースを有効にする前に、SQLトレース・ファイルを保存するようにデータベースを構成する必要があります。詳細は、Autonomous Databaseでの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トレースの設定時に構成したバケット内のトレース・ファイルに書き込まれます。詳細は、Autonomous DatabaseでのSQLトレースの無効化を参照してください。

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

Autonomous Databaseで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トレースを無効にすると、トレースを有効にしたセッションの実行中に収集されたトレース・データが表にコピーされ、クラウド・オブジェクト・ストアのトレース・ファイルに送信されます。トレース・データを表示するには、2つのオプションがあります:

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

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

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_NUMBERTRACEという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にはすべての繰返しのトレース・データが累積的に表示されます。したがって、前にトレースを無効にしてからセッションでトレースを再度有効にしても、前の繰返しで生成されたトレース・データは削除されません。