[SQLP] 1-4. 라이브러리 캐시 최적화

🧷 라이브러리 캐시 최적화

🖇️ 바인드 변수

1. SQL 공유 및 재사용 단점

사용자 정의 함수/ 프로시저, 트리거, 패키지 등 의 경우

 

이러한 오브젝트들은 Stored Object로 분류되며, 생성 시 고유한 이름을 갖는다. 생성과 동시에 컴파일된 상태로 데이터 딕셔너리에 저장되며, 사용자가 삭제하지 않는 한 영구적으로 보관된다. 실행 시에는 라이브러리 캐시에 적재되어 여러 사용자가 공유하며 재사용할 수 있다.

SQL의 경우

 

SQL은 Transient Object로 분류되며, 고유한 이름이 없고 SQL 텍스트 자체가 이름 역할을 한다. 데이터 딕셔너리에 저장되지 않고, 처음 실행 시 최적화 과정을 거쳐 동적으로 생성된 내부 프로시저가 라이브러리 캐시에 적재된다. 이를 통해 여러 사용자가 동일한 SQL을 공유하며 재사용할 수 있다. 하지만 캐시 공간이 부족할 경우 해당 SQL은 캐시에서 제거되고, 이후 다시 실행 시 최적화 과정을 거쳐야 한다.

 

IBM DB2와 같은 DBMS는 SQL을 영구적으로 저장할 수 있는 기능을 제공하지만, Oracle이나 SQL Server와 같은 DBMS는 그렇지 않다. SQL 텍스트가 이름 역할을 하므로, 작은 부분이라도 달라지면 서로 다른 객체로 인식된다. 모든 SQL을 영구적으로 저장하려면 방대한 저장 공간이 필요하며, 이로 인해 SQL을 검색하는 속도도 느려질 수 있기 때문이다.

 

2. 바인드 변수의 중요성

동시 다발적 하드 파싱 발생

 

I/O 작업이 거의 발생하지 않더라도, SQL 하드 파싱이 동시다발적으로 발생하면 CPU 사용률이 급격히 상승하며 DBMS에 부하를 초래할 수 있다.

 

예를 들어, 특정 이벤트로 인해 동시 접속자가 대량으로 발생하여 요청이 몰릴 경우를 생각해 보자. 이때, FOR LOOP를 통해 조건절의 값을 변경하며 SQL을 실행하면, 각 SQL에 대해 새로운 커서가 생성되고 매번 하드 파싱이 발생한다. 결과적으로 SQL 옵티마이저와 로우 생성기가 만든 내부 프로시저가 대량으로 적재되면서 성능 저하를 유발할 수 있다.

공유 가능한 SQL

 

동시다발적 하드 파싱 문제를 방지하기 위해 파라미터 **Driven 방식을 사용하는데, 여기서 핵심이 되는 것이 바로 바인드 변수다. 바인드 변수를 활용하면, 라이브러리 캐시를 조회할 때 아래와 같은 단일 SQL만 발견된다.

SELECT * FROM TABLE FROM ID = :1

 

이 SQL은 최초 실행 시 단 한 번만 하드 파싱이 이루어지고, 이후에는 동일한 프로시저를 여러 사용자가 공유하며 반복 재사용하게 된다. 이를 통해 DBMS 부하를 효과적으로 줄일 수 있다.

• 커서 생성 횟수 감소: 불필요한 커서 생성을 방지한다.
• 메모리와 CPU 사용량 절감: 동일한 SQL을 반복 재사용함으로써 메모리 소모와 파싱에 소요되는 시간을 줄인다.
• DB 성능 및 확장성 향상: 리소스 절약(메모리와 CPU 사용량 절감)을 통해 데이터베이스의 성능과 확장성을 개선한다.
• 동시 사용자 접속 시 효과 극대화: 특히 동시 접속자가 많은 환경에서 더욱 중요한 역할을 한다.

 

3. 바인드 변수의 부작용

