적응형 기능을 다 켜야 추정이 맞는다며 설정 한 줄을 넣은 뒤로 처음 보는 문장이 실행 횟수 1위에 올랐다

중급DDL한빛유통646 / 700

상황

지난주 금요일 18:20 오과장이 운영 DB에 설정 한 줄을 넣었다. "12c 적응형 기능을 다 켜야 E-Rows가 맞는대요. 기본값이 FALSE인 건 보수적이라서고요." 월요일 AWR의 「SQL ordered by Executions」 1위에 처음 보는 문장이 올라왔고, 하드 파싱 시간이 세 배로 늘었다. 정산팀은 「고객 취소 주문」 화면이 가끔 한 박자 늦게 뜬다고 한다. 박팀장은 그 설정을 그대로 둘지 정해 달라고 했다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 고객마다 상태가 하나(CANCEL 고객 40명 · 고객당 500건) · ORD_ST에 FREQUENCY 히스토그램(통계 수집 정책서 1호, 5월 6일부터)
SQL Plan Directive: 5월부터 테이블 4개에 지시문 11개가 쌓여 있다(기본 설정에서는 적어 두기만 한다)
그 밖의 옵티마이저 설정은 기본값 · Diagnostic Pack 계약 있음

원본 SQL

sql

-- 금요일 18:20 (오과장)
ALTER SYSTEM SET optimizer_adaptive_statistics = TRUE SCOPE = BOTH;

-- 정산팀 「고객 취소 주문」 (고객 번호는 화면이 리터럴로 넣는다)
SELECT ORD_SEQ, ORD_DT, ORD_AMT
  FROM T_ORD_BIG
 WHERE CUS_ID = 'CUS_000001'
   AND ORD_ST = 'CANCEL';

실행계획 학습용 예시 · E-Rows는 교재 10화 §10의 실측값

xplan

[AWR · 월 09:00 ~ 10:00 · 지난주 월요일 같은 시간과 비교]
 hard parse elapsed time        41.2초 → 133.8초
 SQL ordered by Executions 1위  (지난주 0번 → 18,240번)
   SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) result_cache */
          SUM(C1) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS("T_ORD_BIG") */ 1 AS C1
                          FROM "T_ORD_BIG" SAMPLE BLOCK(…) WHERE …) innerQuery

[DBA_SQL_PLAN_DIRECTIVES · DBA_SQL_PLAN_DIR_OBJECTS · T_ORD_BIG]
 DIRECTIVE  TYPE              STATE   REASON                                CREATED  LAST_USED   대상 열
 …4417      DYNAMIC_SAMPLING  USABLE  SINGLE TABLE CARDINALITY MISESTIMATE  05-06    월 09:12    CUS_ID, ORD_ST
 …9203      DYNAMIC_SAMPLING  USABLE  SINGLE TABLE CARDINALITY MISESTIMATE  05-06    월 09:31    ORD_DT, ORD_ST

[「고객 취소 주문」 · 하드 파싱될 때마다]
 금요일 전(FALSE)   E-Rows 10                        Note 없음
 월요일 1회차       E-Rows 500   Note: dynamic statistics used: dynamic sampling (level=AUTO)
 월요일 2회차       E-Rows 500         1 Sql Plan Directive used for this statement
 월요일 3회차       E-Rows 537
 월요일 4회차       E-Rows 555
 실제 A-Rows 500 · 계획은 넷 다 같다(TABLE ACCESS FULL · 5,826블록)

[참고 · 지시문 …9203 이 생긴 날의 조건(10화)]
 6월 AND CANCEL   히스토그램 없음 9,445 · FREQUENCY 567 · 실제 549

문제

설정 한 줄 뒤에 일어난 일을 지시문, 동적 샘플링, 하드 파싱으로 설명하시오. 같은 SQL의 E-Rows가 하드 파싱마다 다른 이유도 쓰시오.
이 설정을 그대로 둘지 판단하고 SQL로 정리하시오. 지시문 두 개를 각각 어떻게 다룰지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 진짜 상관인 (CUS_ID, ORD_ST)은 통계로 알려 준다 : 컬럼 그룹 + 그룹 히스토그램 (10화 §8의 규칙)
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'T_ORD_BIG', '(CUS_ID,ORD_ST)') FROM dual;
BEGIN
  DBMS_STATS.SET_TABLE_PREFS(USER, 'T_ORD_BIG', 'METHOD_OPT',
    'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 ORD_ST FOR COLUMNS SIZE 2048 (CUS_ID,ORD_ST)');
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORD_BIG');
END;
/

-- ② 설정은 기본값으로 되돌린다 (지시문은 다시 적어 두기만 한다)
ALTER SYSTEM SET optimizer_adaptive_statistics = FALSE SCOPE = BOTH;

