2025年9月3日水曜日

Autonomous DatabaseのCS_RESOURCE_MANAGERによるSQLの中断を確認する

Oracle Databaseのリソース・マネージャに関する設定について調べていたところ、Autonomous Databaseで提供されているパッケージCS_RESOURCE_MANAGERを使ってプラン・ディレクティブを更新できることを知りました。

プロシージャはUPDATE_PLAN_DIRECTIVEです。
CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
   consumer_group         IN VARCHAR2,
   io_megabytes_limit     IN NUMBER   DEFAULT NULL,
   elapsed_time_limit     IN NUMBER   DEFAULT NULL,
   shares                 IN NUMBER   DEFAULT NULL,
   concurrency_limit      IN NUMBER   DEFAULT NULL);
変更できる引数の内、io_megabytes_limitおよびelapsed_time_limitについては、使用上のノートに以下のように記載されています。
指定されたサービス内のSQL文が指定されたランタイム制限(elapsed_time_limit)を超えたり、指定した量(io_megabytes_limit)を超えるI/Oを実行すると、SQL文は終了します。
実際にプラン・ディレクティブを更新して、動作を確認してみました。結果としては、elapsed_time_limitについては設定した経過秒数を超過するとSQLは中断されますが、io_megabytes_limitについては中断されません。

以下より、動作を確認するために実施した手順を紹介します。

Autonomous Transaction Processingに管理者ユーザーADMINで接続します。

現状のコンシューマ・グループの設定値を一覧します。Autonomous Transaction ProcessingなのでHIGHMEDIUMLOWに加えてTPTPURGENTのコンシューマ・グループが作成されています。

select * from cs_resource_manager.list_current_rules();

SQL> select * from cs_resource_manager.list_current_rules();


CONSUMER_GROUP       ELAPSED_TIME_LIMIT    IO_MEGABYTES_LIMIT    SHARES    CONCURRENCY_LIMIT    DEGREE_OF_PARALLELISM 

_________________ _____________________ _____________________ _________ ____________________ ________________________ 

HIGH                                                                  4                    3                        2 

MEDIUM                                                                2                    2                        2 

LOW                                                                   1                   30                        1 

TP                                                                    8                   30                        1 

TPURGENT                                                             12                   30                          


SQL> 


CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVEを呼び出し、コンシューマ・グループTPURGENTelapesed_time_limit20を設定します。
begin
    cs_resource_manager.update_plan_directive(
        consumer_group => 'TPURGENT',
        elapsed_time_limit => 20
    );
end;
/

SQL> begin

  2  cs_resource_manager.update_plan_directive(

  3  consumer_group => 'TPURGENT',

  4  elapsed_time_limit => 20

  5  );

  6  end;

  7* /


PL/SQLプロシージャが正常に完了しました。


SQL> 


再度設定値を一覧します。

コンシューマ・グループTPURGENTelapesed_time_limit20になっていることを確認します。

SQL> select * from cs_resource_manager.list_current_rules();


CONSUMER_GROUP       ELAPSED_TIME_LIMIT    IO_MEGABYTES_LIMIT    SHARES    CONCURRENCY_LIMIT    DEGREE_OF_PARALLELISM 

_________________ _____________________ _____________________ _________ ____________________ ________________________ 

HIGH                                                                  4                    3                        2 

MEDIUM                                                                2                    2                        2 

LOW                                                                   1                   30                        1 

TP                                                                    8                   30                        1 

TPURGENT                             20                              12                   30                          


SQL> 


一般ユーザーでTPURGENTのサービスに接続します。

時間のかかるSQLを実行します。

set time on timing on
select count(*) from all_objects, all_objects;

経過時間が20秒でSQLの実行が中断されることが確認できます。

SQL> set time on timing on

14:15:44 SQL> select count(*) from all_objects, all_objects;


次のコマンドの開始中にエラーが発生しました : 行 1 -

select count(*) from all_objects, all_objects

エラー・レポート -

ORA-56735: 経過時間の制限を超えました - コールは中断されました


https://docs.oracle.com/error-help/db/ora-56735/


More Details :

https://docs.oracle.com/error-help/db/ora-56735/

経過時間: 00:00:21.305

14:16:26 SQL> 


elapsed_time_limitnullを設定すると、制限が解除されます。

管理者ユーザーADMINで実行します。
begin
    cs_resource_manager.update_plan_directive(
        consumer_group => 'TPURGENT',
        elapsed_time_limit => null
    );
end;
/

SQL> begin

  2      cs_resource_manager.update_plan_directive(

  3          consumer_group => 'TPURGENT',

  4          elapsed_time_limit => null

  5      );

  6  end;

  7* /


PL/SQLプロシージャが正常に完了しました。


SQL> 


続いてio_megabytes_limitを設定します。
begin
    cs_resource_manager.update_plan_directive(
        consumer_group => 'TPURGENT',
        io_megabytes_limit => 2
);
end;
/

