리뷰 검수 화면이 하루에 서너 번, 한 번씩만 튄다

중급SELECT이커머스571 / 700

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

상황

이커머스 관리자 「리뷰 검수」 화면은 운영자가 대개 오늘 들어온 리뷰를 보고, 품질팀은 1년치를 내려 본다. 하루 4,000번쯤 호출된다. DBA가 보낸 표에는 이 문장이 하루 서너 번 3,000블록 넘게 읽는 실행이 있고, 바로 다음부터는 1년을 넘겨도 775블록으로 내려간다. 공유 풀이 빠듯해서 이 문장은 하루에도 몇 번씩 밀려났다가 다시 올라온다(LOADS 4). 개발 리더가 물었다. "오라클이 알아서 고친다면 그냥 둬도 되는 것 아닌가요?"

테이블과 인덱스

reviews: 15,000행 · 775블록 · created_at DATE(시분초 있음, 2020 ~ 2024 · 하루 약 8건) · 행은 created_at 순서와 상관없이 흩어져 있다 · content CLOB은 이 화면에서 읽지 않는다
인덱스: PK(review_id) · IX_REV_CREATED (created_at)(이 문제용 · 높이 2 · 리프 약 40블록)
통계 최신 · 히스토그램 없음 · statistics_level = TYPICAL · 바인드 피킹 켬

원본 SQL

sql

SELECT review_id, product_id, customer_id, rating, created_at
  FROM reviews
 WHERE created_at >= :d1
   AND created_at <  :d2
 ORDER BY created_at DESC

실행계획 학습용 예시

xplan

[V$SQL — 16:20 · 13:05에 다시 올라온 커서]
 CHILD  EXEC  BUFFER_GETS  IS_BIND_SENSITIVE  IS_BIND_AWARE  IS_SHAREABLE  PLAN_HASH
     0     2        3,014  Y                  N              N             1503917384
     1     6        4,668  Y                  Y              Y             2865032113
     2   611        6,110  Y                  Y              Y             1503917384

[V$SQL_CS_HISTOGRAM]  자식 0 → 0:1 1:1 2:0 · 자식 1 → 0:6 · 자식 2 → 0:611
[V$SQL_SHARED_CURSOR] 자식 1 · 2 : BIND_EQUIV_FAILURE = Y
[V$SQL_CS_SELECTIVITY]
 CHILD  PREDICATE  RANGE_ID  LOW       HIGH
     1  >=D1              0  0.180108  0.220132
     1  <D2               0  0.899955  1.099945
     2  >=D1              0  0.000495  0.000605
     2  <D2               0  0.899955  1.099945

[자식 0 · 13:41 · 1년 조회 한 번 (자식 0은 13:05에 오늘 하루로 태어났다)]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |   3001 |00:00:00.04 |    3004 |          |
|*  1 |  FILTER                        |                |      1 |        |   3001 |00:00:00.04 |    3004 |          |
|   2 |   TABLE ACCESS BY INDEX ROWID  | REVIEWS        |      1 |      8 |   3001 |00:00:00.04 |    3004 |          |
|*  3 |    INDEX RANGE SCAN DESCENDING | IX_REV_CREATED |      1 |      8 |   3001 |00:00:00.01 |      10 |          |
-------------------------------------------------------------------------------------------------------------------
   1 - filter(:D1<:D2)
   3 - access("CREATED_AT"<:D2 AND "CREATED_AT">=:D1)

[자식 1 · 1년 · 'ALLSTATS LAST']
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |   3001 |00:00:00.01 |     775 |          |
|   1 |  SORT ORDER BY                 |                |      1 |   3003 |   3001 |00:00:00.01 |     775 |  267K (0)|
|*  2 |   FILTER                       |                |      1 |        |   3001 |00:00:00.01 |     775 |          |
|*  3 |    TABLE ACCESS FULL           | REVIEWS        |      1 |   3003 |   3001 |00:00:00.01 |     775 |          |
-------------------------------------------------------------------------------------------------------------------
   2 - filter(:D1<:D2)
   3 - filter(("CREATED_AT">=:D1 AND "CREATED_AT"<:D2))

문제

13:05 이후 이 문장에 일어난 일을 순서대로 설명하시오. 왜 하루에 서너 번 같은 일이 되풀이되는지도 쓰시오.
개발 리더의 말처럼 그냥 둬도 되는지 판단하고, 나쁜 실행 한 번까지 없애도록 SQL을 고치시오. 고친 방법의 대가도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 값의 범위마다 맞는 계획이 다른 문장 : 처음 파싱할 때부터 bind aware 로 둔다 (나쁜 한 번을 건너뛴다)
SELECT /*+ BIND_AWARE */
       review_id, product_id, customer_id, rating, created_at
  FROM reviews
 WHERE created_at >= :d1
   AND created_at <  :d2
 ORDER BY created_at DESC

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

