기본 콘텐츠로 건너뛰기

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

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

개선 전


  1. (문제 상황) 평이한 실행계획이나 넓은 데이터 범위를 액세스하면서 10초 가량 소요됨(특히 계좌가 많은 기업고객인 경우)
  2. (문제 원인) 16천 여건의 결과 모두를 리턴해야 하므로 절대적으로 많은 시간이 소요될 수 밖에 없고, 특히 클러스터링  팩터가 좋지 못해 읽은 로우수에 비해 읽은 블록도 많은 편임 - 인덱스 스캔 결과가 16899건 인데, 테이블 (12182-281)  블록을 읽었음
  • 참고로, 조건절 정보(Predicate Information)를 볼때, IX04 인덱스는 고객번호+...+여신활동구분코드로 구성되어 있음을 추정할 수 있음

문제 원인 분석



  • 기업고객의 계좌가 한꺼번에 개설되지 않고 시차를 두고 개설되었다면 해당 고객의 계좌 정보는 각기 다른 데이터  블록에 저장됨
  • 참고로 하나의 데이터 블록은 (보통) 8K byte 크기이며, 대개 100건 이상의 로우가 저장될 수 있음
  • 참고로, 차세대 또는 통합 프로젝트의 데이터이행 과정을 통해서도 데이터가 크게 흩어질 수 있음


  • 계좌 데이터가 저장된 모든 데이터 블록을 액세스 해야 하는데, 여신계약기본과 같은 대형 테이블의 데이터 블록이 DB 캐시에 있을 확률은 (보통) 10% 내외이므로 거의 계좌 건수 만큼 물리 읽기를 해야 함
  • 이 DB의 경우 물리 읽기 1회 당 1ms~5ms 정도 소요되므로, 10000건의 계좌를 읽는데, (DB 서버 구간에서만) 10~50초가 소요될 수 있음

개선 방안



  • 계좌가 많은 고객번호로 여신계약기본을 고객번호로 조회하는 경우에 겪게되는 성능 이슈이므로 테이블을 고객번호로 정렬재구성하여 데이터 분산도를 개선함
  • 여신계약기본의 경우, 계좌번호로 파티션되어 있고, 인덱스가 모두 ‘Local’이어서 파티션 단위 작업이 가능함
  • DBA가 작성한 스크립트로 정렬 작업 가능하나, 업무 중지  시간이 확보되어야 한다는 어려움이 있음
  • LiveReorg 같은 온라인 리오그 솔루션을 이용하면, 업무 중지 없이 작업 가능함(단, 그럼에도 거래가 빈번한 시간대는 피해서 작업하는 것을 권고)


  • 위의 예시로 보면, 종전 5개 블록을 읽었던 쿼리문이, 정렬 후 1개 블록만 읽으면 됨
  • 개선전 테이블에서 1만여 블록의 물리 읽기가 발생했는데, 정렬재구성 수행하면 약 80%이상 감소할 것으로 예상됨
  • 물리 읽기가 80% 감소된다면 종전 소요 시간이 10초인 쿼리문이 2초로 단축되는 효과가 있음을 의미함

댓글

  1. 잘봤습니다. 결과건수보다 read한 블럭이 현저하게 적어야하는게 핵심이군요.

    답글삭제
    답글
    1. 그렇습니다. 다만, Reads 수와 Buffers 수를 함께 봐야, 좀 더 정확한 판단을 할 수 있을 거 같습니다.

      삭제

댓글 쓰기

이 블로그의 인기 게시물

사례 : 실행계획 분리 - 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회로 감소하였으며, 소요시간도 크게 감소함