오라클 운영 DB ALTER TABLE 중단 후 CPU 100% 해결기 | 세션 몰림·KILL SESSION 절망편

오늘은 운영 DB에 ALTER 한 줄 날렸다가 CPU 100%를 구경하고, 식은땀 흘리며 수습한 이야기를 공유하려고 합니다.

컬럼 길이 하나 늘리는 아주 간단한 작업이었고, 테스트 서버에서는 1초도 안 걸려서 끝났습니다. 그래서 운영 서버에서도 당연히 1초면 끝날 줄 알았죠. 네, 그게 절망의 시작이었습니다.

결론만 궁금하신 분들은 제일 밑 "결론 & 다음부터는 이렇게"만 보셔도 무관합니다만, 제 뻘짓도 구경해 주시면 감사하겠습니다.

※ 회사 정보 보호를 위해 테이블·계정·서버 이름은 가상의 이름으로 바꿨고, 캡처도 실제 화면 대신 똑같이 재현한 화면입니다. 흐름과 명령어는 실제로 겪은 그대로예요.

1. 하려던 작업: 컬럼 길이 늘리기

사이즈 코드를 담는 컬럼이 5자리였는데, 신규 코드 체계가 들어오면서 10자리로 늘려야 했습니다. 데이터를 지우는 것도 아니고 길이만 늘리는 거라 부담 없는 작업이라고 생각했어요.

📋 제가 실행한 SQL (테이블 이름은 가상)

ALTER TABLE APP.ORDERS MODIFY (SIZE_CD VARCHAR2(10 BYTE));

테스트 서버에서 실행했을 때는 엔터 치자마자 Table altered.가 떨어졌습니다. 자신감이 하늘을 찔렀죠.

2. 절망의 시작: 엔터를 쳤는데 안 끝난다

운영 서버에서 같은 문장을 실행했는데, 1초, 5초, 30초... 커서만 깜빡거립니다. 그 사이 모니터링 화면에서는 세션 수가 쭉쭉 올라가기 시작했어요. 주문 화면을 쓰는 세션들이 하나둘 대기 상태로 쌓이는 게 보이는데, 등에서 식은땀이 났습니다.

"이러다 서비스 멈추겠다" 싶어서 급하게 실행을 중지했습니다. 그리고 서버에 접속해 top을 쳐 봤더니...

▲ CPU 99.7%, load average 48. 오라클 프로세스들이 줄줄이 CPU를 먹고 있었습니다 (재현 화면)

CPU가 꽉 차 있었습니다. 이미 실행은 멈췄는데 CPU는 내려올 생각을 안 하더군요. 여기서부터 걱정이 두 갈래로 갈라졌습니다.

1. ALTER를 중간에 끊었는데, 테이블이 반쯤 바뀐 채로 깨진 건 아닐까?
2. CPU는 왜 안 내려오지? 뭘 죽여야 하지?

3. 첫 번째 걱정: ALTER를 끊으면 테이블이 깨질까?

제일 무서웠던 건 이거였습니다. 구글링을 하면 "DDL은 원자적이라 괜찮다"는 말이 많은데, 솔직히 남의 말만 믿고 넘어가기엔 운영 DB라 근거가 필요했어요. 그래서 오라클 공식 문서를 찾아봤습니다.

• 오라클 SQL 레퍼런스의 COMMIT 문서에는 문법이 맞는 DDL을 실행하기 전에, 그리고 DDL이 오류 없이 끝난 뒤에 자동으로 COMMIT한다고 적혀 있습니다. DDL 앞뒤로 트랜잭션이 딱 끊긴다는 뜻이에요.
• 또 오라클은 문장 단위 원자성을 보장해서, 실패하거나 중단된 문장은 그 문장이 한 일만 되돌립니다.

정리하면 ALTER는 끝까지 반영되거나, 아예 반영되지 않거나 둘 중 하나입니다. 다만 저처럼 "끝났는지 안 끝났는지" 모르는 상황에서는 직접 확인하는 게 제일 확실하죠. 아래 쿼리로 컬럼 길이를 봤더니 5 그대로였습니다. ALTER는 반영되지 않았고, 테이블도 멀쩡했습니다. 첫 번째 고비 통과.

📋 컬럼 길이가 바뀌었는지 확인 (OWNER·테이블·컬럼명은 본인 것으로)

SELECT column_name, data_type, data_length, char_used
  FROM all_tab_columns
 WHERE owner = 'APP'
   AND table_name = 'ORDERS'
   AND column_name = 'SIZE_CD';

4. 두 번째 걱정: 범인을 찾아라

테이블은 무사한데 CPU는 여전히 100%입니다. 누가 무엇을 기다리고 있는지부터 봐야 했어요. v$session에서 대기 이벤트와 누가 누구를 막고 있는지(blocking_session)를 같이 뽑아 봤습니다.

📋 지금 막혀 있는 세션과, 막고 있는 세션 보기

SELECT sid, serial#, username, status, event,
       blocking_session, last_call_et, sql_id
  FROM v$session
 WHERE blocking_session IS NOT NULL
    OR sid IN (SELECT blocking_session
                 FROM v$session
                WHERE blocking_session IS NOT NULL)
 ORDER BY last_call_et DESC;

▲ 맨 위 빨간 줄 보이시나요? LAST_CALL_ET 6,048,211초 = 약 70일 (재현 화면)

결과를 보고 눈을 의심했습니다. 맨 위에 약 70일째 돌고 있는 SELECT가 하나 있었어요. 그 밑으로 제 ALTER가 그 SELECT를 기다리고 있었고, 다시 그 ALTER 뒤에 일반 조회·수정 세션들이 library cache lock을 기다리며 줄줄이 서 있었습니다.

