Oracle ORA-00057 오류 원인과 해결 방법 완벽 가이드

#oracle #database #dba #sql

ORA-00057 maximum number of temporary table locks exceeded 는?

ORA-00057 에러는 Oracle 데이터베이스에서 임시 테이블(Temporary Table)에 대한 잠금(Lock) 수가 시스템에서 허용하는 최대치를 초과했을 때 발생하는 에러입니다. Oracle 내부적으로 Global Temporary Table(GTT) 또는 임시 세그먼트에 접근하는 세션이 동시에 너무 많아지거나, 단일 세션 내에서 과도하게 많은 임시 테이블 잠금을 획득하려 할 때 이 에러가 트리거됩니다. 특히 대규모 OLTP 환경이나 배치 처리 시스템에서 다수의 세션이 동시에 Global Temporary Table을 사용하는 경우 실무에서 종종 맞닥뜨리게 되는 에러입니다.

주요 발생 원인

1. Global Temporary Table(GTT)의 과도한 동시 세션 접근

Oracle의 Global Temporary Table은 세션 또는 트랜잭션 단위로 데이터를 격리하지만, 내부적으로는 잠금 메커니즘을 사용하여 각 세션의 접근을 관리합니다. 수백 개 이상의 세션이 동시에 동일한 GTT에 접근하거나, 애플리케이션에서 커넥션 풀(Connection Pool)을 통해 짧은 시간 내에 폭발적으로 세션이 증가할 경우, 임시 테이블 잠금 슬롯이 고갈되어 ORA-00057이 발생할 수 있습니다. 이 상황은 특히 웹 애플리케이션에서 트래픽이 급증하는 피크 타임(peak time)에 자주 관찰됩니다.

2. 세션이 임시 테이블 잠금을 해제하지 않고 장시간 유지

정상적으로 COMMIT 또는 ROLLBACK이 수행되지 않는 장기 미결 트랜잭션(Long-running Transaction)이 존재할 경우, 해당 세션은 임시 테이블 잠금을 계속 보유하게 됩니다. 특히 ON COMMIT DELETE ROWS 옵션의 GTT는 트랜잭션이 종료될 때까지 잠금을 유지하므로, 트랜잭션이 제대로 종료되지 않으면 잠금 자원이 누적되어 결국 시스템 한계에 도달하게 됩니다. 애플리케이션 코드에서 예외 처리 누락으로 ROLLBACK이 실행되지 않는 경우가 대표적인 원인입니다.

3. 초기화 파라미터 TRANSACTIONS 또는 관련 설정의 부족

Oracle 내부적으로 임시 테이블 잠금 수는 TRANSACTIONS 파라미터 및 관련 파생 파라미터와 연관이 있습니다. 데이터베이스가 처음 구성될 때의 파라미터 값이 현재의 동시 접속 세션 수나 트랜잭션 규모를 반영하지 못하고 있다면, 시스템 성장에 따라 임시 테이블 잠금 자원이 부족해질 수 있습니다. 이 경우 데이터베이스를 재시작하지 않고는 일부 파라미터를 반영할 수 없기 때문에, 계획적인 용량 검토(Capacity Planning)가 중요합니다.

해결 방법

원인 1 해결: 현재 GTT 잠금 세션 확인 및 정리

먼저 현재 GTT에 잠금을 보유 중인 세션을 파악하고 필요 시 종료합니다.

-- GTT 관련 잠금을 보유한 세션 조회
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.machine,
    s.program,
    s.last_call_et AS elapsed_seconds,
    l.type,
    l.lmode,
    l.request,
    o.object_name,
    o.object_type
FROM
    v$session s
    JOIN v$lock l ON s.sid = l.sid
    JOIN dba_objects o ON l.id1 = o.object_id
WHERE
    o.object_type = 'TABLE'
    AND o.temporary = 'Y'
ORDER BY
    s.last_call_et DESC;

-- 장기 실행 중인 미결 트랜잭션 세션 확인
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    t.start_time,
    t.used_ublk,
    t.used_urec,
    ROUND((SYSDATE - TO_DATE(t.start_time, 'MM/DD/YY HH24:MI:SS')) * 24 * 60, 2) AS minutes_active
FROM
    v$session s
    JOIN v$transaction t ON s.taddr = t.addr
WHERE
    ROUND((SYSDATE - TO_DATE(t.start_time, 'MM/DD/YY HH24:MI:SS')) * 24 * 60, 2) > 30
ORDER BY
    minutes_active DESC;

-- 문제가 되는 세션 강제 종료 (DBA 권한 필요)
-- SID와 SERIAL# 값은 위 쿼리에서 확인한 값으로 대체
ALTER SYSTEM KILL SESSION '1234,56789' IMMEDIATE;

원인 2 해결: 트랜잭션 미결 세션 정리 및 GTT 사용 패턴 개선

애플리케이션 레벨에서 GTT 사용 후 반드시 COMMIT 또는 TRUNCATE를 수행하도록 코드를 수정하는 것이 근본적인 해결책입니다.

