매장 관리 도구에서 하루 주문 목록을 열 때만 22,422블록을 읽는다

중급SELECT버거576 / 700

상황

버거 본사 화면(Java)과 매장 관리 도구(C#, ODP.NET, 2022)가 같은 「매장 하루 주문 목록」 SQL을 쓴다. 본사 화면은 0.01초인데 매장 관리 도구는 0.4초쯤 걸린다. 점장들이 하루 1만 5천 번쯤 열어서 DB CPU의 상당 부분을 차지한다. 매장 관리 도구 개발자가 말했다. "다국어 때문에 문자열 파라미터는 모두 NVarchar2로 넘기라는 사내 규칙이 있어서요."

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · 날짜순 적재 · ORD_DT VARCHAR2(8) 하루 약 1,370건 · ORD_HMS VARCHAR2(6) · SHOP_NO VARCHAR2 'SH0001' ~ 'SH0200'(매장마다 하루 약 7건)
인덱스 ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS): CF 22,223
DB 문자셋 AL32UTF8 · 국가별 문자셋 AL16UTF16 · 통계 최신

원본 SQL

sql

-- 「매장 하루 주문 목록」 (두 프로그램이 같은 문자열)
SELECT ORD_NO, ORD_HMS
  FROM ORD
 WHERE ORD_DT = :d
   AND SHOP_NO = :shop
 ORDER BY ORD_HMS

// 매장 관리 도구 (C#, ODP.NET) : 사내 규칙대로 문자열은 NVarchar2
cmd.Parameters.Add(new OracleParameter("d", OracleDbType.NVarchar2) { Value = ordDt });
cmd.Parameters.Add(new OracleParameter("shop", OracleDbType.NVarchar2) { Value = shopNo });

실행계획 학습용 예시

xplan

[V$SQL — 같은 SQL_ID의 자식 둘]
 CHILD  PLAN_HASH    EXECUTIONS  BUFFER_GETS/EXEC  MODULE
     0  3702938145        3,240                38  JDBC Thin Client
     1  1093856276       14,872            22,422  ShopManager.exe

[V$SQL_SHARED_CURSOR — Y인 칸]   child 1 : BIND_MISMATCH = Y
[V$SQL_BIND_CAPTURE]
 CHILD  NAME   DATATYPE_STRING
     0  :D     VARCHAR2(32)
     0  :SHOP  VARCHAR2(32)
     1  :D     NVARCHAR2(32)
     1  :SHOP  NVARCHAR2(32)

[자식 1 — 매장 관리 도구 한 번]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |         |      1 |        |      7 |00:00:00.38 |   22422 |          |
|   1 |  SORT ORDER BY             |         |      1 |    100 |      7 |00:00:00.38 |   22422 | 2048  (0)|
|*  2 |   TABLE ACCESS FULL        | ORD     |      1 |    100 |      7 |00:00:00.38 |   22422 |          |
-------------------------------------------------------------------------------------------------------------
   2 - filter((SYS_OP_C2C("ORD_DT")=:D AND SYS_OP_C2C("SHOP_NO")=:SHOP))

[자식 0 — 본사 화면 한 번]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |         |      1 |        |      7 |00:00:00.01 |      38 |          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| ORD    |      1 |      7 |      7 |00:00:00.01 |      38 |          |
|*  2 |   INDEX RANGE SCAN         | ORD_X01 |      1 |   1370 |   1370 |00:00:00.01 |       7 |          |
-------------------------------------------------------------------------------------------------------------
   1 - filter("SHOP_NO"=:SHOP)
   2 - access("ORD_DT"=:D)

문제

같은 SQL 텍스트에 자식이 둘이고, 매장 관리 도구의 자식만 테이블 전체를 읽는 이유를 위 자료로 설명하시오.
매장 관리 도구도 인덱스를 타도록 고치시오. 사내 규칙(다국어 때문에 NVarchar2)을 어떻게 고쳐 쓸지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

