매일 10시 정각이면 주문 화면들이 3분 동안 버벅인다

기초PLSQL이커머스600 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

ora-newshop 개발팀은 매일 10:00에 crontab으로 통계 스크립트를 돌린다. 스크립트 주석에는 이렇게 적혀 있다. "통계는 최신이 좋고, 새 통계는 바로 써야 한다." 10:00부터 10:03까지 주문 조회, 장바구니, 결제 화면의 응답이 0.1초에서 2 ~ 4초로 늘고, 10:04부터는 돌아온다. 운영팀은 CPU를 늘리자고 한다.

테이블과 인덱스

스키마 ECOMMERCE: 테이블 30개 · 가장 큰 product_views 100,000행 · 367블록, orders 34,908행 · 193블록
공유 풀에 이 스키마를 읽는 커서가 약 3,800개 있다(화면 SQL은 바인드 변수를 쓴다).
자동 통계 수집은 켜져 있다(평일 22:00부터 4시간). 스크립트를 만든 뒤로 밤의 자동 수집은 거의 할 일이 없다.
CPU 2개(Free)

원본 SQL

sql

-- crontab · 매일 10:00 (개발팀)
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(ownname          => 'ECOMMERCE',
                                 estimate_percent => 100,
                                 cascade          => TRUE,
                                 no_invalidate    => FALSE);
END;
/

실행계획 학습용 예시

xplan

[09:59 ~ 10:04 · 1분 단위 · 시스템 통계와 ASH 요약]
 시각    hard parses/s   parse time cpu / DB CPU   활성 세션   상위 대기 (ASH 표본)
 09:59        0.4                  1%                 0.6      -
 10:00       61.3                 72%                 4.8      cursor: pin S wait on X · library cache lock
 10:01       58.8                 70%                 4.5      cursor: pin S wait on X · row cache mutex
 10:02       44.1                 61%                 3.1      library cache: mutex X
 10:03       12.7                 23%                 1.2      -
 10:04        0.6                  1%                 0.6      -

[GATHER_SCHEMA_STATS 한 번 (10:00:00 ~ 10:00:41)]
 테이블 30개 · 인덱스 44개 · 그중 STALE 이던 테이블 2개 (orders · point_transactions)
 column histograms : HEIGHT BALANCED 3개 (estimate_percent 100 으로 모은 status · grade · tx_type)

[V$SQL — orders 를 읽는 주문 조회 SQL 하나]
 10:00 직전  CHILD 0 · INVALIDATIONS 4 · PLAN_HASH_VALUE 3312650782
 10:00 직후  CHILD 0 · INVALIDATIONS 5 · PLAN_HASH_VALUE 3312650782   ← 다시 파싱했지만 계획은 그대로

문제

10시 정각의 3분이 무엇 때문인지 위 숫자로 설명하시오. 통계 대부분이 거의 바뀌지 않았는데도 화면이 느려진 까닭을 쓰시오.
통계 스크립트를 고치시오. 언제, 무엇을, 어떤 무효화 옵션으로 모을지와 그 근거를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 10:00 crontab 을 지운다. 평소에는 밤 22:00 자동 수집 창에 맡긴다 (STALE 이거나 통계가 없는 것만 · 선호도대로)
-- 따로 모아야 하는 날만, 밤에 :
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(ownname       => 'ECOMMERCE',
                                 options       => 'GATHER AUTO',
                                 no_invalidate => DBMS_STATS.AUTO_INVALIDATE);
END;
/
-- estimate_percent 는 주지 않는다 (기본값 AUTO_SAMPLE_SIZE)
-- 한 테이블의 틀린 통계를 고쳐 그 계획을 '지금' 바꿔야 할 때만 그 테이블에 no_invalidate => FALSE

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

xplan

[다음 날 10:00]  hard parses/s 0.4 · parse time cpu 1% · 상위 대기 없음
[22:00 자동 수집 창]  STALE 테이블 2개 · 3.1초 · 무효화는 기본값 (의존 커서는 그 뒤 몇 시간에 걸쳐 하나씩 다시 파싱)

모범 답안 서술

10:00:00에 스크립트가 30개 테이블의 통계를 no_invalidate => FALSE로 다시 썼다. 통계를 쓰는 순간 그 테이블을 읽는 커서가 모두 무효화된다. 그래서 공유 풀의 커서 약 3,800개가 다음 실행 때 한꺼번에 하드 파싱되어 hard parses가 0.4 → 61.3/s로 뛰고, parse time이 DB CPU의 72%를 차지했다. 같은 SQL을 여러 세션이 동시에 실행하면 한 세션이 파싱하는 동안 나머지가 cursor: pin S wait on X로 기다린다. CPU 2개에서 활성 세션이 4.8개이므로 화면이 2 ~ 4초로 늘었다. 더 아까운 것은 대부분이 헛일이었다는 점이다. 30개 가운데 STALE이던 테이블은 2개뿐이었고, 다시 파싱한 주문 조회 SQL의 계획 해시 값은 그대로였다. 통계는 거의 그대로인데 계획을 다시 세우는 비용만 피크 시간에 치렀다. 고친 스크립트는 낮의 crontab을 지우고 밤 22:00 자동 수집 창에 맡긴다. 자동 수집은 STALE이거나 통계가 없는 것만, 선호도대로 모은다. 따로 돌려야 하는 날에도 밤에 GATHER AUTO로 모으고, 무효화는 기본값 AUTO_INVALIDATE로 둔다. 그러면 의존 커서를 한 번에 무효화하지 않고 몇 시간(기본 5시간)에 걸쳐 나눠 흩으므로 다시 파싱하는 일도 흩어진다. no_invalidate => FALSE는 한 테이블의 틀린 통계를 고쳐 그 계획을 지금 바꿔야 할 때 그 테이블에만 쓴다. estimate_percent => 100도 뺀다. 테이블을 전부 읽어 느리고, 표본을 비율로 지정하면 히스토그램이 구형 HEIGHT BALANCED로 만들어진다. 기본값 AUTO_SAMPLE_SIZE가 빠르면서 NDV도 거의 정확하다. CPU 증설은 답이 아니다. 피크 시간의 헛 파싱을 없애면 지금 CPU로 충분하다.

해설

no_invalidate의 세 값: FALSE는 즉시 무효화, TRUE는 무효화하지 않음(새 통계는 커서가 밀려나야 쓰인다), AUTO_INVALIDATE(기본값)는 다음 실행부터 일정 시간 안에 하나씩 나눠 무효화한다.
즉시 무효화가 맞는 때도 있다. 빈 테이블 통계 하나 때문에 핵심 화면이 무너진 아침이라면, 그 테이블 하나를 고치고 바로 바꾸는 것이 맞다(OPT-001, OPT-002). 스키마 전체의 정기 수집과는 다르다.
10시를 고른 이유를 물어보면 대개 "출근해서 확인하려고"다. 확인은 밤에 모으고 아침에 USER_TAB_STATISTICS의 LAST_ANALYZED로 하면 된다.
하드 파싱의 값은 11화에서 잰 대로다(3만 번 클릭 중 CPU의 96%가 파싱). 이번에는 리터럴 SQL이 아니라 통계 수집이 파싱을 불렀다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황