5년 만에 통계를 다시 모으자는데 핵심 화면 여섯 개가 어떻게 바뀔지 모른다

실전PLSQL유통606 / 700

상황

본사 레거시 DB는 2016년에 자동 통계 수집을 끄고, 손으로 적은 목록만 밤마다 모아 왔다. 점검해 보니 PUP 스키마 18개 테이블 가운데 7개의 통계가 2019년 3월 이관 때의 것이다. 선배는 오늘 밤 모두 다시 모으고 자동 수집도 켜자고 했다. 본사 IT 팀장의 조건은 이렇다. "계획이 바뀌는 화면이 있으면 미리 알려 주고, 문제가 생기면 바로 되돌릴 수 있어야 합니다." 조민우는 어제 03:00의 운영 사본(복사본)에서 먼저 다시 모아 핵심 SQL 여섯 개를 돌려 보았다.

테이블과 인덱스

PUP 스키마 18개 테이블 · 통계가 2019-03의 것인 테이블 7개: CF02 · CF05 · CF07 · CF11(실제 1,096,708행 · 통계 109,860) · CF12 · CF13 · CF15
핵심 SQL 여섯 개: 화면 S1 ~ S4, 배치 S5와 S6 (빈도는 아래 표)
통계 이력 보존 31일 · PUBLISH 기본값 TRUE · 자동 통계 수집 꺼짐

원본 SQL

sql

-- 선배의 첫 안 (오늘 밤 23:00)
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(ownname       => 'PUP',
                                 options       => 'GATHER',
                                 no_invalidate => FALSE);
  DBMS_AUTO_TASK_ADMIN.ENABLE('auto optimizer stats collection', NULL, NULL);
END;
/

실행계획 학습용 예시

xplan

[복사본(어제 03:00 운영 사본)에서 18개를 다시 모은 뒤 · 핵심 SQL 여섯 개 · 같은 바인드 값]
 SQL  무엇 (하루 빈도)                  지금 계획                 Buffers   다시 모은 뒤 계획            Buffers
 S1   점장 「어제 상품별 판매」 (600)     HASH JOIN · CF11 FULL       8,215   NL · CF11_PK                     389
 S2   「일별 판매 건수」 (3,000)           INDEX RANGE SCAN                5   그대로                             5
 S3   「상품 판매 순위」 (40)              HASH JOIN · CF11 FULL       8,210   그대로                         8,210
 S4   「발주 진행」 (2,000)                NL · CF13_PK                   38   HASH JOIN · CF13 FULL             61
 S5   「입출고 대사」 배치 (1)             NL · CF15_PK                1,410   HASH JOIN · CF15 FULL            406
 S6   「일 재고 마감」 배치 (1)            INDEX RANGE SCAN                4   그대로                             4

 · 복사본은 하루 전 데이터이고, 운영의 바인드 값 분포와 공유 풀 상태를 그대로 갖고 있지 않다.
 · 복사본에서 18개를 모으는 데 41초 · 공유 풀의 PUP 커서 약 2,100개

문제

선배의 첫 안을 그대로 돌릴 때의 위험을 두 가지 이상 쓰시오.
운영에서 새 통계를 공개하기 전에 미리 보고, 고른 시각에 공개하고, 문제가 생기면 되돌리는 절차를 SQL로 쓰시오.
S4처럼 일량이 조금 늘어나는 SQL을 어떻게 판단하고 기록할지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ⓪ 되돌릴 곳 : 지금 통계를 따로 떠 둔다 (이력 31일과는 별도로 · 기한 없이)
BEGIN
  DBMS_STATS.CREATE_STAT_TABLE('PUP', 'STATS_BAK_0612');
  DBMS_STATS.EXPORT_SCHEMA_STATS('PUP', stattab => 'STATS_BAK_0612');
END;
/

-- ① 모으되 공개하지 않는다 (보류 통계 · 다른 세션은 지금 통계를 그대로 쓴다)
BEGIN
  DBMS_STATS.SET_SCHEMA_PREFS('PUP', 'PUBLISH', 'FALSE');
  DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'PUP', options => 'GATHER AUTO');   -- STALE 이거나 없는 것만
END;
/

-- ② 운영에서, 내 세션만 보류 통계로 S1 ~ S6 을 돌려 본다 (DISPLAY_CURSOR ALLSTATS LAST 를 지금 판과 나란히)
ALTER SESSION SET optimizer_use_pending_statistics = TRUE;
-- S1 ~ S6 실행 · 계획과 Buffers 기록
ALTER SESSION SET optimizer_use_pending_statistics = FALSE;
-- 받아들일 수 없는 테이블이 있으면 그 테이블만 : EXEC DBMS_STATS.DELETE_PENDING_STATS('PUP', 'CF13');

-- ③ 화면이 한가한 시각(23:00)에 공개한다 · 무효화는 기본값(AUTO_INVALIDATE)으로 몇 시간에 나눠
BEGIN
  DBMS_STATS.PUBLISH_PENDING_STATS(ownname => 'PUP', tabname => NULL);
  DBMS_STATS.SET_SCHEMA_PREFS('PUP', 'PUBLISH', 'TRUE');
  DBMS_AUTO_TASK_ADMIN.ENABLE('auto optimizer stats collection', NULL, NULL);
