온라인 SQL 튜닝 통계

이 글은 125개 온라인 SQL 튜닝보고서의 문제 상황, 문제 원인, 개선 방안을 구분한 후에 취합한 통계 현황이다.

오라클 SQL 튜닝 대상은 다음과 같은 경로로 선정하였다.

AP 또는 DB 성능 모니터링 도구에서 평균 응답시간 등의 기준을 정하고 추출
SQL 튜너가 동적뷰나 AWR 등을 통해 직접 DB시간, CPU시간, 블록수 등의 기준을 정하고 Top SQL을 추출
업무팀 또는 개발팀의 튜닝 요청

참고로, 실무에서 SQL 튜닝 대상을 선정하는 기준이 상당히 중요하긴 하나, 본 게시글의 주된 주제가 아니어서 구체적인 내용은 생략한다.

그리고, 필자가 이번에 수행한 과업의 SQL 튜닝 대상은 튜닝 가능 여부에 따라 다음과 같이 분류하였다.

튜닝 대상의 약 50%가 SQL 또는 인덱스로 개선 가능
단, 튜닝 요청 건의 경우 80% 이상 개선 가능
나머지 50% 중 약 20%는 넓은 범위 액세스 등의 사유로 SQL 튜닝 불가 처리
나머지 30%는 개선 노력 대비 효과가 크지 않아서 SQL 튜닝 제외 처리

위에서 튜닝 가능한 대상 125개에 대해서는 튜닝가이드를 작성하여 운영DB에 적용되도록 하였고, 튜닝 현황을 파악할 수 있도록, 다음과 같이 문제 상황, 문제 원인, 개선 방안을 구분하고 통계를 작성하였다.

문제 상황 통계

“테이블/인덱스 과다 액세스"가 82건으로 66%를 차지함
"조인 순서 부적합"이 27건으로 22%를 차지함

문제 원인 통계

"필요한 인덱스가 없음"이 50건으로 40%를 차지함
"선택적으로 사용되는 조건절"이 27건으로 22%를 차지함

개선 방안 통계

"인덱스 설계 변경"이 51건으로 41%를 차지함
"실행계획 분리"가 23건으로 18%를 차지함
"조인순서 변경"이 10건으로 8%를 차지함

이 블로그의 인기 게시물

사례 : 실행계획 분리 - 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 인덱스가 사용되었고, 개선 전에 비해 액세스량/소요시간이 현저히 감소함

자세한 내용 보기

사례 : 실행계획 분리 - OR EXPANSION

복잡한 선택적 조건절을 사용하는 경우, 옵티마이저가 최적 실행계획을 수립하기 어려운데, 쿼리 수정 또는 USE_CONCAT 힌트 사용을 통해 OR EXPANSION을 유도하거나, UNION ALL 구문으로 입력 케이스별로 실행계획을 강제적으로 분리시키는 방법을 사용하여 성능을 최적화한다. 개선 전 (문제 상황) PK 인덱스가 참조번호+거래일련번호로 구성되었음에도 불구하고, 거래일련번호 컬럼 조건절이 인덱스 access 조건이 되지 못하고 인덱스 filter 조건이 됨 (문제 원인) 조건절 로직에 따라 :b1값이 입력되는 경우와 입력되지 않는 경우의 실행계획이 분리될 필요가 있었는데(OR EXPANSION 실행계획이 수립될 수도 있었는데), 현재의 조건절은 그렇게 작성되지 않았음 개선 후 (개선 방안) OR EXPANSION 실행계획이 자연스럽게 만들어질 수 있도록 조건절을 수정함 (개선 결과) :b1 변수값 입력 여부에 따라 실행계획이 분리되었고(CONCATENATION 및 FILTER 오퍼레이션 등장), 개선 전 보다 액세스량이 현저히 감소함

자세한 내용 보기

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

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

자세한 내용 보기

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

이 블로그 검색