xplan

[다시 올라온 뒤 · 오늘 하루 → 1년 순서]
 CHILD  EXEC  BUFFER_GETS/EXEC  IS_BIND_AWARE  계획
     0   418                10  Y              INDEX RANGE SCAN DESCENDING IX_REV_CREATED (하루)
     1     5               775  Y              TABLE ACCESS FULL + SORT (1년 · 첫 실행부터)
(1년 첫 실행 3,004 → 775 · LOADS가 늘어도 같은 순서로 바로 갈라진다)

모범 답안 서술

13:05 이후의 순서: 공유 풀에서 밀려났던 문장이 13:05에 다시 올라오면서 자식 0이 운영자의 오늘 하루를 엿보고 태어났다(인덱스를 거꾸로 읽는 계획, 10블록 안팎). 범위 바인드라 IS_BIND_SENSITIVE = Y다. 13:41에 품질팀이 1년을 넘기자 자식 0의 인덱스 계획으로 3,001행을 하나씩 찾아가 3,004블록을 읽었다. 테이블 전체가 775블록인데 그 네 배다. 이것이 DBA 표의 "튀는 한 번"이다. 자식 0의 두 실행은 일량 칸이 0번과 1번으로 섞였고(0:1 1:1), 다음 파스에서 오라클은 이 문장을 bind aware로 바꿨다. 1년 값을 다시 엿봐 FULL 계획의 자식 1을 만들고(BIND_EQUIV_FAILURE), 오늘 하루가 오자 자식 1의 범위에 들지 않아 인덱스 계획의 자식 2를 또 만들었다. V$SQL_CS_SELECTIVITY를 보면 자식 1은 >= :d1의 선택도 0.18 ~ 0.22(1년, 엿본 값의 ±10%), 자식 2는 0.0005 언저리(하루)를 맡는다. 자식 0은 IS_SHAREABLE = N이 되어 더 쓰이지 않는다. 되풀이되는 이유: ACS가 배운 칸과 선택도 범위는 커서에만 있다. 이 문장은 하루에 몇 번씩 공유 풀에서 밀려나므로(LOADS 4), 다시 올라올 때마다 자식 0부터 새로 시작하고 1년 조회가 나쁜 한 번을 다시 겪는다. 하루 서너 번 튀는 까닭이다. 판단과 고침: ACS는 결국 고쳐 주지만, 고치는 방식이 "나쁜 실행을 한 번 겪은 뒤"다. 그 한 번도 누군가의 화면이고, 이 문장에서는 커서가 올라올 때마다 되풀이된다. 이 문장은 값의 범위마다 맞는 계획이 정말 다르므로(하루는 인덱스, 1년은 FULL) ACS의 방향은 옳다. 그래서 /*+ BIND_AWARE */를 붙여 처음 파싱할 때부터 bind aware로 둔다. 그러면 첫 1년 실행부터 선택도로 자식을 골라 3,004 → 775블록이 되고, 나쁜 한 번이 사라진다. 대가: bind aware 커서는 파스마다 바인드의 선택도를 계산해 자식들의 범위와 맞춰 보므로 소프트 파싱이 조금 무거워진다. 기간 길이가 다양하면 범위마다 자식이 몇 개 더 생긴다(PARSE-023처럼 계획이 하나로 충분한 문장에는 붙이지 않는다). 어느 값이 어느 계획을 타는지가 문장 밖의 뷰에 있다는 점도 기록해 둔다.

해설

오늘 화면과 기간 조회를 문장으로 나누는 것도 답이다. 오늘 화면을 created_at >= TRUNC(SYSDATE)로 쓰면 바인드가 없어 피킹할 값도 없다. 다만 화면 코드를 둘로 나눠야 하고, 기간 조회 쪽에는 여전히 하루와 1년이 섞인다.
공유 풀에서 자주 밀려나는 것 자체가 또 다른 증상이다. 리터럴 문장이 공유 풀을 휘젓고 있지 않은지(PARSE s1) 함께 본다.
교재 11화 [C]는 100건 화면에서 ACS가 나쁜 한 번(8,334블록) 뒤에 고쳐지는 것을 쟀다. 1,000건 화면처럼 두 일량이 같은 칸에 들어가면 그 한 번 뒤에도 고쳐지지 않는다(PARSE-002).
측정할 때 statistics_level = ALL을 켜면 ACS가 세는 일량이 달라질 수 있다. 운영과 같은 TYPICAL에서 V$SQL.BUFFER_GETS 증가분으로 잰다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황