사례 : FIRST_ROWS 모드 보완

불완전한 비용기반 옵티마이저인 FIRST_ROWS 모드 사용으로 인해 잘못된 실행계획이 수립되는 경우, 온전한 비용기반 옵티마이저 모드인 ALL_ROWS 또는 FIRST_ROWS_N 모드를 사용하여 개선한다.

개선 전

(문제 상황) 점번호 컬럼 조건이 인덱스 access 조건이 되지 못하여 총계정기본 테이블 및 인덱스 액세스 량이 과다해 짐

SQL문을 확인해 보면, 기준일자 컬럼 조건절 외에 점번호 컬럼 조건절도 인덱스 access 조건이 되어야 총계정기본 테이블 또는 인덱스 액세스 범위를 줄일 수 있는데, 실행계획이 그렇게 수립되지 못함

개선 방안

(문제 원인) 기준일자와 점번호 컬럼이 선두인 인덱스가 있음에도 불구하고 점번호 결과집합을 만드는 IN 서브쿼리 부분이 먼저 실행되지 못한 것은 FIRST_ROWS 모드 특성 때문임. FIRST_ROWS 옵티마이저 모드는 ORDER BY 오퍼레이션을 회피할 수 있는 대안을 항상 (비용 산정 않고) 선택하는데, 여기서는 PK 인덱스를 범위 스캔하는 것이 그 대안이었음
(개선 방안) FIRST_ROWS 옵티마이저 모드 대신 온전한 비용기반 옵티마이저인 ALL_ROWS 모드를 사용하도록 힌트 사용함

개선 후

(개선 결과) IN서브쿼리 부분이 먼저 수행되었고, 총계정기본 데이터 액세스 량이 개선전 보다 현저히 감소함

조건절 정보(Predicates Information)을 통해, 기준일자 조건절과 점번호 조건절이 모두 인덱스 access 조건이 된 것을 확인할 수 있음

이 블로그의 인기 게시물

사례 : 실행계획 분리 - UNION ALL

복잡한 선택적 조건절을 사용하는 경우, 옵티마이저가 최적 실행계획을 수립하기 어려운데, 쿼리 수정 또는 USE_CONCAT 힌트 사용을 통해 OR EXPANSION을 유도하거나, UNION ALL 구문으로 입력 케이스별로 실행계획을 강제적으로 분리시키는 방법을 사용하여 성능을 최적화 한다. 개선 전 - 트레이스 결과 (문제 상황) 외환기본_IX08 인덱스 액세스에 대부분의 시간이 소요됨 IX08 인덱스가 거래일자와 과목코드 순으로 구성되어 있어서, 거래일자 범위 조건은 인덱스 access 조건이 되었지만, 과목코드 조건을 인덱스 filter 조건이 됨 (참고로, 과목코드 값이 ‘EX’인 것은 전체 로우의 약 5%임) 개선 전 - SQL문 (문제 원인) 고객번호 및 관리점번호 입력값이 필수가 아니어서, 해당 값 입력 여부에 따라 최적 실행계획이 달라질 수 있는데, 앞의 실행계획을 보면, 고객번호(:B3) 입력 여부 따라 실행계획이 분리되었음 (CONCATENATION/FILTER 오퍼레이션 등장) INDEX 힌트를 사용하여 항상 IX08 인덱스가 사용되도록 했는데, 이 INDEX 힌트를 제거하더라도 최적 실행계획이 수립되지 않았음 개선 방안 (개선 방안) 고객번호와 관리점번호 입력 여부에 따른 3가지 경우 각각을 구분하여 처리할 수 있도록 UNION ALL 사용 이때, 배타적인 3개 집합은 반드시 상호 배제되고 전체를 포괄해야 함 (MECE, Mutually Exclusive, Collectively Exhaustive) 개선 후 (개선 결과) 3가지 경우 각각에 최적인 실행계획이 수립됨(각각 IX01, IX04, IX08 인덱스를 사용) (개선 결과) 본 사례는 고객번호가 입력된 경우로서 IX01 인덱스가 사용되었고, 개선 전에 비해 액세스량/소요시간이 현저히 감소함

