Autonomous Database：SQLの実行結果をカスタム・メトリックとしてOCI Monitoringにパブリッシュしてみた

Last updated at 2025-01-30Posted at 2025-01-30

はじめに

Autonomous Databaseでカスタム・メトリックをパブリッシュする方法に関するマニュアルが公開されました。

こちらのマニュアルの内容を参考にして、今回は、メトリックのネームスペース、メトリック名およびメトリックの値を取得するためのSQLをパラメータとして渡すと、SQLの実行結果をOCI Monitoringのカスタム・メトリックの値としてパブリッシュするPL/SQLプロシージャを作成し、このプロシージャを実行するDBMS_SCHEDULERのスケジュール・ジョブでカスタム・メトリックを一定間隔でパブリッシュしてみました。

なお、ここではOCI MonitoringにアクセスするためのクレデンシャルとしてAutonomous Databaseのリソース・プリンシパルを使用していますので、事前にプロシージャを実行するAutonomous Databaseを含む動的グループ（この例ではadb_db）を作成し、以下のポリシーでその動的グループに対してOCI Monitoringへのアクセスを許可しておく必要があります。

Allow dynamic-group adb_dg to use metrics in compartment コンパートメントのOCID

また、adminユーザとしてAutonomous Databaseに接続して、以下のSQLでリソース・プリンシパルを有効化する必要があります。

EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL;

※こちらの内容はあくまで個人が検証した内容であり、他の環境で正しく動作することを保証するものではありません。また、こちらの記事を元にしたOracleサポートへの問い合わせはご遠慮ください。

1. 事前準備

今回はカスタム・メトリックとして、表のレコード数をパブリッシュするので、その対象となる表testを作成します。

SQL> CREATE TABLE test (id NUMBER);

Table created.

SQL>

2. カスタム・メトリックをパブリッシュするPL/SQLプロシージャの作成

今回は、メトリックのネームスペース、メトリック名およびメトリックの値を取得するためのSQLをパラメータとして渡すと、SQLの実行結果をOCI Monitoringのカスタム・メトリックの値として登録（Publish）するPL/SQLプロシージャ「publish_custom_metric」を作成しました。

なお、こちらのプロシージャでは、プロシージャを実行するAutonomous Databaseが存在するリージョン、およびコンパートメントにカスタム・メトリックをパブリッシュします。

publish_custome_metricプロシージャ

CREATE OR REPLACE PROCEDURE publish_custom_metric (namespace IN VARCHAR2, metric_name IN VARCHAR2, p_sql_statement IN VARCHAR2)
IS
    l_result NUMBER;
    l_compartment_ocid VARCHAR2(255);
    l_db_name VARCHAR2(255);
    l_region VARCHAR2(255);
    l_json_payload CLOB;
    l_cloud_identity CLOB;
