매장 한 곳의 하루치 판매를 보는데 판매상품 테이블을 통째로 읽는다

기초SELECT유통595 / 700

상황

본사 레거시 유통의 점장 화면 「어제 상품별 판매」는 매장 여섯 곳의 점장이 아침마다 수십 번씩 누른다. 조민우가 전했다. "매장 한 곳, 하루치인데 0.4초씩 걸리고 아침에는 화면이 밀려요." 이 DB는 2016년에 자동 통계 수집을 껐다. 그 뒤로는 DBA가 만든 야간 잡 PKG_STATS.NIGHTLY가 목록 표 STATS_TARGET에 적힌 테이블만 매일 23:40에 모은다. 목록은 사람이 손으로 고친다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) · 하루 약 700건 · 매장 6곳(SHP_NO) · 통계는 어젯밤 23:41(목록에 있다)
CF11 판매상품: 실제 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 판매 한 건에 평균 2.2줄
CF11의 통계는 NUM_ROWS 109,860 · BLOCKS 822 · 마지막 수집 2019-03-14(2019년 이관 때 목록에서 빠졌다)
인덱스: CF10_PK (SLE_NO) · IX_CF10_DT_SHP (SLE_DT, SHP_NO) · CF11_PK

원본 SQL

sql

SELECT b.ITM_NO, SUM(b.SLE_QTY) AS qty, SUM(b.SLE_QTY * b.SLE_UPRC) AS amt
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT = :dt
   AND a.SHP_NO = :shp
   AND b.SLE_NO = a.SLE_NO
 GROUP BY b.ITM_NO

실행계획 학습용 예시

xplan

[USER_TAB_STATISTICS — 오늘 08:50]
 TABLE_NAME   NUM_ROWS  BLOCKS  LAST_ANALYZED   STALE_STATS   실제 COUNT(*)
 CF10          500,000   4,583  어제 23:41      NO                  500,000
 CF11          109,860     822  2019-03-14      YES               1,096,708
 (USER_TAB_MODIFICATIONS — CF11 : INSERTS 986,848 · 마지막 수집 이후)

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |     64 |00:00:00.41 |    8215 |
|   1 |  HASH GROUP BY                        |                |      1 |     61 |     64 |00:00:00.41 |    8215 |
|*  2 |   HASH JOIN                           |                |      1 |    250 |    256 |00:00:00.41 |    8215 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    114 |    117 |00:00:00.01 |      11 |
|*  4 |     INDEX RANGE SCAN                  | IX_CF10_DT_SHP |      1 |    114 |    117 |00:00:00.01 |       3 |
|   5 |    TABLE ACCESS FULL                  | CF11           |      1 |    109K|   1096K|00:00:00.30 |    8204 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."SLE_NO"="A"."SLE_NO")
   4 - access("A"."SLE_DT"=:DT AND "A"."SHP_NO"=:SHP)

문제

실행계획의 어느 줄에서 옵티마이저가 무엇을 잘못 알고 있는지 찾고, 그 숫자가 어디서 왔는지 쓰시오.
그 숫자 때문에 이 계획을 고른 이유를 쓰고, 오늘 할 조치와 같은 일이 다시 생기지 않게 할 조치를 SQL로 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 오늘 : CF11 통계를 지금 크기로 (인덱스 통계까지 · 이 화면의 커서도 다음 실행 때 바로 다시 파싱)
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname       => 'PUP',
                                tabname       => 'CF11',
                                cascade       => TRUE,
                                no_invalidate => FALSE);
END;
/

-- ② 재발 방지 : 손으로 고치는 목록 대신 '오래된 통계' 표시를 따라 모은다 (STALE 이거나 통계가 없는 것)
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'PUP', options => 'GATHER AUTO');
END;
/
-- 야간 잡을 이것으로 바꾸거나, 자동 수집을 다시 켠다
-- EXEC DBMS_AUTO_TASK_ADMIN.ENABLE('auto optimizer stats collection', NULL, NULL);

