점심 피크마다 CPU가 100%인데, 하드 파싱은 거의 없고 라이브러리 캐시 대기가 1위다

실전DDL버거560 / 700

상황

버거 본사 DB는 점심 11:30 ~ 13:00에 CPU가 100%에 붙는다. 하드 파싱 문제는 지난달에 정리해서 이제 거의 없는데도 그렇다. AWR 리포트의 대기 1위는 library cache: mutex X다. 회의에 대안 다섯이 올라왔다.

① 인프라팀: CPU를 16코어에서 32코어로 늘린다.
② DBA: 공유 풀을 3GB에서 6GB로 늘린다.
③ POS 협력사: CURSOR_SHARING = FORCE를 켠다.
④ 선배: SESSION_CACHED_CURSORS를 50에서 200으로 올린다.
⑤ POS 개발사: POS 프로그램에 문장 캐시(ODP.NET Statement Cache Size 0 → 150)를 켜고 재배포한다(2주 걸린다).

테이블과 인덱스

POS 단말 400대(매장 200곳 × 2대)가 저마다 DB 세션 하나로 붙는다(2계층, ODP.NET). 세션은 개점할 때 접속해서 마감까지 붙어 있다.
주문 한 건에 SQL을 약 25번 실행한다(ITEM 메뉴 조회, SHOP 매장 조회, ORD 1건, ORD_ITEM 2~3건 등). 점심 흐름에서 세션 하나가 되풀이해 쓰는 문장은 약 130개다.
모든 문장이 바인드 변수로 쓰여 있다. POS 프로그램은 실행할 때마다 명령 객체를 만들고 실행한 뒤 버린다(문장 캐시 0).
cpu_count = 16 · 공유 풀 3GB · session_cached_cursors = 50 · open_cursors = 300 · CURSOR_SHARING = EXACT

원본 SQL

sql

// PosOrder.cs : 명령을 실행할 때마다 새로 만들고 버린다 (POS 문장 130개가 모두 이 모양)
using (var cmd = new OracleCommand("SELECT ITEM_NM, ITEM_TYPE_CD, SALE_YN FROM ITEM WHERE ITEM_ID = :item_id", conn))
{
    cmd.Parameters.Add("item_id", OracleDbType.Int32).Value = itemId;
    using (var rd = cmd.ExecuteReader()) { /* 메뉴 한 건 */ }
}

// 연결 문자열 : Statement Cache Size를 지정하지 않았다 (0)
// "User Id=pos;Data Source=BURGER;Pooling=false"

-- 지금 파라미터
-- session_cached_cursors = 50 · open_cursors = 300 · shared_pool_size = 3G · cpu_count = 16

실행계획 학습용 예시

xplan

[AWR · 12:00 ~ 13:00 · Load Profile (초당)]
  DB Time                  38.2
  DB CPU                   14.1
  Executes (SQL)        6,240
  Parses (SQL)          6,180
  Hard parses (SQL)         0.3
  Logical read         41,050

[Instance Efficiency]
  Soft Parse %             99.99
  Execute to Parse %        0.96
  % Non-Parse CPU          63.40
  Library Hit %            99.98

[Top Foreground Events]
  이벤트                        대기(초)   % DB time
  library cache: mutex X         51,570        37.5
  DB CPU                         50,760        36.9
  cursor: pin S                  19,530        14.2
  cursor: mutex S                 4,260         3.1
  log file sync                   3,300         2.4

[Instance Activity · 같은 한 시간]
  execute count                    22,464,000
  parse count (total)              22,248,000
  parse count (hard)                    1,080
  session cursor cache hits         2,470,000    (파싱의 11%)
  session cursor cache count        세션마다 50 (설정값에 붙어 있다)

[SQL ordered by Parse Calls · 상위 셋]
  Parse Calls   Executions   SQL
    1,612,400    1,612,400   SELECT ITEM_NM, ITEM_TYPE_CD, SALE_YN FROM ITEM WHERE ITEM_ID = :item_id
    1,108,900    1,108,900   INSERT INTO ORD_ITEM (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS) VALUES (:1, :2, :3, :4, :5)
      402,300      402,300   SELECT SHOP_NM FROM SHOP WHERE SHOP_NO = :shop_no

문제

하드 파싱이 거의 없는데 라이브러리 캐시 대기가 1위인 이유를 Instance Efficiency와 Instance Activity의 숫자로 설명하시오.
대안 다섯을 각각 효과와 부작용으로 평가하시오.
당장 할 일과 다음에 할 일을 정하고, 필요한 설정과 반영 방법을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 당장 (이번 주 일요일 정기 재기동) : 세션 커서 캐시를 세션이 되풀이해 쓰는 문장 수(130)보다 크게
ALTER SYSTEM SET SESSION_CACHED_CURSORS = 200 SCOPE = SPFILE;

-- 재기동 전까지 : POS 계정이 접속할 때만 세션 값으로 (POS가 다시 접속하는 다음 개점부터 적용)
CREATE OR REPLACE TRIGGER TRG_POS_SCC AFTER LOGON ON DATABASE
BEGIN
  IF SYS_CONTEXT('USERENV', 'SESSION_USER') = 'POS' THEN
    EXECUTE IMMEDIATE 'ALTER SESSION SET SESSION_CACHED_CURSORS = 200';
  END IF;
END;

// 다음 (POS 재배포) : 문장 캐시를 켜서 파스 호출 자체를 없앤다 · 캐시 150 ≤ open_cursors 300
// "User Id=pos;Data Source=BURGER;Pooling=false;Statement Cache Size=150"