바인드 변수를 사용하면 최초 실행 시 최적화 과정을 거쳐 생성된 실행 계획이 캐시에 적재되고, 이후에는 해당 계획을 재사용하며 값을 바인딩하여 반복적으로 활용된다. 즉, 최적화 시점은 조건절에 값을 바인딩하는 시점이 아닌 실행 시점이다. 이러한 특성으로 인해 다음과 같은 문제점이 발생할 수 있다.

• SQL 최적화 시점에 조건절 컬럼의 히스토그램 정보를 활용하지 못한다.
• 데이터 분포가 균일하지 않은 경우, 평균 분포를 기반으로 생성된 실행 계획이 최적의 선택이 아닐 수 있다.

(1) 바인드 변수 Peeking

바인드 변수의 부작용을 극복하기 위해 Oracle 9i부터 도입된 기능이다. SQL이 처음 하드 파싱될 때, 바인드 변수의 값을 "엿보고(peeking)" 이를 바탕으로 컬럼 분포를 고려한 실행 계획을 수립한다. (SQL Server의 경우, Parameter Sniffing)

그러나 실행 계획이 적절하지 않을 경우, SQL 실행 속도가 갑자기 느려지거나, 자주 실행 계획이 변경되어 성능 변동이 심해지는 문제가 발생할 수 있다. 이러한 이유로 대부분의 시스템에서 현재는 비활성화된 상태로 운영된다.

(2) 적응적 커서 공유(Adaptive Cursor Sharing)

Oracle 11g에 도입된 기능으로, 입력된 바인드 변수 값의 분포에 따라 서로 다른 실행 계획을 사용할 수 있도록 설계되었다.

1. 선택도가 높은 조건에 대해 테이블을 Full Scan 하는 실행 계획을 생성하고, 이를 커서로 재사용.
2. 선택도가 낮은 조건이 입력되면, 히스토그램 정보를 기반으로 인덱스를 활용하는 새로운 실행 계획을 생성.
3. 두 개의 Child Cursor가 캐시에 저장되고, 선택도에 따라 적합한 커서를 선택적으로 재사용.
4. 현재 바인드 값에 적합한 커서가 없을 경우, 하드 파싱을 통해 새로운 실행 계획을 생성.

이 기능을 활용하려면 조건절 컬럼에 히스토그램이 생성되어 있어야 한다. 다만, 기존 커서를 먼저 실행한 후 성능이 좋지 않다고 판단되었을 때 새로운 실행 계획을 생성하는 방식으로, 즉각적인 최적화가 이루어지지 않는 단점이 있다.

(3) 입력 값에 따라 SQL 분리

바인드 변수 사용에 따른 부작용을 피하기 위해, 바인드 값에 따라 실행 계획을 분리하는 방안을 고려할 수 있다.

SELECT /*+ FULL(A)*/ *
FROM TABLE 
WHERE :O IN ('A','B')
AND OPTION = :O
UNION ALL
SELECT /*+ INDEX(A IDX)*/ *
FROM TABLE 
WHERE :O NOT IN ('A','B')
AND OPTION = :O

단점:

• 라이브러리 캐시 효율 저하: UNION ALL로 결합된 N개의 SQL은 옵티마이저가 N개를 모두 최적화해야 하며, Shared Pool에서 공간 낭비가 발생.
• CPU 부하 증가:
  • Parse 단계에서 많은 CPU 리소스를 소모.
  • 실행되지 않는 분기 조건까지 포함되어 Execute 단계에서도 추가적인 CPU 사용 발생.
• 네트워크 트래픽 증가: SQL 분리로 인해 메시지 전송량 증가.

(4) 예외적으로 Literal 상수값 사용

조건절 컬럼의 값 종류가 소수일 경우, 바인드 변수 대신 Literal 상수를 사용하는 것이 더 효율적일 수 있다. 입력 값 종류가 적다면 하드 파싱 부하가 크지 않으며, 옵티마이저가 최적의 실행 계획을 수립할 가능성이 높아지기 때문이다.

Literal 상수 사용이 유리한 경우:

