【Oracle Database】ユーザーが使用可能なCPUリソースを制限する方法

CPUリソースを制限する方法

意図しない高負荷処理の実行や優先度の低いユーザーの処理がCPUを過度に使用してしまうことで、本来実行したい処理のパフォーマンスダウンが起きてしまうといったトラブルが発生することがあります。このようなトラブルを防ぐために「ユーザーが使用可能なCPUリソースを制限したい」というお問い合わせをいただくことがあります。

今回はユーザーが使用可能なCPUリソースを制限する方法として、プロファイルとResouce Manager（Maximum Utilization Limit）を紹介します。

プロファイルによる制限（CPU_PER_SESSION）

プロファイルではユーザーごとの同時実行セッション数やパスワードの期限など、様々な制限をかけることが可能です。CPU_PER_SESSIONでは1セッション当たりのCPU時間を100分の1秒単位で制限できます。

CPU_PER_SESSIONの制限を超えてCPUを使用したセッションは「ORA-02392: CPU使用に対するセッション制限を超えました。ログオフ中です。」のエラーを受けて切断されます。

次の例ではセッションが使用できるCPU時間を30秒に制限するプロファイルを作成し、ユーザー名CPU_TESTに割り当てています。

　
　--CPU使用時間を30秒に制限したプロファイルを作成
　SQL> CREATE PROFILE cpu_resource_limit LIMIT
　  2     SESSIONS_PER_USER          UNLIMITED
　  3     CPU_PER_SESSION            3000
　  4     CPU_PER_CALL               UNLIMITED
　  5     CONNECT_TIME               UNLIMITED
　  6     LOGICAL_READS_PER_SESSION  DEFAULT
　  7     LOGICAL_READS_PER_CALL     UNLIMITED
　  8     PRIVATE_SGA                UNLIMITED
　  9     COMPOSITE_LIMIT            UNLIMITED;
　
　プロファイルが作成されました。
　
　--作成したプロファイルを確認
　SQL> SELECT
　  2      resource_name,
　  3      resource_type,
　  4      limit
　  5  FROM
　  6      dba_profiles
　  7  WHERE
　  8      PROFILE = 'CPU_RESOURCE_LIMIT';
　
　RESOURCE_NAME                  RESOURCE LIMIT    
　------------------------------ -------- ---------
　COMPOSITE_LIMIT                KERNEL   UNLIMITED
　SESSIONS_PER_USER              KERNEL   UNLIMITED
　CPU_PER_SESSION                KERNEL   3000     
　CPU_PER_CALL                   KERNEL   UNLIMITED
　LOGICAL_READS_PER_SESSION      KERNEL   DEFAULT  
　LOGICAL_READS_PER_CALL         KERNEL   UNLIMITED
　IDLE_TIME                      KERNEL   DEFAULT  
　CONNECT_TIME                   KERNEL   UNLIMITED
　PRIVATE_SGA                    KERNEL   UNLIMITED
　FAILED_LOGIN_ATTEMPTS          PASSWORD DEFAULT  
　PASSWORD_LIFE_TIME             PASSWORD DEFAULT  
　PASSWORD_REUSE_TIME            PASSWORD DEFAULT  
　PASSWORD_REUSE_MAX             PASSWORD DEFAULT  
　PASSWORD_VERIFY_FUNCTION       PASSWORD DEFAULT  
　PASSWORD_LOCK_TIME             PASSWORD DEFAULT  
　PASSWORD_GRACE_TIME            PASSWORD DEFAULT  
　
　
　--リソース制限を有効に変更
　SQL> ALTER SYSTEM SET resource_limit = TRUE;
　
　システムが変更されました。
　
　
　--作成したプロファイル（CPU_RESOURCE_LIMIT）をCPU_TESTユーザーに割り当て
　SQL> ALTER USER cpu_test PROFILE cpu_resource_limit;
　
　ユーザーが変更されました。
　
　SQL> SELECT username,profile FROM dba_users WHERE USERNAME='CPU_TEST';
　
　USERNAME             PROFILE
　-------------------- ------------------------------
　CPU_TEST             CPU_RESOURCE_LIMIT
　
　--CPUを使用する処理を実行
　SQL> conn cpu_test/cpu_test
　接続されました。
　SQL> set timing on
　SQL> declare
　  2       foo number;
　  3   begin
　  4       while (1=1) LOOP
　  5       foo := 1+1;
　  6       end loop;
　  7   end;
　  8   /
　declare
　*
　行1でエラーが発生しました。:
　ORA-02392: CPU使用に対するセッション制限を超えました。ログオフ中です。
　
　
　経過: 00:00:30.07

