SQLP 튜닝 연습장

테마

튜닝 연습인덱스 사용 조건
IDX-028

새 키오스크에서만 영수증 재출력이 판매상품 110만 줄을 다 읽는다

중급SELECT유통28 / 700

상황 ​

유통 매장에 새로 들인 셀프 키오스크는 영수증 재출력 화면에서 판매번호로 판매상품을 읽는다. 카운터 POS 프로그램도 같은 SQL을 쓰는데 POS에서는 바로 뜨고, 키오스크에서만 1초 가까이 걸린다. 키오스크 개발사는 "바인드 변수도 쓰고 SQL도 POS와 글자 하나 다르지 않다"라고 한다. 키오스크 앱의 DB 접속 설정에는 다국어 메뉴를 위해 defaultNChar=true(문자열을 모두 국가 문자 타입으로 보낸다)가 켜져 있다.

테이블과 인덱스 ​

  • CF11 판매상품: 1,096,708행 · 8,204블록 · SLE_NO VARCHAR2(15) · 판매 한 건에 상품 평균 2.2개
  • 인덱스 CF11_PK (SLE_NO, ITM_NO): BLEVEL 2 · 리프 약 4,290블록 · 통계 최신
  • 데이터베이스 문자 집합 AL32UTF8 · 국가 문자 집합 AL16UTF16 · 이 스키마에 NVARCHAR2 컬럼은 하나도 없다

원본 SQL ​

sql
SELECT ITM_NO, SLE_QTY, SLE_UPRC
  FROM CF11
 WHERE SLE_NO = :1
 ORDER BY ITM_NO

실행계획 학습용 예시 ​

xplan
[키오스크 · '20231115-0412']
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |      3 |00:00:00.92 |    8207 |
|   1 |  SORT ORDER BY              |         |      1 |  10967 |      3 |00:00:00.92 |    8207 |
|*  2 |   TABLE ACCESS FULL         | CF11    |      1 |  10967 |      3 |00:00:00.92 |    8207 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SYS_OP_C2C("SLE_NO")=:1)

[POS · 같은 판매번호]
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |      3 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CF11    |      1 |      2 |      3 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN          | CF11_PK |      1 |      2 |      3 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SLE_NO"=:1)

[V$SQL_BIND_CAPTURE · 같은 SQL_ID의 자식 커서 둘]
CHILD_NUMBER  NAME  DATATYPE_STRING
------------  ----  ---------------
           0  :1    VARCHAR2(32)       ← POS
           1  :1    NVARCHAR2(128)     ← 키오스크

문제 ​

  1. 같은 SQL인데 키오스크에서만 TABLE ACCESS FULL이 되는 이유를 2번 줄의 SYS_OP_C2C와 바인드 정보로 설명하시오.
  2. 키오스크에서도 CF11_PK로 찾아가도록 고치는 방법을 SQL 쪽과 접속 설정 쪽으로 나눠 제시하시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자