1. 범위 검색 조건: 부등호나 BETWEEN 조건절을 사용할 경우, Literal 상수를 사용하는 것이 최적화에 유리
2. 배치 프로그램 및 정보계 시스템(DW/OLAP):
   • 대부분 기간 조건을 포함하며 범위 검색이 많기 때문에 Literal 상수를 사용할 때 더 나은 실행 계획이 수립
   • Long-running 쿼리의 특성상 파싱 시간은 전체 쿼리 시간에서 큰 비중을 차지하지 않음.
3. 사용 빈도가 매우 낮은 경우:
   • 하드 파싱에 따른 라이브러리 캐시 부하가 거의 없을 때, OLTP 시스템에서도 제한적으로 Literal 상수를 사용할 수 있음.
   • 단, OLTP 환경에서는 특수한 경우를 제외하고는 반드시 바인드 변수를 사용하는 것이 원칙.
4. 조건절 컬럼 값 종류가 소수일 경우: 값의 종류가 적어 캐시에 저장된 SQL 재사용이 용이한 경우 Literal 사용이 효과적일 수 있음.

 

🖇️ 세션 커서 캐싱 기능

(1) 문제 발생

커서를 공유할 수 있는 형태로 SQL을 작성하면 하드 파싱을 최소화하여 시스템 확장성을 높일 수 있다. 그러나 하드 파싱을 피하더라도 SQL 구문을 분석하고 해시 값을 계산하며, Library Cache 래치를 획득한 뒤 라이브러리 캐시에서 커서를 탐색하는 작업 자체가 부담이 되는 작업이다. 특히, SQL을 동시 실행하는 빈도가 높아지면 경합이 발생하여 시스템 부하를 유발한다.

(2) 세션 커서 캐싱을 통해 문제 해결

Shared Pool에 위치한 공유 커서를 실행하기 위해 PGA로 인스턴스화된 커서를 세션 커서 라고 한다. 쿼리를 실행한 후 커서를 닫으면, 세션 커서에 의해 할당된 메모리와 공유 커서를 가리키는 포인터가 즉시 해제된다. 이후 동일한 SQL을 실행하면 커서를 열기 위해 다시 라이브러리 캐시 탐색 과정을 거쳐야 한다.

이를 해결하기 위해 오라클은 자주 실행되는 세션 커서를 세션 커서 캐시(Session Cursor Cache)에 저장하는 기능을 제공한다. 이를 통해 Parse Call에 따른 부하를 효과적으로 줄일 수 있다.

(3) 기능 활성화

1. Parse Call 횟수 ≥ 3인 커서를 닫을 때, 해당 커서를 세션 커서 캐시에 저장한다.
2. 세션 커서 캐시에는 SQL 텍스트와 함께 공유 커서를 참조하는 포인터를 저장한다.
3. 커서를 닫더라도 공유 커서에 대한 참조를 유지하므로, 이후 동일한 SQL 실행 시 더 빠르게 커서를 열 수 있다.

(4) 특징

• LRU 알고리즘 : 세션 커서 캐시 내에서도 LRU(Least Recently Used) 알고리즘을 적용하여, 새로운 엔트리를 저장할 공간이 필요할 경우 사용 빈도가 낮은 커서를 밀어낸다.
• Parse Call 부하 감소: Parse Call 자체를 줄이는 것이 아니라, Parse Call로 인한 시스템 부하를 감소시키는 기능이다.
• PL/SQL과 연계: PL/SQL에서는 SQL 커서를 자동으로 캐싱한다. 단, Oracle 10g부터는 session_cached_cursors 파라미터가 0보다 큰 값으로 설정되어 있어야 작동한다.

(5) 장점

1. 라이브러리 캐시 부하 경감: 자주 실행되는 SQL에 의해 발생하는 부하를 줄인다.
2. CPU 사용량 감소: SQL 구문 분석 및 라이브러리 캐시 탐색 과정에서 소모되는 CPU 자원을 줄인다.
3. 래치 요청 횟수 감소: 소프트 파싱 과정에서 발생하는 래치 요청을 줄여 시스템 성능을 향상시킨다.

 