プロファイルに設定した制限を超えたセッションは切断されます。必要以上に短い時間で設定をすると、実行されるべき処理が行われないなどの問題が発生する可能性があります。設定には事前に十分な検証が必要です。

プロファイルで制御できるリソースは SQL言語リファレンスのマニュアルに記載されています。CPUリソース以外にもプロファイルによる制限を検討される場合はこちらをご参照ください。

Resource Managerによる制限（Maximum Utilization Limit）

Oracle Database 11gR1までのResource Managerでは、CPU使用率が100%になるまで制限を行わない動作でした。Oracle Database 11gR2ではMaximum Utilization Limitとインスタンス・ケージングという機能が追加され、CPU使用率が100%でなくとも割り当て制限が行えるようになりました。

Maximum Utilization LimitではCPUの使用率をコンシューマ・グループ単位で指定し、ユーザーに割り当てることが可能です。制限に達したコンシューマ・グループのセッションはResouce Managerによって「resmgr:cpu quantum」の待機イベントで待機させられながら、指定したCP使用率に収まるよう処理を継続します。

次の例ではCPU使用率を20%に制限するコンシューマ・グループを作成し、ユーザー名CPU_TESTに割り当てています。※Resource ManagerはEnterprise Editionで利用可能な機能です

　
　--ペンディング・エリアを作成
　SQL> execute DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
　
　--コンシューマ・グループを作成
　SQL> BEGIN
　  2      DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
　  3        consumer_group =>'TEST_GROUP1',
　  4        comment =>'テストです');
　  5  END;
　  6  /
　
　PL/SQLプロシージャが正常に完了しました。
　--リソース・プランを作成
　SQL> BEGIN
　  2    DBMS_RESOURCE_MANAGER.CREATE_PLAN(
　  3      plan=>'TEST_PLAN',
　  4      comment=>'テストプラン');
　  5  END;
　  6  /
　
　PL/SQLプロシージャが正常に完了しました。
　
　--プラン・ディレクティブを作成
　SQL> BEGIN
　  2    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
　  3      plan=>'TEST_PLAN',
　  4      group_or_subplan=>'TEST_GROUP1',
　  5      MAX_UTILIZATION_LIMIT => 20,
　  6      comment=>'MAX_UTILIZATION_LIMITを20で設定'
　  7    );
　  8  END;
　  9  /
　
　-- OTHER_GROUPの設定（必須）
　SQL> BEGIN
　  2   DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
　  3      PLAN => 'TEST_PLAN',
　  4      GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
　  5      COMMENT => NULL
　  6    );
　  7  END;
　  8  /
　
　PL/SQLプロシージャが正常に完了しました。
　
　SQL> BEGIN
　  2    DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
　  3      attribute=>DBMS_RESOURCE_MANAGER.ORACLE_USER,
　  4      value=>'CPU_TEST',
　  5      consumer_group =>'TEST_GROUP1'
　  6    );
　  7  END;
　  8  /
　
　PL/SQLプロシージャが正常に完了しました。
　
　--ペンディング・エリアの検証
　SQL> execute DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA
　
　PL/SQLプロシージャが正常に完了しました。　
　
　--ペンディング・エリアを確定
　SQL> 　execute DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA
　
　PL/SQLプロシージャが正常に完了しました。
　
　--リソース・コンシューマ・グループの切替え権限を付与
　SQL> BEGIN
　  2    DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
　  3      GRANTEE_NAME => 'CPU_TEST',
　  4      CONSUMER_GROUP => 'TEST_GROUP1',
　  5      GRANT_OPTION => FALSE
　  6  );
　  7  END;
　  8  /
　
　PL/SQLプロシージャが正常に完了しました。　
　
　--CPU_TESTのリソース・コンシューマ・グループを設定
　SQL> BEGIN
　  2    DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP(
　  3      user=>'CPU_TEST',
　  4      consumer_group=>'TEST_GROUP1'
　  5    );
　  6  END;
　  7  /
　
　PL/SQLプロシージャが正常に完了しました。
　
　--リソース・マネージャ・プランを設定
　SQL> execute DBMS_RESOURCE_MANAGER.SWITCH_PLAN ('TEST_PLAN')
　PL/SQLプロシージャが正常に完了しました。
  　
　--プランが切り替わっていることを確認
　SQL> sho parameter resource_manager_plan
　
　NAME                                 TYPE        VALUE
　------------------------------------ ----------- ------------------------------
　resource_manager_plan                string      TEST_PLAN
　
　
　--CPU_TESTのみRESOURCE_CONSUMER_GROUPがTEST_GROUP1になっていることを確認
　SQL> select
 　 2      username
 　 3      ,resource_consumer_group
 　 4  from
 　 5      v$session
 　 6  where