SQL> begin

  2  cs_resource_manager.update_plan_directive(

  3  consumer_group => 'TPURGENT',

  4  io_megabytes_limit => 2

  5  );

  6  end;

  7* /


PL/SQLプロシージャが正常に完了しました。


SQL> 


設定値を確認します。

SQL> select * from cs_resource_manager.list_current_rules();


CONSUMER_GROUP       ELAPSED_TIME_LIMIT    IO_MEGABYTES_LIMIT    SHARES    CONCURRENCY_LIMIT    DEGREE_OF_PARALLELISM 

_________________ _____________________ _____________________ _________ ____________________ ________________________ 

HIGH                                                                  4                    3                        2 

MEDIUM                                                                2                    2                        2 

LOW                                                                   1                   30                        1 

TP                                                                    8                   30                        1 

TPURGENT                                                    2        12                   30                          


SQL> 


一般ユーザーで以下のSQLを実行します。

create table t1 as select * from dba_objects;
select bytes, blocks, (bytes/blocks) from user_segments where segment_name = 'T1';
create table t2 as select * from t1;
select bytes, blocks, (bytes/blocks) from user_segments where segment_name = 'T2';

SQL> create table t1 as select * from dba_objects;


Table T1は作成されました。


SQL> select bytes, blocks, (bytes/blocks) from user_segments where segment_name = 'T1';


      BYTES    BLOCKS    (BYTES/BLOCKS) 

___________ _________ _________________ 

   18874368      2304              8192 


SQL> create table t2 as select * from t1;


Table T2は作成されました。


SQL> select bytes, blocks, (bytes/blocks) from user_segments where segment_name = 'T2';


      BYTES    BLOCKS    (BYTES/BLOCKS) 

___________ _________ _________________ 

   18874368      2304              8192 


SQL> 


io_megabytes_limit
2、つまり2MBを設定して上で約19MBの表T1を作成していますが、SQLの実行は中断されません。また、物理的な表T1を元に表T2を作成しても、やはりSQLの実行は中断されません。

どうしたものかと調べてみると、Adrian Capitanuさんの以下の記事を見つけました。プロファイルを作成してIO制限を実装しています。

Protecting the Autonomous Database from runaway queries
https://medium.com/@adrian.capitanu/protecting-the-autonomous-database-from-runaway-queries-eba925112bbb

この記事にそってプロファイルMY_CUSTOMを作成し、プロファイルにLOGICAL_READS_PER_CALLを設定することにより、IO制限をかけてみます。

最初にio_megabytes_limitの制限を解除します。作業は管理者ユーザーADMINで実施します。
begin
    cs_resource_manager.update_plan_directive(
        consumer_group => 'TPURGENT',
        io_megabytes_limit => null
    );
end;
/

SQL> begin

  2      cs_resource_manager.update_plan_directive(

  3          consumer_group => 'TPURGENT',

  4          io_megabytes_limit => null

  5      );

  6  end;

  7* /


PL/SQLプロシージャが正常に完了しました。


SQL> 


プロファイルMY_CUSTOMを作成します。表T1のブロック数は2304なので、この表を読み込むときはロジカル・リードが最低2304回は実行されると想定できます。LOGICAL_READS_PER_CALLの値は、2000, 3000、4000、5000と設定値を変更してcreate table文がIO制限にかかる上限値を確認した上で、4000を設定しています。
create profile my_custom
limit
LOGICAL_READS_PER_CALL 4000
;

SQL> create profile my_custom

  2  limit

  3  LOGICAL_READS_PER_CALL 4000

  4* ;


Profile MY_CUSTOMは作成されました。


SQL> 


一般ユーザーWKSP_APEXDEVにプロファイルMY_CUSTOMを割り当てます。

alter user wksp_apexdev profile my_custom;

SQL> alter user wksp_apexdev profile my_custom;


User WKSP_APEXDEVが変更されました。


SQL> 


一般ユーザーでデータベースに接続し、以下のSQLを実行します。

create table t3 as select * from t1;

SQL> create table t3 as select * from t1;


次のコマンドの開始中にエラーが発生しました : 行 1 -

create table t3 as select * from t1

エラー・レポート -

ORA-02395: I/O使用に対するコール制限を超えました。


https://docs.oracle.com/error-help/db/ora-02395/

02395. 00000 -  "exceeded call limit on IO usage"

*Cause:    An attempt was made to exceed the maximum I/O for a

           call, a parse, execute, or fetch, allowed by the

           LOGICAL_READS_PER_CALL clause of the user profile.

*Action:   If this happens often, ask the database administrator

           to increase the LOGICAL_READS_PER_CALL limit of the user

           profile.

SQL> 


以上で、プロファイルの設定によってIO負荷の高いSQLが中断されることが確認できました。

今回の記事は以上になります。