🖇️ 애플리케이션 커서 캐싱

문제 발생

 

세션 커서를 캐싱하면 SGA의 공유 커서를 빠르게 찾아 커서를 열 수 있다. 그러나, 세션 커서 캐시에 있는 SQL을 실행하더라도 여전히 공유 커서 힙을 Pin 하고, 실행에 필요한 메모리 공간을 PGA에 할당하는 작업은 반복된다.

이러한 작업을 생략하고 SQL을 더욱 빠르게 실행하려면 애플리케이션 커서 캐싱 기능을 사용해야 한다. 이 기능은 공유 커서를 Pin 한 상태로 유지하여 Parse Call이 최초 1회만 발생하게 한다. 일반적으로는 Execute Call 횟수만큼 Parse Call이 반복된다.

1. Java에서의 커서 캐싱

Java에서 커서 캐싱을 위해 아래 조건을 만족해야 한다:

• 묵시적 캐싱(implicit caching) 옵션을 활성화하거나, Statement(커서)를 닫지 않아야 한다.
• 하드 파싱을 최초 1회만 수행하려면 바인드 변수를 사용해야 한다.

(1) 커서를 닫지 않고 재 사용할 경우

public static void CursorHolding (Connection conn, int count) throws Exception{
  PreparedStatement stmt = conn.prepareStatement ( 
  "SELECT /* cursor holding */ ?,?,?, a .* FROM emp a WHERE a.ename LIKE 'W%'");
  ResultSet rs;

  for (int i = 1; i <= count; i++) {

    stmt.setInt(1,i);
    stmt.setInt(2,i);
    stmt.setString(3,"test");
    rs=stmt.executeQuery();
    rs.close();

  }

  stmt.close(); // 루프 외부에서 Cursor Close

}

 

바인드 변수를 사용했으므로 하드 파싱은 최초 1회만 수행된다. 커서를 루프 외부에서 닫아 재사용 가능하며, Parse Call은 1회만 발생한다.

 

(2) 묵시적 캐싱 기능 사용할 경우

public static void CursorCaching(Connection conn, int count) throws Exception{
  ((OracleConnection) conn) .setStatementCacheSize(1); // 캐시 사이즈를 1로 지정
  ((OracleConnection) conn) .setImplicitCachingEnabled(true); // 묵시적 캐싱 기능 활성화

  for (int i = 1; i <= count; i++) {
    PreparedStatement stmt = conn.prepareStatement (
    "SELECT /* implicit caching */ ?,?,?,a .* " +
    "FROM emp a WHERE a.ename LIKE 'W%'");
    stmt.setInt(1,i);
    stmt.setInt(2,i);
    stmt.setString(3,"test");
    ResultSet rs=stmt.executeQuery();

    rs.close();

    stmt.close(); // 루프 내부에서 Cursor Close
  }
}

 

커서를 루프 내부에서 닫더라도 묵시적 캐싱 옵션이 활성화되어 내부적으로 캐시에 저장되기 때문에 커서를 재사용할 수 있다. 이는 묵시적 캐싱 옵션을 활성화했기 때문이다.

2. PL/SQL에서의 커서 캐싱

PL/SQL에서는 별도의 옵션 없이도 자동으로 커서를 캐싱한다. 단, Static SQL일 경우에만 캐싱이 적용되며, Dynamic SQL이나 Cursor Variable(Ref Cursor)을 사용하면 자동 캐싱 효과가 사라진다.

• Oracle 9i까지는 open_cursors 파라미터가 캐싱 가능한 SQL 개수를 결정했다.
• Oracle 10g부터는 session_cached_cursors 파라미터가 이를 대신한다. 해당 파라미터는 세션 커서과도 관련 있기 때문에 세션 커서 캐싱 기능이 비활성화되면 PL/SQL의 자동 커서 캐싱도 비활성화된다.

3. n-Tier 환경에서의 커서 캐싱