BEGIN
 -- 1. SQLを実行して結果を取得
    EXECUTE IMMEDIATE p_sql_statement INTO l_result;
 
 -- 2. v$pdbsのcloud_identity列からADBがあるコンパートメントのOCID、ADBのDB名およびADBのあるリージョンを取得
    SELECT JSON_VALUE(cloud_identity, '$.COMPARTMENT_OCID'), JSON_VALUE(cloud_identity, '$.DATABASE_NAME'), JSON_VALUE(cloud_identity, '$.REGION')
      INTO l_compartment_ocid, l_db_name, l_region FROM v$pdbs;
 
 -- 3. メトリック出力のためのJSONペイロードを構成
    l_json_payload :=
      '{' ||
      ' "metricData": [' ||
      ' {' ||
      ' "namespace": "' || namespace ||'",' ||
      ' "compartmentId": "' || l_compartment_ocid || '",' ||
      ' "name": "' || metric_name || '",' ||
      ' "dimensions": {' ||
      ' "dbName": "' || l_db_name || '"' ||
      ' },' ||
      ' "datapoints": [' ||
      ' {' ||
      ' "timestamp": "' || TO_CHAR(SYSTIMESTAMP AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"') || '",' ||
      ' "value": ' || l_result ||
      ' }' ||
      ' ]' ||
      ' }' ||
      ' ]' ||
      '}';
 
 -- 4. DBMS_CLOUD.SEND_REQUESTファンクションを使用してメトリックをパブリッシュ
    DECLARE
        l_response DBMS_CLOUD_TYPES.resp;
    BEGIN
        l_response := DBMS_CLOUD.SEND_REQUEST(
                        credential_name => 'OCI$RESOURCE_PRINCIPAL',
                        uri => 'https://telemetry-ingestion.' || l_region || '.oraclecloud.com/20180401/metrics',
                        method => 'POST',
                        body => UTL_RAW.CAST_TO_RAW(l_json_payload)
                      );
 -- 5. レスポンスのステータスを確認
        IF DBMS_CLOUD.GET_RESPONSE_STATUS_CODE(l_response) = 200 THEN
            DBMS_OUTPUT.PUT_LINE('Metric published successfully!');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Error publishing metric: ' || DBMS_CLOUD.GET_RESPONSE_STATUS_CODE(l_response));
            DBMS_OUTPUT.PUT_LINE(DBMS_CLOUD.GET_RESPONSE_TEXT(l_response));
        END IF;
 
        EXCEPTION
          WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error during SEND_REQUEST: ' || SQLERRM);
    END;

    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

上記のSQLを実行して、PL/SQLプロシージャを作成します。

SQL> CREATE OR REPLACE PROCEDURE publish_custom_metric (namespace IN VARCHAR2, metric_name IN VARCHAR2, p_sql_statement IN VARCHAR2)
  2  IS
  3    l_result NUMBER;
  4    l_compartment_ocid VARCHAR2(255);
  5    l_db_name VARCHAR2(255);
  6    l_region VARCHAR2(255);
  7    l_json_payload CLOB;
  8    l_cloud_identity CLOB;
  9  BEGIN
 10   -- 1. SQLを実行して結果を取得
 11    EXECUTE IMMEDIATE p_sql_statement INTO l_result;
 12  
 13   -- 2. v$pdbsのcloud_identity列からADBがあるコンパートメントのOCID、ADBのDB名およびADBのあるリージョンを取得
 14    SELECT JSON_VALUE(cloud_identity, '$.COMPARTMENT_OCID'), JSON_VALUE(cloud_identity, '$.DATABASE_NAME'), JSON_VALUE(cloud_identity, '$.REGION')
 15  	 INTO l_compartment_ocid, l_db_name, l_region FROM v$pdbs;
 16  
 17   -- 3. メトリック出力のためのJSONペイロードを構成
 18    l_json_payload :=
 19  	 '{' ||
 20  	 ' "metricData": [' ||
 21  	 ' {' ||
 22  	 ' "namespace": "' || namespace ||'",' ||
 23  	 ' "compartmentId": "' || l_compartment_ocid || '",' ||
 24  	 ' "name": "' || metric_name || '",' ||
 25  	 ' "dimensions": {' ||
 26  	 ' "dbName": "' || l_db_name || '"' ||
 27  	 ' },' ||
 28  	 ' "datapoints": [' ||
 29  	 ' {' ||
 30  	 ' "timestamp": "' || TO_CHAR(SYSTIMESTAMP AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"') || '",' ||
 31  	 ' "value": ' || l_result ||
 32  	 ' }' ||
 33  	 ' ]' ||
 34  	 ' }' ||
 35  	 ' ]' ||
 36  	 '}';
 37  
 38   -- 4. DBMS_CLOUD.SEND_REQUESTファンクションを使用してメトリックをOCI Monitoringにパブリッシュ
 39    DECLARE
 40  	 l_response DBMS_CLOUD_TYPES.resp;
 41    BEGIN
 42  	 l_response := DBMS_CLOUD.SEND_REQUEST(
 43  			 credential_name => 'OCI$RESOURCE_PRINCIPAL',
 44  			 uri => 'https://telemetry-ingestion.' || l_region || '.oraclecloud.com/20180401/metrics',
 45  			 method => 'POST',
 46  			 body => UTL_RAW.CAST_TO_RAW(l_json_payload)
 47  		       );
 48   -- 5. レスポンスのステータスを確認
 49  	IF DBMS_CLOUD.GET_RESPONSE_STATUS_CODE(l_response) = 200 THEN
 50  	  DBMS_OUTPUT.PUT_LINE('Metric published successfully!');
 51  	ELSE
 52  	  DBMS_OUTPUT.PUT_LINE('Error publishing metric: ' || DBMS_CLOUD.GET_RESPONSE_STATUS_CODE(l_response));
 53  	  DBMS_OUTPUT.PUT_LINE(DBMS_CLOUD.GET_RESPONSE_TEXT(l_response));
 54  	END IF;
 55  
 56  	EXCEPTION
 57  	  WHEN OTHERS THEN
 58  	    DBMS_OUTPUT.PUT_LINE('Error during SEND_REQUEST: ' || SQLERRM);
 59  	END;
 60  
 61  EXCEPTION
 62    WHEN OTHERS THEN
 63  	 DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
 64  END;
 65  /

Procedure created.

SQL>

3. 作成したプロシージャの動作確認

作成したPL/SQLプロシージャを実行します。
ここでは、以下のようなパラメータを指定しています。
・ネームスペース名；test_adb_custom_metrics
・メトリックス名：test_row_count
・実行するSQL：SELECT COUNT(*) FROM test

SQL> set serveroutput on
SQL> EXEC publish_custom_metric('test_adb_custom_metrics','test_row_count','SELECT COUNT(*) FROM test')
Metric published successfully!

PL/SQL procedure successfully completed.

SQL>

OCIコンソールでMonitoringのMetric Explorerに移動します。

「Show Data Table」のトグルをオンにします。

画面下部のQueryのセクションで、以下の通り条件を設定し、「Update Chart」をクリックします。
・Compartment：Autonomous Databaseが属するコンパートメント
・Metric Namespace：プロシージャ実行時に指定したネームスペース名（ここではtest_adb_custom_metrics）
・Metric Name：プロシージャ実行時に指定したメトリック名（ここではtest_row_count）

カスタム・メトリックに値が0のデータがパブリッシュされていることがわかりました。

表testに1行データを追加します。

SQL> INSERT INTO test VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>

再度プロシージャを実行して、カスタム・メトリックをパブリッシュします。

SQL> EXEC publish_custom_metric('test_adb_custom_metrics','test_row_count','SELECT COUNT(*) FROM test')
Metric published successfully!

PL/SQL procedure successfully completed.

SQL>

カスタム・メトリックを再度確認すると、値が1の新しいデータがメトリックに追加されていることがわかります。

4. DBMS_SCHUDULERジョブの作成

PL/SQLプロシージャの動作が確認できたので、こちらPL/SQLプロシージャを1分間隔で実行してカスタム・メトリックをパブリッシュするDBMS_SCHEDULERジョブ「publish_test_row_count_job」を作成します。
ここではジョブ実行時にPL/SQLプロシージャに渡すパラメータとして、DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUEプロシージャを使用して、以下のように設定しています。
1つめのパラメータ（ネームスペース名）：adb_custom_metrics
2つめのパラメータ（メトリック名）：test_row_count
3つめのパラメータ（実行するSQL）：SELECT COUNT(*) FROM admin.test

BEGIN
 -- 1分間隔でpublish_custom_metricプロシージャを実行してメトリックをPublishするスケジューラ・ジョブを作成
    DBMS_SCHEDULER.CREATE_JOB (
        job_name => 'publish_test_row_count_job',
        job_type => 'STORED_PROCEDURE',
        job_action => 'admin.publish_custom_metric',
        number_of_arguments => 3,
        start_date => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
        enabled => FALSE,
        auto_drop => FALSE,
        comments => 'Publishes the count of rows in admin.test every 1 minute'
    );
 -- ジョブ実行時のpublish_custom_metricプロシージャの1つめのパラメータ（ネームスペース名）を設定
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
       job_name => 'publish_test_row_count_job',
       argument_position => 1,
       argument_value => 'adb_custom_metrics'
    );
 -- ジョブ実行時のpublish_custom_metricプロシージャの2つめのパラメータ（メトリック名）を設定
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
       job_name => 'publish_test_row_count_job',
       argument_position => 2,
       argument_value => 'test_row_count'
    );
 -- ジョブ実行時のpublish_custom_metricプロシージャの3つめのパラメータ（実行するSQL）を設定
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
       job_name => 'publish_test_row_count_job',
       argument_position => 3,
       argument_value => 'SELECT COUNT(*) FROM admin.test'
    );

   DBMS_SCHEDULER.ENABLE('ADMIN.PUBLISH_TEST_ROW_COUNT_JOB');
