데이터베이스/SQL 튜닝

[SQL튜닝/아키텍처 기반 튜닝 원리] SQL 파싱 부하 (SQL 처리과정, 캐싱된 SQL 공유, Bind 변수 사용, Static/Dynamic SQL, Application Cursor Caching)

Se On 2025. 2. 10. 18:24

🖥️ 들어가며

📌 SQL 파싱 부하의 핵심
: 파싱 최소화, 쿼리 최적화, 인덱스 관리를 통해 시스템 자원 효율성을 극대화하는 것입니다.
(Hard Parsing, Soft Parsing, Bind 변수, Application Cursor Caching)

✏️ 1. SQL 처리과정

  • 처리과정 1: SQL 파싱(Parsing)
    • (1) Syntax 검사: SQL 문장에 문법적 오류가 없는지
    • (2) Semantic 검사: 의미상 오류가 없는지
    • (3) SQL 실행 계획이 Library Cache(SQL Server: Procedure Cache)에 존재하는지, 존재 여부 점검
      => ⭐⭐⭐ 캐싱 O: Soft Parsing, 캐싱 X: Hard Parsing
파싱 종류 설명
소프트 파싱 (Soft Parsing) SQL과 실행계획을 Library Cache*에서 찾아 → 곧바로 실행단계로 넘어가는 경우
*Library Cache: Hash 구조로 관리됩니다.
하드 파싱 (Hard Parsing) Library Cache에서 찾지 못해 "최적화 과정"을 거치고 나서 → 실행계획 단계로 넘어가는 경우
  • 처리과정 2: 최적화(Optimization)
    • 옵티마이저
      • SQL 최적화를 담당함
      • 사용자가 요청한 SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로(처리비용)을 선택해 주는 DBMS의 핵심
    • 최적화 과정 예시
      • 5개의 테이블을 조인한다면, 순서만 고려해도 5!(=120)개의 실행계획 평가
      • 120가지의 실행계획에 포함된 각 단계별 다양한 조인방식 고려
      • 테이블을 Full scan할지 인덱스를 사용할지, 어떤 인덱스를 어떤 방식으로 스캔할지 고려
        • 이러한 힘든과정을 거쳐 최적화된 SQL 실행계획을 한번만 쓰고 버린다면 엄청난 비효율 발생
          => 파싱 과정을 거친 SQL과 실행계획을 여러 사용자가 공유/재사용할 수 있도록 공유 메모리에 캐싱합니다.

 

 

 

✏️ 2. 캐싱된 SQL 공유

  • 실행계획 공유조건
    • 문법적 오류와 의미상 오류가 없는지 검사
    • 해시 함수로부터 반환 받은 해시값으로 → 라이브러리 캐시 내 해시버킷 탐색 → 찾아간 해시버킷에 체인으로 연결된 엔트리를 차례로 스캔 → 같은 SQL 문장 검사
    • SQL 문장을 찾으면 함께 저장된 실행계획을 가지고 바로 실행
    • 찾아간 해시 버킷에서 SQL 문장을 찾지 못하면 최적화를 수행
    • 최적화를 거친 SQL과 실행계획을 방금 탐색한 해시버킷 체인에 연결
    • 방금 최적화한 실행계획을 가지고 SQL 실행
⭐ 하드파싱 반복 X, 캐싱된 버전을 찾아 재사용하는 방법: SQL 먼저 찾아가기
⭐ 캐시에서 SQL을 찾기 위해 사용되는 key 값 = SQL 문장 그 자체
⚠️ 이 때문에 SQL 문장 안의 작은 공백 하나라도 DMBS는 서로 다른 SQL 문장으로 인식할 수 있습니다.

 

  • 실행계획을 공유하지 못하는 경우
    • (1) 공백 또는 줄바꿈
    • (2) 대문자 구분
    • (3) 주석
    • (4) 테이블 Owner 명시
    • (5) 옵티마이저 힌트 사용
    • (6) 조건절 비교값 (⭐⭐⭐바인드 변수 사용 고려)
    • ⚠️ 문장 의미와 상관 없이 Text 그 자체(Literal Query)가 다를 경우 공유 불가능합니다.
-- 1. 공백 또는 줄바꿈
SELECT * FROM CUSTOMER;
SELECT *    FROM CUSTOMER; 

-- 2. 대문자 구분
SELECT * FROM CUSTOMER; 
SELECT * FROM Customer; 

- 3. 주석(Comment)
SELECT * FROM CUSTOMER; 
SELECT /* 주석문 */ * FROM CUSTOMER; 

-- 4. 테이블 Owner 명시
SELECT * FROM CUSTOMER; 
SELECT * FROM HR.CUSTOMER; 