n-Tier 환경에서는 DB Connection 사용 후 즉시 커넥션 풀에 반환되므로 애플리케이션 수준에서 커서 캐싱 기법을 효과적으로 활용하기 어렵다. 그러나 PL/SQL로 작성된 함수나 프로시저를 적극적으로 활용하면, 애플리케이션 커서 캐싱기법을 활용할 수 있다. 이 접근 방식은 라이브러리 캐시 효율을 크게 향상시킨다.

🖇️ Static SQL vs Dynamic SQL

Static SQL (=Embedded SQL) 이란? 문자열 변수에 담지 않고 코드 사이에 직접 기술된 SQL문을 의미한다.

• Static SQL을 지원하는 개발 언어는 PowerBuilder, PL/SQL, Proc*C, SQLJ 뿐이고 이외에는 모두 Dynamic SQL을 지원한다.

Dynamic SQL 이란? 문자열 변수에 담아서 기술하는 SQL문을 의미한다.

• 변수의 값을 동적으로 변경할 수 있으므로 조건에 따라 SQL문을 유연하게 조정할 수 있다.
• 실행 시 사용자 입력을 받아 SQL문의 일부 또는 전체를 구성하여 실행할 수 있다.

성능과 바인드 변수 사용

Static SQL과 Dynamic SQL은 애플리케이션 개발 관점에서의 구분일 뿐, 데이터베이스 입장에서는 동일한 SQL문으로 처리된다. 따라서 애플리케이션 커서 캐싱 기능을 활용하지 않는 한, Static, Dynamic 구분은 라이브러리 캐시 효율에 영향을 미치지 않는다.

 

결국, 라이브러리 캐시 효율을 논할 때 초점은 바인드 변수 사용 여부에 맞춰져야 한다. Dynamic SQL의 사용 자체가 성능 문제를 유발하는 것이 아니라, 바인드 변수를 사용하지 않을 때 문제가 되는 것이다.

 

따라서 SQL 성능 최적화를 위해서는 Static, Dynamic 구분보다 바인드 변수를 적절히 활용하는 것에 초점을 맞추는 것이 중요하다. Static과 Dynamic SQL을 바인드 변수 사용 여부로 구분하려는 것은 적절하지 않다.

1. Dynamic SQL 사용 기준

(1) Dynamic SQL 사용에 관한 기본 원칙

1. Static SQL을 지원하는 개발환경이라면, Static SQL로 작성하는 것이 원칙으로 한다.
   • 애플리케이션 캐싱 기능이 필요한 상황에서 Dynamic SQL을 사용하면 성능이 저하될 수 있기 때문이다.
2. Dynamic SQL 사용이 허용되는 경우
   • PreCompile 과정에서 컴파일 에러가 발생하는 SQL 구문을 사용할 때 (예: 스칼라 서브쿼리, 분석 함수, ANSI 조인 등).
   • 상황과 조건에 따라 생성되는 SQL의 경우의 수가 너무 많아 Static SQL로 작성하면 개발 생산성이 떨어지고 유지보수 비용이 크게 증가하는 경우.
3. Dynamic SQL을 사용할 때도 조건절에는 바인드 변수를 사용하는 것을 원칙으로 한다.
   • 특히 사용 빈도가 높고 조건절의 컬럼 값 종류가 많은 경우에는 반드시 준수해야 한다.
