사례 : 동적 조건절 구성

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

개선 전

(문제 상황) 문서관리기본 및 신분증기본의 테이블/인덱스 블록을 많이 액세스함
(문제 원인) 문서관리기본 및 신분증기본 테이블 모두 (액세스 범위를 크게 줄일 수 있는) 계좌번호 컬럼 선두인 인덱스가 있으나, 쿼리문에 사용한 INDEX 힌트와 OR 구문으로 인해 그 인덱스를 사용하지 못함

개선 방안

(개선 방안) 조건절이 AP를 통해‘dynamic’하게 추가되므로 특정 인덱스를 고정적으로 사용하는 대신, 만들어진 조건절에 따라 옵티마이저가 최선의 인덱스를 선택할 수 있도록 현 index 힌트를 삭제함
(개선 방안) 실명번호 값 입력시 "A.실명번호 = :변수" 조건절을 추가하듯, 계좌번호 값 입력 시 "A.계좌번호 = :변수" 조건절 추가하도록 AP 로직을 수정하고, 대신 현재 고정적으로 사용되고 있는 OR 조건절은 쿼리문에서 삭제함

개선 후

(개선 결과) X11 및 X03 인덱스가 사용되고, 계좌번호 조건절이 access 조건이 되면서 액세스 량이 현저히 감소함

위 결과는 SQL문에 OR 구문으로 작성된 ‘복잡한’ 계좌번호 조건절 대신 ‚계좌번호 = :변수‛와 같이 심플한 조건절을 사용할 때, 수립되는 실행계획과 트레이스 결과임
참고로, 운영DB에서는 실명번호 조건절이 있는 것과 없는 것 2개 SQL문이 실행되고 있으며, 현 index 힌트를 제거하더라도 실명번호 조건절이 있는 경우에는 개선 전과 같이 X07과 X01 인덱스 사용하는 것을 확인함

이 블로그의 인기 게시물

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

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

자세한 내용 보기

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

자세한 내용 보기

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

시계열성으로 쌓여진 (대용량) 거래 내역을 특정 고객번호로 조회하면, 액세스할 블록이 거의 로우당 1개인 경우가 일반적인데, 이때 고객번호별로 데이터를 모아두는 정렬재구성을 하면, 액세스해야 할 블록이 크게 감소한다. 참고로, 온라인 업무나 업무 배치 처리 과정에서 자연스럽게 데이터가 흩어진게 아니고, 차세대/고도화 사업 데이터이행 과정에서 데이터가 크게 흩어지는 경우도 빈번하다. 개선 전 (문제 상황) 평이한 실행계획이나 넓은 데이터 범위를 액세스하면서 10초 가량 소요됨(특히 계좌가 많은 기업고객인 경우) (문제 원인) 16천 여건의 결과 모두를 리턴해야 하므로 절대적으로 많은 시간이 소요될 수 밖에 없고, 특히 클러스터링 팩터가 좋지 못해 읽은 로우수에 비해 읽은 블록도 많은 편임 - 인덱스 스캔 결과가 16899건 인데, 테이블 (12182-281) 블록을 읽었음 참고로, 조건절 정보(Predicate Information)를 볼때, IX04 인덱스는 고객번호+...+여신활동구분코드로 구성되어 있음을 추정할 수 있음 문제 원인 분석 기업고객의 계좌가 한꺼번에 개설되지 않고 시차를 두고 개설되었다면 해당 고객의 계좌 정보는 각기 다른 데이터 블록에 저장됨 참고로 하나의 데이터 블록은 (보통) 8K byte 크기이며, 대개 100건 이상의 로우가 저장될 수 있음 참고로, 차세대 또는 통합 프로젝트의 데이터이행 과정을 통해서도 데이터가 크게 흩어질 수 있음 계좌 데이터가 저장된 모든 데이터 블록을 액세스 해야 하는데, 여신계약기본과 같은 대형 테이블의 데이터 블록이 DB 캐시에 있을 확률은 (보통) 10% 내외이므로 거의 계좌 건수 만큼 물리 읽기를 해야 함 이 DB의 경우 물리 읽기 1회 당 1ms~5ms 정도 소요되므로, 10000건의 계좌를 읽는데, (DB 서버 구간에서만) 10~50초가 소요될 수 있음 개선 방안 계좌가 많은 ...

자세한 내용 보기

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

이 블로그 검색