간단한 쿼리가 굉장히 느린 현상

고승원·2023년 9월 27일

TIL

목록 보기

21/24

간단한 쿼리가 굉장히 느린 현상 (오라클)

상황

update ORDER_DETAILS
set STATUS = "asdf,
    MODIFYDATE = sysdate
where ORDERCD = 'asdf';

이렇게 간단한 쿼리가 10분이 지나도 완료되지 않는다.

ORDERCD는 PK여서 원래 수십 ms면 끝나는 쿼리인데 말이다.

DataGrip과 컴퓨터를 재시작해도 변함없었다.

데드락인가 해서 찾아봐도 데드락이 감지되지 않았다.

원인

가설

처음 의심한 것은 DB 서버가 많은 트래픽을 받아서 느려진거라고 생각했다.

무슨일인가 해서 다른 쿼리들을 실행해보니 똑같이 느렸기 때문이다.

따라서 DB서버 리소스 사용량을 확인했다.

CPU를 창조해서 쓰고 있는 모습.

결론부터 말하면 이 가설은 틀렸다. DB는 충분히 감당할 수 있을만큼의 트래픽을 감당하고 있었다.

해결

해당 테이블에 대한 락이 굉장히 많이 걸려 있었다.

무슨 일인지 모르지만 해당 테이블에 락이 굉장히 많이 잡혀있었다.

조회 쿼리는 다음과 같고, 조회하니 10개가 넘게 있었다.

조회 테이블

SELECT 
							"V$LOCK"."TYPE"
            , "V$SESSION"."SID"
            , "V$SESSION"."SERIAL#"
            , "V$SESSION"."MACHINE"
            , "V$SESSION"."TERMINAL"
            , "V$SESSION"."OSUSER"
            , "V$SESSION"."USERNAME"
            , "V$SESSION"."PROGRAM"
            , "OBJ"."OBJECT_NAME"
            , "V$LOCK"."ID1"
            , "V$LOCK"."REQUEST"
            , "V$SESSION"."STATUS"
       FROM "OBJ"
          , "V$LOCK"
          , "V$SESSION"
       WHERE (v$session.sid = v$lock.sid)
         AND (v$lock.id1 = obj.object_id (+))
         AND ("V$SESSION"."USERNAME" is not
         null)

OBJ

객체에 대한 정보를 포함하는 시스템 카탈로그 테이블이다. 객체의 유형과 소유자를 알기위해 조인했다.

V$LOCK

현재 활성화된 락 정보를 제공하는 동적 성능 뷰다. 락이 발생한 세션, 락 유형, 락 모드 등의 정보를 조회할 수 있다.

V$SESSION

세션에 대한 정보를 제공하는 동적 성능 뷰다. 데이터베이스에 연결된 세션의 상태, 세션 ID, 사용자, 프로세스 ID 등의 정보를 확인할 수 있다.

위 쿼리를 실행하면 어떤 세션이 어떤 테이블에 어떤 락을 걸었는지 볼 수 있다.

Type

여기서 Type은 락의 유형을 뜻한다.

TM (DML enqueue): 데이터 수정 작업에 대한 락
TX (Transaction enqueue): 트랜잭션에 대한 락
UL (User-defined Lock): 사용자가 정의한 락
TM-6 (Distributed Transaction enqueue): 분산 트랜잭션에 대한 락
CF (Control File enqueue): 제어 파일(Oracle의 내부 파일)에 대한 변경이나 액세스 시 발생하는 락
HW (High Water Mark enqueue): 데이터 블록의 높은 물리적 경계를 변경할 때 발생한다.
BL (Buffer Cache): 데이터 버퍼 캐시에 대한 락을 나타냅니다. 데이터 블록을 메모리에 로드하거나 수정할 때 발생한다.
DX (Distributed Transaction): 분산 트랜잭션에서 사용되는 락을 나타냅니다. 분산 환경에서 트랜잭션을 관리하는 데 필요한 락

해결

해당하는 세션을 kill하면 된다

ALTER SYSTEM KILL SESSION 'SID,SERIAL';

관련된 락을 해제하고 실행하니 바로 빠르게 처리되었다.

마무리

이 문제를 찾아보며 총 세가지 궁금증이 들었다.

데드락이 아닌데 느려진 이유:
- 데드락은 다른 세션 간에 서로의 락을 기다리는 상황인데, 여기서는 데드락의 조건 4가지가 충족되지 않아 데드락이 아니다.
- 대량의 업데이트를 하고 있는경우 느려질 수 있다.
재시작을 했음에도 세션이 끝나지 않은 이유:
- Oracle 세션은 데이터베이스에서의 세션 연결을 나타내며, 데이터그립을 재시작한다고 해서 이러한 세션들이 즉시 종료되지는 않는다.
- 일반적으로 세션은 사용자가 명시적으로 로그아웃하거나 세션 타임아웃 (SQLNET.EXPIRE_TIME)에 따라 종료된다.
expire_time을 넘도록 살아있을 수 있는 이유:
- 세션이 종료되려면 해당 세션의 활동이 있어야 한다.
- 세션은 SQL 쿼리를 실행하거나 다른 활동을 수행하지 않으면 계속해서 활성 상태를 유지할 수 있는데, 대기하고 있는 세션들이 활동중이 아니어서 죽지 않았던 것.