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

SQL 처리 시간이 오래 걸리는 직접적인 이유는 (대부분) 그 SQL문이 데이터 블록을 많이 액세스하기 때문이고, SQL 튜닝 방안 대부분은 데이터 블록 액세스 최소화 노력이다.

SQL 튜닝 경험이 풍부하면, SQL 트레이스 리포트만 있어도 데이터 블록 액세스 상황이 어떠한지? 어느 단계에서 가장 많은 시간이 많이 소요되었는지? 를 신속하게 파악할 수 있고, 이후 구체적인 문제 원인을 분석하고, 개선 방안을 찾는 것을 일사천리로 진행할 수 있다.

다음은 비교적 SQL 튜닝 경험이 많지 않은 초심자도 SQL 튜닝 실무에 쉽게 접근할 수 있도록 SQL 트레이스 분석, 문제 원인 분석, 개선 방안 수립 과정 전체 과정을 마인드 맵 형태로 도식하였고, 도식 내용에 대한 설명도 첨언하였다.

• (문제 상황) SQL 트레이스 결과 만으로 쉽게 구분 가능하도록 3가지 유형만 정의하였는데, 먼저 넓은 범위 액세스 여부를 판별하고, 그 다음 조인 순서 부적합 여부를 판별하고, 그외의 경우인 경우 마지막 테이블/인덱스 과다 액세스로 구분한다.  
• (문제 원인) SQL 트레이스 결과, SQL문 인덱스 정보, 데이터 구조와 값 등을 분석하여 종합적인 판단으로 주요하게 영향을 미친 문제 원인을 1개 이상 열거한다.
• (개선 방안) 문제 원인을 개선할 수 있고, 개선 효과도 충분히 큰 것을 우선 고려하고, 적용 가능 여부도 함께 고려하여 1개 이상의 개선 방안을 제시한다.

사실, SQL 트레이스 결과(또는 이에 버금가는 정보)를 보지 않고, (여기서 정의된) 데이터 액세스 이슈인지, 아니면 다른 이슈 인지를 판단하는 것은 쉽지 않을 수 있다. 예를 들어, 락 경합, 버퍼 경합과 같은 이슈는 데이터 액세스 이슈와는 그 성격이 다른 것이고, 그런 이슈는 대기 이벤트 정보가 있어야 문제 상황을 파악할 수 있다.

이렇듯 위 도식에 모든 SQL 성능 이슈, 모든 데이터 이슈 관련한 문제 상황, 문제 원인, 개선 방안이 열거된 건 분명 아니다. 하지만, 필자가 운영 단계에서 수행한 SQL 튜닝 경험에 비춰볼 때, 온라인 SQL 튜닝 실무 사례의 70% 이상은 커버할 수 있을것 같다.

도식에 열거된 문제 상황, 문제 원인, 개선 방안에 대해 좀 더 이해를 돕기 위한 설명은 다음과 같다.

🔘 문제 상황

넓은 범위 액세스

• 테이블 액세스 후에도 결과 건수가 줄지 않고 수천 건 이상임
• 다시 말해, 절대적으로 액세스해야 할 로우/블록이 많은 경우임

조인 순서 부적합

• 조인 순서상 뒷쪽 테이블 액세스 후에 결과 건수 크게 줄어듦

테이블/인덱스 과다 액세스

• (과다한 테이블 액세스) 인덱스 액세스 결과 건수는 많고, 테이블  액세스 후 결과 건수가 크게 줄어듦

• (과다한 인덱스 액세스) 인덱스 블록을 많이 액세스하였고 시간도 가장 많이 소요됨

🔘 문제 원인

넓은 범위 액세스(페이징처리없음)

• 예를 들어, 법인고객의 계좌, 동명이인 성명, 대량 거래내역 조회 등을 위해, 수천 건 이상의 데이터를 읽고, 조인하고, 결과를 리턴하는 경우(중간에 그룹핑한 경우 포함), SQL 수정 또는 인덱스 변경 만으로 성능 문제를 해소하기 어려운게 일반적임

넓은 범위 액세스(페이징처리있음)

• 수천 건 이상의 데이터를 읽고, 조인하지만, 최종 결과는 (페이징처리에 의해) 정해진 건수만 리턴하는 경우라면, SQL 수정 또는 인덱스 변경을 통해 성능 문제를 일부 해소할 여지가 있음

최종/최초 이력 액세스 비효율

• 수천 건 이상의 데이터를 읽은 후 MAX/MIN 함수를 통해 최종 또는 최초 이력을 얻는 경우, 최종 또는 최초 데이터만 읽는 것과 같은, 보다 효율적인 방안을 찾을 필요가 있음

