주문 화면이 고객 등급 하나를 확인하는 3블록짜리 조회를 하루 300만 번 보낸다

모범 답안 SQL ​

-- 키 값을 해시 함수로 블록 주소로 바꾼다 → 인덱스를 거치지 않고 1블록
-- 3년 뒤 약 4,000명을 내다보고 HASHKEYS 5000 · 고객 한 행(약 120바이트)에 맞춰 SIZE 128
CREATE CLUSTER C_CUS_HASH (CUS_ID VARCHAR2(40))
  SIZE 128
  HASHKEYS 5000;

CREATE TABLE M_CUS_HC
  CLUSTER C_CUS_HASH (CUS_ID)
AS
SELECT * FROM M_CUS;

ALTER TABLE M_CUS_HC ADD CONSTRAINT PK_M_CUS_HC PRIMARY KEY (CUS_ID);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'M_CUS_HC');

-- 점검 시간에 바꿔 단다
ALTER TABLE M_CUS    RENAME TO M_CUS_OLD;
ALTER TABLE M_CUS_HC RENAME TO M_CUS;

SELECT CUS_GD, RGN_ID      -- SQL 은 그대로
  FROM M_CUS
 WHERE CUS_ID = :v_cus;

개선 후 실행계획 학습용 예시 · 고객 테이블 규모는 schemas 그대로 ​

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |      1 |00:00:00.01 |       1 |
|*  1 |  TABLE ACCESS HASH          | M_CUS    |      1 |      1 |      1 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------
   1 - access("CUS_ID"=:V_CUS)

모범 답안 서술 ​

3블록은 PK_M_CUS의 루트 블록, 리프 블록, 그리고 ROWID로 찾아간 테이블 블록이다(BLEVEL 1이라 브랜치는 없다). 고객 2,000명의 리프는 6블록이고 테이블 블록은 30개로 나뉘지만, 루트는 하나뿐이라 300만 번 모두가 같은 루트 블록을 읽는다. 행사 날의 cache buffers chains 래치 대기는 여러 세션이 같은 블록을 동시에 읽으려 해서 생긴다. 단일 테이블 해시 클러스터로 바꾸면 CUS_ID 값을 해시 함수로 계산해 그 행이 들어 있는 블록 주소를 바로 얻는다. 인덱스를 거치지 않으므로 TABLE ACCESS HASH 한 번, 1블록으로 끝난다(Buffers 3 → 1). 하루 900만 → 300만 블록이고, 모든 세션이 몰리던 루트 블록이 없어져 조회가 고객 블록 81개로 흩어진다. HASHKEYS는 3년 뒤 고객 수(약 4,000명)를 넘게 잡고, SIZE는 고객 한 행의 크기(약 120바이트)에 맞춘다. 8KB 블록에 키 약 62개가 들어가므로 만들 때 약 81블록을 미리 잡는다. 제약과 대가는 다음과 같다. 해시 클러스터는 = 조건으로만 찾을 수 있다. CUS_ID를 범위나 LIKE로 찾으면 클러스터 전체를 읽는다. 지금은 그런 SQL이 없다. 등급별 매출 집계처럼 테이블을 FULL로 읽는 SQL은 미리 잡은 81블록을 다 읽는다(30 → 81블록). 고객이 HASHKEYS를 크게 넘으면 같은 해시 값끼리 블록을 나눠 쓰다가 체인 블록이 생겨 1블록이 2 ~ 3블록이 된다. 클러스터 테이블은 TRUNCATE TABLE과 직접 경로 적재(APPEND)를 쓸 수 없고 파티션도 할 수 없다. 가입과 정보 수정이 하루 수십 건인 마스터 테이블이라 이 대가를 받아들일 수 있다.

해설 ​

• 다른 정답: M_CUS를 IOT로 만들면 루트와 리프 2블록으로 끝나고 테이블 액세스가 없다. 범위 검색도 된다. 다만 1블록과 2블록의 차이도 하루 300만 번이면 300만 블록이고, 루트 블록에 몰리는 것은 그대로다.
• 거의 바뀌지 않는 등급을 화면마다 다시 읽는 것 자체를 줄이는 방법(애플리케이션 캐시, RESULT_CACHE 결과 캐시)도 있다. 등급이 바뀐 직후의 일관성을 어떻게 맞출지 함께 정해야 한다.
• 히트율이 99%여도 논리적 읽기 하나하나에 CPU와 래치가 든다. 3블록을 1블록으로 줄이는 일은 디스크가 아니라 CPU를 줄이는 일이다(교재 07화).
• HASHKEYS를 너무 작게 잡으면 체인으로, 너무 크게 잡으면 빈 블록으로 FULL이 커진다. 고객 수가 예상을 크게 넘으면 클러스터를 다시 만들어야 한다.

관련 교재 ​

• 07화 · 히트율 99퍼센트인데 서버가 탄다