-- 세션 종료 전 GTT 데이터 명시적 정리 (ON COMMIT DELETE ROWS인 경우)
BEGIN
    -- 작업 처리
    INSERT INTO my_temp_table (col1, col2)
    SELECT col1, col2 FROM source_table WHERE condition = 'Y';

    -- 비즈니스 로직 처리
    -- ...

    -- 명시적 COMMIT으로 잠금 해제
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        -- 예외 발생 시에도 반드시 ROLLBACK 수행
        ROLLBACK;
        RAISE;
END;
/

-- ON COMMIT PRESERVE ROWS 타입 GTT는 세션 종료 전 수동 TRUNCATE 권장
TRUNCATE TABLE my_preserve_temp_table;

-- GTT 생성 시 타입 확인 방법
SELECT
    table_name,
    duration,
    temporary
FROM
    dba_tables
WHERE
    temporary = 'Y'
ORDER BY
    table_name;

원인 3 해결: TRANSACTIONS 파라미터 검토 및 조정

-- 현재 TRANSACTIONS 관련 파라미터 확인
SELECT
    name,
    value,
    description
FROM
    v$parameter
WHERE
    name IN (
        'transactions',
        'transactions_per_rollback_segment',
        'sessions',
        'processes'
    )
ORDER BY
    name;

-- 현재 활성 트랜잭션 수 확인
SELECT COUNT(*) AS active_transactions FROM v$transaction;

-- 최근 최대 동시 세션 수 확인 (AWR 활용)
SELECT
    snap_id,
    TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24:MI') AS snap_time,
    maxvalue AS max_sessions
FROM
    dba_hist_sysstat ds
    JOIN dba_hist_snapshot sn ON ds.snap_id = sn.snap_id AND ds.dbid = sn.dbid
WHERE
    stat_name = 'session cursor cache count'
ORDER BY
    snap_id DESC
FETCH FIRST 20 ROWS ONLY;

-- TRANSACTIONS 파라미터 조정 (재시작 필요)
-- 일반적으로 SESSIONS 파라미터의 1.1배를 권장
ALTER SYSTEM SET TRANSACTIONS = 500 SCOPE = SPFILE;

-- SPFILE 적용 후 DB 재시작
-- SHUTDOWN IMMEDIATE;
-- STARTUP;

-- 재시작 없이 적용 가능한 세션 수 조정 (일부 제한 있음)
ALTER SYSTEM SET SESSIONS = 450 SCOPE = SPFILE;
ALTER SYSTEM SET PROCESSES = 400 SCOPE = SPFILE;

예방 방법

1. GTT 사용 현황 정기 모니터링 및 임계치 알림 설정

운영 환경에서는 GTT에 대한 동시 잠금 수를 주기적으로 수집하고, 일정 임계치 이상이 되면 DBA에게 알림이 가도록 모니터링 스크립트를 구성하는 것이 중요합니다. 아래 스크립트를 Oracle Scheduler 또는 외부 모니터링 툴(Zabbix, OEM 등)과 연동하여 활용하세요.

-- GTT 잠금 수 모니터링 스크립트 (정기 실행 권장)
SELECT
    o.object_name AS temp_table_name,
    COUNT(l.sid) AS lock_count,
    MAX(s.last_call_et) AS max_elapsed_sec,
    MIN(s.logon_time) AS oldest_session_logon
FROM
    v$lock l
    JOIN dba_objects o ON l.id1 = o.object_id
    JOIN v$session s ON l.sid = s.sid
WHERE
    o.temporary = 'Y'
    AND o.object_type = 'TABLE'
GROUP BY
    o.object_name
HAVING
    COUNT(l.sid) > 50   -- 임계치: 환경에 맞게 조정
ORDER BY
    lock_count DESC;

2. 애플리케이션 커넥션 풀 설정 최적화 및 GTT 대안 검토

커넥션 풀의 최대 연결 수를 데이터베이스의 PROCESSES 및 SESSIONS 파라미터와 일치시켜 세션 폭증을 방지해야 합니다. 또한, GTT를 반드시 사용해야 하는 경우가 아니라면 PL/SQL의 컬렉션 타입(ASSOCIATIVE ARRAY, NESTED TABLE, VARRAY)이나 WITH절(CTE)을 활용하는 방향으로 설계를 개선하면 임시 테이블 잠금 이슈 자체를 원천 차단할 수 있습니다.

-- GTT 대신 PL/SQL 컬렉션 사용 예시
DECLARE
    TYPE t_emp_rec IS RECORD (
        emp_id   NUMBER,
        emp_name VARCHAR2(100)
    );
    TYPE t_emp_tab IS TABLE OF t_emp_rec INDEX BY PLS_INTEGER;
    l_emp_data t_emp_tab;
    l_idx      PLS_INTEGER := 1;
BEGIN
    FOR rec IN (SELECT employee_id, first_name FROM employees WHERE department_id = 10) LOOP
        l_emp_data(l_idx).emp_id   := rec.employee_id;
        l_emp_data(l_idx).emp_name := rec.first_name;
        l_idx := l_idx + 1;
    END LOOP;

    -- 이후 컬렉션 데이터 활용 (DB 잠금 불필요)
    FOR i IN 1 .. l_emp_data.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(l_emp_data(i).emp_id || ' : ' || l_emp_data(i).emp_name);
    END LOOP;
END;
/