바인드 변수로 바꾼 고액 주문 집계에 자식 커서가 40개 넘게 붙는다

중급SELECT한빛유통568 / 700

상황

정산 대시보드의 「기간 고액 주문」 위젯과 같은 이름의 API가 한 문장을 같이 쓴다. 기간(하루 ~ 3년)과 하한 금액을 바인드 변수로 넘기고, 하루 4만 번쯤 실행된다. 지난달 리터럴을 바인드 변수로 바꾼 문장이다. 이번 주 AWR에서 이 문장이 「SQL ordered by Version Count」 1위, 「Parse CPU」 3위에 올랐다. 오전 피크에는 cursor: mutex S 대기도 보인다. 박팀장이 물었다. "바인드로 바꿨으면 커서는 하나여야 하는 것 아닌가요?"

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT 2022-01-01 ~ 2024-12-31(전부 자정) · ORD_AMT
인덱스 IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT): 리프 약 3,320블록 · 6월 한 달이 91블록 · 이 문장이 쓰는 컬럼을 모두 담는다
히스토그램은 ORD_ST에만 있다 · statistics_level = TYPICAL · 바인드 피킹 켬

원본 SQL

sql

SELECT COUNT(*) AS ORD_CNT, SUM(ORD_AMT) AS AMT
  FROM T_ORD_BIG
 WHERE ORD_DT >= :d1
   AND ORD_DT <  :d2
   AND ORD_AMT >= :amt

실행계획 학습용 예시

xplan

[V$SQLAREA — 15:00]  VERSION_COUNT 43 · LOADS 43 · EXECUTIONS 41,206 · SHARABLE_MEM 2.6MB

[V$SQL — 자식 43개를 계획별로 묶은 것]
 PLAN_HASH    계획                               자식 수  IS_BIND_AWARE   IS_SHAREABLE = Y   실행     BUFFER_GETS/실행
 2207145832   INDEX RANGE SCAN IX_ORD_BIG_DT_AMT      38  Y 37 · N 1                     6   38,914                 61
 3519962670   INDEX FAST FULL SCAN (같은 인덱스)        5  Y 5                             2    2,292              3,341

[V$SQL_SHARED_CURSOR — Y 칸 세기]  BIND_EQUIV_FAILURE 41 · LOAD_OPTIMIZER_STATS 1
[V$SQL_CS_HISTOGRAM — 자식 0]  0:6  1:3  2:0

[V$SQL_CS_SELECTIVITY — 자식 둘만]
 CHILD  PREDICATE  RANGE_ID  LOW       HIGH
    17  >=D1              0  0.175934  0.215030
    17  <D2               0  0.749863  0.916499
    17  >=AMT             0  0.081052  0.099063
    29  >=D1              0  0.623288  0.761796
    29  <D2               0  0.893151  1.091629
    29  >=AMT             0  0.405000  0.495000

[계획 2207145832 — 한 달 · 하한 10만 원 · 'ALLSTATS LAST']
------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |      1 |00:00:00.01 |      91 |
|   1 |  SORT AGGREGATE    |                   |      1 |      1 |      1 |00:00:00.01 |      91 |
|*  2 |   FILTER           |                   |      1 |        |   2465 |00:00:00.01 |      91 |
|*  3 |    INDEX RANGE SCAN| IX_ORD_BIG_DT_AMT |      1 |   2471 |   2465 |00:00:00.01 |      91 |
------------------------------------------------------------------------------------------------------
   2 - filter(:D2>:D1)
   3 - access("ORD_DT">=:D1 AND "ORD_AMT">=:AMT AND "ORD_DT"<:D2)
       filter("ORD_AMT">=:AMT)

[계획 3519962670 — 3년 · 하한 1만 원]
------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |      1 |        |      1 |00:00:00.09 |    3341 |
|   1 |  SORT AGGREGATE       |                   |      1 |      1 |      1 |00:00:00.09 |    3341 |
|*  2 |   FILTER              |                   |      1 |        |   450K |00:00:00.08 |    3341 |
|*  3 |    INDEX FAST FULL SCAN| IX_ORD_BIG_DT_AMT |      1 |   450K |   450K |00:00:00.06 |    3341 |
------------------------------------------------------------------------------------------------------
   2 - filter(:D2>:D1)
   3 - filter(("ORD_DT">=:D1 AND "ORD_AMT">=:AMT AND "ORD_DT"<:D2))

[같은 3년을 INDEX RANGE SCAN으로 (힌트)]  Buffers 3,322 · 0.08초

문제

