04.라이브러리 캐시 최적화 원리 - 10. Dynamic SQL 사용 기준

(1)Dynamic SQL 사용에 관한 원칙
- Static SQL을 지언하는 개발환경이라면 Static SQL로 작성하는 것을 원칙으로 합니다. Static SQL은 Precompile 과정을 거치므로 런타임 시 안정적인 프로그램 build가 가능하다는 장점이 있습니다. 그리고 Dynamic SQL을 사용하면 애플리케이션 커서 캐싱기능이 작동하지 않는 경우가 있는데, 이 기능이 필요한 상황(예를 들면, 루프 내에서 반복 수해되는 쿼리)에서 Dynamic SQL을 사용하면 성능이 나빠지기 때문입니다.
아래의 경우에는 Dynamic SQL을 사용해도 무방합니다.
- Precompille 과정에서 컴파일 에러가 나는 구문을 사용할 때, 예를 들어 Pro*C에서 스칼라 서브쿼리,분석함수,ANSI 조인 등
- 상황과 조건에 따라 생성될 수 있는 SQL 최대 개수가 많이 Static SQL로 일일이 나눠서 작성하려면 개발 생산성이 저하되고 유지보수 비용이 매우 커질때
- 위의 경우에 해당해서 Dynamic SQL을 사용하더라도 조건절에는 바인드 변수를 사용하는 것을 원칙으로 합니다. 특히, 사용빈도가 높고 조건절 컬럼의 값 종류가 매우 많을때는 반드시 준수합니다.
바인드 변수 사용원칙을 준수하되 아래의 경우에는 예외로 합니다.
- 배치 프로그램이나 DW,OLAP 등 정보계 시스템에서 사용되는 Long Running 쿼리. 이들 쿼리는 파싱 소요시간이 쿼리 총 소요시간에서 차지하는 비중이 매우 낮고, 수행빈도가 낮아 쿼리 총 소요시간에서 차지하는 비중이 매우 낮고, 수행빈도가 낮아 하드파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 적음
- OLTP성 애플리케이션이더라도 사용빈도가 매우 낮아 하드파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 없을 때, 예외적으로 인정하는 것이므로 단순히 바인드 변수 정의하는게 귀찮다고 그렇게 해서는 안됨
- 조건절 컬럼의 값 종류가 소수일때, 특히 값 분포가 균일하지 않아 옵티마이저가 컬럼 히스토그램 정보를 활용하도록 유도하고자 할 때.
Static SQL과 Dynamic SQL의 정의
Static SQL : SQL repository(주로 xml파일 형태로 관리)에 완성된 형태로 저장한 SQL
Dynamic SQL : SQL repository에 불완전한 형태로 저장한 후 런타임 시 상황과 조건에 따라 동적으로 생성되도록 작성한 SQL

(2) 기본원칙이 잘 지켜지지 않는 첫 번째 이유, 선택적 검색 조건
앞에서 Dynamic SQL 사용 원칙을 정해도 잘 지켜지지 않는 경우가 많습니다. 원인 중 많은 이유를 차지하는 것이 검색 조건이 다양해 사용자 선택에 따라 조건절이 동적으로 바뀌는 경우입니다. 크기가 작은 테이블이라면 상관없지만 대용량 테이블일 경우에는 성능이슈가 발생할 수 있습니다.
대용량 테이블에 대한 조회 요건이 복작하다면 적어도 현업과의 협의를 통해 필수입력 항목을 수렴해서 프로그램에 반영해야 합니다. 또한 기간 조건(between , < ,<= 등)에 대해서는 입력값 범위를 가능한 짧게 제한하려는 노력을 반드시 해야 합니다.
예)

거래일자만 필수입력조건이고, 나머지는 모두 선택적 입력조건이라고 가정하겠습니다. 아래는 위의 값을 불러오는 Dynamic SQL을 사용한 SQL입니다.
select 거래일자,종목코드,투자자유형,주문매체,체결건수,체결수량,거래대금
from 일별종목거래
where 거래일자 between :시작일자 and :종료일자
%option
사용자의 선택이나 입력 값에 따라 %option부분에 조건절을 아래와 같이 동적으로 붙여나갑니다.
%option = “ and 종목코그 = ‘KR123456’ and 투자자유형코드 = ‘1000’ “