// 매장 관리 도구 (C#) : 컬럼이 VARCHAR2 이면 파라미터도 Varchar2 · 크기도 컬럼에 맞춘다
cmd.Parameters.Add(new OracleParameter("d", OracleDbType.Varchar2, 8) { Value = ordDt });
cmd.Parameters.Add(new OracleParameter("shop", OracleDbType.Varchar2, 6) { Value = shopNo });

-- SQL 은 그대로 (본사 화면과 같은 자식을 쓴다)
SELECT ORD_NO, ORD_HMS
  FROM ORD
 WHERE ORD_DT = :d
   AND SHOP_NO = :shop
 ORDER BY ORD_HMS

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

xplan

[다음 날 — V$SQL]
 CHILD  PLAN_HASH    EXECUTIONS  BUFFER_GETS/EXEC  MODULE
     0  3702938145       18,190                38  JDBC Thin Client · ShopManager.exe
(ORD_X01 범위 + 테이블 · 인덱스 순서가 ORD_HMS 순이라 정렬도 없다)

모범 답안 서술

V$SQL_BIND_CAPTURE를 보면 자식 0의 바인드는 VARCHAR2, 자식 1의 바인드는 NVARCHAR2다. 오라클은 텍스트가 같아도 바인드 타입이 다르면 기존 자식을 같이 쓰지 않고 새로 만든다(BIND_MISMATCH = Y). 그래서 같은 SQL_ID에 계획이 둘이다. 자식 1은 VARCHAR2 컬럼과 NVARCHAR2 값을 비교해야 한다. 국가별 문자셋(AL16UTF16) 쪽이 더 넓은 타입이므로 오라클은 컬럼 쪽을 바꾼다. 2번 줄의 SYS_OP_C2C("ORD_DT"), SYS_OP_C2C("SHOP_NO")가 그 변환이다. 컬럼이 함수로 가공되었으니 ORD_X01에서 하루치를 지목하지 못하고, 테이블 전체(22,422블록)를 읽으며 행마다 문자셋을 바꿔 비교한다. 본사 화면의 자식 0은 같은 문장을 ORD_X01 범위로 읽어 38블록이다. 매장 관리 도구도 컬럼과 같은 OracleDbType.Varchar2로 넘기고 크기도 컬럼에 맞춘다. 그러면 타입과 그릇이 본사 화면과 같아져 자식 0을 같이 쓴다. 한 번에 22,422 → 38블록이고, 하루 1만 5천 번이면 약 3억 3,600만 → 57만 블록이다. 사내 규칙은 "다국어 때문에"라는 이유가 이 DB에는 맞지 않는다. DB 문자셋이 AL32UTF8(유니코드)이라 VARCHAR2 컬럼에 한글과 다국어가 이미 담긴다. 규칙은 "파라미터 타입은 컬럼 타입을 따른다. NVarchar2는 NVARCHAR2 컬럼에만 쓴다"로 고친다.

해설

SQL 쪽에서 WHERE ORD_DT = TO_CHAR(:d) AND SHOP_NO = TO_CHAR(:shop)으로 바인드를 DB 문자셋으로 바꿔도 인덱스는 탄다. 하지만 바인드 타입은 여전히 달라 자식이 둘로 남고, 다른 프로그램이 같은 실수를 하면 또 막아야 한다. 타입은 넘기는 쪽에서 맞춘다.
SYS_OP_C2C("ORD_DT")로 함수 기반 인덱스를 만드는 방법도 있다. 그러나 인덱스가 하나 더 생겨 POS 적재 비용이 늘고, 내부 함수에 기대는 인덱스라 다음 사람이 이유를 알기 어렵다.
숫자 바인드로 문자 컬럼을 비교한 IDX-022의 TO_NUMBER("ORD_DT")와 같은 구조다. 오라클은 더 넓은 쪽으로 맞추고, 바뀐 쪽이 컬럼이면 인덱스를 잃는다(교재 03화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황