계획 해시가 그대로라 계획 문제는 아니라던 고객 최근 주문 조회가 실행마다 예순 배 넘게 읽는다

중급SELECT한빛유통644 / 700

상황

고객센터 「최근 완료 주문」 화면은 고객 번호를 받아 완료된 주문을 최신순으로 20건 보여 준다. 상담원 40명이 시간당 1,900번쯤 연다. 목요일 아침부터 화면이 굼뜨다는 말이 나왔다. DBA의 첫 답은 이랬다. "AWR에서 PLAN_HASH_VALUE가 수요일과 같습니다. DISPLAY_AWR로 펼쳐 봐도 계획이 똑같아요. 계획 문제는 아니고 서버 쪽을 봐야겠습니다." 목요일 05:30에는 고객센터 WAS 패치가 있었다(JDBC 드라이버 교체, 연결 설정 파일도 업체 기본값으로 바뀜). DB 쪽 변경은 없었다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_ST COMPLETE 90% · 고객 2,000명(고객마다 상태가 하나 · COMPLETE 고객 1,800명은 각 500건) · CUS_ID VARCHAR2(40)
인덱스 IX_ORD_BIG_ST_CUS_DT (ORD_ST, CUS_ID, ORD_DT): BLEVEL 2 · 리프 3,330블록(COMPLETE 부분 약 3,000블록)
DB 문자셋 AL32UTF8 · 국가별 문자셋 AL16UTF16 · 통계 최신

원본 SQL

sql

-- 「최근 완료 주문」 (SQL_ID 0v7c2nq8m4k1x)
SELECT ORD_SEQ, ORD_DT, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_ST = 'COMPLETE'
   AND CUS_ID = :cus_id
 ORDER BY ORD_DT DESC
 FETCH FIRST 20 ROWS ONLY

실행계획 학습용 예시

xplan

[DBA_HIST_SQLSTAT · SQL_ID 0v7c2nq8m4k1x]
 SNAP_ID  시작        PLAN_HASH_VALUE  EXECUTIONS_DELTA  BUFFER_GETS_DELTA  실행당 블록
    5210  수 10:00       2786091352             1,820             41,860          23
    5211  수 11:00       2786091352             1,905             43,815          23
    5230  목 10:00       2786091352             1,884          2,865,564       1,521
    5231  목 11:00       2786091352             1,913          2,909,673       1,521

[DBMS_XPLAN.DISPLAY_AWR('0v7c2nq8m4k1x') · 계획 하나 · AWR에는 조건절이 남지 않는다]
 VIEW → WINDOW NOSORT STOPKEY → TABLE ACCESS BY INDEX ROWID T_ORD_BIG → INDEX RANGE SCAN DESCENDING IX_ORD_BIG_ST_CUS_DT

[V$SQL · 지금]  자식 0(LAST_ACTIVE 수 23:58) · 자식 1(목 06:12부터) · 둘 다 PLAN_HASH_VALUE 2786091352
[V$SQL_SHARED_CURSOR · 자식 1]  BIND_MISMATCH = Y
[V$SQL_BIND_CAPTURE]  자식 0  :CUS_ID  VARCHAR2(128)   ·   자식 1  :CUS_ID  NVARCHAR2(128)

[자식 0 · 수요일 · :cus_id = 'CUS_001207']
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                      |      1 |        |     20 |00:00:00.01 |      23 |
|*  1 |  VIEW                          |                      |      1 |     20 |     20 |00:00:00.01 |      23 |
|*  2 |   WINDOW NOSORT STOPKEY        |                      |      1 |    500 |     20 |00:00:00.01 |      23 |
|   3 |    TABLE ACCESS BY INDEX ROWID | T_ORD_BIG            |      1 |    500 |     20 |00:00:00.01 |      23 |
|*  4 |     INDEX RANGE SCAN DESCENDING| IX_ORD_BIG_ST_CUS_DT |      1 |    500 |     20 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=20)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORD_DT") DESC )<=20)
   4 - access("ORD_ST"='COMPLETE' AND "CUS_ID"=:CUS_ID)

[자식 1 · 목요일 · 같은 고객]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                      |      1 |        |     20 |00:00:00.03 |    1212 |
|*  1 |  VIEW                          |                      |      1 |     20 |     20 |00:00:00.03 |    1212 |
|*  2 |   WINDOW NOSORT STOPKEY        |                      |      1 |   9000 |     20 |00:00:00.03 |    1212 |
|   3 |    TABLE ACCESS BY INDEX ROWID | T_ORD_BIG            |      1 |   9000 |     20 |00:00:00.03 |    1212 |
|*  4 |     INDEX RANGE SCAN DESCENDING| IX_ORD_BIG_ST_CUS_DT |      1 |   9000 |     20 |00:00:00.03 |    1192 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=20)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORD_DT") DESC )<=20)
   4 - access("ORD_ST"='COMPLETE')
       filter(SYS_OP_C2C("CUS_ID")=:CUS_ID)

문제

계획 해시가 같은데 실행당 블록이 크게 늘어난 이유를 두 자식 커서의 Predicate Information으로 설명하시오. DBA가 AWR만 보고 계획이 같다고 판단한 것의 한계도 쓰시오.
무엇이 조건절을 바꿨는지 찾아 쓰고, 화면이 다시 23블록 안팎으로 읽도록 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- 바인드를 컬럼 타입(VARCHAR2)에 맞춰 바꾼다 : 컬럼은 가공하지 않는다
SELECT ORD_SEQ, ORD_DT, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_ST = 'COMPLETE'
   AND CUS_ID = TO_CHAR(:cus_id)
 ORDER BY ORD_DT DESC
 FETCH FIRST 20 ROWS ONLY;