4. 바인드 변수 미사용이 예외적으로 인정되는 경우 → [[#(4) 예외적으로, Literal 상수값 사용|Literal 상수값 사용]]
   • Long Running 쿼리: 배치 프로그램, DW, OLAP 등 정보계 시스템에서 사용하는 쿼리로, 파싱 소요 시간이 전체 쿼리 수행 시간에서 차지하는 비중이 매우 낮고, 수행 빈도도 낮아 하드파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 적은 경우.
   • OLTP 애플리케이션에서의 예외: 사용 빈도가 매우 낮아 하드파싱으로 인한 부하 가능성이 없는 경우 예외적으로 허용된다. 다만, OLTP 환경에서는 가능하면 바인드 변수를 사용하는 것이 원칙이다.
   • 조건절 값의 종류가 소수이며, 값 분포가 균일하지 않아 옵티마이저가 컬럼 히스토그램 정보를 활용할 수 있도록 유도해야 하는 경우.

(2) Static SQL의 선택적 검색 조건

Dynamic SQL 사용 원칙을 정해도 실제 개발에서는 제대로 지켜지기 어려운 경우가 많다. 특히 검색 조건이 다양해 사용자 선택에 따라 조건절이 동적으로 바뀌는 상황에서는 Static SQL만으로 대응하기 어렵다.

 

Dynamic SQL 사용을 줄이기 위해 NVL과 같은 방법으로 단일 실행 계획을 공유하도록 작성할 수 있다. 이는 라이브러리 캐시 효율 측면에서 유리하지만, 쿼리 성능에 문제를 일으킬 수 있다.

 

문제점 : 조건절 컬럼이 인덱스인 경우, 인덱스를 전혀 사용하지 못하거나 비효율적으로 사용하는 문제가 발생할 수 있다.

 

예시 코드 :

SELECT *
FROM TABLE
WHERE 일자 BETWEEN :ST AND :ET
AND 구분 = NVL(:GUBUN, 'GUBUN')
AND 유형 = NVL(:CODE, 'CODE')

 

이처럼 NVL을 사용하면 라이브러리 캐시 효율은 높아지지만, I/O 효율은 저하될 수 있다. 이러한 경우 현실적인 대안이 필요하다.

 

현실적인 대안 :

1. Static SQL 사용 원칙 준수:
   사용자 입력 조건에 따라 생성될 수 있는 SQL 개수가 너무 많을 경우, Dynamic SQL 사용을 허용한다.
   • Dynamic SQL로 생성된 SQL 중 일부만 자주 사용되기 때문에, 실질적인 하드파싱 부하는 크지 않다.
   • 다만, 바인드 변수 사용 원칙은 반드시 준수해야 한다.
2. Dynamic SQL의 단점:
   • 조건절이 동적으로 변경되어 인덱스 설계가 어려워질 수 있다.
   • 옵티마이저 힌트를 사용해야 할 경우, SQL이 동적으로 변경되면 힌트 적용이 어렵다.
3. 결론:
   개발 생산성과 성능을 모두 고려해 Static SQL 사용을 기본 원칙으로 하고, Dynamic SQL은 신중하게 필요할 때만 사용해야 한다.

 

선택적 검색 조건 시 Static SQL 활용 방안

 

다양한 검색 조건을 처리할 때는 아래 방법들을 활용할 수 있다. 각 방법의 장단점을 이해하고 적절히 선택해야 한다.

1. (:CODE IS NULL OR CODE = :CODE)
   • 단점: OR 조건이 포함되면 TABLE FULL SCAN이 발생한다. 인덱스를 활용해야 하는 경우에는 적합하지 않다.
2. CODE LIKE :CODE || '%'
   • 장점: 인덱스 사용이 가능하다.
   • 단점:
     • 사용자가 값을 입력하지 않은 경우에도 인덱스를 사용해 성능이 저하될 수 있다.
     • NULL 허용 컬럼일 때는 결과 집합이 달라질 수 있다.
3. 조건절 = NVL(:CODE, 'CODE') / 조건절 = DECODE(:CODE, NULL, 'CODE', :CODE)
   • 장점: 변수 입력 여부에 따라 TABLE FULL SCAN과 INDEX SCAN으로 실행 계획이 분기된다.
   • 단점:
     • NULL 허용 컬럼에서는 결과 집합이 달라질 수 있다.
     • 여러 컬럼에 대한 사용할 때는 그중 변별력이 가장 좋은 컬럼 기준으로 한번만 분기가 일어난다. 이렇기 때문에 복잡한 옵션 조건을 처리할 때, 이 방식에만 의존하기 어렵다.
4. UNION ALL 사용: 조건에 따라 쿼리를 명시적으로 분기 처리할 수 있다.
   • 장점: 명확한 실행 계획으로 인덱스 활용이 용이하다.

선택 기준 :

• NOT NULL 컬럼 : NVL/DECODE를 사용하는 것이 편리하다.
• NULL 허용 컬럼 : 인덱스 액세스가 중요한 경우, UNION ALL을 사용해 명시적으로 분기 처리한다.
• 인덱스 액세스가 필요 없는 경우 : 인덱스 필터 또는 테이블 필터 조건으로만 사용되는 컬럼은 어떤 방식을 사용해도 무방하다.

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

사용자가 입력한 검색 조건에 따라 SQL이 다양하게 변경되는 경우가 있다. 이때 Dynamic SQL을 사용하는 동시에 조건절 비교 값에 Literal 상수를 사용하는 사례가 빈번하다. 필요하지 않은 경우 Dynamic SQL 사용을 지양하고 Static SQL을 활용하는 것이 바람직하다. 아래는 다양한 상황에서 Static SQL을 구현하는 기법들이다.

(1) IN-List 항목이 가변적이지만 최대 경우 수가 적은 경우

모든 경우의 수를 SQL로 작성하기 어려운 경우, 최대 개수만큼 바인드 변수를 처리하는 방식이 있다.

SELECT * FROM TABLE WHERE COL IN (:A, :B, :C);

• 입력하지 않은 항목에는 NULL 값을 입력하면 결과 집합에서 자동으로 제외된다.
• 전체 조회가 필요한 경우, DECODE 문을 활용한다

SELECT * FROM TABLE WHERE COL IN ( DECODE(:A, 'ALL'),'01',:A),
                                   DECODE(:B, 'ALL'),'02',:B),
                                   DECODE(:C, 'ALL'),'03',:C),);

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

