매시 정각이 되면 1~2분 동안 매장 POS 결제가 멈칫한다

중급SELECT버거557 / 700

상황

버거 본사 DB에는 2014년 겨울 ORA-04031이 난 뒤 DBA가 걸어 둔 스케줄러 잡이 있다. 매시 정각에 공유 풀을 비우는 잡이다. 그 뒤로 04031은 다시 나지 않았다. 요즘 매장들에서 정각마다 1~2분씩 결제가 멈칫한다는 연락이 온다. 점심 12시 정각이 가장 심하다. 오과장이 물었다. "공유 풀을 비우고 나면 오히려 깨끗해야 하는 거 아닌가요?"

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_X01 (ORD_DT, ORD_HMS) · SHOP_NO는 'SH0001' 모양(매장 200곳)
매장마다 POS 2대(DB 세션 400개)와 주방 모니터 1대가 붙어 있다. POS 문장은 바인드 변수로 쓰여 있다.
주방 모니터는 3초마다 "마지막으로 본 시각 이후의 주문"을 읽는다.
CURSOR_SHARING = EXACT · 공유 풀 2GB

원본 SQL

sql

-- 스케줄러 잡 FLUSH_SP_HOURLY (2014년 DBA) : 매시 정각
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'FLUSH_SP_HOURLY',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN EXECUTE IMMEDIATE ''ALTER SYSTEM FLUSH SHARED_POOL''; END;',
    repeat_interval => 'FREQ=HOURLY; BYMINUTE=0; BYSECOND=0',
    enabled         => TRUE);
END;

-- 주방 모니터 : 3초마다 · 매장, 날짜, 마지막으로 본 시각을 문자열로 이어 붙인다
SELECT ORD_NO, ORD_HMS
  FROM ORD
 WHERE SHOP_NO = 'SH0042'
   AND ORD_DT  = '20131231'
   AND ORD_HMS > '121503'
 ORDER BY ORD_HMS

실행계획 학습용 예시

xplan

[V$SYSSTAT 1분 차분 · parse count (hard)]
  11:58     4,260
  11:59     4,310
  12:00     5,940    ← 공유 풀을 비운 직후
  12:01     4,480
  12:02     4,290

[ASH · 12:00:00 ~ 12:01:30 · 활성 세션 표본 (평소 같은 시간대의 평균 활성 세션은 6명 안팎)]
  이벤트                         표본 비율   평균 활성 세션
  cursor: pin S wait on X            34%           41
  library cache: mutex X             17%           20
  latch: shared pool                 12%           14
  row cache mutex                     8%           10
  CPU                                22%           26

[POS 결제 INSERT 응답 시간]  평소 p99 0.02초 · 12:00 ~ 12:01 p99 3.8초

[V$SQLAREA · 11:59 (비우기 직전) · FORCE_MATCHING_SIGNATURE로 묶은 상위 무리]
  PARENTS   EXECS     MB   SAMPLE
    3,940   3,960   57.8   SELECT ORD_NO, ORD_HMS FROM ORD WHERE SHOP_NO = 'SH0042' AND ORD_DT = '20131231' AND ORD_HMS > '115904' ORDER BY ORD_HMS
       11     240    0.2   SELECT ITEM_NM, SALE_YN FROM ITEM WHERE ITEM_TYPE_CD = '10' …

[주방 모니터 한 번 실행의 계획]
----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |      1 |00:00:00.01 |       3 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| ORD     |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN          | ORD_X01 |      1 |      7 |      2 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("SHOP_NO"='SH0042')
   2 - access("ORD_DT"='20131231' AND "ORD_HMS">'121503')

문제

정각마다 결제가 멈칫하는 이유를 ASH와 분별 하드 파싱 수로 설명하시오. 하드 파싱 수는 크게 늘지 않았는데 세션이 몰린 까닭도 쓰시오.
2014년의 ORA-04031을 부른 원인을 위 자료에서 찾아 고치고, 스케줄러 잡을 어떻게 할지 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- ① 주방 모니터 : 매장, 날짜, 마지막으로 본 시각은 바인드 변수로 (문장은 하나)
SELECT ORD_NO, ORD_HMS
  FROM ORD
 WHERE SHOP_NO = :shop_no
   AND ORD_DT  = :ord_dt
   AND ORD_HMS > :last_hms
 ORDER BY ORD_HMS;

-- ② 주방 모니터 배포를 확인한 다음 날, 매시 정각의 공유 풀 비우기를 멈춘다
BEGIN
  DBMS_SCHEDULER.DISABLE('FLUSH_SP_HOURLY');