-- 5. 옵티마이저 힌트사용
SELECT * FROM CUSTOMER;
SELECT /*+ all_rows */ * FROM CUSTOMER; 

-- 6. 조건절 비교값 (☆바인드 변수: 실행계획 공유 O, Hard Parsing 횟수 감소 O☆)
SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'tommy'; 
SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'kara';

 

 

 

✏️ 3. 바인드 변수 사용하기

  • 사용자가 로그인하는 프로그램이 위 6번과 같이 Literal SQL로 만들어져 있다면, 로그인 사용자가 생길 때마다 프로시저가 하나씩 만들어지게 됩니다.
  • 이러한 경우 로그인 ID를 파라미터로 받아서 하나의 프로시저로 처리해야 합니다.
    => 바인드 변수를 사용하면 하나의 프로시저를 공유하면서 반복 재사용할 수 있습니다.
  • 바인드 변수 사용 시
    (1) 처음 수행한 세션이 하드 파싱을 통해 실행계획을 작성
    (2) 다른 세션들이 해당 SQL을 수행하면 라이브러리에 캐싱된 정보를 재사용함
    (3) 캐시에서 실행계획을 얻어 입력한 값만 새롭게 바인딩하면서 바로 실행 (소프트 파싱)
-- Literal SQL: 로그인 사용자가 생길 때마다 프로시저가 하나씩 만들어짐
procedure LOGIN_TOMMY() {...}
procedure LOGIN_KARA() {...}
.
.
.

-- ☆바인드 변수☆: 하나의 프로시저를 공유하면서 반복 재사용 가능
procedure LOGIN(login_id in varchar2) {...}
SELECT * FROM CUSTOMER WHERE LOGIN_ID = :LOGIN_ID;
  • 바인드 변수의 중요성
    • 바인드 변수 사용 시 효과
      • SQL과 실행계획을 반복적으로 재사용 → 파싱 소요시간과 메모리 사용량을 감소 시켜줍니다.
      •  궁극적으로 시스템 전반의 CPU, 메모리 사용률을 낮춰 → 데이터베이스 성능, 확장성을 높입니다.
    • 바인드 변수를 사용하지 않아도 되는 예외상황
      • 배치 프로그램이나 DW, OLAP 등 정보계 시스템에서 사용되는 Long Running 쿼리
        - 파싱 소요시간이 총 소요시간에서 차지하는 비중이 낮음
        - 수행빈도가 낮아 하드파싱에 의한 라이브러리 캐시 부하 유발 가능성이 낮음
        => 따라서 상수 조건절을 사용해 옵티마이저가 컬럼 히스토그램 정보를 활용할 수 있도록 유도하는 것이 유리합니다.
      • 조건절 컬럼의 값 종류(Distinct value)가 소수일 때
        - 분포도가 좋지 않은 값은 옵티마이저가 컬럼 히스토그램 정보를 활용할 수 있도록 유도하는 것이 유리합니다.
      • Literal SQL을 자동으로 변수화 시켜주는 기능
        - Oracle: cursor_sharing 파라미터 force 또는 similar 설정(응급처방, 영구적으로 사용하지 말 것)
        - SQL Server: 단순매개 변수화 활성화(default / 2000번까지 자동 활성화)

 

  • 바인드 변수 사용 시 주의사항
    • 컬럼의 분포가 균일할 때는 바인드 변수 처리가 나쁘지 않으나
    • 컬럼의 분포가 균일하지 않을 때는 실행시점에 바인딩되는 값에 따라 쿼리 성능이 다르게 나타날 수 있습니다.
      => 이럴때는 상수값을 사용하는 것이 나을 수 있습니다.
-- 보험계약 테이블의 인덱스: 계약고객번호
SELECT *
FROM   보험계약
WHERE  계약고객번호 = :CUST_NO;

/*
대부분의 고객은 1~2개 계약을 유지하나, ☆특정 법인이 전체계약의 20%☆를 가지고 있는 경우
- 일반 고객: 인덱스 활용이 유리
- 특정 법인: 인덱스 활용이 불리
*/

 

  • 바인드 변수 부작용 극복하기
    • 바인드변수 Peeking 기능 도입
      : 첫번째 바인드 변수 값을 엿보고 → 그 값에 대한 분포를 이용하여 → 실행계획을 결정하는 기능입니다.
    • Oracle: 바인드변수 Peeking
      SQL Server: Parameter Sniffing
    • ⚠️주의할 점
      - 처음 엿본 값에 따라서 실행계획이 수립되므로 위험합니다. 따라서 대부분의 운영환경에서는 비활성화 시키고 있습니다.
      (alter system set "_optim_peek_user_binds" = FALSE;)
      - Oracle 11g 이상: 적응적 커서공유(Adaptive Cursor Sharing)를 도입하여 컬럼 분포에 따라 다른 실행계획이 사용되도록 처리했지만, 이 또한 완전한 기능은 아니므로 주의해서 사용해야 합니다.
    • 그외로 다음과 같이 실행계획을 분리할 수 있습니다.
