[김정삼의 SQL튜닝] 대량목록조회성능이슈 2편

[김정삼의 SQL튜닝] 대량목록조회성능이슈 1편 보기

   I. 개요   

여기서 선택적인 조건이란, 어떤 입력 변수 값이 선택적이어서 SQL문 조건절에 "A.COL_ID = NVL(:col_id, A.COL_ID)"와 같이 구문을 작성하는 경우를 의미하며, 이런 SQL 실행 시 성능문제가 흔히 발생합니다.

그리고, 이런 선택적인 조회 조건 처리 SQL문에서 성능 문제가 자주 발생하는 주된 이유는 크게 실행계획 분리 문제와 전체 범위 처리 문제로 압축할 수 있습니다.

실행계획 분리 문제

• 선택적인 조건절이 하나만 있는 경우 옵티마이저는 최적화 과정을 통해 실행계획을 분리할 수 있습니다. 하지만, 둘 이상의 선택적인 조건절이 있는 경우, 옵티마이저가 모든 경우의 수 만큼 실행계획을 분리하지 않기 때문에, 결국, 여러 입력 케이스 중 일부 케이스는 최적일 수 있으나, 일부 케이스는 매우 불리한 실행계획으로 실행될 수 있습니다.

전체 범위 처리 문제

• 선택적인 조건절이 있는 쿼리문의 결과 건수는 불과 1건일 수도 있지만, 많은 경우 수 천건 이상일 수 있습니다. 이렇게 결과 건수가 많은  경우, ‘페이징 처리’ 방식으로 화면을 구성하는게 일반적입니다. 

• 그런데, 페이징 처리 로직으로 인해 부분범위 처리 기능만 제대도 작동해도, 전체 결과 건수 상관없이 첫번째 한 페이지 해당 데이터만 읽으면 되므로, 성능 이슈가 발생하지 않습니다. 하지만, 페이징 처리 시  결과 목록에 대한 정렬 요건이 포함되고, 그런 요건을 반영하기 위해 정렬 로직이 SQL문에 포함되게 되면 성능이 심각해 질 수 있습니다.

성능 개선

• 선택적인 조건 처리에 대한 성능 개선 방안은 1) 적절한 단위로 실행계획을 분리하되, 페이징 처리 쿼리인 경우 2) 부분범위 처리가 되도록하고, 3) 분리된 각각의 실행계획에 최적인 인덱스가 설계되어야 합니다. 

• 참고로 SQL구문에 선택적인 조건절 자체를 사용하지 않도록 하는 방안으로 SQL을 동적으로 구성하는 방법이 있는데, 이 부분에 대한 설명과 비교는 이 글에서 다루지 않습니다. 

   ll. 성능 이슈   

다수의 선택적인 조회 조건절이 사용되면, 오라클 옵티마이저의 최적화 기능에는 분명한 한계가 있습니다.

테스트 케이스 1) :col_id 값이 입력된 경우 (:col_id = 101)의 실행 결과는 다음과 같습니다.

테스트 케이스 2) :col_nm 값이 입력된 경우 (:col_nm = ‘DUAL’)의 실행 결과는 다음과 같습니다.

테스트 케이스 3) :col_cd 값이 입력된 경우 (:col_cd = 'TABLE')의 실행 결과는 다음과 같습니다.

테스트 케이스 4) :col_dt2 값이 입력된 경우(:col_dt2 = '20090901')의 실행 결과는 다음과 같습니다.

테스트 케이스 1) ~ 4)를 요약하면 다음과 같습니다.

   llI. 성능개선 1단계 - 실행계획 분리를 위한 쿼리 재작성   

8가지 케이스를 성능적인 측면에서 4개로 그룹핑하고, 각 그룹에 해당하는 조건으로 실행 계획을 분리합니다.

테스트 케이스 1) :col_id 값이 입력된 경우 (:col_id = 101)의 실행 결과는 다음과 같습니다.

테스트 케이스 2) :col_nm 값이 입력된 경우 (:col_nm = ‘DUAL’)의 실행 결과는 다음과 같습니다.

테스트 케이스 3) :col_cd 값이 입력된 경우(:col_cd = 'TABLE')의 실행 결과는 다음과 같습니다.

테스트 케이스 4) :col_dt2 값이 입력된 경우(:col_dt2 = '20090901')의 실행 결과는 다음과 같습니다. 

테스트 케이스 1) ~ 4)를 다시 요약하면 다음과 같습니다.

   lV. 성능개선 2단계 - 부분범위 처리 유도   

인덱스를 적절히 활용하면 ORDER BY 오퍼레이션을 사용하지 않고도 정렬 요구사항을 수용할 수 있습니다.

테스트 케이스 3) :col_cd 값이 입력된 경우 (:col_cd = 'TABLE')의 실행 결과는 다음과 같습니다.

테스트 케이스 4) :col_dt2 값이 입력된 경우(:col_dt2 = '20090901')의 실행 결과는 다음과 같습니다. 

테스트 케이스 1) ~ 4)를 마지막으로 요약하면 다음과 같습니다.