SQL튜닝 - SQL 공유 및 재사용(소프트파싱,하드파싱, 실행계획공유조건, 바인드변수, 어플리케이션 커서 캐싱)

소프트파싱과 하드파싱

SQL과 실행계획이 캐싱되는 시스템 공유메모리 영역을 라이브러리 캐시라고 합니다. 사용자가 SQL을 실행하면 SQL 파서가 SQL 문장에 문법적 오류와 의미상 오류를 검사합니다. 검사를 마치면 SQL과 그에 대한 실행계획이 라이브러리 캐시에 캐싱돼있는지 확인합니다. 만약 캐싱돼 있다면 최적화 과정을 거치지 않고 곧바로 실행할 수 있습니다.

 

SQL과 실행계획을 캐시에서 찾아 곧바로 실행단계로 넘기는 경우를 소프트파싱, 캐시에서 찾지 못해 최적화 과정을 거친 후 실행단계로 넘어가는 경우를 하드파싱 이라고 합니다. 라이브러리캐시는 해시구조로 관리되기 때문에 SQL 마다 hash 값에 따라 여러 해시 버킷으로 나눠 저장됩니다. SQL을 찾을때는 SQL 문장을 해시 함수에 입력해 반환된 해시 값을 이용해 해시버킷을 탐색합니다.

 

SQL 공유 및 재사용의 필요성

최적화 과정은 무거운 작업을 수반합니다. 옵티마이저가 SQL 최적화를 위해 참조하는 정보는 다음과 같습니다.

 

- 테이블, 칼럼, 인덱스 구조에 대한 기본정보
- 오브젝트 통계 : 테이블 통계, 인덱스 통계, 히스토그램을 포함한 칼럼 통계
- 시스템 통계 : CPU 속도, SINGLE BLOCK I/O 속도, MULTIBLOCK I/O 속도
- 옵티마이저 관련 파라미터

 

쿼리의 실행계획에 대한 후보군을 도출하고, 짧은 순간 딕셔너리와 통계정보를 읽어 각각에 대한 효율성을 판단하는 과정은 무겁습니다. 어려운 작업을 거쳐 생생한 내부 프로시져를 한 번만 사용하고 버린다면 비효율적입니다. 파싱 최적화 과정을 거친 SQL과 실행계획을 여러 사용자가 공유하면서 재사용할 수 있도록 공유 메모리에 캐싱해두는 이유입니다.

 

실행계획 공유 조건

 

SQL은 다음과 같은 실행절차를 거칩니다.

 

- 문법 오류, 의미상 오류 검사
- 라이브러리 캐시내의 해시버킷 탐색
- 찾은 해시버킷에 체인으로 연결된 인트리를 스캔하며 캐싱된 SQL 탐색
- SQL 문장을 찾으면 함께 저자오딘 실행계획을 가지고 실행
- 찾지 못하면 최적화 수행
- 최적화를 거진 SQL과 실행계획을 방금 탐색한 해시버킷 체인에 연결
- 최적화한 실행계획으로 실행

 

중요한 사실은 해시버킷에서 실행계획을 캐싱하기 위한 키가 SQL문장 그 자체라는 점입니다. 그렇기에 문장에 공백문자 하나만 추가되더라도 DBMS는 서로 다른 SQL 문장으로 인식하기 때문에 캐싱된 버전을 사용하지 못하게 됩니다. 이를 방지하기위해 SQL 작성 표준을 정해 이를 준수해야 합니다. 사용자가 입력값을 조건절에 문자열로 붙여가며 매번 다른 SQL을 싱해아믄 경우 큰 부하가 발생할 수 있습니다.

 

바인드 변수 사용

 

SQL을 조건절의 비교값이 변경되는 식으로 작성하면 프로시저가 로그인 사용자마다 옵티마이저에 의해 하나씩 만들어지게 됩니다. 모든 프로시저의 처리 루틴이 동일하다면 여러 개를 생성하기보다 비교값을 파라미터러 받아 하나의 프로시저로 처리하는 것이 마땅합니다. 아래처럼 Parameter Driven 방식으로 SQL을 작성하는 방법이 제공되는데, 그것이 곧 바인드 변수입니다 바인드 변수를 사용하면 하나의 프로시저를 공유하면서 반복 재사용할 수 있습니다.

 

procedure login(login_id in varchar2) {...}
SELECT * FROM CUSTOMER WHERE LOGIN_ID = :LOGIN_ID;

 

바인드 변수를 사용하면 이를 처음 수행한 세션이 하드파싱을 통해 실행계획을 생성합니다. 실행계획을 캐싱해 같은 SQL을 수행하는 다른 세션이 재사용할 수 있습니다. 이후 세션은 캐시에서 실행계획을 얻어 입력 값만 새로 바인딩해 쿼리를 실행합니다.

 

바인드 변수를 사용하면 SQL과 실행계획 여려 개를 캐싱하지 않고 재사용하므로 파싱 소요시간과 메모리 사용량을 줄일 수 있습니다. 궁극적으로 시스템 전반

의 CPU와 메모리 사용률을 낮춥니다.

 

바인드 변수를 사용하지 않아도 되는 경우

배치프로그램, DW, OLAP 등 정보계 시스템에서 사용되는 LONG RUNNING 쿼리

 

