[4) 라이브러리 캐시 최적화 원리] 10. Dynamic SQL 사용 기준

Yu River·2022년 7월 16일

SQLP oracle 오라클성능고도화

오라클성능고도화1 정리요약

목록 보기

38/55

[1] Dynamic SQL 사용에 관한 기본 원칙

(1) Static SQL 개발환경 지원 여부

1. Static SQL을 지원하는 개발 환경

Static SQL로 작성하는 것을 원칙으로 한다.
- PreCompile 과정을 거치므로 런타임 시 안정적인 프로그램 Build가 가능하기 때문이다.
- Dynamic SQL은 애플리케이션 커서 캐싱 기능이 작동하지 않는 경우가 있기에 이 기능이 필요한 상황(루프 내에서 반복 수행되는 쿼리..등)에서 Dynamic SQL을 사용하면 성능이 저하될 수 있다.

2. Static(=Embedded)SQL을 지원하지 않는 개발 환경

모든 SQL이 Dynamic SQL이지만 런타임 시 SQL이 동적을 바뀌도록 개발하지 않는다.
Static과 Dynamic SQL을 편의상 아래와 같이 재정의하고, 앞으로 제시될 기본 원칙을 동일하게 적용할 것을 권고한다.
✅ Static SQL 과 Dynamic SQL 재정의
- Static SQL : SQL Repository 에 완성된 형태로 저장한 SQL이다.
- Dynamic SQL : SQL Repository에 불완전한 형태로 저장한 후 런타임 시 상황과 조건에 따라 동적으로 생성되도록 작성한 SQL이다.

(2) Dynamic SQL를 사용해도 무방한 경우

1. PreCompile 과정에서 컴파일 에러가 나는 구문을 사용할 때

예를 들어, Pro*C에서 스칼라 서브쿼리, 분석함수, ANSI 조인 등에는 컴파일 에러가 난다.

2. 상황과 조건에 따라 생성될 수 있는 SQL 최대 개수가 많아 Static SQL로 일일이 나눠서 작성하려면 개발 생산성이 저하되고 유지보수 비용이 매우 커질 때

Dynamic SQL를 사용하더라도 조건절에는 바인드 변수를 사용하는 것을 원칙으로 한다.
🔥 특히 조건절 컬럼의 값 종류가 매우 많을 때는 반드시 준수한다.
- (예시) 계좌번호, 상품번호, 회원번호 등
바인드 변수 사용원칙을 준수하되 아래 경우에는 바인드 변수 미사용을 예외적으로 인정한다.
✅ 바인드 변수 미사용을 예외적으로 인정하는 경우
- 배치 프로그램이나 DW, OLAP등 정보계 시스템에서 사용되는 Long Running쿼리
  - 파싱 소요시간이 쿼리 총 소요시간에서 차지하는 비중이 매우 낮고, 수행빈도가 낮아 하드파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 적을 때
- OLTP성 애플리케이션이더라도 사용빈도가 매우 낮아 하드파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 없을 때
- 조건절 컬럼의 값 종류(Distinct Value)가 소수일 때, 특히 값 분포가 균일하지 않아 옵티마이저가 컬럼 히스토그램 정보를 활용하도록 유도하고자 할 때.
  - 증권시장구분코드 = ( '유가', '코스닥', '주식파생', '상품파생' )

[2] Static SQL에서의 선택적 검색 조건

(1) 선택적 검색 조건 구현

Static SQL에서 기본 원칙이 잘 지켜지지 않는 이유는 바로 ⭐️선택적 검색 조건 구현⭐️ 때문이다.
Static SQL을 지원하는 개발 환경에서조차 검색 조건이 다양해 자주 Dynamic SQL를 사용해 조건절을 동적으로 구성하게 된다.

⭐️ Static SQL에서 선택적 검색 조건 구현 예시 ⭐️

[예시1] 거래일자만 필수입력 조건이고, 나머지는 모두 선택적 입력 조건일 때

사용자의 선택이나 입력 값에 따라 %option 부분에 조건절을 아래와 같이 동적으로 붙여나간다.

