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なのでHIGH、MEDIUM、LOWに加えてTPとTPURGENTのコンシューマ・グループが作成されています。
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を呼び出し、コンシューマ・グループ
TPURGENTの
elapesed_time_limitに
20を設定します。
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>
再度設定値を一覧します。
コンシューマ・グループTPURGENTのelapesed_time_limitが20になっていることを確認します。
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_limitに
nullを設定すると、制限が解除されます。
管理者ユーザー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
この記事にそってプロファイル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を割り当てます。
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が中断されることが確認できました。
今回の記事は以上になります。
完
