SQL 공유 및 재사용

친절한 SQL 튜닝 Study - 조시형 저(DBian)

소프트 파싱 / 하드 파싱

Library Cache(라이브러리 캐시) : PL/SQL, SQL에 대한 분석 정보(Parse Tree) 및 실행 계획을 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간으로서, Shared Pool의 영역 안에 있다.

소프트 파싱이란 SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것을 말한다. 하드 파싱이란 라이브러리 캐시에  SQL 파싱 결과가 없을 때, 최적화 부터 로우 생성 단계까지 모두 거치는 것을 말한다.

옵티마이저가 SQL을 최적화할 때, 많은 정보를 이용하여 최적화 작업을 수행한다. 예를 들어, N개의 테이블을 조인하는 쿼리문을 최적화 할 때, 조인 순서만 해도 N Factorial 이 된다. 여기에다양한 조인 방식이 있으며, 테이블 스캔 범위, 인덱스 사용 유무를 결정해야 하고, 인덱스 스캔 방식도 결정해야 한다. 

따라서 하나의 쿼리를 수행하는 데 있어 옵티마이저는 수많은 실행경로를 도출하고, 짧은 순간에 딕셔너리와 통계정보를 읽어 각각에 대한 효율성을 판단하는 과정은 매우 Hard 하다고 할 수 있다.

참고로 옵티마이저가 최적화 작업을 수행하면서 사용하는 정보는 다음과 같다.

• 테이블, 컬럼, 인덱스 구조에 관한 기본 정보
• 오브젝트 통계 : 테이블 통계, 인덱스 통계, 컬럼 통계
• 시스템 통계 : CPU속도, Single Block I/O 속도, Multiblock I/O 속도 등
• 옵티마이저 관련 파라미터

이름 없는 SQL

 사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때부터 이름이 있지만, SQL은 이름이 따로 없다. 전체 SQL Text가 이름 역할을 한다. 

함수, 프로시저, 트리거, 패키지 등은 딕셔너리에 영구 저장되고, 실행할 때마다 Library Cache에 적재하여 여러 사용자가 공유하여 재사용한다. 

이에 반해 SQL은 딕셔너리에 저장되지도 않고, 처음 실행할 때 최적화 과정을 거쳐 동적으로 생성한 내부 프로시저를 라이브러리 캐시에 적재함으로써 여러 사용자가 공유하면서 재사용한다. 캐시 공간이 부족하면 버려졌다가 다음에 다시 실행할 때 똑같은 최적화 과정을 거쳐 캐시에 적재된다.^[각주:1]

이름 없는 SQL이 문제가 되는 것은 방금 언급한대로, SQL 자체가 이름이기 때문에 조그만 텍스트 하나만 바뀌어도 Dbms에서는 다른 sql 로 인지하기 때문이다. 따라서 아래와 같은 sql은 결과는 동일하지만, 실행할 때 마다, Library cache에 별도로 저장된다.

select * from employees where employee_id = 106;
Select * FROM EMPLOYEES WHERE employee_id = 106;
SELECT * from employees Where employee_id = 106;

실제로 아래와 같은 프로그램으로 sql을 작성했을 경우에, 그 순간 라이브러리 캐시(V$SQL)를 조회해 보면, 아래와 같은 sql로 가득 차 있다.

String sql = "SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = '"+EMP_ID+"'";
Statement st = con.createStatement();
st.executeQuery(sql);




SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = '1001'
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = '1002'
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = '1003'
...
...

이러한 이름 없는 SQL 실행의 문제를 방지하기 위해, 아래와 같이 바인드 변수로 사용해야 한다. 

아래 프로그램의 sql에 대한 하드파싱은 최초 한 번만 일어나고, 여러 고객이 재사용할 수 있다.

String sql = "SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = ? ";
PreparedStatement st = con.prepareStatement(sql);
st.setString(1, emp_id);
st.executeQuery(sql);

1. 조시형, 천절한 SQL 튜닝, p32 [본문으로]