select 거래일자, 종목코드, 투자자유형코드, 주문매체코드 , 체결건수, 체결수량, 거래대금
from 일별종목거래
where 거래일자 between  :시작일자 and  :종료일자
%option ;

👉 %option = " and 종목코드 = 'KR123456' and 투자자유형코드 = '1000'"

[예시2] 예시1의 SQL을 다른 방식으로 구현(NVL 활용)

select 거래일자, 종목코드, 투자자유형코드, 주문매체코드 , 체결건수, 체결수량, 거래대금
from 일별종목거래
where 거래일자 between :시작일자 and :종료일자
and 종목코드 = nvl(:종목코드, 종목코드) and 투자자유형코드 = nvl(:투자자유형코드, 투자자유형코드)
and 주문매체구분코드 = nvl(:주문매체구분코드, 주문매체구분코드) ;

단 한 개의 실행계획을 공유하면서 반복 재사용하게 되므로 라이브러리 캐시 효율 측면에서는 최상의 선택이다.
인덱스를 이용한 액세스가 효과적인 상황에서 인덱스를 전혀 사용하지 못하거나(종목코드나 투자자유형코드 또는 주문매체구분코드가 선두컬럼일때) 사용하더라도 비효율적으로 사용하여 쿼리 수행 속도 측면에서 낭패를 본다.

[예시3] SQL을 모두 분리하기

종목코드, 투자자유형코드, 주문매체구분코드 입력여부에 따라 SQL을 모두 분리해서 구현하면
라이브러리 캐시 효율과 I/O 효율을 모두 고려할 수 있지만 입력 조건을 처리하는 데에만 모두 8개의 SQL을 따로 작성해야 하므로 실질적으로 구현은 어렵다!

1. 거래일자 between
2. 거래일자 between, 종목코드 =
3. 거래일자 between, 종목코드 =, 투자자유형코드 =
4. 거래일자 between, 종목코드 =, 투자자유형코드 =, 주문매체구분코드 =
5. 거래일자 between, 종목코드 =, 주문매체구분코드 =
6. 거래일자 between, 투자자유형코드 =
7. 거래일자 between, 투자자유형코드 =, 주문매체구분코드 =
8. 거래일자 between, 주문매체구분코드 =

[예시4] 인덱스 구성을 고려해 변별력이 좋은 컬럼 중심으로 2~3개의 SQL로 분기

< 인덱스 구성 >
INDEX01 : 종목코드 + 거래일자
INDEX02 : 투자자유형코드 + 거래일자 + 주문매체구분코드
INDEX03 : 거래일자 + 주문매체구분코드

애플리케이션에서 IF문을 이용해 분기하는 방법이 있다.
union all을 사용하여 일반적인 SQL 작성 표준보다는 튜닝 차원에서 접근하고 필요에 따라 적절히 활용하도록 한다.