잘못된 조인 순서 사용

• 복잡한 로직 또는 잘못된 비용 계산으로 인해 옵티마이저가 성능에 불리한 조인 순서를 선택한 경우, 조인 순서 변경을 통해 성능 문제를 해소할 필요가 있음

주요 조건절이 2개 테이블 이상에 분산

• 액세스 범위를 크게 줄일 수 있는 주요 조건 컬럼이 2개 이상의 테이블에 분산된 경우, 단순히 조인 순서 변경만으로는 성능 문제를 해소할 수 없음

선택적으로 사용되는 조건절

• OR, DECODE/NVL, LIKE 등의 구문을 사용하여, 입력 변수 값 유무 또는 구분 값에 따라 특정 조건절이 액세스 범위를 크게 줄일 수도 있고, 그렇지 않을 수도 있는 경우, 옵티마이저가 모든 경우에 최적인 실행계획을 수립하기가 매우 어려움 

필요한 인덱스가 없음

• 주요 조건 컬럼이 선두인 인덱스가 없거나, 주요 조건절이 테이블filter조건 또는 인덱스 filter조건으로 사용되어 많은 액세스량을 초래한 경우, 인덱스 신규 생성 또는 컬럼 추가 만로도 성능 문제를 해소할 수 있음

잘못된 조인/액세스 방법 사용

• 복잡한 로직 또는 잘못된 비용 계산으로 인해 옵티마이저가 성능에 불리한 조인 방법, 액세스 방법을 선택한 경우, 조인 방법 또는 사용 인덱스 변경을 통해 성능 문제를 해소할 수 있음

데이터 유무 확인 액세스 비효율

• "ROWNUM <= 1" 조건을 사용하여, 단순히 데이터 존재 유무만 확인하는데, 많은 시간이 소요되는 경우, 조회 요건과 데이터 특성을 고려한 개선 방안을 찾을 수 있음 

예외 로직으로 인해 인덱스 사용 제약

• "컬럼명 = :입력변수"와 같이 평이한 로직이 아니라 "SUBSTR(컬럼명,1,2) = :입력변수" 같이 예외 로직을 사용함으로써 인덱스를 사용할 수 없는 경우, 로직을 변경하거나, 데이터를 정제하거나, 함수기반인덱스(Function-based Index)를 사용하여 인덱스를 사용하도록 할 수 있음

(공통) 잘못된 힌트 사용

• 사용한 힌트가 오히려 최적 실행계획을 방해하는 경우, 단순히 그 힌트를 삭제하는 것 만으로도 성능 문제를 해소할 수 있음

(공통) 통계 정보 미수집

• 통계정보가 수집되지 않거나 잘못된 통계정보로 인해 옵티마이저가 잘못된 실행계획을 수립한 경우, 통계정보 재수집 만으로도 성능 문제를 해소할 수 있음

(공통) FIRST_ROWS 모드 특성

• 옵티마이저 모드가 FIRST_ROWS 인 경우, 비용 계산없이 ORDER BY 오퍼레이션을 회피하는 실행계획이 수립되는데, 이런 실행계획이 오히려 성능에 불리한 경우, SQL 또는 세션 수준에서 옵티마이저 모드를 ALL_ROWS, FIRST_ROWS_N 로만 바꾸어도 성능 문제가 해소될 수 있음

🔘 개선 방안

정렬재구성으로 분산도 개선

• 시계열성으로 쌓여진 (대용량) 거래 내역을 특정 고객번호로 조회하면, 액세스할 블록이 거의 로우당 1개인 경우가 일반적인데, 이때 고객번호별로 데이터를 모아두는 정렬재구성을 하면, 액세스해야 할 블록이 크게 감소함
• 참고로, 온라인 업무나 업무 배치 처리 과정에서 자연스럽게 데이터가 흩어진게 아니고, 차세대/고도화 사업 데이터이행 과정에서 데이터가 크게 흩어지는 경우도 빈번함

반정규화

• 원부 및 거래 성격의 데이터는 파생되거나(derived), 중복된(duplicated) 컬럼이 없는 정규화된 구조를 가져야 거래처리 성능과 데이터품질관리 측면에서 유리하나, 조회 성능 보장을 위해 반정규화된 구조를 선택해야 할 수 있음
• 이때, 반정규화는 원천 데이터를 복제한 값을 유지 하는 복제 컬럼, 원천 데이터를 요약 또는 집계한 테이블 등을 의미함

부분범위 처리 유도