END;

-- ③ 그 뒤 한 주 동안 지켜본다 : 공유 풀 여유와 리터럴만 다른 문장 무리
SELECT pool, name, ROUND(bytes / 1048576) AS mb
  FROM v$sgastat
 WHERE pool = 'shared pool' AND name = 'free memory';

SELECT force_matching_signature, COUNT(*) AS parents, SUM(executions) AS execs
  FROM v$sqlarea
 WHERE force_matching_signature <> 0
 GROUP BY force_matching_signature
HAVING COUNT(*) >= 50
 ORDER BY parents DESC;

개선 후 실행계획 학습용 예시

xplan

[고친 뒤 (학습용 예시)]
  parse count (hard) 1분 차분      11:58 3 · 11:59 4 · 12:00 4   (잡을 멈춘 뒤)
  주방 모니터 문장                  부모 1 · 실행 시간당 약 24만
  ASH 12:00 ~ 12:01                cursor: pin S wait on X 없음 · 평균 활성 세션 6명
  POS 결제 p99                     12:00에도 0.02초
  shared pool free memory          한 주 내내 310 ~ 380MB

모범 답안 서술

정각의 하드 파싱은 분당 4,310 → 5,940으로 조금 늘었을 뿐인데, 활성 세션은 평소 6명에서 100명 넘게 몰렸고 맨 위 대기는 cursor: pin S wait on X다. 공유 풀을 비우면 리터럴 문장만이 아니라 POS가 쓰던 바인드 문장까지 모두 나간다. 정각이 지나면 POS 400개 세션이 같은 결제 문장과 메뉴 문장을 거의 동시에 다시 찾는데, 커서가 없으니 한 세션이 하드 파싱을 하고 나머지는 그 세션이 커서를 다 만들 때까지 기다린다. 그 기다림이 cursor: pin S wait on X다. 공유 풀에 새 자리를 만들고(latch: shared pool) 딕셔너리 캐시를 다시 채우느라(row cache mutex) 줄이 더 길어진다. 하드 파싱의 개수보다 같은 문장을 같은 순간에 기다리는 세션 수가 문제다. 그래서 결제 p99가 0.02초 → 3.8초가 된다. 04031을 부른 원인은 비우기 직전 V$SQLAREA 맨 위의 무리다. 주방 모니터가 마지막으로 본 시각(ORD_HMS)을 문자열로 이어 붙이므로 폴링할 때마다 처음 보는 문장이 된다. 매장 200곳이 3초마다 부르니 초당 약 67개, 한 시간이면 부모 3,940개(57.8MB)가 공유 풀에 남아 있고 그보다 훨씬 많은 문장이 지나갔다. 평소 분당 하드 파싱 4,300번도 거의 이 몫이다. 매장, 날짜, 시각을 모두 바인드 변수로 넘기면 문장은 하나가 되고 공유 풀을 채우던 것이 사라진다. 공유 풀 비우기는 증상을 가리는 처방이었다. 원인을 없앤 뒤에는 잡을 멈춘다. 주방 모니터 배포를 확인한 다음 날 DBMS_SCHEDULER.DISABLE로 끄고, 한 주 동안 V$SGASTAT의 shared pool free memory와 FMS 무리를 지켜본다. 정각의 하드 파싱은 분당 4건 안팎이 되고, 결제 p99도 정각에 0.02초를 지킨다.

해설

공유 풀 비우기는 모두에게 하드 파싱을 시킨다. 리터럴 문장 몇천 개를 치우려고 잘 쓰던 바인드 문장 수백 개까지 버리는 셈이다. 운영 중에 쓰는 처방이 아니다.
바인드 변수로 바꾼 뒤에도, 수백 세션이 함께 쓰는 커서가 무효화되거나 밀려나는 순간에는 같은 줄이 생긴다. 업무 시간의 통계 수집과 DDL을 조심해야 하는 이유다(PARSE-014).
ORA-04031이 다시 나면 공유 풀 크기보다 먼저 리터럴 무리와 V$SHARED_POOL_RESERVED(큰 요청이 실패했는지)를 본다. 교재 11화의 실험에서는 3만 개를 넣어도 공유 풀이 04031 대신 밀어내기로 버텼다.
폴링은 바인드 변수로 바꾼 뒤에도 실행 수가 많다(시간당 약 24만). 화면이 3초마다 물어야 하는지, 새 주문이 생길 때만 알리는 방식이 맞는지도 협력사와 이야기할 거리다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황