통계정보와 실행계획 변화

#핵심 요약

통계정보는 옵티마이저 비용 계산의 기초 자료임
부정확하거나 오래되면 예상 건수와 비용 계산이 틀어져 비효율적인 실행계획이 선택될 수 있음

오브젝트 통계(테이블, 인덱스, 컬럼, 히스토그램)와 시스템 통계(CPU 속도, Single/Multi Block I/O, I/O 처리량)로 나뉨

값이 한쪽에 쏠린 컬럼은 히스토그램이 있어야 선택도를 정확히 계산함

통계가 없거나 믿기 어려우면 다이나믹 샘플링이 파싱 때 테이블 일부를 읽어 임시 정보를 만듦

SQL이 갑자기 느려졌다면 먼저 통계정보가 최신인지, 실제 분포와 맞는지 확인함

#개념과 동작 원리

설명: CLUSTERING_FACTOR는 인덱스 순서와 테이블 저장 순서가 얼마나 비슷한지를 나타내며, 인덱스 스캔 뒤 테이블 블록을 얼마나 효율적으로 읽을지 판단하는 중요한 기준임(옵티마이저와 비용)

#주요 용어와 구성 요소

#필수 명령어 또는 SQL

#통계 확인과 수집

SELECT column_name, num_distinct, num_buckets, histogram
  FROM user_tab_col_statistics
 WHERE table_name = '<TABLE_NAME>';

SELECT table_name, num_rows, last_analyzed
  FROM user_tables
 WHERE table_name = '<TABLE_NAME>';

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, TABNAME => '<TABLE_NAME>', METHOD_OPT => 'for all columns size auto');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, '<TABLE_NAME>', CASCADE => TRUE);

결과 해석: 첫 수집은 컬럼 히스토그램 버킷 수를 자동으로 정함
두 번째의 CASCADE => TRUE는 관련 인덱스 통계도 함께 수집함
통계를 다시 모으면 이후 하드 파싱되는 SQL의 계획이 바뀔 수 있음

#다이나믹 샘플링

ALTER SYSTEM SET optimizer_dynamic_sampling = 0;

결과 해석:

주석: 기본값은 2임
0으로 바꾸면 다이나믹 샘플링을 끈다는 실습 의도로 보이나 결과는 기재 없음
인스턴스 전체 설정이므로 실습 DB에서만 바꿈

#자동 통계 수집 작업

SELECT window_name, repeat_interval, duration
  FROM dba_scheduler_windows;

SELECT client_name, status, window_group
  FROM dba_autotask_client;

결과 해석:

설명: 매일 정해진 시간에 Oracle이 자동 작업(자동 통계 수집 auto optimizer stats collection 포함)을 돌림
실습에서는 배치 작업에 영향을 줘서 대부분 끄고 쓴다고 적었음

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => NULL);
END;
/

결과 해석:

자동 수집을 끄면 통계를 직접 관리해야 함
"대부분 끈다"는 실습의 현장 의견임
자동 옵티마이저 통계 수집은 유지하는 것이 권장되며 작업 충돌은 유지보수 창 조정을 먼저 검토함

#Result Cache

SELECT * FROM v$result_cache_statistics;

SELECT name, type, status, block_count, row_count, scan_count
  FROM v$result_cache_objects
 ORDER BY scan_count DESC;

SELECT /*+ result_cache */ empno, ename, sal
  FROM emp
 WHERE sal >= 2000;

결과 해석:

설명: RESULT_CACHE 힌트로 조회 결과를 결과 캐시에 올림
수업 자료 메모의 통계 값 해석에는 "캐시 히트 2"를 "3번"으로 적은 곳이 있음
캐시 크기는 ALTER SYSTEM SET result_cache_max_size =...로 늘렸음(인스턴스 설정 변경)

#SQL 튜닝 어드바이저

DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext   CLOB;
BEGIN
  my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n = 1';
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                    sql_text    => my_sqltext,
                    user_name   => '<USERNAME>',
                    scope       => 'COMPREHENSIVE',
                    time_limit  => 60,
                    task_name   => 'my_sql_tuning_task_1',
                    description => 'Task to tune a query on a specified table');
END;
/
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'my_sql_tuning_task_1');

SET LONG 70000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_1') FROM dual;

결과 해석:

SQL Tuning Advisor는 Tuning Pack 적용 기능이며 선행 조건인 Diagnostics Pack도 확인함

#구문과 옵션 설명

GATHER_TABLE_STATS는 대상 소유자 또는 일반 객체의 ANALYZE ANY 권한을 가진 계정이 수행함
METHOD_OPT의 SIZE AUTO는 히스토그램 생성을 자동 판단하며 모든 컬럼에 반드시 히스토그램을 만드는 뜻은 아님

OPTIMIZER_DYNAMIC_SAMPLING은 0~11 범위이며 OPTIMIZER_FEATURES_ENABLE이 10.0.0 이상일 때 기본값은 2임
0 지정은 동적 통계를 비활성화하는 설정이므로 전역 변경의 영향을 확인함

V$RESULT_CACHE_OBJECTS의 TYPE은 Result와 Dependency를 구분함
STATUS의 Published는 사용 가능, Invalid는 재사용 불가 상태임
결과에 사용한 객체가 변경되면 캐시가 무효화되어 힌트가 있어도 적중하지 않을 수 있음

튜닝 작업의 COMPREHENSIVE는 종합 분석 범위, time_limit은 분석 시간 한도임
입력 60은 기존 작업의 초 단위 한도이며 실제 소요 시간이나 개선 효과가 아님

#사용 조건과 주의사항

OPTIMIZER_MODE는 ALTER SESSION, ALTER SYSTEM으로 변경 가능한 동적 파라미터임
이 값을 바꾸기 위해 항상 재기동해야 하는 것은 아님

튜닝 작업 인터페이스에는 ADVISOR 권한이 필요함
기능 활성화 파라미터나 패키지 호출 권한만으로 Pack 사용 권한이 부여되지는 않음

통계 수집, 히스토그램, 전역 파라미터 변경은 계획을 바꿀 수 있으므로 적용 범위와 기존 계획을 확인함
자동 수집을 끄는 경우에는 누락, 오래된 통계를 대신 관리할 수집 정책이 필요함