　  7      username is not null
　  8  ;
　
　USERNAME                       RESOURCE_CONSUMER_GROUP
　------------------------------ --------------------------------
　CPU_TEST                       TEST_GROUP1
　SCOTT                          OTHER_GROUPS
　SYS                            OTHER_GROUPS
　CPU_TEST                       TEST_GROUP1
　
　
　--CPU_TEST/SCOTTでCPUリソースを使用する処理を実行
　SQL> declare
　  2       foo number;
　  3  begin
　  4      while (1=1) LOOP
　  5      foo := 1+1;
　  6      end loop;
　  7  end;
　  8  /
　
　--ユーザCPU_TESTのみ「resmgr:cpu quantum」でCPUリソースの割り当てを待機させられている
　SQL> select username,state,status,event from v$session where username is not null;
　
　USERNAME   STATE               STATUS   EVENT
　---------- ------------------- -------- ---------------------------
　CPU_TEST   WAITED KNOWN TIME   ACTIVE   resmgr:cpu quantum
　SCOTT      WAITED KNOWN TIME   ACTIVE   SQL*Net message from client
　SYS        WAITED SHORT TIME   ACTIVE   SQL*Net message to client
　CPU_TEST   WAITED KNOWN TIME   ACTIVE   resmgr:cpu quantum
　
　--コンシューマ・グループTEST_GROUP1のCPUリソースの使用率が20%で抑えられている
　SQL> SELECT
　  2      begin_time
　  3      ,end_time
　  4      ,consumer_group_name
　  5      ,cpu_utilization_limit
　  6      ,avg_cpu_utilization
　  7  FROM
　  8      V$RSRCMGRMETRIC
　  9  WHERE
　 10      consumer_group_name in ('TEST_GROUP1' ,'OTHER_GROUP');
　
　BEGIN_TIME          END_TIME            CONSUMER_GROUP_NAME CPU_UTILIZATION_LIMIT AVG_CPU_UTILIZATION
　------------------- ------------------- ------------------- --------------------- -------------------
　2017-09-07 14:00:25 2017-09-07 14:01:24 TEST_GROUP1                            20              19.685

Maximum Utilization Limitはセッションごとに制限をかけるプロファイルとは異なり、コンシューマ・グループに所属しているユーザー（セッション）全体で指定したCPU使用率を超えないよう制限します。

Resource Managerによるリソース管理の方法については Oracle Database 管理者ガイドに記載されています。利用を検討されている場合はこちらもご参照ください。

まとめ

ユーザーごとにCPUリソースの使用を制限する機能としてプロファイルとResource Managerを紹介しました。これらの機能を利用することで、意図しない過度なCPUリソースの使用を防ぐことができます。

ただし、設定を誤ると必要な処理にCPUリソースが割り当てられず、却ってトラブルを引き起こす要因になり得ます。制限をされる場合は十分な検証を行った上で設定ください。また、日々の運用の中でCPUリソースの不足が起きていないかも監視いただけるようお願いします。

筆者情報

大野高志

サービス事業部サポートセンター

2007年にアシスト入社後、Oracle Databaseのサポート業務に従事。現在はサポート業務の傍ら、未解決のトラブルを一つでも多く減らせるよう、サポートセンターに蓄積されているノウハウを社内外に伝える活動を行っている。

■本記事の内容について
　本記事に記載されている製品およびサービス、定義及び条件は、特段の記載のない限り本記事執筆時点のものであり、予告なく変更になる可能性があります。あらかじめご了承ください。

■商標に関して
　・Oracle®、Java及びMySQLは、Oracle、その子会社及び関連会社の米国及びその他の国における登録商標です。
　・Amazon Web Services、AWS、Powered by AWS ロゴ、[およびかかる資料で使用されるその他の AWS 商標] は、Amazon.com, Inc. またはその関連会社の商標です。
　　文中の社名、商品名等は各社の商標または登録商標である場合があります。

【Oracle AI Database 26ai】同時リフレッシュでON COMMITマテリアライズド・ビューのenqueue待ちにサヨナラ！OLTP／DWHスループットを実測検証

本記事では、従来のON COMMIT MViewが抱えてきたこのようなスループット低下や待機イベントのボトルネックを振り返りつつ、Oracle AI Database 26aiが提供する「同時リフレッシュ（Concurrent Refresh）」によって、OLTP／DWHそれぞれのユースケースでどのような改善が見込めるのかを検証していきます。