END;
/

上記SQLを実行してスケジューラ・ジョブを作成し、プロシージャに渡すパラメータを設定してから、スケジューラ・ジョブを有効化します。

SQL> BEGIN
  2   -- 1分間隔でpublish_custom_metricプロシージャを実行してメトリックをPublishするスケジューラ・ジョブを作成
  3  	 DBMS_SCHEDULER.CREATE_JOB (
  4  	     job_name => 'publish_test_row_count_job',
  5  	     job_type => 'STORED_PROCEDURE',
  6  	     job_action => 'admin.publish_custom_metric',
  7  	     number_of_arguments => 3,
  8  	     start_date => SYSTIMESTAMP,
  9  	     repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
 10  	     enabled => FALSE,
 11  	     auto_drop => FALSE,
 12  	     comments => 'Publishes the count of rows in admin.test every 1 minute'
 13  	 );
 14   -- ジョブ実行時のpublish_custom_metricプロシージャの1つめのパラメータ（ネームスペース名）を設定
 15  	 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
 16  	    job_name => 'publish_test_row_count_job',
 17  	    argument_position => 1,
 18  	    argument_value => 'adb_custom_metrics'
 19  	 );
 20   -- ジョブ実行時のpublish_custom_metricプロシージャの2つめのパラメータ（メトリック名）を設定
 21  	 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
 22  	    job_name => 'publish_test_row_count_job',
 23  	    argument_position => 2,
 24  	    argument_value => 'test_row_count'
 25  	 );
 26   -- ジョブ実行時のpublish_custom_metricプロシージャの3つめのパラメータ（実行するSQL）を設定
 27  	 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
 28  	    job_name => 'publish_test_row_count_job',
 29  	    argument_position => 3,
 30  	    argument_value => 'SELECT COUNT(*) FROM admin.test'
 31  	 );
 32  
 33  	DBMS_SCHEDULER.ENABLE('ADMIN.PUBLISH_TEST_ROW_COUNT_JOB');
 34  END;
 35  /

PL/SQL procedure successfully completed.

SQL>

6. DBMS_SCHEDULERジョブの動作確認

先ほどと同様に、Metric Explorerで画面下部のQueryのセクションで、以下の通り条件を設定し、「Update Chart」をクリックします。
・Compartment：Autonomous Databaseが属するコンパートメント
・Metric Namespace：プロシージャ実行時に指定したネームスペース名（ここではadb_custom_metrics）
・MEtric Name：プロシージャ実行時に指定したメトリック名（ここではtest_row_count）

設定通り、表testのレコード数が1分おきにカスタム・メトリックとして登録されていることが確認できました。

参考情報

Use Custom Metrics on Autonomous Database

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up