max_idle_blocker_time 파라미터

Oracle 19c에 max_idle_blocker_time 파라미터가 추가되었습니다. 블로킹 세션이 이 파라미터에 지정한 시간동안 IDLE 상태로 유지되면 세션을 자동으로 종료됩니다. 기본값은 0으로 미사용입니다. 블로킹 세션으로 인해 성능 이슈가 발생하고 있다면 해당 파라미터의 적용을 고려할 수 있습니다.

-- 1
SELECT name, value, default_value, description
  FROM v$parameter
 WHERE name = 'max_idle_blocker_time';

NAME                  VALUE DEFAULT_VALUE DESCRIPTION
--------------------- ----- ------------- ---------------------------------------------------
max_idle_blocker_time 0     0             maximum idle time for a blocking session in minutes

1개의 행이 선택되었습니다.

 

테스트를 위해 아래와 같이 t1 테이블을 생성하겠습니다.

-- 2
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS SELECT 1 AS c1 FROM DUAL;

 

S1 세션에서 max_idle_blocker_time 파라미터를 1로 변경하고 t1 테이블을 갱신합니다. 이어서 S2 세션에서 t1 테이블을 갱신하면 트랜잭션이 블로킹됩니다. 1분이 지나면 S1 세션이 종료되고, S2 세션에서 t1 테이블이 갱신됩니다. S1 세션에서 EXIT 명령어를 수행하면 ORA-03113 에러가 발생합니다.

-- 3-1: S1
ALTER SYSTEM SET max_idle_blocker_time = 1;

UPDATE t1 SET c1 = 2;

-- after 1 minute

EXIT

ORA-03113: 통신 채널에 EOF 가 있습니다

-- 3-2: S2
UPDATE t1 SET c1 = 2;

-- after 1 minute

1 행이 업데이트되었습니다.

 

19c 이전 버전은 아래의 프로시저로 블로킹 세션을 종료할 수 있습니다.

-- 4
CREATE OR REPLACE PROCEDURE prc_kill_blocker (i_minutes IN NUMBER)
    AUTHID CURRENT_USER
IS
    v_sql_text VARCHAR2(100);
BEGIN
    FOR f1 IN (SELECT a.inst_id, a.sid, a.serial#
                 FROM gv$session a
                WHERE a.status = 'INACTIVE'
                  AND a.event = 'SQL*Net message from client'
                  AND a.wait_time_micro >= (i_minutes * 60) * 1e6
                  AND EXISTS (SELECT 1
                                FROM gv$session x
                               WHERE x.blocking_instance = a.inst_id
                                 AND x.blocking_session = a.sid
                                 AND x.blocking_session_status = 'VALID'
                                 AND x.event = 'enq: TX - row lock contention'
                                 AND x.wait_time_micro >= 60 * 1e6))
    LOOP
        BEGIN
            v_sql_text := q'[ALTER SYSTEM KILL SESSION '{SID},{SERIAL#},@{INST_ID}' IMMEDIATE]';
            v_sql_text := REPLACE (v_sql_text, '{SID}', f1.sid);
            v_sql_text := REPLACE (v_sql_text, '{SERIAL#}', f1.serial#);
            v_sql_text := REPLACE (v_sql_text, '{INST_ID}', f1.inst_id);

            EXECUTE IMMEDIATE v_sql_text;
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    END LOOP;
END;
/

 

아래와 같이 DBMS_JOB 패키지로 10초마다 프로시저를 수행하는 작업을 등록할 수 있습니다.

-- 5
DECLARE
    v_job NUMBER;
BEGIN
    DBMS_JOB.SUBMIT (
        job       => v_job
      , what      => 'BEGIN PRC_KILL_BLOCKER (1); END;'
      , next_date => TRUNC (SYSDATE, 'MI') + (1 / 24 / 60)
      , interval  => 'SYSDATE + (10 / 86400)'
    );

    COMMIT;
END;
/