-- 보험계약 테이블의 인덱스: 계약고객번호
-- 기존
SELECT *
FROM   보험계약
WHERE  계약고객번호 = :CUST_NO;

-- 실행계획 분리
SELECT /*+ INDEX(A IDX_01)*/ *
FROM   보험계약
WHERE  계약고객번호 = :CUST_NO
AND    계약고객번호 <> '대량법인고객'
UNION ALL
SELECT /*+ FULL(A)*/ *
FROM   보험계약
WHERE  계약고객번호 = '대량법인고객';

 

 

 

✏️ 4. Static SQL과 Dynamic SQL

  • Static SQL
    • String형 변수에 담지 않고 코드 사이에 직접 기술한 SQL 문입니다. (Embedded SQL)
    • SQL이 런타임 시점에 변하지 않습니다.
    • Pro-Compile 단계에서 구문 분석, 유효 오브젝트 여부, 오브젝트 권한 등을 점검할 수 있습니다.
    • 개발언어: PowerBuilder, PL/SQL, Pro*C, SQLJ
  • Dynamic SQL
    • String형 변수에 담아서 기술하는 SQL문입니다.
    • SQL이 런타임 단계에서 변경됩니다.
    • Pro-Compiler: 내용 확인을 하지 않고 DBMS에 전달합니다.
      구문 분석, 유효 오브젝트 여부, 오브젝트 권한 등을 점검하는 것이 불가능합니다.
-- Pro*C 구분으로 Static SQL 작성한 예시
int main() 
{ 
  printf("사번을 입력하십시오 : "); 
  scanf("%d", &empno); 
  EXEC SQL WHENEVER NOT FOUND GOTO notfound; 
  EXEC SQL SELECT ENAME INTO :ename 
           FROM EMP 
           WHERE EMPNO = :empno; 
  printf("사원명 : %s.\n", ename); 

notfound: 
  printf("%d는 존재하지 않는 사번입니다. \n", empno); } 

-- Dynamic SQL 예시
int main() 
{ 
   char select_stmt[50] = "SELECT ENAME FROM EMP WHERE EMPNO = :empno"; -- ☆바인드변수☆
   // scanf("%c", &select_stmt); → SQL문을 동적으로 입력 받을 수도 있음
   EXEC SQL PREPARE sql_stmt FROM :select_stmt; 
   EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt; 
   EXEC SQL OPEN emp_cursor USING :empno; 
   EXEC SQL FETCH emp_cursor INTO :ename; 
   EXEC SQL CLOSE emp_cursor; 
   printf("사원명 : %s.\n", ename); 
}
  • ⭐⭐⭐ 바인드 변수의 중요성
    • Static이든 Dynamic SQL이든 옵티마이저는 "SQL 문장 자체"만을 인식할 뿐 성능에 영향을 주지 않습니다.
    • Library Cache 효율은 "바인드 변수의 사용여부"에 초점을 맞춰야 합니다.

 

 

 

✏️ 5. 애플리케이션 커서 캐싱

  • 같은 SQL을 여러 번 반복해서 수행할 때, 첫 번째는 하드 파싱이 일어나지만 이후부터는 Library Cache에 공유된 버전을 찾아 실행할 수 있습니다.
  • 하지만 여전히 비효율은 존재합니다.
    (SQL 문장의 문법적, 의미적 오류 확인 → 해시함수로부터 반환된 해시값을 이용해서 캐시에서 실행계획을 찾고 → 수행이 필요한 메모리를 할당받는 등의 작업 매번 반복)
  • 위와 같은 과정을 생략하고 빠르게 SQL을 수행하는 방법이 애플리케이션 커서 캐싱입니다.
형태 바인드 변수 사용 세션 커서 캐싱 어플리케이션 커서 캐싱 DBMS 부하
Hard Parsing - - - 매우 높음
Soft Parsing O △ - 낮음
Parsing 無 - - O 매우 낮음 
-- Hard Parsing
call    count cpu  elapsed  disk query current rows 
----- ------ ----- ------- ----- ------- ------ ----- 
Parse      1  ...     
Execute    1  ...     
Fetch      1  ...     
----- ------ ----- -------  ----- ------- ------  ----- 
...
Misses in library cache during parse: 1 