END;
/

-- ④ 공개 뒤 문제가 생기면 그 테이블만 되돌린다
-- EXEC DBMS_STATS.RESTORE_TABLE_STATS('PUP', 'CF13', as_of_timestamp => TIMESTAMP '2024-06-12 22:59:00', no_invalidate => FALSE);
-- 또는 EXEC DBMS_STATS.IMPORT_TABLE_STATS('PUP', 'CF13', stattab => 'STATS_BAK_0612', no_invalidate => FALSE);

모범 답안 서술

첫 안의 위험: ① 7개 테이블의 통계가 5년 만에 한꺼번에 바뀌므로 어떤 SQL의 계획이 바뀌는지 모른 채 공개된다. 복사본 결과가 있지만 복사본은 하루 전 데이터이고 운영의 바인드 값과 공유 풀 상태를 그대로 갖고 있지 않다. ② no_invalidate => FALSE로 모으면 통계를 쓰는 순간 PUP 커서 약 2,100개가 한꺼번에 무효화되어 하드 파싱이 몰린다. ③ options => 'GATHER'는 멀쩡한 11개까지 다시 모아 바뀌는 것을 더 늘린다. 되돌릴 준비도 이력 31일에만 기대고 있다. 절차: 먼저 지금 통계를 EXPORT_SCHEMA_STATS로 통계 표에 떠 둔다. 이력 31일과 별도로 기한 없이 남는 되돌릴 곳이다. 다음으로 스키마 선호도 PUBLISH를 FALSE로 두고 GATHER AUTO로 모은다. 새 통계는 보류 통계(pending)로만 저장되고 다른 세션은 지금 통계를 그대로 쓴다. 운영에서 내 세션만 optimizer_use_pending_statistics = TRUE로 바꿔 S1 ~ S6을 돌리고, 계획과 Buffers를 지금 판과 견준다. 복사본이 아니라 운영 데이터와 운영 바인드 값으로 미리 보는 것이다. 받아들일 수 없는 테이블이 있으면 그 테이블만 DELETE_PENDING_STATS로 버린다. 문제가 없으면 화면이 한가한 23:00에 PUBLISH_PENDING_STATS로 공개하고, 무효화는 기본값(AUTO_INVALIDATE)에 맡겨 다시 파싱하는 일을 몇 시간에 나눠 흩는다. 공개한 뒤 PUBLISH를 TRUE로 되돌리고 자동 수집을 켠다. 공개 뒤에 문제가 생기면 그 테이블만 RESTORE_TABLE_STATS(이력 31일)나 IMPORT_TABLE_STATS(떠 둔 통계)로 되돌린다. S4의 판단: 38 → 61블록이면 하루 2,000번 × 23블록 = 약 4.6만 블록이 늘고, 한 번에 0.001초 수준이다. S1 하나가 하루 600번 × 7,826블록 = 약 470만 블록을 줄이는 것과 견주면 받아들인다. 이 한 SQL 때문에 7개 테이블의 통계를 낡은 채로 둘 이유는 없다. 다만 기록은 남긴다. 계획 변경 대장에 S4의 전후 계획과 Buffers, 빈도, 받아들인 근거를 적고, 하루 빈도가 열 배로 늘면 다시 본다는 조건을 붙인다. 화면 응답에 영향이 있을 만큼 나빠지는 SQL이 나왔다면, 공개 전에 지금 계획을 SQL plan baseline으로 올려 지키거나 그 SQL을 따로 고친다(18화).

해설

보류 통계는 운영에서 미리 보는 장치다. 복사본 시험은 데이터와 바인드 값, 공유 풀이 다르다. 보류 통계는 운영 데이터로 모은 통계를 내 세션에서만 써 볼 수 있게 한다.
틀린 통계가 우연히 좋은 계획을 고르는 경우도 있다. 통계가 맞아지면 그 우연이 사라져 일량이 조금 늘 수 있다(10화 §7의 5,008 → 6,031). 맞는 숫자를 고를지 우연을 지킬지는 숫자와 빈도로 판단해 기록한다.
RESTORE는 그 시각의 통계로 되돌릴 뿐이고 31일이 지나면 사라진다. 5년 만의 변경이라면 기한 없는 되돌릴 곳(EXPORT)을 함께 둔다.
공개 뒤 자동 수집을 켜면 그다음부터는 STALE 10% 기준으로 저절로 바뀐다. 이번처럼 미리 보고 싶은 테이블이 있으면 그 테이블만 PUBLISH FALSE를 남겨 둘 수 있다. 그때는 누군가 보류 통계를 정기적으로 공개해야 한다는 일도 함께 적는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황

테이블과 인덱스

원본 SQL

실행계획 학습용 예시

문제

모범 답안 SQL

모범 답안 서술

해설

관련 교재