실무로 배우는시스템 성능 최적화Ch6. SQL(Oracle) 튜닝

Part#2아꿈사Cecil

•SQL 실행 계획•SQL 성능 개선

실행 계획이란 ?

옵티마이저가 특정 SQL 을 실행하기 위해 수행할 일련의 동작을 표현한

계획

실행 계획 보기오라클 MySQL

EXPLAIN PLAN statement_id=‘sql_id’ FOR

SELECT ~ WHERE ~ ;SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,

‘sql_id’, null));

ALTER SESSION SET STATISTICS_LEVEL=ALL;

SELECT ~ WHERE ~ ;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR

(NULL, NULL, ‘ALLSTATS LAST’));

EXPLAIN SELECT ~ WHERE ~ ;

실행 계획의 이해• 가장 안쪽에서 바깥쪽으로• 같은 레벨에서는 위에 것이 먼저

동작

주요 항목Operation 수행한 내부 동작 이름

Name 사용한 테이블 명 혹은 인덱스 명Starts 해당 동작의 수행횟수 (Nested Loops)

E-Rows 옵티마이저가 예측한 사용 레코드 수A-Rows 실제 처리된 레코드 수Buffers 읽은 논리 블록의 수

조인 - Nested loops join

• 선행 테이블에서 탐색된 레코드 수 만큼 후행 테이블 랜덤 엑세스

• 후행 테이블의 조인 항목은 꼭 인덱스가 있어야 함

• 선행 테이블의 조회 건수가 전체 작업량을 좌우

조인 - Merge join• 두 테이블의 조인 항목에

인덱스가 없는 경우• 각 테이블을 로드하여

조인 항목으로 정렬 후 Merge

조인 - Hash join• Merge join 에서 정렬의

부하를 줄임• 선행 테이블 로드 후 해시

테이블 생성 , 이후 후행 테이블 데이터의 해시 값으로 조인

• 선행 테이블의 건수가 적은 것이 유리

조인 - ETC• 아우터 조인• 세미 조인

• 서브 쿼리를 사용할때 나타나는 조인 방식 (exists)

• 주 / 서브 쿼리 간의 종속 관계가 형성 됨 . ( 서브 쿼리는 확인용도만 사용 )

• 안티 조인• 세미 조인과 반대로 not exists 시 발생

• 카테시안 조인 : 연결 고리가 없는 경우

액세스와 필터• 액세스 : 데이터 블록을 읽기 위해 찾아가는 경로와 범위를 결정하는 조건• 필터 : 데이터 블록을 읽어내어 걸러내기 위해 사용하는 조건

인덱스• 테이블의 특정 항목과 ROWID 를 색인하여 빠른 검색을 수행할

수 있도록 함• 옵티마이저가 실행 계획을 세울때 가장 중요한 판단 기준• DB 튜닝시 SQL 성능 개선의 핵심적인 활동• 중복 허용 여부에 따라 , Unique 인덱스와 일반 인덱스로 구분• 항목의 수에 따라 단일 인덱스 , 복합 인덱스로 구분

인덱스 생성 원칙• 우선 순위

• 테이블의 기본 키• 조인시 사용되는 참조 키• 액세스 경로 상에서 빈번하게 관찰되는 항목의 그룹

• 선택도 (1/Distinct( 항목 )) 고려 사항• 선택도가 낮은 것이 인덱스 효과가 좋음• 선행 항목은 선택도가 낮은 것이 유리

인덱스 종류 - B* 트리• 루트 , 브랜치 , 리프로 구성• 데이터가 많아질 경우 브랜치

블록의 단계가 증가• 리프노드는 색인된 항목의

값과 ROWID 로 구성

인덱스 종류 - 비트맵 인덱스• 선택도가 낮은 항목에 대해 저장

공간을 줄여 탐색 성능 확보• 항목의 값 단위로 해당 값의

유무를 1, 0 비트로 표기• 인덱스에 대한 락이 블록

단위임으로 , 변경이 잦은 테이블인 경우 대기 시간이 크게 발생

인덱스 종류 - ETC• 리버스 인덱스

• 각 항목의 단위를 바이트 단위로 역순으로 기술해 인덱싱하여 분산을 유도

• 함수 기반 인덱스• 항목 간에 계산된 결과를 기반으로 인덱스를 생성

• 역순 인덱스• 내림 차순으로 인덱스를 생성

인덱스를 사용하지 못하는 경우

인덱스 탐색 방법• INDEX UNIQUE SCAN

• 유니크 인덱스이고 , 인덱스의 모든 항목이 WHERE 절에 있는 경우• INDEX RANGE SCAN

• 유니크 인덱스가 아닌 경우 혹은 유니크 인덱스이고 , 항목의 일부만 WHERE 절에 있는 경우• INDEX FULL SCAN

• INDEX FAST FULL SCAN( 병렬 )

• INDEX SKIP SCAN• 인덱스의 선행 항목이 WHERE 절에 없지만 , 선행 항목이 선택도가 클 경우

인덱스 ETC - 파티션 테이블• 한 테이블에 존재하는 레코드가 대용량일 경우 , 특정 항목을

기반으로 여러개의 테이블에 나누어 레코드를 저장하는 기법• 사용 인덱스

• 로컬 인덱스 : 파티션 수 만큼 인덱스도 생성 됨• WHERE 절에 파티션 항목이 없을 경우 전체 인덱스를 탐색

• 글로벌 인덱스 : 전체 파티션에 대해 단일 인덱스를 생성• 일부 파티션을 삭제 및 통합하는 경우 전체 인덱스가 사용 불가하므로 성능 저하를

유발

SQL 성능 개선

힌트• SQL 문에 힌트를 제공해 실행 계획을 유도하는 기능

• 옵티마이저의 한계로 불합리한 실행 계획이 발생한 경우• 옵티마이저가 100% 힌트를 따르지는 않음• 사용 방법

• SELECT 뒤에 /*+ hints */ 내에 기술

힌트 사용• 옵티마이저 정책 변경• 테이블 조인 순서 유도• 테이블 조인 방식 유도• 테이블 액세스 방식 유도• 서브 쿼리 조인 방식 유도• 뷰와 인라인 뷰 동작 유도

그외 성능 개선 포인트(1/2)

• 인덱스 부재• 적절한 인덱스의 생성 여부를 점검

• 인덱스 항목 순서• 일치 조건으로 검색되는 항목이 선행 항목이 되어야 함• 모두 일치 조건일 경우 선택도가 낮은 것이 선행 항목으로

• LIKE 에 의한 성능 저하• 항상 마지막에 ‘ %’ 를 사용

그외 성능 개선 포인트(2/2)

• 테이블 조인 이상• 테이블과 인덱스의 통계가 실제와 다른 경우 -> 통계 갱신• 인덱스 이상 -> 올바른 인덱스가 있는지 확인• SQL 문이 복잡하여 연결 고리가 누락되어 카테시안 곱으로 조인 : SQL 수정• 옵티마이저가 실행 계획을 잘못세우는 경우 : 힌트 사용

•권문수 . 실무로 배우는 시스템 성능 최적화 . 경기도 파주시 위키 북스 , 2016.

References