[정보처리기사 실기] 7장 SQL 응용 - 데이터 조작 프로시저 최적화

데이터 조작 프로시저 성능 개선

쿼리 성능 개선의 개념

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

쿼리 성능 개성 절차

1. 문제 있는 SQL 식별 : 앱 성능을 관리 및 모니터링 도구인 APM 활용
2. 옵티마이저 통계 확인 : 개발자가 작성한 SQL을 가장 빠르고 효율적으로 수행할 최적의 처리 경로를 생성해주는 데이터베이스 핵심 모듈
3. SQL 문 재구성 : 범위가 아닌 특정 값 지정으로 범위를 줄여 처리 속도 빠르게 함, 힌트로서 옵티마이저의 접근 경로 및 조인 순서 제어
4. 인덱스 재구성 : 액세스 경로를 고려하여 인덱스 생성, 실행계획을 검토하여 기존 인덱스의 열 순서 변경/추가
5. 실행 계획 유지 관리 : 디비 버전 업그레이드, 데이터 전환 등 환경의 변경 사항 발생 시에도 실행 계획 유지 관리

옵티마이저 통계 확인

(1) 개념

- 가장 빠르고 효율적으로 수행할 최적의 처리 경로 생성하는 핵심 엔진, SQL 처리 경로를 실행 계획이라 한다

 

(2) 유형

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

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

• 쿼리 변환 : 좀 더 일반적이고 표준화된 형태로 변화
• 비용 산정 : 각 단계의 선택도 카디널리티 비용 계산, 전체에 대한 총 비용 계산
• 계획 생성 : 후보군이 될 만한 실행 계획들을 생성해내는 역할

힌트 사용

- 실행 계획을 원하는 대로 변경 할 수 있게 한다, 항상 최선의 실행 계획을 수립할 수 없어 명시적인 힌트를 통해 실행 계획 변경

• /* + 힌트 */
  • RULE : 규칙 기반 접근 방식 사용하도록 지정
  • CHOOSE : 오라클 옵티마이저 디폴트 값에 따름
  • INDEX(테이블명 인덱스명) : 지정된 인덱스를 강제적으로 사용하도록 지정
  • USE_HASE(테이블명) : Hash Join 형식으로 유도
  • USE_MERGE(테이블명) : Sort Merge 형식으로 유도
  • USE_NL(테이블명) : Nested Loop 형식으로 유도

SQL 재구성

• 특정 값 지정 : 조건절의 =를 사용, 범위가 아닌 특정 값 지정으로 범위 줄임
• 별도의 SQL 사용 : UNION ALL 사용, 최적화
• 힌트 사용 : 힌트로서 액세스 경로 및 조인 순서 제어
• HAVING 미사용 : HAVING 사용 시 인덱스 미사용
• 인덱스만 질의 사용 : 가능한 인덱스만 이용해 질의 수행해 옵티마이저가 최적의 경로를 찾도록 유도

인덱스 재구성

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