select 거래일자, 투자자유형코드, 회원번호 , 체결건수, 체결수량, 거래대금
from  일별종목거래
where :종목코드 is not null
and   거래일자 between :시작일자 and :종료일자
and   종목코드 = :종목코드
and   투자자유형코드 = nvl(:투자자유형, 투자자유형코드)
and   주문매체구분코드 = nvl(:주문매체, 주문매체코드)
union all
select 거래일자, 투자자유형코드, 회원번호 , 체결건수, 체결수량, 거래대금
from  일별종목거래
where :종목코드 is null and :투자자유형 is not null
and   거래일자 between :시작일자 and :종료일자
and   투자자유형코드 = nvl(:투자자유형, 투자자유형코드)
and   주문매체구분코드 = nvl(:주문매체, 주문매체코드
union all
select 거래일자, 투자자유형코드, 회원번호 , 체결건수, 체결수량, 거래대금
from  일별종목거래
where :종목코드 is null and :투자자유형 is null
and 거래일자 between :시작일자 and :종료일자
and 주문매체구분코드 = nvl(:주문매체, 주문매체구분코드) ;

(2) 선택적 검색 조건에 대한 현실적인 대안

Static SQL 사용을 원칙으로 하나 사용자 입력 조건에 따라 생성될 수 있는 SQL 최대 개수가 너무 많을 때는 Dynamic SQL 사용을 허용한다.

이 때 , 라이브러리 캐시 효율화의 핵심인 바인드 변수 사용 원칙만큼은 준수한다.

[예시] 선택적 검색 조건에 대한 현실적인 대안

SQLStmt := 'SELECT 거래일자, 종목코드, 투자자유형코드, 주문매체코드, 체결건수, 체결수량, 거래대금 '
|| 'FROM 일별종목거래 '
|| 'WHERE 거래일자 BETWEEN :1 AND :2 ';

IF :종목코드 IS NULL Then
        SQLStmt := SQLStmt || 'AND :3 IS NULL ';
Else
        SQLStmt := SQLStmt || 'AND 종목코드 = :3 ';
End If;
If :투자자유형 IS NULL Then
        SQLStmt := SQLStmt || 'AND :4 IS NULL ';
Else
        SQLStmt := SQLStmt || 'AND 투자자유형코드 =:4 ';
End If;

EXECUTE IMMEDIATE SQLStmt
INTO :A, :B, :C, :D, :E, :F, :G
USING :시작일자, :종료일자, :종목코드, :투자자유형코드;

(3) 선택적 검색 조건에 맞는 인덱스 설계

인덱스 설계시 SQL 패턴을 참조할 때 완성된 형태의 SQL들은 SQL Repository에 저장된 것을 참조한다.
그렇지 않은 건 수행된 최종 SQL들을 수집하여 자주 나타나는 액세스 유형을 기준으로 인덱스 설계를 진행한다.
✅ 수행된 최종 SQL 수집 방법
- SQL 트레이스 조회
- v$sql 뷰 조회
- 등등 ^^

[인덱스 설계 예시]

Dynamic 패턴으로 SQL을 던지면 오라클 서버를 통해 수집되는 최종 SQL은 Static SQL로 작성했을 때와 차이가 없다.
따라서 이들 액세스 경로 기준으로 인덱스를 설계한다.

SELECT 거래일자, 종목코드, 투자자유형코드, 주문매체코드 , 체결건수, 체결수량, 거래대금
FROM 일별종목거래
WHERE 거래일자 BETWEEN ? AND ? ;

SELECT 거래일자, 종목코드, 투자자유형코드, 주문매체코드 , 체결건수, 체결수량, 거래대금
FROM 일별종목거래
WHERE 거래일자 BETWEEN ? AND ?
AND 종목코드 = ? 등등.. ;

인덱스 구성전략만으로 튜닝이 되지 않을 때

인덱스 구성전략만으로 튜닝이 되지 않을 때는 옵티마이져 힌트를 사용한다.
하지만 조건절이 위와 같이 동적으로 바뀌는 경우엔 힌트를 함부로 사용할 수 없다.
- Static SQL을 사용할 때 인덱스 구성과 컬럼 분포, 자주 사용되는 액세스 유형들을 고려해 SQL을 통합하고 힌트를 기술할 수 있는 형태로 재작성해야한다.

(4) 선택적 검색 조건에서의 몇가지 NVL 사용 대안

(null값이면 그냥 1=1 처리가 되어야함)

1. OR 조건 사용

select *
from 일별종목거래
where (:isu_cd is null or isu_cd = :isu_cd);

=====================================================
Execution Plan
---------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS
1  0  TABLE ACCESS (FULL) OF '일별종목거래' (TABLE)
=====================================================

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

2. LIKE 연산자를 사용

select *
from 일별종목거래
where isu_cd like :isu_cd || '%';

=====================================================
Execution Plan
---------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS
1  0  TABLE ACCESS (BY LOCAL INDEX ROWID) OF '일별종목거래' (TABLE)
2  1   INDEX (RANGE SCAN) OF '일별 종목거래_PK' (INDEX (UNIQUE))
=====================================================

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

3. NVL 함수를 사용

select *
from 일별종목거래
where isu_cd = nvl(:isu_cd, isu_cd) ;

===================================================================
Execution Plan
---------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS
1  0  CONCATENATION
2  1   FILTER
3  2    TABLE ACCESS (FULL) OF '일별종목거래' (TABLE)
4  1   FILTER
5  4    TABLE ACCCESS (BY LOCAL INDEX ROWID) OF '일별종목거래' (TABLE)
6  5     INDEX (RANGE SCAN) OF '일별종목거래_PK' (INDEX (UNIQUE))
===================================================================

사용자의 :isu_cd 입력 여부에 따라 Full Table Scan과 Index Scan으로 실행계획이 자동으로 분기된다.
nvl을 여러 컬럼에 대해 사용했을 때에는 그 중 변별력이 가장 좋은 컬럼 기준으로 한번만 분기가 일어난다.
복잡한 옵션 조건을 처리 할 때 이 방식에만 의존하기 어려우며 nvl 함수를 사용할 때는 해당 컬럼이 not null 컬럼이어야 한다.

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

select *
from 일별종목거래
where isu_cd = decode(:isu_cd, null, isu_cd, :isu_cd);

===================================================================
Execution Plan
---------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS
1  0  CONCATENATION
2  1   FILTER
3  2    TABLE ACCESS (FULL) OF '일별종목거래' (TABLE)
4  1   FILTER
5  4    TABLE ACCESS (BY LOCAL INDEX ROWID) OF '일별종목거래' (TABLE)
6  5     INDEX (RANGE SCAN) OF '일별종목거래_PK' (INDEX (UNIQUE))
===================================================================

사용자의 :isu_cd 입력 여부에 따라 Full Table Scan과 Index Scan으로 실행계획이 자동으로 분기된다.
decode를 여러 컬럼에 대해 사용했을 때에는 그 중 변별력이 가장 좋은 컬럼 기준으로 한번만 분기가 일어난다.
복잡한 옵션 조건을 처리 할 때 이 방식에만 의존하기 어려우며 decode 함수를 사용할 때는 해당 컬럼이 not null 컬럼이어야 한다.
✅ nvl 또는 decode 함수 사용시 주의할점
- null 허용 컬럼일 때는 결과 집합이 달라지므로 주의한다.
  - 사용자가 :isu_cd 값을 입력하지 않았을 때는 조건절이 isu_cd = isu_cd가 되는데,
    isu_cd 컬럼 값이 null일 때 오라클은 false를 리턴하기 때문이다.
  - 즉, null = null 비교가 불가능하다는 소리이다.
    ( 참고로, null = null 비교가 가능한 DBMS도 있기는 하다. )
```
SQL> select * from dual where NULL = NULL;
  =============
  선택된 레코드가 없습니다.
  =============
```

5. union all를 사용

select * from 일별종목거래 where :isu_cd is null
union all
select * from 일별종목거래 where :isu_cd is not null and isu_cd = :isu_cd
===================================================
Execution Plan
---------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS
1  0  UNION-ALL
2  1   FILTER
3  2    TABLE ACCESS (FULL) OF '일별종목거래' (TABLE)
4  1   FILTER
5  4    TABLE ACCESS (BY LOCAL INDEX ROWID) OF '일별종목거래' (TABLE)
6  5     INDEX (RANGE SCAN) OF '일별종목거래_PK' (INDEX (UNIQUE))
===================================================

(5) 정리

1. not null 컬럼일 때는 nvl, decode를 사용하는 것이 편하다.

2. null 값을 허용하고 인덱스 액세스 조건으로 의미있는 컬럼이라면 union all을 사용해 명시적으로 분기한다.

3. 인덱스 액세스 조건으로 참여하지 않는 경우... 즉, 인덱스 필터 또는 테이블 필터 조건으로만 사용되는 컬럼이라면 어떤 방식을 사용해도 무방하다.

👉 (:c isnull or cao = :c) 또는 (c like :c || '%') 등등 ...

Yu River

도광양회(韜光養晦) ‘빛을 감추고 어둠속에서 힘을 기른다’

이전 포스트

[4) 라이브러리 캐시 최적화 원리] 9. Static vs. Dynamic SQL

다음 포스트