[SQLP] SQL 공유 및 재사용

애플리케이션 캐싱과 Static 및 Dynamic SQL 내용은 생략했다.

1. 소프트 파싱 VS 하드 파싱

• 시스템 공유 메모리에서 SQL 과 실행계획이 캐싱되는 영역을 Oracle 에선 라이브러리 캐시라고 한다.
• 사용자가 SQL을 실행하면 제일 먼저 SQL Parser가 SQL 문장의 문법적 오류가 없는지를(Syntax 검사)한다.
• 문법적 오류가 없으면 의미상 오류(Semantic 검사) 를 검사한다.
  • 예를 들어 존재하지 않거나 권한 없는 객체를 사용했는 지 등을 조사한다.
• 그리고 나서 라이브러리 캐시에 캐싱됬는지 확인한다.
• 소프트 파싱: SQL과 실행계획을 캐시에서 찾아 곧바로 실행 단계로 넘어가는 경우
• 하드 파싱: SQL과 실행계획을 캐시에서 찾지 못해 최적화 과정을 거치고 나서 실행단계로 넘어가는 경우

실행계획 공유 조건

실행계획을 한 번만 쓰고 버리면 너무 리소스 측면에서 비효율적이기 때문에, 해당 쿼리와 관련해서 이전에 이력이 있는 지 조회해서 재사용하여 효율성을 높이는 것이다.

• 순서는 아래와 같다.

1. 문법, 의미 오류 검사
2. 해시 함수로부터 반환된 해시 값으로 라이브러리 캐시 내 해시 버킷을 찾아감
3. 이전에 사용한 SQL 문장을 찾는다
4. 찾으면 함께 저장된 실행계획을 가지고 바로 실행
5. 찾지 못하면 최적화 수행
6. 최적화 거친 후 SQL과 실행계획을 방금 탐색한 해시버킷 체인에 연결한다.
7. 최적화한 실행계획을 가지고 실행한다.

실행 계획을 공유하지 못하는 경우

• 공백 문자 또는 줄 바꿈

SELECT * FROM CUSTOMER;
SELECT *    FROM CUSTOMER;

• 대소문자 구분

SELECT * FROM CUSTOMER;
SELECT * FROM customer;

• 주석

SELECT * FROM CUSTOMER;
SELECT /* 주석 */ * FROM CUSTOMER;

• 테이블 Owner 명시

SELECT * FROM CUSTOMER;
SELECT *    FROM CUSTOMER;

• 옵티마이저 힌트 사용

SELECT * FROM CUSTOMER;
SELECT /*+ all_rows */ * FROM CUSTOMER;

• 조건절 비교 값

해당 케이스처럼 매번 다른 문자열 붙이면서 다른 SQL를 실행되는 것을 리터럴 SQL 이라고 한다.

SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'ASDASD';
SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'ASDDDD';
SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'ASD222';
SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'AS111';

2. 바인드 변수 사용

• 리터럴 SQL 같은 경우 트래픽이 몰리면 성능 문제가 딱 생기기 좋은 케이스이다.
• 아래와 같이 바인드 변수를 사용하면 최초 수행시 하드 파싱을 수행하여 라이브러리 캐싱해두면서 이 후 실행에서는 캐시에서는 실행계획을 얻어 입력 값만 바뀌면서 사용할 수 있다.

SELECT * FROM CUSTOMER WHERE LOGIN_ID = :LOGIN_ID

아래의 실행계획을 보자

call    count   cpu     elapsed disk    query   current rows
Parse   20000   0.16    0.17    0       0       0       0
Execute 20000   0.22    0.42    0       60000   0       20000
Fetch   20000   0.45    0.48    0       0       0       20000
total   60000   1.23    1.07    0       60000   0       20000

Misses in library cache during parse: 1

• Parse Call은 SQL 문장을 캐시에서 찾으려고 시도한 횟수를 말하는데 Execute Call 횟수만큼 Parse Call이 반복된 것을 볼 수 있다.
• 이건 매번 하드 파싱했다는 말이다.

아래 경우는 바인드 변수를 꼭 사용하지 않아도 된다.

• 배치나 DW, OLAP 등 정보계 시스템에서 사용되는 Long Running 쿼리
  • 이들 쿼리는 파싱 소요시간이 쿼리 총 소요시간에서 차지하는 비중이 매우 낮고, 수행빈도도 낮아 하드파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 낮음
• 조건절 컬럼의 Distinct Value가 소수일 때