경우의 수가 많은 상황에서는 IN 조건을 처리하기 어려운 경우가 있다. 이를 해결하기 위해 컬럼과 변수 위치를 바꾸는 방법을 사용할 수 있다. 문자열을 처리하는 오라클 내부 알고리즘 상 LIKE 연산자 보다 INSTR 함수가 더 빠르기 때문에 아래와 같이 작성하였다.

:LIST := '01,02,03,....,30'

SELECT *
FROM 이력
WHERE INSTR(:LIST, CODE) > 0
AND   GUBUN = :B

 

문제점:

• LIKE나 INSTR은 컬럼을 가공한 형태로 사용되므로, 인덱스 액세스 조건으로 활용하지 못한다.
• CODE 컬럼이 선두 컬럼인 인덱스에서는 인덱스 효율이 떨어질 수 있다.

해결 방안:

• 인덱스 구성 [CODE + GUBUN]인 경우 : LIKE나 INSTR은 컬럼 사용 시, 선두 컬럼이 가공한 형태 이므로 인덱스를 사용하지 못하거나 Index Full Scan 처리해야 하므로 IN 연산자가 더 유리하다.
• 인덱스 구성 [GUBUN + CODE]인 경우 :
  • CODE 항목 개수가 소수인 경우, IN 연산자가 유리하다.
  • CODE 항목 개수가 다수인 경우, 인덱스를 그만큼 여러 번 탐침 해야만 한다. INSTR/ LIKE 구문을 통해 인덱스 필터 조건으로 사용하는 것이 더 유리할 수 있다.

인덱스 구성 [GUBUN + CODE] 일 때, 인덱스를 좀 더 효율적으로 액세스 할 수 있는 방법

 

① Nested Loop Join 활용 :

:LIST := '01,02,03,....,30'

SELECT /*+ ORDERED USE_NL(B) */ B.*
FROM (
    SELECT CODE
    FROM 이력 코드
    WHERE INSTR(:LIST, CODE) > 0
) A, 이력 B
ON  B.CODE = A.CODE
AND B.GUBUN = :B

 

이력 코드 테이블을 Full Scan 하면서 NL 조인 방식으로 CODE 값을 이력 테이블에 던져주기 때문에 인덱스를 정상적으로 이용할 수 있다. 단, 이력 코드 테이블을 Full Scan 할 때, 비효율이 없어야 한다.

 

