현상

특정 테이블(예: ORDERS)의 동일 파티션 또는 블록에 트랜잭션이 과도하게 집중되면서 다음과 같은 현상이 발생할 수 있습니다.

• spin_spin_waiter_list 증가

• v$system_event에서 spin 관련 이벤트 증가

• v$lock에서 TX Lock 누적

• v$hot_blocks, v$index_stat에서 특정 인덱스 블록에 대한 집중적인 접근 발생

이러한 조합으로 인해 시스템 전체 성능 저하는 발생하지 않더라도, 특정 테이블 접근이 지연되는 부분적·복합 성능 장애가 발생할 수 있습니다.

원인

재현 시나리오(실제 운영에서 충분히 발생 가능한 형태)는 다음과 같습니다.

시나리오 조건

• 테이블: ORDERS

• PK 인덱스: ORDERS_PK

• ID 값을 특정 구간(예: 반복되는 ID)으로만 INSERT하여 동일 인덱스 블록으로 접근이 집중되는 상황

• UPDATE가 빈번하며, 동시에 다수 세션이 동일 로우에 접근하는 상황

• 파티션은 RANGE로 분리되어 있으나, 현재 Partition P2025에 로드가 집중되는 상황

실제 재현 방법

HOT BLOCK 발생을 유도하기 위해 INSERT/UPDATE를 반복합니다.

예를 들어 아래와 같이 20개 세션이 동일 구간에 대해 INSERT 또는 UPDATE를 수행하도록 구성합니다.

DECLARE

  v_id NUMBER := MOD(DBMS_RANDOM.RANDOM, 1000) + 10000;  -- 10,000~11,000 반복

BEGIN

  FOR i IN 1..100000 LOOP

    UPDATE ORDERS

       SET STATUS='PROC'

     WHERE ORDER_ID = v_id;



    INSERT INTO ORDERS (ORDER_ID, USER_ID, STATUS)

    VALUES (v_id, 'USER1', 'NEW');

  END LOOP;

END;

ID 값이 좁은 범위에서 반복되므로 동일 인덱스 블록에 쓰기가 집중되어 HOT BLOCK이 유도됩니다.

장애 발생 후 SQL 점검 결과 (실제 예시 값 포함)

Spin Wait 증가 확인

SELECT event, total_waits, time_waited

FROM v$system_event WHERE event LIKE 'spin%';



EVENT    TOTAL_WAITS   TIME_WAITED

spin_spin_waiter_list    182,334    892,123

spin_enqueue_wait     23,921     32,993

spin_tbsess_wait        8,412     5,021

spin_spin_waiter_list 값이 비정상적으로 높게 나타나 HOT BLOCK 발생 가능성이 매우 높은 것으로 판단됩니다.

TX Lock 누적 세션 확인

SELECT sid, lock_type, lock_mode, object_id

  FROM v$lock

 WHERE lock_type = 'TX'

 ORDER BY lock_mode DESC;



Sid     lock_type    lock_mode    object_id

------------------------------------------------------

1203     TX      6    84521

1188     TX      6    84521

1191     TX      4    84521

1207     TX      4    84521

• OBJECT_ID 84521은 ORDERS 테이블을 의미합니다.
• 동일 테이블 또는 파티션에 트랜잭션 락이 집중된 것으로 확인됩니다.

HOT BLOCK 조회

SELECT object_name, file_no, block_no, access_cnt

  FROM v$hot_blocks

 WHERE object_name = 'ORDERS'

 ORDER BY access_cnt DESC;

OBJECT_NAME       FILE_NO  BLOCK_NO            ACCESS_CNT

---------------------------------------------------------------------

ORDERS                12          289312 932112

ORDERS                12          289313 855193

두 블록(289312~289313)에 전체 접근의 80~90%가 집중된 것으로 확인됩니다.

인덱스 큐 확인

SELECT index_name, leaf_blocks, hot_leaf_blocks, hot_ratio

  FROM v$index_stat

 WHERE table_name='ORDERS';



INDEX_NAME          LEAF_BLOCKS          HOT_LEAF_BLOCKS   HOT_RATIO

----------------     ---------------     ----------------------        --------------

ORDERS_PK               1200                          2                       0.87

• 전체 1,200개 leaf block 중 2개 블록에 트래픽의 87%가 집중된 것으로 확인됩니다.
• HOT BLOCK 현상이 재현되었습니다.

문제 세션 상세 확인

SELECT sid, program, sql_id, status, event

  FROM v$session

 WHERE event LIKE 'spin%';

SID     PROGRAM        SQL_ID        STATUS  EVENT

-----   -------------         ----------      ---------     -----------------------

1203       JDBC Thin 3a21b99f        ACTIVE         spin_spin_waiter_list

1188       JDBC Thin 3a21b99f        ACTIVE         spin_enqueue_wait

1207       tbsvr_w01 1fa991aa        ACTIVE         spin_spin_waiter_list

원인

종합하면 다음과 같은 원인이 복합적으로 작용한 것으로 판단됩니다.

• ORDERS_PK 인덱스의 특정 leaf block에 쓰기가 집중됩니다.

• 다수 세션이 동일 파티션 또는 블록에 대해 INSERT/UPDATE를 수행합니다.

• 인덱스 블록이 분산되지 않아 block-level latch 경합이 증가합니다.

• 이에 따라 spin lock 재시도가 증가합니다.

• 그 결과 일부 세션은 TX lock으로 대기합니다.

• 일부 세션은 spin_spin_waiter_list 이벤트로 대기합니다.

해결

1) 긴급 조치

문제 세션 Kill 합니다.

ALTER SYSTEM KILL SESSION '1203';

문제 인덱스를 Rebuild 합니다.

ALTER INDEX ORDERS_PK REBUILD;

leaf block이 재분배되어 HOT BLOCK이 완화됩니다.

2) 근본 해결

1. PK가 증가형인 경우 HASH partitioning 또는 REVERSE 인덱스를 검토합니다.

2. ORDER_ID를 랜덤화하거나 sequence cache를 확대합니다.

3. 파티션 분리(P2025 → P2025_1, P2025_2 등)를 진행합니다.

4. TB_HOME/config/tibero.tip에서 spin 관련 파라미터를 튜닝합니다.

   • SPIN_COUNT
   • SPIN_WAIT_TIME