자세한 내용 보기

데이터 액세스 이슈와 SQL 튜닝

SQL 처리 시간이 오래 걸리는 직접적인 이유는 (대부분) 그 SQL문이 데이터 블록을 많이 액세스하기 때문이고 , SQL 튜닝 방안 대부분은 데이터 블록 액세스 최소화 노력이다. SQL 튜닝 경험이 풍부하면, SQL 트레이스 리포트만 있어도 데이터 블록 액세스 상황이 어떠한지? 어느 단계에서 가장 많은 시간이 많이 소요되었는지? 를 신속하게 파악할 수 있고, 이후 구체적인 문제 원인을 분석하고, 개선 방안을 찾는 것을 일사천리로 진행할 수 있다. 다음은 비교적 SQL 튜닝 경험이 많지 않은 초심자도 SQL 튜닝 실무에 쉽게 접근할 수 있도록 SQL 트레이스 분석, 문제 원인 분석, 개선 방안 수립 과정 전체 과정을 마인드 맵 형태로 도식하였고, 도식 내용에 대한 설명도 첨언하였다. (문제 상황) SQL 트레이스 결과 만으로 쉽게 구분 가능하도록 3가지 유형만 정의하였는데, 먼저 넓은 범위 액세스 여부를 판별하고, 그 다음 조인 순서 부적합 여부를 판별하고, 그외의 경우인 경우 마지막 테이블/인덱스 과다 액세스로 구분한다. (문제 원인) SQL 트레이스 결과, SQL문 인덱스 정보, 데이터 구조와 값 등을 분석하여 종합적인 판단으로 주요하게 영향을 미친 문제 원인을 1개 이상 열거한다. (개선 방안) 문제 원인을 개선할 수 있고, 개선 효과도 충분히 큰 것을 우선 고려하고, 적용 가능 여부도 함께 고려하여 1개 이상의 개선 방안을 제시한다. 사실, SQL 트레이스 결과(또는 이에 버금가는 정보)를 보지 않고, (여기서 정의된) 데이터 액세스 이슈인지, 아니면 다른 이슈 인지를 판단하는 것은 쉽지 않을 수 있다. 예를 들어, 락 경합, 버퍼 경합과 같은 이슈는 데이터 액세스 이슈와는 그 성격이 다른 것이고, 그런 이슈는 대기 이벤트 정보가 있어야 문제 상황을 파악할 수 있다. 이렇듯 위 도식에 모든 SQL 성능 이슈, 모든 데이터 이슈 관련한 문제 상황, 문제 원인,...

자세한 내용 보기

사례 : 인덱스 설계 변경 - 테이블 filter 조건 개선

주요 조건절 컬럼으로 구성된 인덱스는 온라인 SQL 최적화에 가장 기본적이고 필수적인 도구이다. 만약, 액세스 범위를 크게 줄일 수 있는 어떤 컬럼 조건절이 테이블 filter 조건이라면, 인덱스를 신규 생성하거나, 현 인덱스에 컬럼을 추가하여 인덱스 access 또는 filter 조건이 되도록 하면 액세스 량이 크게 감소한다. 개선 전 (문제 상황) 거래내역 테이블을 15513회 만큼 액세스 후 결과 건수가 4362로 감소하였는데, 테이블 액세스에 많은 시간이 소요됨 (문제 원인) 데이터 확인을 통해 구분코드 컬럼 조건절이 15513에서 4361건으로 감소하게 된 결정적인 요인임을 확인함 개선 후 (개선 방안) 구분코드 조건절이 인덱스 access 조건이 되도록 현 IX05 인덱스에 구분코드 컬럼 추가함 변경 전 : 고객번호, 거래일자 변경 후 : 고객번호, 거래일자, 구분코드 (개선 결과) 인덱스 변경 반영함에 따라 구분코드 컬럼 조건절이 인덱스 access 조건이 되었고, 그 결과 테이블 랜덤 액세스 횟수가 15513회에서 4735회로 감소하였으며, 소요시간도 크게 감소함

자세한 내용 보기

데이터와이즌, 데이터를 잘 다루다!

이 블로그 검색