② SUBSTR로 코드 생성 :

:LIST := '01,02,03,....,30'

SELECT /*+ ORDERED USE_NL(B) */ B.*
FROM (
    SELECT SUBSTR(:LIST, (LEVEL - 1) * 2 + 1, 2) AS CODE
    FROM DUAL
    CONNECT BY LEVEL <= LENGTH(:LIST) / 2
) A, 이력 B
ON  B.CODE = A.CODE
AND B.GUBUN = :B

 

해당 방법은 변수에서 데이터를 가공하여 코드성 테이블을 인위적으로 만들어 이력 테이블과 NL 조인하기 때문에 인덱스를 정상적으로 이용할 수 있다.

(3) 체크 조건이 가변적인 경우

DECODE 함수를 사용하여 동적으로 조건을 처리할 수 있다.

SELECT COL, SUM(NUM)
FROM 이력 A
WHERE DATE = :DATE
AND   GUBUN = :GUBUN
AND EXISTS (
    SELECT '1'
    FROM 이력코드
    WHERE CODE = A.CODE
    AND   CHK = DECODE(:YN, 'Y', 'Y', CHK)
)
GROUP BY COL

 

문제점:

• 집계 조건이 전체를 대상으로 확장될 때, 불필요한 서브쿼리를 수행함으로써 블록 I/O가 증가할 수 있다.
• 조건에 따라 SQL을 분리하거나 UNION ALL을 사용하는 것이 더 나은 선택일 수 있다.

대안:

SELECT COL, SUM(NUM)
FROM 이력 A
WHERE DATE = :DATE
AND   GUBUN = :GUBUN
AND EXISTS (
    SELECT '1'
    FROM DUAL
    WHERE :YN = 'N'
    UNION ALL
    SELECT '1'
    FROM 이력코드
    WHERE CODE = A.CODE
    AND   CHK = 'Y'
    AND   :YN = 'Y'
)
GROUP BY COL

 

EXISTS 서브쿼리는 조건에 부합하는 첫 번째 레코드를 찾으면 즉시 true를 반환하고 실행을 종료한다. 특히 10g 이후부터는 DUAL 테이블이 FAST DUAL 방식으로 최적화되었기 때문에 블록 I/O가 발생하지 않는다.

 

9i에서는 DUAL 테이블을 Full Scan 방식으로 읽게 되므로, 다음과 같은 문제점이 발생할 수 있다. 필터 캐싱 효과가 있지만, 최소 DATE, GUBUN 조건 결과 건수에 따라 DUAL 테이블을 반복적으로 읽어야 한다. 반복적으로 읽는 과정에서 세그먼트 헤더 포함 2~4개 블록 I/O가 추가로 발생할 수 있다.

(4) SELECT-LIST가 동적으로 바뀌는 경우

DECODE나 CASE 함수를 활용하여 SELECT-LIST를 동적으로 변경할 수 있다.

DECODE(:GUBUN, '01', SUM(QTY), AVG(AMT)) AS NUM

 

문제점 : 집계 함수 내부에 DECODE나 CASE 함수를 사용하면, 반복 수행되어 성능이 저하될 수 있다.

SUM(DECODE(:GUBUN, '01', QTY, AMT)) AS NUM

(5) 연산자가 바뀌는 경우

사용자 입력에 따라 <, >, >=, <=, = 와 같은 연산자가 달라지는 경우에는 Dynamic SQL을 사용할 수밖에 없다. 그러나. Static SQL로 처리하기 위해 BETWENN 구문을 활용할 수 있다.

SELECT * 
FROM TABLE 
WHERE COL BETWEEN :START_VAL AND :END_VAL;

 

조건에 따라 :START_VAL과 :END_VAL에 적절한 값을 바인딩하여 연산자를 대체할 수 있다.

Reference

• 친절한 SQL 튜닝
• 오라클 성능고도화 원리와 해법 1