파싱이 전체 소요시간에서 차지하는 비중이 매우 낮고, 수행빈도도 낮아 하드파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 낮습니다. 이때 상수 조건절을 사용함으로써 옵티마이저가 칼럼 히스토리를 활용할 수 있도록 하는 것이 더 유리합니다.

 

조건절 칼럼의 값 종류가 소수일 때

 

리터럴 SQL 위주로 개발하면 라이브러리 캐시 경합 많이 발생합니다. 이에 각 DBMS는 조건절 비교 값이 리터럴 상수일 때 이를 자동으로 변수화하는 기능을 제공합니다. 리터럴 쿼리로 인한 파싱부하를 줄이는데 도움이 되지만 실행계획이 바뀌어 기존에 최적화 상태로 수행되던 쿼리가 느려질 수 있습니다.

 

바인드 변수 사용시 주의사항

 

바인딩 변수에 값을 바인딩 하는 작업은 최적화 이후 이뤄집니다. 나중에 반복 수행될 때 어떤 값이 입력될지 알 수 없기에 옵티마이저는 조건절 칼럼의 데이터 분포가 균일하다는 가정을 세우고 최적화를 수행합니다. 칼럼에 대한 히스토그램이 딕셔너리에 저장돼있어도 이를 활용하지 못합니다. 실제 칼럼분포가 균일하지 못한 상황에서는 오히려 성능이 떨어질 수 있습니다. 이럴때는 바인드 변수를 사용하는 것 보다 상수 값을 사용하는 것이 낫습니다.

 

바인드 변수의 부작용을 극복하기 위한 노력 : BIND PEEKING

 

SQL이 처음 수행될때 바인드 변수에 할당된 값에 대한 칼럼 분포를 이용해 실행계획을 결정합니다. 이는 사실 매우 위험한 기능으로, 처음 실행될 때 입력된 값과 전혀 다른 분포를 갖는 값이 나중에 입력되면 성능이 느려지는 현상이 발생할 수 있습니다.

 

쿼리 수행 전에 확인하는 실행계획은 바인드 변수 PEEKING 기능이 적용되지 않은 실행계획임을 기억해야합니다. 사용자는 평균분포에 의한 실행계획을 참조하여 프로그램을 배포하지만 BIND PEEKING으로 인해 다른 방식으로 수행될 수 있습니다.

 

BIND PEEKING을 방지하기 위해 기능을 끄거나 바인딩 되는 값에 따라 쿼리를 분리하는 방안을 고려할 수 있습니다.

 

alter system set "_optim_peek_user_binds" = FALSE;

SELECT * FROM 아파트매물 WHERE 도시 = :CITY;

SELECT /*+FULL(a)*/ *
FROM 아파트매물 a
WHERE :CITY IN ('서울시', '경기도')
AND 도시 = :CITY
UNION ALL
SELECT /*+INDEX(a IDX01)*/ *
FROM 아파트매물 a
WHERE :CITY not IN ('서울시', '경기도')
AND 도시 = :CITY

 

어플리케이션 커서 캐싱

 

소프트 파싱을 한다고 하더라도 SQL의 문장의 문법적, 의미적 오류가 없는지 확인하고 해시함수로부터 반환된 해시값을 이용해 실행계획을 찾고, 수행헤 필요한 메모리를 할당하는 작업을 반복하는 것은 비효율 적입니다. 이를 생략하고 빠르게 SQL을 수행하는 방법이 '에플리케이션 커서 캐싱' 입니다.

 

일반적인 방식으로 SQL을 반복수행하면 PARSE CALL 횟수가 EXECUTE CALL 횟수와 같게 나타납니다. 반면 에플리케이션 캐싱이 동작한 트레이스에서는 PARSE CALL이 한 번만 발생하고 이후에는 발생하지 않습니다.

 

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 ?, ?, ?, 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();
  }
}

// 아래 처럼 Statement를 닫지 않고 재사용해도 같은 효과를 얻을 수 있다.
public static void CursorHolding(Connection conn, int count) throws Exception {

 PreparedStatement stmt = conn.prepareStatement(
      "SELECT ?, ?, ?, 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();
}

 

PL/SQL 에서는 위와 같은 옵션을 별도로 적용하지 않더라도 자동적으로 커서를 캐싱합니다. 단 STATIC SQL을 사용할 때만 해당됩니다. DYNAMIC SQL을 사용하거나 CURSOR VARIABLE을 사용할 때는 커서를 자동으로 캐싱하는 효과가 사라집니다.

 

Static SQL과 Dynamic SQL

Static SQL은 조건값을 변수에 담지 않고 코드 상에 직접 기술한 SQL을 말합니다. Dynamic SQL은 조건 값을 STRING 변수에 담아 기술하는 SQL을 말합니다. 조건에 따라 문을 동적으로 바꿀 수 있고, 런타임 시에 사용자로부터 SQL문 일부 또는 전부를 입력받아 실행할 수 있습니다. 따라서 PRECOMPILE 시 SYNTAX, SEMANTIC 체크가 불가능하므로 그대로 DBMS에 전달됩니다.

 

바인드 변수의 중요성

 

바인드 변수만 잘 사용하면 라이브러리 캐시 효율을 떨어뜨리지 않습니다. 바인드 변수를 사용하지 않고 LITERAL 값을 SQL 문자열에 결합하는 방식을 사용할때 하드파싱으로 인한 캐싱부하가 발생하기 쉽습니다.