アイドル時間の制限機能を試す（Oracle Database 19c)

Idle Timeの設定

　セッションが一定時間アイドル状態になると、セッションを強制切断する機能があります。Oracle Database 19cには以下の３種類で実現できます。

• 初期化パラメーターmax_idle_time
• PROFILEの制約idle_time
• Resource Managerのディレクティブmax_idle_time

本記事ではそれぞれの特徴と組み合わせについて記述しています。

初期化パラメーター

　初期化パラメーターidle_time にはアイドル時間の最大値を分単位で指定できます。この初期化パラメーターはインスタンス（またはPDB）単位で指定します。

SQL> ALTER SYSTEM SET max_idle_time = 3;

System altered.

アイドル時間が指定された時間を超過すると、ローカル接続の場合は「ORA-03135: connection lost contact」、リモート接続の場合は「ORA-03113: end-of-file on communication channel」が発生して、次回実行したSQL文がエラーになります。

ローカル接続の場合

SQL> SELECT * FROM dual;
SELECT * FROM dual
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 29787
Session ID: 272 Serial number: 26974

リモート接続の場合

SQL> SELECT * FROM dual;
SELECT * FROM dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 29589
Session ID: 45 Serial number: 35232

PROFILE

PROFILEではIDLE_TIMEリソースを指定します。指定する単位は分です。下記の例では新規プロファイルidle_profile1を作成し、ユーザーSCOTTのPROFILE属性に指定しています。初期化パラメーターresource_limitをTRUE（デフォルト値）に設定する必要があります。

SQL> CREATE PROFILE idle_profile1 LIMIT IDLE_TIME 2;

Profile created.

SQL> ALTER USER scott PROFILE idle_profile1;

User altered.

SQL> SELECT USERNAME, PROFILE FROM DBA_USERS WHERE USERNAME='SCOTT';

USERNAME                       PROFILE                        
------------------------------ ------------------------------
SCOTT                          IDLE_PROFILE1                 

Resource Manager

Resource Managerを使う場合はディレクティブmax_idle_timeを変更します。初期化パラメーターやPROFILEと異なり、アイドル時間を秒単位で指定します。下記の例では新規のプランidle_plan1を作成し、ユーザーSCOTTのコンシューマーグループと初期化パラメーターresource_manager_planを変更しています。

SQL> BEGIN
     DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

     DBMS_RESOURCE_MANAGER.CREATE_PLAN(
         PLAN => 'IDLE_PLAN1'
     );

     DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
        CONSUMER_GROUP => 'IDLE_GROUP1',
        COMMENT => 'IDLE GROUP1'
     );

     DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
        ATTRIBUTE => DBMS_RESOURCE_MANAGER.ORACLE_USER,
        VALUE => 'SCOTT',
        CONSUMER_GROUP => 'IDLE_GROUP1'
     );

     DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        PLAN => 'IDLE_PLAN1',
        GROUP_OR_SUBPLAN => 'IDLE_GROUP1',
        COMMENT => 'SET IDLE TIMEOUT',
        MAX_IDLE_TIME => 90
     );

     DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        PLAN => 'IDLE_PLAN1',
        GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
        COMMENT => 'Other groups'
     );

     DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
     DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

PL/SQL procedure successfully completed.

SQL> ALTER SYSTEM SET resource_manager_plan = 'IDLE_PLAN1';

System altered.
SQL>
SQL> SELECT username, INITIAL_RSRC_CONSUMER_GROUP FROM dba_users WHERE username='SCOTT';

USERNAME                       INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
SCOTT                          IDLE_GROUP1

異なる設定の競合

目的が同じ機能が３種類存在するため、異なる値で設定した場合の動作を検証しました。

Resource Managerを60秒に、PROFILEを2分に設定します。
1分経過後にSQL文を実行すると、強制切断されます。

SQL> SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 868
Session ID: 237 Serial number: 44994

次にPROFILEを60秒に、Resource Managerを90秒に設定します。
1分経過後にSQL文を実行すると、やはり切断されます。

SQL> SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again

初期化パラメーターを3分にし、Resource Managerを90秒に指定します。

SQL> ALTER SYSTEM SET max_idle_time = 3;

System altered.

2分後にSQL文を実行すると、やはり強制的に切断されます。上記の結果から、これら３つの機能はお互いに優先順位などは存在せず、最も短い時間の制限が有効になることがわかります。