-- SQL 은 그대로
SELECT b.ITM_NO, SUM(b.SLE_QTY) AS qty, SUM(b.SLE_QTY * b.SLE_UPRC) AS amt
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT = :dt
   AND a.SHP_NO = :shp
   AND b.SLE_NO = a.SLE_NO
 GROUP BY b.ITM_NO;

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

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |     64 |00:00:00.01 |     389 |
|   1 |  HASH GROUP BY                        |                |      1 |     61 |     64 |00:00:00.01 |     389 |
|   2 |   NESTED LOOPS                        |                |      1 |    250 |    256 |00:00:00.01 |     389 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    114 |    117 |00:00:00.01 |      11 |
|*  4 |     INDEX RANGE SCAN                  | IX_CF10_DT_SHP |      1 |    114 |    117 |00:00:00.01 |       3 |
|   5 |    TABLE ACCESS BY INDEX ROWID        | CF11           |    117 |      2 |    256 |00:00:00.01 |     378 |
|*  6 |     INDEX RANGE SCAN                  | CF11_PK        |    117 |      2 |    256 |00:00:00.01 |     238 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("A"."SLE_DT"=:DT AND "A"."SHP_NO"=:SHP)
   6 - access("B"."SLE_NO"="A"."SLE_NO")

모범 답안 서술

5번 줄 CF11의 E-Rows는 109K인데 A-Rows는 1,096K다. 109,860은 USER_TAB_STATISTICS의 NUM_ROWS이고, LAST_ANALYZED는 2019-03-14다. 이관 때 야간 잡의 목록에서 빠진 뒤로 한 번도 다시 모으지 않은 오래된 통계다. STALE_STATS는 YES이고 USER_TAB_MODIFICATIONS에도 그 뒤 INSERT 986,848건이 쌓여 있지만, 자동 수집을 꺼 두어 아무도 그 표시를 보지 않았다. 옵티마이저는 CF11을 822블록짜리 작은 테이블로 믿었다. 그렇다면 판매 117건마다 CF11_PK로 찾아가는 NL보다 한 번 통째로 읽고 해시 조인하는 편이 싸다. 통계로 보면 맞는 판단이었지만 실제 테이블은 열 배 크기라서 8,204블록을 다 읽는다. 오늘은 CF11 통계를 인덱스까지 지금 크기로 다시 모으고 커서를 무효화한다. 그러면 E-Rows가 실제와 맞아 NL로 판매 117건의 상세만 읽고, Buffers는 8,215 → 389로 준다. 화면 SQL은 고치지 않는다. 재발을 막으려면 손으로 고치는 목록을 기준으로 삼지 않는다. 야간 잡을 GATHER_SCHEMA_STATS(options => 'GATHER AUTO')로 바꿔 STALE이거나 통계가 없는 테이블을 모두 모으게 하거나, 자동 수집을 다시 켠다. 목록을 꼭 유지해야 한다면 CF11을 목록에 넣고, 목록에 없는 테이블 가운데 LAST_ANALYZED가 오래된 것을 찾는 점검을 정기 작업에 더한다.

해설

힌트(USE_NL)로 이 SQL만 고치는 안은 기각한다. 통계가 틀린 채 남아 CF11을 읽는 다른 SQL이 같은 오판을 한다. 힌트도 오늘 규모에 맞춘 판단이다(06화).
찾는 법: USER_TAB_STATISTICS에서 LAST_ANALYZED가 오래되고 STALE_STATS가 YES인 테이블, USER_TAB_MODIFICATIONS에서 INSERTS와 DELETES가 큰 테이블을 고른다. 마지막으로 실제 COUNT(*)와 NUM_ROWS를 견준다.
5년 만의 수집이면 CF11을 읽는 다른 SQL의 계획도 한꺼번에 바뀐다. 테이블이 여럿이면 먼저 공개하지 않은 통계(pending)로 미리 보는 길이 있다(OPT-016).
자동 수집이나 GATHER AUTO는 바뀐 행이 10%(STALE_PERCENT)를 넘어야 모은다. 날짜가 자라는 테이블은 그 10%를 기다리는 동안 최댓값 밖을 묻게 되므로 적재 직후 수집이 따로 필요하다(10화 §7).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황