• ORDER BY, GROUP BY, 분석함수, 해시조인(building) 등은 액세스 해야 할 데이터 범위 모두를 액세스한 후 결과를 리턴할 수 있는 "전체범위 처리" 오퍼레이션이어서 "부분범위 처리"의 잇점을 누릴 수가 없는데,  SQL 수정 및 인덱스 변경 등을 통해 "부분 범위 처리" 가능한 실행계획이 수립되도록 함
• 참고로, '부분범위 처리"는 결과 로우 전체를 한꺼번에 리턴하는 대신 FETCH 단위로 리턴할 수 있는 기능이며, Multi-Tier 환경에서는 SQL문에 ROWNUM 조건절을 사용하고, 전체 범위 처리 오퍼레이션이 없어야 그 잇점을 누릴 수 있음. 

최종/최초 이력 액세스 로직 개선

• 수천 건 이상의 데이터를 읽은 후 MAX/MIN 함수를 통해 최종 또는 최초 이력을 얻는 것이 당연할 수도 있겠지만, 다양한 방법을 통해 최종 또는 최초 데이터에 직접 도달하는 효율적인 방안이 있을 수 있음

인덱스 조인

• 주요 조건절이 2개 테이블에 분산된 경우, 인덱스만으로 2개 테이블을 조인하고, 주요 조건절을 모두 사용한 후, 그외 사용해야 할 조건절과 컬럼값 획득을 위해 테이블을 액세스하도록 함으로써 테이블 액세스량을 크게 줄임 (여기서는 오라클의 INDEX JOIN과는 다른 개념으로 사용함)

조인 순서 변경

• 옵티마이저의 비용 추정 오류 또는 조건절(조인 조건 포함)에 맞는 인덱스 부재로 인해 최적 조인 순서의 실행계획이 수립되지 않았다면, LEADING 힌트 사용, PUSH_SUBQ 힌트 사용, 인덱스변경, SQL 로직 수정 등의 방법을 통해 최적 조인 순서를 유도함

실행계획 고정

• 옵티마이저는 Query Transformation 기능 중 하나인 OR EXPANSION(실행계획 분리)을 통해 선택적인  조건절을 최적화할 수 있는데, 그런 기능이 적용된 실행계획이 오히려 성능에 불리한 경우, NO_EXPAND,  INDEX 힌트 등을 통해 OR EXPANSION 기능이 작동하지 않도록 함

실행계획 분리

• 복잡한 선택적 조건절을 사용하는 경우, 옵티마이저가 최적 실행계획을 수립하기 어려운데, 쿼리 수정 또는 USE_CONCAT 힌트 사용을 통해 OR EXPANSION을 유도하거나, UNION ALL 구문으로 입력 케이스별로 실행계획을 강제적으로 분리시키는 방법을 사용하여 성능을 최적화함

동적인 조건절 구성

• 선택적인 조건절을 사용하는 대신, AP로직을 통해 SQL문 조건절을 동적으로 구성(흔히 Dynamic SQL 이라고 함)하여 옵티마이저가 최적 실행계획을 수립하기 용이하도록 할 수 있음

인덱스 설계 변경

• 주요 조건절 컬럼으로 구성된 인덱스는 온라인 SQL 최적화에 가장 기본적이고 필수적인 도구임

조인/액세스 방법 변경

• 옵티마이저가 잘못된 조인 방법을 선택한 경우, USE_NL, USE_HASH, USE_MERGE 등의 힌트를 사용하여 조인 방법을 바꿀 수 있고, 옵티마이저가 잘못된 액세스 방법을 선택한 경우 INDEX, INDEX_DESC,  INDEX_SS, INDEX_FFS, FULL 힌트 등을 사용하여 액세스 방법을 바꿀 수 있음
• 참고로, 인덱스 신규 생성이나 컬럼 추가는 인덱스 설계 변경으로 구분하고, 단순히 힌트를 통해 있는 인덱스를 사용하도록 하는 것은 액세스 방법 변경으로 구분함  

데이터 유무 확인 로직 개선

• 조회 요건과 데이터 특성을 고려하여 데이터 유무 확인을 위한 1건 찾는 로직을 개선함

예외 로직 개선

• 컬럼의 부분값 검색을 위해 SUBSTR 함수 같은 예외적인 로직을 사용함으로써 인덱스를 사용하지 못하고, 최적 실행계획이 수립되지 않는 경우, 그런 예외 로직을 개선함 

데이터 정제

• 비정상적인 값에 대한 예외 처리를 위해, DECODE, NVL, OR 등을 사용하면서 인덱스를 사용하지 못하는 경우, 데이터 정제를 실행하고, 예외 처리 로직을 제거함

FIRST_ROWS 모드 보완

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