7.5 데이터 조작 프로시저 최적화

쿼리 성능 개선

DB에서 프로시저에 있는 SQL 실행 계획을 분석, 수정을 통해 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 작업. SQL 성능 개선을 통해 데이터 조작 프로시저의 성능 개선 가능.

 

쿼리 성능 개선 절차

문제 있는 SQL 식별 -> 옵티마이저 통계 확인 -> SQL문 재구성 -> 인덱스 재구성 -> 실행계획 유지관리

 

1) 문제 있는 SQL 식별 : APM 활용.

* APM(Application Performance Monitoring) : 안정적인 시스템 운영을 위해 부하량, 접속자 파악 및 장애진단 등을 목적으로 하는 성능 모니터링 도구 의미.

 

2) 옵티마이저 통계 확인 : SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DB 핵심 모듈

 

3) SQL문 재구성 : 범위가 아닌 특정 값 지정으로 범위를 줄여 처리속도를 빠르게 함. 옵티마이저가 비정상적인 실행계획 수립 시, 힌트로서 옵티마이저의 접근 경로 및 조인 순서를 제어함.

 

4) 인덱스 재구성 : 성능에 중요한 액세스 경로를 고려해 인덱스 생성. 실행 계획 검토하여 기존 인덱스의 열 순서 변경 및 추가.

 

5) 실행계획 유지관리 : DB 버전 업그레이드, 데이터 전환 등 시스템 환경의 변경 사항 발생 시에도 실행 계획이 유지되고 있는지 관리함.

 

옵티마이저

SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부 핵심엔진.

SQL 처리경로 = 실행 계획

 

옵티마이저 유형

규칙기반 옵티마이저(RBO)	비용기반 옵티마이저(CBO)
통계 정보가 없는 상태에서 사전 등록된 규칙에 따라 질의 실행 계획을 선택하는 옵티마이저	통계 정보로부터 모든 접근 경로를 고려한 질의 실행 계획을 선택하는 옵티마이저
규칙(우선 순위) 기반	비용(수행 시간) 기반
인덱스 구조, 연산자, 조건절 형태 등으로 평가	레코드 개수, 블록 개수, 평균 행 길이, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 등으로 평가
사용자가 원하는 처리경로로 유도하기가 쉬움	옵티마이저의 이해도가 낮아도 성능보장이 가능

 

SQL 수행과정 내 옵티마이저 역할

- 쿼리 변환(Query Transformer) : SQL을 좀 더 일반적이고 표준화된 형태로 변환.

- 비용 산정(Estimator) : 쿼리 명령어 각 단계의 선택도, 카디널리티, 비용 계산. 실행계획 전체에 대한 총비용을 계산.

- 계획 생성(Plan Generator) : 하나의 쿼리를 수행 시 후보군이 될 만한 실행계획들을 생성해내는 역할.

 

힌트 사용

SQL 성능 개선의 핵심 부분으로 옵티마이저의 실행 계획을 원하는 대로 변경할 수 있게 함.

옵티마이저가 항상 최선의 실행계획을 수립할 순 없기에 명시적 힌트를 통해 실행계획을 변경함.

 

SQL문 재구성

특정 값 지정 / 별도 SQL 사용 / 힌트 사용 / HAVING 미사용 / 인덱스만 질의 사용

 

인덱스 재구성

자주쓰는 컬럼 선정 / SORT 명령어 생략 / 분포도 고려 / 변경 적은 컬럼 선정 / 결합 인덱스 사용