-- ③ 지시문은 추정이 크게 틀리는 열을 알려 주는 목록으로 읽는다
SELECT d.DIRECTIVE_ID, d.STATE, d.REASON, d.LAST_USED, o.OBJECT_NAME, o.SUBOBJECT_NAME
  FROM DBA_SQL_PLAN_DIRECTIVES d
  JOIN DBA_SQL_PLAN_DIR_OBJECTS o ON o.DIRECTIVE_ID = d.DIRECTIVE_ID
 WHERE o.OWNER = USER
 ORDER BY d.LAST_USED DESC;

모범 답안 서술

금요일에 optimizer_adaptive_statistics를 TRUE로 바꾼 것이 시작이다. 이 값이 FALSE(기본값)일 때 SQL Plan Directive는 적어 두기만 하고 계획에는 쓰이지 않는다(교재 10화 §10). TRUE가 되자 5월부터 쌓여 있던 지시문 11개가 쓰이기 시작했다. 지시문이 걸린 테이블과 열을 읽는 SQL은 하드 파싱할 때마다 동적 샘플링(level=AUTO)으로 표본을 다시 뜬다. AWR 1위의 /* DS_SVC */ 문장이 그 표본 쿼리이고(지난주 0번, 이번 주 시간당 18,240번), 하드 파싱 시간이 41.2초 → 133.8초로 늘어난 것도 그 때문이다. 「고객 취소 주문」이 가끔 한 박자 늦는 것은 그 SQL이 하드 파싱되는 순간이다. 화면이 고객 번호를 리터럴로 넣으므로 고객마다 하드 파싱이 일어난다. E-Rows가 500, 537, 555로 흔들리는 것도 표본 때문이다. 표본은 뜰 때마다 다른 블록을 고르므로 같은 SQL이라도 하드 파싱마다 추정이 조금씩 다르다. 이번에는 계획이 바뀔 만큼 크지 않았지만, 문턱 근처의 SQL이라면 파싱할 때마다 계획이 달라질 수 있다. 지시문 둘의 사정은 다르다. …9203 (ORD_DT, ORD_ST)는 5월 6일, ORD_ST에 히스토그램이 없던 때(6월 AND CANCEL 9,445 대 실제 549) 생겼다. 지금은 FREQUENCY 히스토그램으로 567을 보므로 고칠 것이 없다. 이미 쓸모없어진 지시문이 표본만 뜨게 하는 셈이다. …4417 (CUS_ID, ORD_ST)는 진짜 상관이다. 고객마다 상태가 하나라 실제는 500인데 히스토그램으로는 10으로 본다. 그래서 설정을 FALSE로 되돌린다. 진짜 상관인 (CUS_ID, ORD_ST)는 하드 파싱마다 표본을 뜨는 대신 통계로 알려 준다. 컬럼 그룹을 만들고 그룹에도 히스토그램을 걸면(10화 §8의 규칙) 표본 없이 500으로 본다. …9203은 따로 손대지 않는다. FALSE로 돌아가면 다시 적어 두기만 하고, 쓰이지 않는 지시문은 보존 기간이 지나면 저절로 지워진다. 지시문은 앞으로도 계속 적히므로 DBA_SQL_PLAN_DIRECTIVES는 추정이 크게 틀리는 열을 알려 주는 목록으로 읽는다. 단일 테이블의 Statistics Feedback은 이 설정과 상관없이 그대로 돈다.

해설

TRUE가 늘 틀린 것은 아니다. 지시문 덕에 「고객 취소 주문」의 E-Rows는 10 → 500으로 맞아졌다. 문제는 그 값을 얻는 방법이 하드 파싱마다 표본을 뜨는 것이라는 점이다. 리터럴 SQL이 많은 시스템일수록 비싸고, 표본이라 흔들린다. 같은 숫자를 통계(컬럼 그룹)로 주면 공짜이고 늘 같다.
설정 하나가 시스템 전체의 하드 파싱을 바꾼다. 화면 하나를 위해 인스턴스 설정을 바꾸기 전에, 그 설정이 바꾸는 범위를 먼저 잰다. 이번처럼 이미 쌓여 있던 지시문이 한꺼번에 깨어나는 일은 설정을 바꾼 사람도 예상하지 못한다(교재 10화 §10의 ⚠️: 원본에서 돌린 쿼리가 지시문을 남긴다).
지시문을 지우려면 권한이 필요하다. DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE와 FLUSH_SQL_PLAN_DIRECTIVE는 일반 계정으로 부르면 ORA-38171이 난다(교재 10화 실측). 급하지 않으면 FALSE로 되돌리고 보존 기간에 맡긴다.
optimizer_adaptive_plans(적응적 조인 방법)는 다른 설정이고 기본값이 TRUE다. 'E-Rows를 맞추는' 기능과 '계획을 실행 중에 바꾸는' 기능은 따로 켜고 끈다(OPT-049).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · E-Rows는 교재 10화 §10의 실측값 ​

문제 ​

모범 답안 SQL ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황

테이블과 인덱스

원본 SQL

실행계획 학습용 예시 · E-Rows는 교재 10화 §10의 실측값

문제

모범 답안 SQL

모범 답안 서술

해설

관련 교재