오래 도는 세션은 이 쿼리로도 바로 찾을 수 있습니다. 운영 DB 작업 전에 미리 한 번 돌려 보는 걸 추천드려요.

📋 오래 실행 중인 세션 찾기 (가장 오래된 순)

SELECT sid, serial#, username, status,
       ROUND(last_call_et / 86400, 1) AS days,
       sql_id, program
  FROM v$session
 WHERE status = 'ACTIVE'
   AND username IS NOT NULL
 ORDER BY last_call_et DESC;

5. 왜 이렇게 됐을까? (알고 보니 줄서기 문제)

나중에 찾아보니 이건 꽤 유명한 패턴이었습니다. 제가 이해한 대로 쉽게 풀어 보면 이렇습니다.

1. ALTER는 테이블을 잠깐 독점해야 합니다. 테이블 정의를 바꾸는 동안 다른 세션이 그 정의를 쓰고 있으면 안 되거든요.
2. 그런데 그 테이블을 70일째 붙잡고 있는 SELECT가 있었습니다. ALTER는 그 SELECT가 끝나길 기다리며 대기합니다.
3. 문제는 그 다음입니다. 대기 중인 ALTER 뒤로 새로 들어오는 조회·수정 세션들이 줄을 섭니다. 앞에서 독점을 요청한 ALTER가 있으니 뒷사람들도 못 들어가는 거죠.
4. 서비스는 계속 요청을 보내니 대기 세션이 수십 개로 불어납니다. 게다가 DDL이 실행되면 그 테이블을 쓰던 SQL 실행 계획들이 무효화돼서 다시 파싱(하드 파싱)이 한꺼번에 몰릴 수 있습니다. 오라클 옵티마이저 블로그에서도 바쁜 테이블에 DDL을 하면 하드 파싱이 폭증해 성능이 떨어질 수 있다고 설명하고 있어요.

테스트 서버에서 1초 만에 끝난 이유도 이제 이해가 됐습니다. 테스트 서버에는 그 테이블을 쓰는 세션이 아무도 없었으니까요. 같은 SQL이라도 운영 서버는 전혀 다른 세상이었습니다.

6. 수습: 70일짜리 세션 정리하기

70일째 도는 SELECT는 누군가 예전에 돌려 놓고 잊어버린 통계성 조회로 보였습니다. 관련 담당자에게 확인한 뒤 정리하기로 했어요.

먼저 top에서 CPU를 많이 먹는 프로세스 번호(PID)가 어느 세션인지 확인했습니다.

📋 top의 PID로 오라클 세션 찾기 (&spid 자리에 PID 입력)

SELECT s.sid, s.serial#, s.username, s.status,
       s.sql_id, s.program, p.spid
  FROM v$session s
  JOIN v$process p ON s.paddr = p.addr
 WHERE p.spid = '&spid';

그리고 문제의 세션을 정리했습니다. IMMEDIATE를 붙이면 진행 중인 트랜잭션을 롤백하고 잠금을 풀면서 바로 제어권을 돌려줍니다.

📋 세션 강제 종료 ('SID,SERIAL#' 자리에 위에서 찾은 값)

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

⚠️ 세션을 죽이기 전에 그게 무슨 작업인지 꼭 확인하세요. 큰 데이터를 수정하던 세션이면 롤백에 오래 걸리고, 배치 작업이면 처음부터 다시 돌려야 할 수도 있습니다. 저도 담당자에게 확인받고 나서 정리했습니다.

세션을 정리하고 나니 줄 서 있던 세션들이 순식간에 풀렸고, CPU도 서서히 내려왔습니다.

▲ 정리 후. CPU 4%, load average도 내려가는 중 (재현 화면)

이후 사용자가 거의 없는 시간에 같은 ALTER를 다시 실행했고, 이번에는 정말로 1초 만에 Table altered.가 떨어졌습니다. 허무할 정도로요.

결론 & 다음부터는 이렇게

ALTER를 중간에 끊어도 테이블이 반쯤 바뀌는 일은 없습니다. DDL은 끝까지 반영되거나 아예 반영되지 않아요. 진짜 문제는 그 테이블을 오래 붙잡고 있던 세션이었습니다. 다음부터는 이 순서를 꼭 지키려고 합니다.

1. 작업 시간: 운영 DB의 DDL은 사용자가 가장 적은 시간에 합니다.
2. 사전 점검: 작업 전에 위의 "오래 실행 중인 세션 찾기" 쿼리로 그 테이블을 쓰는 장기 세션이 있는지 확인합니다.
3. 무한 대기 방지: 세션에 DDL 잠금 대기 시간을 걸어 둡니다. 정해진 시간 안에 잠금을 못 잡으면 줄을 세우는 대신 ORA-00054 오류를 내고 깔끔하게 포기합니다.
4. 확인: 작업 후에는 컬럼 정보를 다시 조회해서 반영 여부를 눈으로 확인합니다.

📋 DDL 잠금 대기 시간 설정 (초 단위, 이 세션에만 적용)

ALTER SESSION SET ddl_lock_timeout = 10;

별것 아닌 ALTER 한 줄로 길게도 써 놨는데, 운영 DB 앞에서 엔터 누르기 직전인 누군가에게 도움이 되었으면 좋겠습니다. 읽어 주셔서 감사합니다.

함께 보면 좋은 글: 윈도우 11에서 oracle vbox 설치하기 절망편

참고한 자료

• Oracle SQL Language Reference — COMMIT (DDL 전후 암묵적 커밋)
• Oracle SQL Reference — ALTER SYSTEM (KILL SESSION)
• ORACLE-BASE — DDL_LOCK_TIMEOUT
• Oracle Optimizer Blog — Fine-Grained Cursor Invalidation