-- 근본 : WAS 연결 설정의 oracle.jdbc.defaultNChar=true 를 되돌리고,
--        NVARCHAR2 컬럼에만 setNString 을 쓰게 한다 (그러면 SQL 을 고치지 않아도 자식 0의 조건절로 돌아간다)

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

xplan

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                      |      1 |        |     20 |00:00:00.01 |      23 |
|*  1 |  VIEW                          |                      |      1 |     20 |     20 |00:00:00.01 |      23 |
|*  2 |   WINDOW NOSORT STOPKEY        |                      |      1 |    500 |     20 |00:00:00.01 |      23 |
|   3 |    TABLE ACCESS BY INDEX ROWID | T_ORD_BIG            |      1 |    500 |     20 |00:00:00.01 |      23 |
|*  4 |     INDEX RANGE SCAN DESCENDING| IX_ORD_BIG_ST_CUS_DT |      1 |    500 |     20 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=20)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORD_DT") DESC )<=20)
   4 - access("ORD_ST"='COMPLETE' AND "CUS_ID"=TO_CHAR(:CUS_ID))

모범 답안 서술

PLAN_HASH_VALUE는 계획의 연산과 객체, 줄의 모양으로 셈하고 Predicate Information은 넣지 않는다. 그래서 두 자식 커서의 해시가 2786091352로 같아도 조건절은 다를 수 있다. 실제로 수요일의 자식 0은 4번 줄이 access("ORD_ST"='COMPLETE' AND "CUS_ID"=:CUS_ID)라 그 고객의 줄만 짚어 3블록, 테이블 20블록, 합계 23블록을 읽었다. 목요일의 자식 1은 access("ORD_ST"='COMPLETE'), filter(SYS_OP_C2C("CUS_ID")=:CUS_ID)다. 인덱스에서 범위를 짚는 데는 ORD_ST만 쓰이고, CUS_ID는 걸러 내는 조건으로 내려갔다. 그래서 완료 주문 90만 건의 인덱스 줄을 뒤에서부터 읽으며 이 고객이 나올 때까지 걸렀다. 'CUS_001207'이면 리프 1,190블록을 지나 1,212블록이고, 고객 평균으로는 실행당 1,521블록이다. DBA의 판단에는 한계가 있었다. DBMS_XPLAN.DISPLAY_AWR이 보여 주는 AWR의 계획에는 조건절이 남지 않는다. 그래서 AWR만으로는 계획 해시가 같으면 같은 계획처럼 보인다. 계획 해시가 같은데 실행당 블록이 66배로 늘었다면 커서 캐시에 남은 자식 커서들을 DISPLAY_CURSOR로 펼쳐 조건절을 견줘야 한다. 조건절을 바꾼 것은 바인드 타입이다. 자식 1은 BIND_MISMATCH로 생겼고, V$SQL_BIND_CAPTURE에서 :CUS_ID가 VARCHAR2에서 NVARCHAR2로 바뀌었다. 목요일 05:30 WAS 패치로 드라이버의 연결 설정이 업체 기본값(defaultNChar=true)으로 바뀌어, 문자열 바인드를 모두 국가별 문자셋으로 보내게 된 것이다. NVARCHAR2와 VARCHAR2 컬럼을 비교하면 컬럼 쪽이 국가별 문자셋으로 바뀐다(SYS_OP_C2C). 컬럼이 가공되었으니 그 열로는 인덱스 범위를 짚지 못한다. SQL에서는 바인드 쪽을 TO_CHAR(:cus_id)로 컬럼 타입에 맞춘다. 컬럼은 그대로이므로 다시 (ORD_ST, CUS_ID)로 범위를 짚고 실행당 1,521 → 23블록, 시간당 약 290만 → 4만 4천 블록이다. 근본은 연결 설정을 되돌려 VARCHAR2 컬럼에는 VARCHAR2 바인드를 보내게 하는 것이다. 그러면 같은 모양의 다른 SQL도 함께 고쳐진다.

해설

계획 해시가 같다는 것은 연산 모양이 같다는 뜻일 뿐이다. 같은 인덱스를 같은 연산으로 읽어도 access와 filter가 다르면 일량은 수십 배 달라진다. 계획 해시가 같은데 실행당 숫자가 늘면 조건절, 바인드 타입, 데이터 분포를 차례로 본다(OPT-047은 실행당 숫자까지 같은 경우다).
같은 실수가 다른 SQL에도 있다. 드라이버 설정은 연결 전체에 걸리므로 고객센터 WAS의 모든 문자열 바인드가 NVARCHAR2로 바뀌었다. V$SQL_BIND_CAPTURE에서 NVARCHAR2 바인드가 붙은 SQL을 찾고 V$SQL_SHARED_CURSOR의 BIND_MISMATCH를 함께 본다. SQL을 하나씩 고치기보다 설정을 되돌리는 것이 맞다.
컬럼에 함수를 씌워 맞추는 것(TO_NCHAR(CUS_ID))은 거꾸로다. 컬럼을 가공하면 지금과 같은 일이 생긴다. 맞추는 쪽은 언제나 바인드다(교재 02화, 03화).
이 화면은 20건에서 멈추는 WINDOW NOSORT STOPKEY 덕에 운 좋게 1,500블록 안팎에서 멈췄다. 같은 원인으로 멈춤 조건이 없는 SQL은 완료 주문 전체를 거르고, 그만큼 비싸 보이므로 계획 해시도 바뀌어 AWR에서 먼저 눈에 띈다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황