-- 하지 않는 것 : CPU 증설 보류 · 공유 풀 3GB 그대로 · CURSOR_SHARING = EXACT 그대로

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

xplan

[스테이징 재현 · POS 40세션 · 점심 흐름 30분 (학습용 예시)]
                                       지금(50)    ④ 200    ④ + ⑤ 문장 캐시 150
  Parses (초당, 40세션)                    618        618                 6
  session cursor cache hits 비율           11%        97%                 —
  library cache: mutex X (% DB time)       37%         9%           1% 미만
  cursor: pin S (% DB time)                14%         5%           1% 미만
  DB CPU (실행 1,000번당 초)               0.63       0.41              0.29
  세션 하나의 메모리 증가                     —     약 0.6MB           약 2MB

[③ FORCE · ② 공유 풀 6GB (같은 재현)]  Hard parses, 대기, CPU 모두 지금과 차이 없음 (오차 범위)

모범 답안 서술

왜 라이브러리 캐시인가: 하드 파싱은 초당 0.3번(Soft Parse 99.99%)이라 문제가 아니다. 문제는 파스 호출 수다. Executes 6,240에 Parses 6,180으로 Execute to Parse가 0.96%이고, SQL ordered by Parse Calls의 상위 문장도 Parse Calls와 Executions가 같다. POS 프로그램이 실행할 때마다 명령을 새로 만들어 버리므로 실행할 때마다 파스 호출이 오고, 오라클은 소프트 파싱을 한다. 세션 커서 캐시가 받아 주면 가볍게 끝나지만 적중은 파싱의 11%뿐이다. 세션 하나가 점심에 되풀이해 쓰는 문장이 130개인데 캐시는 50칸이라, 꽉 찬 캐시에서 밀려났다 다시 들어오기를 되풀이한다. 나머지 89%는 해시 체인을 따라가 커서를 찾으며 뮤텍스를 잡는데, 400개 세션이 같은 문장(메뉴 조회, 주문상품 INSERT)을 동시에 찾으니 library cache: mutex X와 cursor: pin S에서 줄을 선다. 뮤텍스를 기다리며 도는(spin) 시간도 CPU로 잡히므로 CPU가 100%에 붙는다. 대안 평가: ① CPU 증설은 도는 시간을 받아 줄 뿐 줄을 없애지 않는다. 세션들이 같은 뮤텍스를 다투는 한 코어를 늘린 만큼 빨라지지 않고, 비용이 크다. 원인을 고친 뒤 다시 따질 일이다. ② 공유 풀 증설은 효과가 없다. Library Hit 99.98%, 하드 파싱 0.3번이므로 커서는 이미 공유 풀에 다 있다. 찾는 횟수가 문제지 자리가 문제가 아니다. ③ FORCE도 효과가 없다. 모든 문장이 이미 바인드 변수라 바꿀 리터럴이 없고, 서버 전체에 부작용만 남긴다(PARSE-003). ④ 세션 커서 캐시 200은 효과가 있다. 130개가 다 들어가므로 재현에서 적중이 11% → 97%, library cache: mutex X가 37% → 9%로 줄었다. 시스템 수준 값은 SCOPE = SPFILE과 재기동이 필요하고, 세션마다 메모리를 조금 더 쓴다(약 0.6MB). 파스 호출 수는 그대로다. ⑤ 문장 캐시는 효과가 가장 크다. POS가 명령을 재사용하므로 파스 호출 자체가 초당 618 → 6으로 사라지고 뮤텍스 대기도 1% 미만이 된다. 대신 재배포에 2주가 걸리고, 세션마다 커서를 150개까지 열어 두므로 open_cursors(300) 안에 드는지 확인해야 한다. 결정: 당장은 ④를 한다. 이번 주 정기 재기동에 맞춰 SESSION_CACHED_CURSORS = 200을 SPFILE에 쓰고, 그 전까지는 POS 계정의 로그온 트리거로 세션 값을 바꾼다(POS가 다시 접속하는 다음 개점부터 적용된다). 다음으로 ⑤를 재배포 일정에 넣는다. ①②③은 하지 않는다. ⑤까지 반영한 뒤에도 CPU가 모자라면 그때 ①을 숫자로 다시 따진다.

해설

Soft Parse %가 99.99%라고 파싱이 건강한 것은 아니다. 그 비율은 파싱 가운데 하드 파싱의 몫만 본다. 파스 호출이 실행만큼 많은지는 Execute to Parse %에서, 그 파싱이 CPU를 얼마나 쓰는지는 % Non-Parse CPU에서 본다(교재 16화 §5).
세션 커서 캐시는 세션이 되풀이해 쓰는 문장 수에 맞춘다. session cursor cache count가 설정값에 붙어 있고 적중 비율이 낮으면 모자란 것이다. 그렇다고 수천으로 키우면 세션 메모리만 는다.
파싱 네 가지로 보면 ④는 소프트 파싱을 세 번째 단계(캐시 적중)로, ⑤는 네 번째 단계(열어 둔 채 다시 실행)로 옮긴다(교재 11화 §2). DB 설정으로 할 수 있는 것은 세 번째까지다.
2계층 POS처럼 세션이 하루 종일 붙어 있는 구조에서는 로그온 트리거가 다음 접속부터만 듣는다. 반영 시점을 매장 개점 시각과 맞춰 적어 둔다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황