SQL) 라이브러리 캐시 최적화 원리 Ⅳ

jinsung·2025년 12월 10일
SQLPoraclesql데이터자격증

SQL

목록 보기
22/46
post-thumbnail

9. Static SQL vs Dynamic SQL

Static SQL

Static SQL이란, String형 변수에 담지 않고 코드 사이에 직접 기술한 SQL문을 말한다.
String 변수를 사용하지 않아 PreCompile 시 Syntax, Semantics 체크가 가능하다.

Dynamic SQL

Dynamic SQL이란, String형 변수에 담아서 기술하는 SQL문을 말한다.
String 변수를 사용하므로 조건에 따라 SQL문을 동적으로 바꿀 수 있고, 또는 런타임 시에 사용자로부터 SQL문을 일부 또는 전부를 입력 받아서 실행할 수도 있다.
따라서, PreCompile 시 Syntax, Semantics 체크가 불가능하다.

Static SQL, Dynamic SQL은 애플리케이션 개발 측면에서의 구분일 뿐이며, 데이터베이스 입장에서는 차이가 없다. 둘 중 뭘 사용하든 오라클 입장에서는 SQL문 그 자체만 인식할 뿐이며, PL/SQL,Pro*C 등에서 애플리케이션 커서 캐싱 기능을 활용하고자 하는 경우 외에는 성능에서 전혀 영향이 없다. 애플리케이션 커서 캐싱 기능을 사용하지 않는다면 둘의 구분은 라이브러리 캐시 효율과도 전혀 무관하다.

그러므로 라이브러리 캐시 효율을 논할 때 초점은 바인드 변수 사용 여부에 맞춰야 한다.

Dynamic SQL을 사용해서 문제가 되는 것이 아니라 바인드 변수를 사용하지 않았을 때 문제가 되는 것이다.

10. Dynamic SQL 사용 기준

Dynamic SQL 사용에 관한 기본 원칙

  1. Static SQL을 지원하는 개발환경이라면 Static SQL로 작성하는 것이 원칙이다. Static SQL은 PreCompile 과정을 거치므로 런타임 시 안정적인 프로그램 빌드가 가능하다는 장점이 있다. 그리고 Dynamic SQL을 사용하면 애플리케이션 커서 캐싱 기능이 작동하지 않을 때가 있는데, 이 기능이 필요한 경우애 Dynamic SQL을 사용하면 성능이 더 나빠진다.

  2. 아래의 경우는 Dynamic SQL을 사용해도 무방하다

  • PreCompile 과정에서 컴파일 에러가 나는 구문을 사용할 때
    ex) Pro*C에서 스칼라 서브쿼리, 분석함수, ANSI 조인 등

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

  1. 2번 경우에 해당해서 Dynamic SQL을 사용했더라도 바인드 변수를 사용하는 것을 원칙으로 한다.
    특히, 사용빈도가 높고 조건절 컬럼의 값 종류가 매우 많을때는 반드시 준수한다.

  2. 3번 바인드 변수 사용원칙을 준수하되 아래 경우는 예외적으로 인정한다.

  • 배치 프로그램이나 DW/OLAP 등 정보계 시스템 시스템에서 사용하는 Long Running 쿼리, 이들 쿼리는 파싱 소요시간이 쿼리 총 소요시간에서 차지하는 비중이 매우 낮고, 수행빈도가 낮아 하드파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 적음

  • OLTP성 애플리케이션이라도 사용빈도가 매우 낮아 하드파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 없을 때

  • 조건절 컬럼의 값 종류가 소수 일때, 특히 값 분포가 균일하지 않아 옵티마이저가 컬럼 히스토그램 정보를 활용하도록 유도하고자 할 때

기본 원칙이 잘 지켜지지 않는 첫 번째 이유 : 선택적 검색 조건

위처럼 Dynamic SQL 사용 원칙을 정하고 개발을 시작해도, 중간에 점검해 보면 잘 지켜지지 않는다. Static SQL을 지원하는 환경에서조차 자주 Dynamic SQL을 사용해 조건절을 동적으로 구성한다. 그 원인은 검색 조건이 다양해 사용자의 선택에 따라 조건절이 동적으로 바뀌는 경우가 대부분이다.

이 경우 대안은?