NVL을 사용한 SQL입니다.
select 거래일자,종목코드,투자자유형,주문매체,체결건수,체결수량,거래대금
from 일별종목거래
where 거래일자 between :시작일자 and :종료일자
and 종목코드 = nvl(:종목코드,종목코드)
and 투자자유형 = nvl(:투자자유형,투자자유형)
and 주문매체 = nvl(:주문매체,주문매체)
이렇게 코딩하면 사용자가 어떻게 값을 입력하더라도 단 한 개의 실행계획을 공유하면서 반복 재사용하게 되므로 라이브러리 캐시 효율 측면에서는 최상의 선택입니다. 하지만 쿼리 수행 속도 측면에서는 인덱스를 전혀 사용하지 못하거나 사용하더라도 비효율적으로 사용하기 때문에 좋지 않습니다.
라이브러리 캐시 효율과 I/O효율 모두 고려하면서 SQL을 개발하려면 종목코드,투자자유형,주문매체 입력여부에 따라 SQL을 모두 분리해서 개발하는 것입니다. 따라서 3개의 선택적 입력조건을 처리하는데 8개의 SQL을 따로 작성해야 합니다.
이것은 하지만 현실성이 떨어지며 불가능에 가깝습니다.
인덱스 구성을 고려해 변별력이 좋은 컬럼 중심으로 2~3개의 SQL로 분리하면 됩니다.
최종 SQL

<인덱스 구성>

INDEX1 : 종목코드 + 거래일자
INDEX2 : 투자자유형 + 거래일자 + 주문매체
INDEX3 : 거래일자 + 주문매체
<SQL>
sselect 거래일자,투자자유형,회원번호,체결건수,체결수량,거래대금
from 일별종목거래
where :종목코드 is not null
and 거래일자 between :시작일자 and :종료일자
and 종목코드 = :종목코드
and 투자자유형 = nvl(:투자자유형,투자자유형)
and 주문매체 = nvl(:주문매체,주문매체)
union all
sselect 거래일자,투자자유형,회원번호,체결건수,체결수량,거래대금 from 일별종목거래 where :종목코드 is not null and :투자자유형 is not null
and 거래일자 between :시작일자 and :종료일자 and 종목코드 = :종목코드 and 투자자유형 = nvl(:투자자유형,투자자유형) and 주문매체 = nvl(:주문매체,주문매체) union all
sselect 거래일자,투자자유형,회원번호,체결건수,체결수량,거래대금 from 일별종목거래 where :종목코드 is null and :투자자유형 is null
and 거래일자 between :시작일자 and :종료일자 and 주문매체 = nvl(:주문매체,주문매체)

(3)선택적 검색 조건에 대한 현실적인 대안
Static SQL 사용을 원칙으로 하되 사용자 입력 조건에 따라 생성될 수 있는SQL 최대 개수가 너무 많을 때는 Dynamic SQL사용을 허용하는 것입니다. 조건절에 따른 SQL 개수가 많더라도 그 중 일부만 주로 사용되므로 실질적인 하드 파싱 부하는 거의 없습니다. 다만 라이브러리 캐시 효율화의 핵심인 바인드 변수 사용원칙만큼은 준수하도록 하여야 합니다.

(4)선택적 검색 조건에 사용할 수 있는 기법 성능 비교
A. OR 조건을 사용하는 경우

항상 Full Scan으로 처리되므로 인덱스 활용이 필요할 때는 이 방식을 사용해서는 안됩니다.

B. LIKE 연산자를 사용하는 경우

인덱스 사용이 가능하지만 사용자가 :isu_cd값을 입력하지 않았을 때 Full Scan 이 유리한데도 인덱스를 사용하게 되므로 성능이 나빠질 수 있습니다.

C.NVL함수를 사용하는 경우

D. DECODE 함수를 사용하는 경우

C와 D는 사용자의 :isu_cd 입력 여부에 따라 Full Table Scan과 Index Scan으로 실행계획이 자동 분기됩니다. 단 NVL또는 decode함수를 사용할 때는 해당 컬럼이 not null 컬럼이어야 하며, null 허용 컬럼일때는 결과집합이 달라지므로 주의해야 합니다. 사용자가 :isu_cd값을 입력하지 않았을 때는 조건절이 isu_cd=isu_cd가 되는데 isu_cd 컬럼값이 null일때 오라클은 false를 리턴하기 때문입니다.
select * from dual where NULL = NULL
선택된 레코드가 없습니다.
select * from dual where Null is Null
DU
—
X
1개 행이 선택되었습니다.

NVL 또는 decode를 여러 컬럼에 대해 사용했을 때는 그 중 변별력이 가장 좋은 컬럼 기줄으로 한번만 분기가 일어난다는 사실도 기억해야 하고 복잡한 옵션 조건을 처리할 때 이방식에만 의존하기 어려운 이유가 그것입니다.

E. UNION ALL을 사용하는 경우

5가지 방식에 대한 선택기준을 정리하면 다음과 같습니다.
1. nou null 컬럼일때는 nvl,decode를 사용하는것이 편합니다.
2. null 값을 허용하고 인덱스 액세스 조건으로 의미 있는 컬럼이라면 union all을 사용해 명시적으로 분기해야 합니다
3.인덱스 액세스 조건으로 참여하지 않는 경우, 즉 인덱스 필터 또는 테이블 필터 조건으로만 사용되는 컬럼이라면 (:c is null or col = :c) 또는 (c like :c||’%’) 어떤 방식을 사용해도 무방합니다.