Oracle Database 19cのSQL検疫（SQL Quarantine）機能とは？リソースマネージャによる暴走クエリ対策の完全ガイド

本記事では、Oracle Database 19cの新機能であるSQL検疫（SQL Quarantine）について詳しく解説します。Oracle® Resource Managerを使用すると、CPUやI/Oといったシステムリソースの使用量を制御・制限できます。さらに注目すべきは、定義された閾値を超える長時間実行クエリの実行自体を防止できる点です。

SQL検疫（SQL Quarantine）とは何か？

「検疫（Quarantine）」とは隔離を意味します。SQL検疫は、暴走クエリ（Runaway Query）がもたらすオーバーヘッドを排除するために使用できる、19cの新機能です。暴走クエリとは、リソースやランタイムの上限を超えてしまい、大量のCPUやI/Oリソースを消費した結果、Resource Managerによって強制終了されるクエリのことを指します。

この機能は、Exadata（Engineered Systems上のOracle Database Enterprise Edition）およびDBCS/ExaCS（Oracle Database Exadata Cloud Service）でのみ利用可能です。この機能をテストするため、筆者は隠しパラメータを設定し、以下のコマンドでデータベースを再起動しました。

Alter system set "_exadata_feature_on"=true scope=spfile;

長時間実行クエリへの対処方法は？

Database Resource Manager（DBRM）はバックグラウンドプロセスであり、IOやCPUなどのリソース使用率が特定の閾値を超えたSQL文を終了させることができます。また、最大実行時間の閾値を超えたクエリも終了可能です。

制限を超えたSQL実行プランとSQL文にはマークが付けられ、検疫されます。つまり、同じSQLが同じ実行プランで再度実行されようとした場合、そのSQL文は即座に終了され、以下のエラーが表示されます。

error: ORA-56955: quarantined plan used.

これらのエラーが発生すると、Object Quarantineはエラーの原因となったオブジェクトを隔離し、データベース全体への影響を監視します。

ここでいう「オブジェクト」とは、テーブルやインデックスのことではなく、Oracleが検疫できるセッション、プロセス、SGAトランザクション、ライブラリキャッシュなどを指します。

これにより、一定の時間閾値より長く実行されているSQLクエリを、終了またはキャンセルできるようになります。以下の例をご覧ください。

図1：暴走しているSQLステートメント
画像出典：https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf

検疫されたオブジェクトの情報を確認する

https://dbaparadise.com/2020/01/everything-you-need-to-know-about-quarantined-objects/ によると、検疫されたオブジェクトに関する情報を取得するには、V$QUARANTINEおよびV$QUARANTINE_SUMMARYビューを照会します。これらのビューから、オブジェクトのタイプ、メモリアドレス、実際に発生したORA-エラー、エラーの日時などを確認できます。

次の例に示すように、暴走クエリを実行した際のサーバーのCPU使用率を確認できます。3つのクエリが同時に実行され、CPU使用率がほぼ100%に達している様子がわかります。

図2：暴走SQLによって消費されたCPU
画像出典：https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf

SQL検疫の仕組みと効果

SQL検疫を使用することで、暴走クエリのオーバーヘッドを排除できます。Resource Managerがリソース制限または実行時間制限を超えるSQL文を検出すると、そのSQL文が使用していた実行プランが検疫されます。

同じSQL文が同じSQLプランで実行されようとすると、即座に終了されます。これにより、システムリソースの使用量を大幅に削減できます。次の図では、少数のクエリ実行時に高いCPU使用率を示していますが、実行前に強制終了されることで、検疫後はシステムリソースを一切消費しなくなることがわかります。

図3：SQL検疫によって節約されたCPU
画像出典：https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf

検疫機能を使う手順

それでは、実際にこの機能の設定方法と動作を確認していきましょう。

まず、Exadata環境で作業しているものとしてデータベースをセットアップします。

alter system set "_exadata_feature_on"=true scope=spfile;
shutdown immediate;
startup;

次に、Resource Managerをセットアップするために、以下の手順を完了させる必要があります。

1. ペンディング領域の作成：

   begin
   dbms_resource_manager.create_pending_area();
   end;
   /
   

2. 1つ以上のリソースコンシューマグループの作成：

   begin
   dbms_resource_manager.create_consumer_group(CONSUMER_GROUP=>'SQL_LIMIT',COMMENT=>'consumer group');
   end;
   /
   

3. リソースプランの作成：

   begin
   dbms_resource_manager.set_consumer_group_mapping(attribute => 'ORACLE_USER',value => 'DBA1',consumer_group =>'SQL_LIMIT' );
   dbms_resource_manager.create_plan(PLAN=> 'NEW_PLAN',COMMENT=>'Kill statement after exceeding total execution time');
   end;
   /
   

4. リソースプランディレクティブの作成。CANCEL_SQLグループはデフォルトで既に存在します：

   begin
   dbms_resource_manager.create_plan_directive(
   plan => 'NEW_PLAN',
   group_or_subplan => 'SQL_LIMIT',
   comment => 'Kill statement after exceeding total execution time',
   switch_group=>'CANCEL_SQL',
   switch_time => 10,
   switch_estimate=>false);
   end;
   /
   begin
   dbms_resource_manager.create_plan_directive(PLAN=> 'NEW_PLAN',GROUP_OR_SUBPLAN=>'OTHER_GROUPS',COMMENT=>'leave others alone', CPU_P1=>100 );
   end;
   /
   