SQL을 Dynamic하게 구성하면, 인덱스를 설계할 때 다소 불편하다는 단점이 있다. SQL Repository에서 SQL을 수집해 테이블별 액세스 유형을 분석하면서 인덱스를 설계해야하는데, 조건절이 프로그램 수행 도중 동적으로 바뀌기 때문이다. 그리고 옵티마이저 힌트를 사용하기도 애매하다.
이런 단점이 있긴 하지만 개발 생산성도 무시할 수 없으므로 Dynamic SQL을 적재적소에 잘 활용하라고 권고를 하는 것이다.
인덱스 설계 문제를 얘기하자면, 완성된 SQL들은 SQL Repository에 저장된 것을 참조하고 그렇지 않은 것들은 수행된 최종 SQL들을 수집해서 자주 나타나는 액세스 유형을 기준으로 인덱스를 설계하면 된다.
원칙은 Static SQL로 작성하는 것이며, 방법이 없거나 SQL이 너무 복잡한 경우에만 Dynamic SQL을 꺼내 들려고 노력해야 한다.

선택적 검색 조건에 사용할 수 있는 기법 성능 비교

1. OR 조건을 사용하는 경우

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

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

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

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

인덱스 사용이 가능하지만 사용자가 :isu_id 값을 입력하지 않았을 때 Table Full Scan이 유리한데도 인덱스를 사용하므로 성능이 나빠진다.
또, NULL 허용 컬럼일 때 결과집합이 달라질 수 있으므로 반드시 NOT NULL 컬럼일 때만 사용해야 한다.

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

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

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

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

3, 4 번은 사용자의 :isu_cd 입력 여부에 따라 Full Table Scan과 Index Scan으로 실행계획이 자동 분기된다. null 허용 컬럼이면 결과집합이 달라질 수 있기 때문에, 둘 다 not null 컬럼일 때 사용해야 한다.

5. Union All을 사용하는 경우

select *
from 일별종목거래
where :isu_cd is not null
and isu_cd = :isu_cd
union all
select *
from 일별종목거래
where :isu_cd is null;

총 정리

  1. not null 컬럼일 때는 nvl, decode을 사용하는 것이 편하다.
  2. null 값을 허용하고 인덱스 액세스 조건으로 의미있는 컬럼이면 union all을 사용해 명시적으로 분기한다.
  3. 인덱스 액세스 조건으로 참여하지 않은 경우, 즉 인덱스 필터 또는 테이블 필터 조건이면 or 조건, like 연산자 방식을 사용해도 무방하다.

11. Static SQL 구현을 위한 기법들

1. IN-LIST 항목이 가변적이지만 최대 경우 수가 적은 경우

select *
from LP회원
where 회원번호 in(:a, :b, :c);

이 쿼리를 사용해 사용자가 입력하지 않은 항목에 null 값을 입력하면 자동으로 결과집합에서 제외된다. 이렇게 Static SQL을 구현하면 Dynamic SQL을 사용하지 않아도 된다.

2. IN-List 항목이 가변적이지만 최대 경우 수가 매우 많은 경우

select * 
from 수시공시내역
where 공시일자 = :일자
and :inlist like '%' || 분류코드 || '%';

:inlist := '01,03,08,14,17,24,33,46,48,53';

select * 
from 수시공시내역
where 공시일자 = :일자
and INSTR(:inlist, 분류코드) > 0;

조건절을 위와 같이 작성하고 사용자가 선택한 분류코드를 ','구분자로 연결해 String 변수에 담아서 바인딩하고 실행시킨다.

오라클 내부적으로 like 보다는 instr함수가 빠르다.
둘 쿼리 다 가공한 컬럼을 조건절에 사용해서 인덱스 액세스 조건으로 사용을 못해 성능이 좋지 않다.

  1. IN-List 를 사용할 때
select * 
from 수시공시내역
where 공시일자 = :일자
and 분류코드 in (...);
  1. like 또는 instr 함수를 사용할 때
select * 
from 수시공시내역
where 공시일자 = :일자
and INSTR(:inlist, 분류코드) > 0;

인덱스가 [분류코드 + 공사일자]일 때는 1번이 유리하다. 2번은 분류코드가 가공한 컬럼이라 인덱스를 활용할 수 없다.
엔덱스가 [공사일자 + 분류코드]일 때는 상황에 따라 다르다.
in list의 항목 개수가 소수이면 1번이 유리하지만 다수일 때는 인덱스를 그만큼 여러 번 탐침해야 하므로 2번이 유리할 수도 있다.
사용자가 대게 소수 항목으로만 조회하거나 인덱스 구성이 [분류코드 + 공사일자]일 때는 인덱스를 좀더 효율적으로 액세스할 수 있는 방법을 강구해야 한다.

profile
jinsung
Data Engineer
이전 포스트

SQL) 라이브러리 캐시 최적화 원리 Ⅲ

다음 포스트

SQL) 데이터베이스 Call 최소화 원리 Ⅰ

0개의 댓글