소프트파싱과 하드파싱
시스템 공유 메모리에서 SQL과 실행계획이 캐싱되는 영역을 ORACLE에서는 라이브러리 캐시라고 합니다.
SQL이 실행되면 제일 먼저 SQL 파서가 문법적 오류 여부를 검사합니다. 문법적 오류가 없다면 의미상 오류 여부를 검사합니다.
오류 검사를 마치면 사용자가 발생한 SQL과 그 실행계획이 라이브러리 캐시에 캐싱됐는지 확인합니다. 만약 캐싱돼 있다면, 최적화 과정을 거치지 않고 곧바로 SQL을 실행합니다. 문법 검사 후 최적화 과정을 거치지 않고 실행되면 소프트 파싱, 라이브러리 캐시에 캐싱돼있지 않아 최적화 과정을 거치고 실행되면 하드 파싱 입니다. 라이브러리 캐시는 해시구조로 관리되기 때문에 SQL별로 해시값에 따라 여러 해시 버킷에 나누어 저장됩니다.
하드파싱
- 테이블 구성, 인덱스 구성, 칼럼 구성에 관한 정보를 조회
- 옵티마이저 관련 파라미터 참조
- 조인순서, 조인 메서드, 테이블 엑세스 방식, 인덱스 스캔방식, 인덱스 결정
테이블 통계, 인덱스 통계, 칼럼 통계와 같은 참조정보는 하드 파싱과정이 아닌 DBA가 설정한 주기에 따라 미리 수집해둡니다. 다이나믹 샘플링이 필요한 상황에서 하드 파싱 과정에 통계정보를 수집하기도하지만 이를 딕셔너리에 저장하지는 않습니다.
SQL 공유 및 재사용의 필요성
최적화 과정은 무거운 작업을 수반합니다. 옵티마이저가 SQL 최적화 과정에 사용하는 정보는 다음과 같습니다.
- 테이블, 칼럼, 인덱스 구조 정보
- 오브젝트 통계 : 테이블 통계, 인덱스 통계, 히스토그램을 포함한 칼럼 통계
- 시스템 통계 : CPU 속도, SINGLE BLOCK I/O 속도, MULTIBLOCK I/O 속도
- 옵티마이저 관련 파라미터
이처럼 많은 변수를 고려하므로 무거운 작업량이 소요됩니다. 무거운 작업을 거쳐 생성한 프로시져를 한 번만 사용하고 버리는 것은 비효율 적입니다. 이에 공유 메모링 캐싱해 사용합니다.
실행계획 공유 조건
SQL 수행절차는 다음과 같습니다.
- 문법적/의미적 오류 검사
- 해시 함수로부터 반환된 해시 값으로 라이브러리 내 해시버킷 탐색
- 해시버킷에 체인으로 연결된 엔트리를 차례로 스켄하면서 캐싱된 SQL 문장 탐색
- 탐색 성공시, 저장된 실행계획으로 바로 실행
- 탐색 실퍠시, 최적화 수행후 해시버킷 체인에 연결 후 실행
라이브러리 캐시에서 SQL을 찾기위해 사용하는 키 값은 SQL 문장 그 자체 입니다. 이러한 특성으로 문장에 공백문자 하나만 추가되더라도 DBMS는 서로 다른 SQL 문장으로 인식하기 때문에 캐싱된 실행계획을 사용하지 못하게 됩니다. 캐시 효율과 직접적인 관련이 있는 패터은 조건절 비교값 변경입니다. 사용자가 입력값을 조건절에 문자열로 붙여가며 매번 다른 SQL을 실행하는 경우에 해당합니다.
바인드 변수의 사용
사용자 로그인을 처리하는 프로그램에서 SQL을 조건절의 비교값이 변경되도록 작성하면 프로시저가 로그인 사용자마다 하나씩 만들어집니다. 쿼리를 프로시저로 만드는 역할은 옵티마이저가 담당합니다.
PROCEDURE LOGIN_TOMMY() {...}
PROCEDURE LOGIN_KARAJAN() {...}
PROCEDURE LOGIN_JAVAKING() {...}
PROCEDURE LOGIN_ORAKING() {...}
로그인 프로시저의 처리방식이 동일하다면 여러 개를 생성하기보다 로그인 ID를 파라미터로 받아 하나의 프로시저로 처리하는 것이 효율적입니다.
PROCEDURE LOGIN(LOGIN_ID IN VARCHAR2){...}
SELECT * FROM CUSTOMER WHERE LOGIN_ID = :LOGIN_ID;
바인드 변수를 사용하면 처음 수행한 세션이 하드파싱을 통해 실행계획을 생성합니다. 이를 라이브러리 캐시에 캐싱해 재사용 가능하게 합니다. 이후 세션들은 캐시에서 실행계획을 얻어 입력 값만 새롭게 바인딩합니다. 이를 통해 파싱 소요시간과 메모리 사용량을 줄일 수 있습니다.
다음과 같은 경우 바인드 변수를 쓰지 않아도 됩니다.
- 배치프로그램이나 DW, OLAP 등 정보계 시스템에서 사용되는 LONG RUNNING 쿼리
위의 경우, 파싱 소요시간이 총 소요시간에서 차지하는 비중이 매우 낮고 수행빈도가 낮아 하드파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 낮습니다. 그러므로 바인드 변수 대신 상수 조건절을 사용하여 옵티마이저가 칼럼 히스토리를 활용 하도록 하는것이 효율적입니다.
- 조건절 칼럼의 값 종류가 적을때
리터럴 SQL 위주로 쿼리를 개발하면 라이브러리 캐시 경합으로 시스템의 정상 가동이 어려워집니다. 이에 대비해 각 DBMS는 조건절 비교 값이 리터럴 상수 일때 자동으로 변수화하는 기능을 제공합니다. 이는 실행계획이 바뀌어 기존에 빠르게 돌아가던 쿼리가 느리게 작동하게 할 수도 있습니다.
- 바인드 변수 사용시 주의사항
변수 바인딩은 최적화 이후 진행됩니다. 나중에 반복 수행될 때 어떤 값이 바인딩 될지 알 수 없기 때문에 옵티마이저는 조건절 칼럼의 데이터 분포가 균일하다는 가정을 세우고 최적화를 수행합니다. 칼럼에 대한 히스토그램 정보가 딕셔너리에 있음에도 이를 활용하지 못합니다. 실제로 칼럼 분포가 균일할때는 이렇게 처리해도 나쁘지 않지만 그렇지 않을때는 바인딩 되는 값에 따라 쿼리 성능이 달라질 수 있습니다. 이때는 바인드 변수를 사용하는 것 보다 상수 값을 사용하는 것이 낫습니다. 그 값에 대한 칼럼 히스토리를 사용해 좀 더 최적의 실행계획을 수립할 가능성이 높기때문입니다.
바인드 변수 부작용을 극복하기 위한 노력
바인드 변수 PEEKING
SQL이 처음 수행될 때 바인드 변수의 값을 참고해서 그 값에 대한 칼럼 분포를 이용해 실행계획을 결정합니다. 이 방식도 부작용이 있습니다. 최초 입력값과 전혀 다른 분포의 값이 입력되면 쿼리 성능이 크게 저하될 수 있습니다.
쿼리 수행 전에 확인하는 실행계획은 바인드 변수 PEEKING이 적용되지 않은 실행계획입니다. 사용자가 쿼리 수행 전에 실행계획을 확인할 때는 변수 값에 바인딩하지 않으므로 값을 PEEKING 할 수 없습니다. 따라서 사용자는 평균 분포에 의한 실행계획을 확인하고 프로그램을 배포하지만 실제 환경에서는 변수 PEEKING을 일으켜 다른 방식으로 실행될 가능성이 있습니다.
다음은 변수 PEEKING 기능을 끄는 쿼리입니다.
alter system set "_optim_peek_user_binds" = FALSE;
쿼리로 테이블을 읽을 때, 선택도가 높은 값이 조건절로 입력될때는 FULL TABLE SCAN이 유리하고, 선택도가 낮은 값이 입력될 때는 인덱스를 경유하는 것이 유리합니다. 이때 쿼리에서 바인딩 되는 값에 따라 실행계획을 분리하는 방안을 고려할 수 있습니다.
SELECT /*+FULL(a)*/ *
FROM 아파트매물 a
WHERE :CITY IN ('서울시', '경기도')
AND 도시 = :CITY
UNION ALL
SELECT /*+INDEX(a IDX01)*/ *
FROM 아파트매물 a
WHERE :CITY not IN ('서울시', '경기도')
AND 도시 = :CITYCURSORS
에플리케이션 커서
세션 커서를 제어하는 클라이언트측 핸들로 라이브러리에서 SQL을 찾는 작업을 생략하고 반복 수행할 수 있는 커서를 말합니다.
에플리케이션 커서 캐싱
같은 SQL을 반복해서 여러 번 수행할 때, 최초 하드 파싱 이후, 소프트 파싱이 일어납니다. 그렇다하더라도 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의 경우 자동으로 커서를 캐싱합니다.
트레이스 결과에 MISSES IN LIBARAY CACHE DURING PARSE 가 있다면 최초 1회 하드파싱이 일어났음을 알 수 있습니다. 트레이스 결과만으로 SQL에 바인드 변수를 사용했는지 알 수 없습니다. 상수 조건으로 반복해도 커서를 공유한다면 결과는 동일합니다. 세션 커서 캐싱의 작동여부는 'session cursor cached' 인자를 통해 확인가능합니다.
공유 커서
라이브러리 캐시에 공유된 SHARED SQL AREA를 의미합니다.
세션 커서
PGA에 할당된 PRIVATE SQL AREA를 의미합니다.
묵시적 커서
DECLARE 문을 생략하고 커서의 OPEN, FETCH, CLOSE도 DBMS가 자동으로 처리하도록 하는 개발 패턴을 의미합니다.
OPEN_CURSORS
OPEN_CURSORS 파라미터는 세션당 OPEN 할 수 있는 커서의 개수를 제한하는 파라미터로 낮게 설정할 수록 캐시 최적화에 유리합니다.
CURSOR_SHARING
cursor_sharing 파라미터를 force로 설정하면, SQL에 사용한 상수 값을 바인드 변수로 강제 변환해 하드파싱에 따른 부하를 줄여줍니다.
변경에 CPU 자원이 소모된다는 단점이 있습니다. 또한 칼럼 히스토리가 도움이 되는 상황에서도 상수 값을 강제로 바인드변수로 처리하여 비효율적인 실행계획이 수립될 수 있습니다. 마지막으로 사용자의도대로 인덱스를 사용하지 못하는 문제가 발생할 수 있습니다.
STATIC SQL과 DYNAMIC SQL
STATIC SQL은 조건절의 값을 STRING 변수에 담지 않고 코드에 직접 기술한 SQL을 말합니다. 반대로, DYNAMIC SQL은 조건절의 값을 STRING 형 변수에 담아 기술하는 SQL을 말합니다.
바인드 변수의 중요성
STATIC SQL과 DYNAMIC SQL는 성능면에서 차이가 없습니다. 라이브러리 캐시 효율을 논할때, 이 차이보다는 바인드변수 사용 여부에 초점을 맞춰야합니다. 바인드 변수를 사용하지않고 리터럴 값을 SQL 문자열에 결합하는 방식으로 개발하면, 반복적인 하드 파싱으로 성능이 크게 저하되고 라이브러리 캐시에 경합이 발생합니다.
