본사 일 매출 화면이 어떤 날은 0.01초, 어떤 날은 0.3초 걸린다

중급SELECT버거565 / 700

상황

본사 「매장별 일 주문 현황」 화면(주문 건수, 첫 주문과 마지막 주문 시각)은 점장과 본사 직원이 전날 하루를 넘겨 연다(하루 약 2,000번). 같은 DAO 메서드를 월 정산 리포트도 쓰는데, 리포트는 한 달을 넘긴다. 리포트는 낮에 본사 직원이 열기도 하고 밤에 예약 실행되기도 한다(합쳐서 하루 30번쯤). 분석용 사본은 매일 01:00에 새로 적재하고 바로 통계를 기본값으로 모은다. 오과장이 말했다. "어떤 날은 괜찮고 어떤 날은 느린데, 그날그날 달라요. 배포도 없었고 SQL도 그대로예요."

테이블과 인덱스

ORD_RANDOM: 1,000,000행 · 22,414블록 · POS 원장 ORD를 무작위 순서로 복제한 본사 분석용 사본 · ORD_DT VARCHAR2(8) · 하루 약 1,370건 · 매장 200곳
인덱스 ORD_RANDOM_X01 (ORD_DT, ORD_HMS): CF 999,958 · BLEVEL 2 · 리프 3,774블록 · 이 문제에서는 다른 인덱스가 없다(화면은 SHOP_NO와 ORD_HMS를 함께 읽으므로 테이블까지 간다)
통계: 매일 01:00 GATHER_TABLE_STATS 기본값(no_invalidate => DBMS_STATS.AUTO_INVALIDATE) · 히스토그램 없음
CURSOR_SHARING = EXACT · 바인드 피킹 켬 · statistics_level = TYPICAL

원본 SQL

sql

-- SalesDao.shopOrderCount(from, to) : 화면은 from = to = 전날, 월 정산 리포트는 한 달
SELECT SHOP_NO, COUNT(*) AS ORD_CNT, MIN(ORD_HMS) AS FIRST_HMS, MAX(ORD_HMS) AS LAST_HMS
  FROM ORD_RANDOM
 WHERE ORD_DT BETWEEN :f AND :t
 GROUP BY SHOP_NO

실행계획 학습용 예시

xplan

[DBA_HIST_SQLSTAT — 같은 SQL_ID · 날마다]
 요일  PLAN_HASH    실행    BUFFER_GETS/실행   커서가 다시 만들어진 시각 · 그때 온 호출
 월    1648203117   2,031          1,975       04:12 · 화면(하루, 첫 점장)
 화    3920571642   2,036         22,415       02:37 · 월 정산 리포트(한 달, 야간 예약 실행)
 수    1648203117   2,029          1,974       05:48 · 화면(하루)
 목    3920571642   2,033         22,416       03:05 · 월 정산 리포트(한 달)

[V$SQL_SHARED_CURSOR — 목요일 새 자식] ROLL_INVALID_MISMATCH = Y
[V$SQL — 목요일 자식] IS_BIND_SENSITIVE Y · IS_BIND_AWARE N · V$SQL_CS_HISTOGRAM 0:0 1:2,033 2:0

[목요일 자식 · 화면(하루) 한 번 · FORMAT 'ALLSTATS LAST +PEEKED_BINDS']
Plan hash value: 3920571642

------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |    200 |00:00:00.29 |   22414 |          |
|   1 |  HASH GROUP BY                        |                |      1 |    200 |    200 |00:00:00.29 |   22414 | 1394K (0)|
|*  2 |   FILTER                              |                |      1 |        |   1370 |00:00:00.28 |   22414 |          |
|*  3 |    TABLE ACCESS FULL                  | ORD_RANDOM     |      1 |  41100 |   1370 |00:00:00.28 |   22414 |          |
------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by name):
----------------------------------
   1 - :F (VARCHAR2(30), CSID=873): '20131101'
   2 - :T (VARCHAR2(30), CSID=873): '20131130'

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:F<=:T)
   3 - filter(("ORD_DT">=:F AND "ORD_DT"<=:T))

[수요일 자식 · 리포트(한 달) 한 번 · 이날 커서는 화면의 하루로 태어났다]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |    200 |00:00:00.61 |   41825 |          |
|   1 |  HASH GROUP BY                        |                |      1 |    200 |    200 |00:00:00.61 |   41825 | 1394K (0)|
|*  2 |   FILTER                              |                |      1 |        |  41666 |00:00:00.58 |   41825 |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD_RANDOM     |      1 |   1370 |  41666 |00:00:00.57 |   41825 |          |
|*  4 |     INDEX RANGE SCAN                  | ORD_RANDOM_X01 |      1 |   1370 |  41666 |00:00:00.02 |     160 |          |
------------------------------------------------------------------------------------------------------------
   2 - filter(:F<=:T)
   4 - access("ORD_DT">=:F AND "ORD_DT"<=:T)

[같은 날 화면(하루) 한 번 · 인덱스 계획]  Buffers 1,377 · A-Time 0.01초

문제