-- Soft Parsing
call    count cpu  elapsed  disk query current rows 
----- ------ ----- ------- ----- ------- ------ ----- 
Parse   5000  ...     
Execute 5000  ...     
Fetch   5000  ...     
----- ------ ----- -------  ----- ------- ------  ----- 
...
Misses in library cache during parse: 1 

-- Application Cursor Caching
call    count cpu  elapsed  disk query current rows 
----- ------ ----- ------- ----- ------- ------ ----- 
Parse  ☆1☆  ...     
Execute 5000  ...     
Fetch   5000  ...     
----- ------ ----- -------  ----- ------- ------  ----- 
...
Misses in library cache during parse: 1

 

  • 개발언어별 어플리케이션 커서 캐싱 구현방법
    • 개발언어마다 구현 방식이 다르므로 이 기능을 활용하려면 API를 살펴보아야 합니다.
    • PL/SQL
      • 기본적으로 Application Cursor Caching 방식으로 동작합니다.
      • ⭐⭐⭐단, Static SQL을 사용할 때만 해당됩니다.
      • Dynamic SQL을 사용하거나 Cursor Variable(=Ref Cursor)을 사용할 때는 커서를 자동으로 캐싱하는 효과가 사라집니다.
-- Pro*C
for(;;) { 
   EXEC ORACLE OPTION (HOLD_CURSOR=YES); 
   EXEC ORACLE OPTION (RELEASE_CURSOR=NO); 
   EXEC SQL INSERT ...... ; // SQL 수행
   EXEC ORACLE OPTION (RELEASE_CURSOR=YES); // 이 구문을 만나면 Cursor Close
} 

-- Java 묵시적 커서 캐싱
-- 옵션: Implict Caching 활성화
public static void cursorCaching(Connection conn, int count)throws Exception{
        // 캐시 사이즈를 1로 지정
        ((OracleConnection)conn).setStatementCacheSize(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();
        }
}

-- PL/SQL의 Application Cursor Caching
-- 위와 같은 옵션을 별도 적용하지 않아도 자동적으로 커서 캐싱(☆단, Static SQL만 해당☆)
SQL> declare 
     i number; 
     begin 
       for i in 1..100 
       loop 
         execute immediate 'insert into t values(' || mod(i, 10) || ')'; 
       end loop; 
     
     commit; 
     end;  

SQL> select count(distinct sql_text) sql_cnt 
  2     , sum(parse_calls) parse_calls 
  3     , sum(executions) executions 
  4  from v$sql 
  5  where sql_text like 'insert into t values%'; 

SQL_CNT   PARSE_CALLS EXECUTIONS 
--------- ----------- ----------
       10         100        100

 

📒 정리하면

  1. SQL 파싱 절차: Syntax 검사(문법 오류) → Semantic 검사(의미 오류) → SQL 실행계획의 Library Cache 존재여부 점검
  2. Soft Parsing 유도: Bind 변수 활용*, Application Cursor Caching으로 Hard Parsing을 최소화
    *미사용 권장상황: Distinct Value가 소수일 때, Long Running 쿼리일 때, cursor_sharing 파라미터 force 또는 similar일 때
    *부작용 극복을 위한 기능: Bind 변수 Peeking 기능 (SQL Server: Parameter Sniffing)
  3. 쿼리 실행계획 공유
    - 동일 SQL 텍스트 유지(공백/대소문자/주석 통일)
    - 문장 의미와 상관없이 Text 그 자체(Literal Query)가 다를 경우 공유할 수 없습니다.
  4. Static SQL
    - String형 변수에 담지 않고 코드 사이에 직접 기술한 SQL문
    - Pro-Compile 단계에서 구문분석, 유효 오브젝트 여부, 오브젝트 권한 등 점검 가능
  5. Dynamic SQL
    - String형 변수에 담아서 기술하는 SQL문
    - Pro Compiler가 내용을 확인하지 않고 DBMS에 전달
  6. Bind 변수의 중요성
    - Static이든 Dynamic SQL이든 옵티마이저는 SQL 문장 자체만 인식할 뿐
    - Library Cache 효율은 Bind 변수 사용여부에 초점을 맞춰야 함
  7. Application Cursor Caching
    1. Hard/Soft Parsing에서 발생하는 비효율적인 과정 생략하고 빠르게 SQL을 수행하는 방법
    2. 개발언어별 어플리케이션 커서 캐싱 구현방법: 각기 상이하므로 API를 살펴보아야 함
      - PL/SQL: 기본적으로 위 방식으로 동작하나 Static SQL을 사용할 때에만 한정됨
      (Dynamic SQL 사용 또는 Cursor Variable 사용 시 커서 자동 캐시 효과 사라짐)
반응형