5. プラン、コンシューマグループ、ディレクティブのペンディング領域の検証と反映：

   begin
   dbms_resource_manager.validate_pending_area();
   end;
   /
   begin
   dbms_resource_manager.submit_pending_area();
   end;
   /
   

ここで、権限の付与とユーザーへのコンシューマグループの割り当てを行います。

6. 権限・ロール・割り当てユーザー用のペンディング領域の作成：

   begin
   dbms_resource_manager.create_pending_area();
   end;
   /
   

7. リソースコンシューマグループの切り替え権限をユーザーまたはロールに付与：

   begin
   dbms_resource_manager_privs.grant_switch_consumer_group('DBA1','SQL_LIMIT',false);
   end;
   /
   

8. ユーザーをリソースコンシューマグループに割り当て：

   begin
   dbms_resource_manager.set_initial_consumer_group('DBA1','SQL_LIMIT');
   end;
   /
   

9. ペンディング領域の検証と反映：

   begin
   dbms_resource_manager.validate_pending_area();
   end;
   /
   begin
   dbms_resource_manager.submit_pending_area();
   end;
   /
   

10. プランの更新とペンディング領域の再反映：

    begin
    dbms_resource_manager.clear_pending_area;
    dbms_resource_manager_create_pending_area;
    end;
    /
    begin
    dbms_resource_manager.update_plan_directive(plan=>'NEW_PLAN',group_or_subplan=>'SQL_LIMIT',new_switch_elapsed_time=>10, new_switch_for_call=>TRUE,new_switch_group=>'CANCEL_SQL');
    end;
    /
    
    begin
    dbms_resource_manager.validate_pending_area();
    dbms_resource_manager.submit_pending_area;
    end;
    /
    

次のステップ：プランの適用と動作確認

以上の手順でResource Managerのセットアップは完了です。完了したら、あとはこのプランをインスタンスに割り当てるだけです。

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=NEW_PLAN;

DBA1ユーザーでログインし、リソースプランで定義した10秒の経過時間閾値を超えるクエリを実行します。

なお、このステートメントはDBA1ユーザーとして実行する必要があり、DBA1にはDBAビューへのアクセス権が必要です。

select a.owner_name,b.product_name,c.location,d.country_code
from import_pr_table a, item_table b, locate_dealer_table c,country_table d;

ERROR at line 1:
ORA-00040: active time limit exceeded - call aborted

ご覧の通り、この場合、Resource ManagerはORA-00040エラーで実行を強制終了しました。

このステートメントのSQL_IDはわかりますか？答えは 3hdkutq4krg4c です。

SQL検疫の作成方法

検疫が必要なSQL文の実行プランに対して検疫構成を作成するには、DBMS_SQLQパッケージを使用します。

検疫対象のSQL文は、SQLテキストまたはSQL_IDのいずれかで指定できます。以下の例をご覧ください。

CREATE_QUARANTINE_BY_SQL_ID
or
CREATE_QUARANTINE_BY_SQL_TEXT

DECLARE
quarantine_sql VARCHAR2(30);
BEGIN
quarantine_sql := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(SQL_ID => '3hdkutq4krg4c');
END;
/

検疫構成を作成したら、DBMS_SQLQ.ALTER_QUARANTINEプロシージャを使用して検疫の閾値を指定できます。

BEGIN
DBMS_SQLQ.ALTER_QUARANTINE(
QUARANTINE_NAME => 'SQL_QUARANTINE_3hdkutq4krg4c',
PARAMETER_NAME => 'ELAPSED_TIME',
PARAMETER_VALUE => '10');
END;
/

これで、DBA_SQL_QUARANTINEビューを照会して、どのSQL文が検疫されているかを確認できます。

SQL検疫が有効になると、同じSQL文を実行しようとしても実行されません。

select a.owner_name,b.product_name,c.location,d.country_code from import_pr_table a, item_table b, locate_dealer_table c,country_table d;
ERROR at line 1:
ORA-56955: quarantined plan used

このエラーメッセージは、このステートメントに使用されたプランが検疫済みプランの一部であることを示しています。クエリは閾値を超えたためキャンセルされました。

V$SQLビューを確認すると、sql_quarantineとavoided_executionsという2つの新しい列が追加されていることがわかります。

select sql_quarantine,avoided_executions from v$sql where sql_id='3hdkutq4krg4c';
SQL> select sql_quarantine,avoided_executions
2  from v$sql where sql_id='3hdkutq4krg4c';

SQL_QUARANTINE       AVOIDED_EXECUTIONS
---------------      ---------------
SQL_QUARANTINE_3hdkutq4krg4c
1

まとめ

SQL検疫機能は、コストの高い検疫済みSQL文の将来の実行を防止することで、データベースのパフォーマンス向上に貢献します。暴走クエリによるリソース枯渇を未然に防ぎたい運用環境において、非常に有効な機能といえるでしょう。

コメントやご質問がある場合は、フィードバックタブをご利用ください。Sales Chatをクリックして、今すぐチャットを始めることもできます。