텍스트가 하나인 문장에 자식이 43개 생긴 과정을 V$SQL_CS_HISTOGRAM, V$SQL_CS_SELECTIVITY, V$SQL_SHARED_CURSOR로 설명하시오.
이 문장에 자식이 여럿 필요한지 판단하고, 자식이 하나로 모이면서 어느 기간이든 맞는 계획을 타도록 고치시오. 고친 방법의 함정도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 자식을 나눌 이유가 없는 문장 : ACS 를 멈추고, 어느 기간이든 맞는 계획(커버링 인덱스 범위 스캔) 하나로 둔다
SELECT /*+ NO_BIND_AWARE INDEX_RS_ASC(T_ORD_BIG IX_ORD_BIG_DT_AMT) */
       COUNT(*) AS ORD_CNT, SUM(ORD_AMT) AS AMT
  FROM T_ORD_BIG
 WHERE ORD_DT >= :d1
   AND ORD_DT <  :d2
   AND ORD_AMT >= :amt

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

xplan

[다음 날 15:00 — 힌트를 넣은 새 문장]  VERSION_COUNT 1 · LOADS 1 · EXECUTIONS 41,388 · SHARABLE_MEM 61KB
------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |      1 |00:00:00.08 |    3322 |
|   1 |  SORT AGGREGATE    |                   |      1 |      1 |      1 |00:00:00.08 |    3322 |
|*  2 |   FILTER           |                   |      1 |        |   450K |00:00:00.08 |    3322 |
|*  3 |    INDEX RANGE SCAN| IX_ORD_BIG_DT_AMT |      1 |   2471 |   450K |00:00:00.06 |    3322 |
------------------------------------------------------------------------------------------------------
(3년 실행 · 하루 4블록 · 한 달 91블록 · 1년 약 1,110블록 · 모두 같은 자식)

모범 답안 서술

처음 만든 자식 0은 범위 바인드라 IS_BIND_SENSITIVE = Y였다. 실행마다 일량을 세 칸에 세는데, 하루와 한 달(91블록)은 0번 칸, 1년과 3년(1,000블록 넘게)은 1번 칸에 들어가 칸이 섞였다(0:6 1:3). 그러자 오라클은 다음 파스 때부터 이 문장을 bind aware로 다루고, 실행마다 바인드 셋의 선택도를 계산해 기존 자식이 맡은 범위(V$SQL_CS_SELECTIVITY, 엿본 값의 ±10%)에 드는지 본다. 범위를 벗어나면 그 값을 엿보고 하드 파싱해 자식을 새로 만든다(BIND_EQUIV_FAILURE 41개). 기간의 시작일은 날마다 바뀌고, 기간 길이와 하한 금액도 제각각이라 범위를 벗어나는 일이 잦다. 그래서 하루 만에 자식이 43개, LOADS가 43이 되었다. 그런데 계획은 두 가지뿐이다. 자식 38개가 같은 범위 스캔이고 5개가 같은 인덱스의 FFS다. 그것도 3년을 범위 스캔으로 읽으면 3,322블록으로 FFS(3,341)와 같다. 이 문장은 쓰는 컬럼을 모두 담은 인덱스가 있어서, 기간이 무엇이든 인덱스 범위 스캔 하나가 맞는다. 값마다 자식을 나눌 이유가 없으니, 자식 43개는 하드 파싱과 공유 메모리, 자식 목록을 뒤지는 동안 잡는 mutex(cursor: mutex S)의 낭비다. 그래서 NO_BIND_AWARE로 이 문장의 ACS를 멈춘다. 다만 이것만 붙이면 자식 하나의 계획이 다시 첫 값에 달린다. 3년이 먼저 파싱되면 FFS가 서고, 하루를 볼 때도 3,341블록을 읽는다(하루 범위 스캔은 4블록). 그래서 INDEX_RS_ASC로 범위 스캔도 함께 고정한다. 힌트가 들어가면 텍스트가 바뀌어 새 부모로 시작하므로 VERSION_COUNT는 43 → 1, 3년 실행은 3,341 → 3,322, 하루는 4블록이다.

해설

ACS는 값마다 다른 계획이 필요할 때 쓰는 장치다. 계획이 하나로 충분한 문장에서 ACS가 움직이면 이득 없이 자식만 는다. 반대로 값마다 계획이 달라야 하는 문장(PARSE-026)에서는 ACS나 BIND_AWARE가 도움이 된다.
SQL Plan Baseline으로 범위 스캔 계획을 고정하는 방법도 있다. 그래도 ACS는 선택도 범위마다 자식을 계속 만들 수 있으므로, 자식 수를 줄이려면 NO_BIND_AWARE가 함께 있어야 한다.
_optimizer_adaptive_cursor_sharing 같은 밑줄 파라미터를 인스턴스에 걸면 ACS가 필요한 다른 문장까지 멈춘다. 문장 하나의 문제는 문장에서 고친다.
자식 수가 많은 문장을 찾을 때는 V$SQLAREA.VERSION_COUNT와 V$SQL_SHARED_CURSOR의 Y 칸을 함께 본다. 사유가 BIND_EQUIV_FAILURE인지, 바인드 그릇(PARSE-019)이나 타입(PARSE-031)인지에 따라 고칠 곳이 다르다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황