배포도 없었는데 날마다 계획이 바뀌는 이유를 통계 수집 방식과 바인드 피킹으로 설명하시오. 커서가 다시 만들어지는 시각이 날마다 다른 이유도 쓰시오.
적응적 커서 공유(ACS)가 이 문장을 고쳐 주지 못하는 이유를 위 자료로 쓰시오.
어느 날이든 화면과 리포트가 각자 맞는 계획을 타도록 SQL을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- 화면 : 언제나 하루만 본다 → 등호 · 자기 문장 (값이 무엇이든 하루치 1,370건 → 인덱스가 맞다)
SELECT SHOP_NO, COUNT(*) AS ORD_CNT, MIN(ORD_HMS) AS FIRST_HMS, MAX(ORD_HMS) AS LAST_HMS
  FROM ORD_RANDOM
 WHERE ORD_DT = :d
 GROUP BY SHOP_NO;

-- 월 정산 리포트 : 한 달 → 자기 문장 · FULL 이 맞다는 판단을 문장에 남긴다
SELECT /*+ FULL(o) */ o.SHOP_NO, COUNT(*) AS ORD_CNT, MIN(o.ORD_HMS) AS FIRST_HMS, MAX(o.ORD_HMS) AS LAST_HMS
  FROM ORD_RANDOM o
 WHERE o.ORD_DT BETWEEN :f AND :t
 GROUP BY o.SHOP_NO;

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

xplan

[화면 문장 — 어느 날이든]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |    200 |00:00:00.01 |    1377 |          |
|   1 |  HASH GROUP BY                        |                |      1 |    200 |    200 |00:00:00.01 |    1377 | 1394K (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | ORD_RANDOM     |      1 |   1370 |   1370 |00:00:00.01 |    1377 |          |
|*  3 |    INDEX RANGE SCAN                   | ORD_RANDOM_X01 |      1 |   1370 |   1370 |00:00:00.01 |       7 |          |
------------------------------------------------------------------------------------------------------------
   3 - access("ORD_DT"=:D)

[리포트 문장 — 한 달] TABLE ACCESS FULL · 22,414블록 (인덱스로 읽던 날의 41,825 대신)

모범 답안 서술

목요일 새 자식의 V$SQL_SHARED_CURSOR에는 ROLL_INVALID_MISMATCH = Y가 찍혀 있다. 01:00 통계 수집은 기본값 AUTO_INVALIDATE로 돈다. 이 값은 커서를 바로 버리지 않고 5시간 안의 아무 때로 시한을 정해 두었다가, 그 뒤 처음 오는 파스가 자식을 새로 만들게 한다(롤링 무효화). 그래서 커서가 다시 만들어지는 시각이 날마다 다르고, 그 순간 들어온 호출도 날마다 다르다. 새 자식은 그 호출의 바인드 값을 엿봐서(바인드 피킹) 계획을 세운다. 화요일과 목요일에는 야간 예약 실행된 월 정산 리포트('20131101' ~ '20131130')가 먼저 왔다. 한 달이면 41,666건이라 무작위로 흩어진 사본(CF 999,958)에서는 인덱스로 한 건씩 찾아가는 것보다 FULL이 싸다. 그날 하루 동안 화면도 그 계획을 같이 써서 1,370건을 위해 22,414블록을 읽는다(0.29초). 월요일과 수요일에는 화면이 먼저 와서 인덱스 계획이 섰고, 그날은 리포트가 한 달을 인덱스로 읽어 41,825블록을 썼다. ACS는 이 문장을 고쳐 주지 못한다. 범위 바인드라 IS_BIND_SENSITIVE = Y이지만, 화면의 1,377이나 22,414, 리포트의 41,825나 22,414가 모두 V$SQL_CS_HISTOGRAM의 1번 칸에 들어간다(1:2,033). 칸이 섞이지 않으니 IS_BIND_AWARE는 끝까지 N이다. 화면은 언제나 하루만 보므로 ORD_DT = :d 등호로 자기 문장을 쓴다. 값이 무엇이든 하루치(1,370건)라서 맞는 계획이 인덱스 하나뿐이고, 피킹이 해를 끼칠 자리가 없다. 리포트는 범위 조건의 문장을 따로 두고, FULL이 맞다는 판단을 힌트로 남긴다. 느린 날의 화면 한 번은 Buffers 22,414 → 1,377이고, 리포트는 인덱스로 읽던 날의 41,825 → 22,414다.

해설

/*+ BIND_AWARE */를 붙여도 고쳐진다. 처음부터 값의 선택도마다 자식을 나누므로 칸이 같아도 갈라진다. 다만 화면과 리포트는 쓰임새가 다른 일이라 문장을 나누는 쪽이 읽기 쉽고, 어느 값이 어느 계획을 타는지가 문장에 드러난다.
통계 수집을 no_invalidate => FALSE로 바꾸면 01:00에 바로 무효화되어 01:00 이후 첫 호출이 계획을 정한다. 시각이 고정될 뿐 "첫 손님" 문제는 그대로다.
원장 ORD(날짜순 적재, CF 22,223)라면 한 달도 인덱스가 싸서 계획이 갈리지 않았다. 같은 SQL이라도 적재 순서가 다른 사본에서는 바인드 피킹의 위험이 달라진다(교재 04화).
화면의 BETWEEN :f AND :t에 :f = :t를 넘기는 것과 = :d는 결과가 같다. 그러나 옵티마이저에게는 "하루"라는 정보가 문장 모양에 있느냐 없느냐의 차이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황