SQL 공유 및 재사용

◎ 라이브러리 캐시(Library Cache)

SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간으로 SGA(System Global Area)의 구성요소다.

SGA는 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어 구조를 캐싱(명령어와 데이터를 캐시 기억 장치 또는 디스크 캐시에 일시적으로 저장하는 것)하는 메모리 공간이다.

 

 

◎ 소프트 파싱 vs 하드 파싱

사용자가 SQL문을 전달시 DBMS는 SQL을 파싱하고 해당 SQL이 라이브러리 캐시에 존재하는지 확인한다.

 

1. 소프트 파싱

사용자 > SQL 파싱 > 캐시에 존재 > 실행

 

2. 하드 파싱

사용자 > SQL 파싱 > 캐시에 존재 X > 최적화 > 로우 소스 생성 > 실행

 

몇 안 되는 CPU를 많이 소비하는 작업 중 하나.

한번만 사용하고 버리는 건 비효율적이기 때문에 라이브러리 캐시가 필요한 것

 

 

◎ 이름없는 SQL 문제

사용자 정의 함수/프로시저, 트리거, 패키지 등은 이름을 갖으며 컴파일한 상태로 딕셔너리에 저장돼 영구적으로 보관된다.

그러나 SQL은 이름이 따로 없으며 전체 텍스트가 이름 역할을 한다. 처음 실행시 최적화 과정을 거쳐 동적으로 생성한 내부 프로시저를 라이브러리 캐시에 적재함으로써 여러 사용자가 공유하며 재사용한다.

캐시 공간이 부족하다면 버려졌다가 다음 실행시 똑같은 최적화 과정을 거쳐 캐시에 적재된다.

 

사용자 정의 함수/프로시저는 내용을 수정해도 이름이 변하지 않으므로 같은 프로그램이 무한히 생성되지 않는다.

SQL은 그 자체가  이름이기 때문에 텍스트 중 작은 부분이라도 수정되면 그 순간 다른 객체가 새로 탄생하는 구조이다.

 

따라서 SQL이 변하면 SQL ID도 변하게 된다.

특히 DBMS에서 수행되는 SQL이 모두 완성된 SQL은 아니며, 특히 개발 과정에서 수시로 변경된다. 일회성도 많다.

 

일회성 또는 무효화된 SQL까지 모두 저장하려면 많은 공간이 필요하고 그만큼 SQL 탐색 속도도 느려진다.

그렇기에 오라클, SQL Server 같은 DBMS가 SQL을 영구 저장하지 않는 쪽을 선택한 이유다.

 

 

◎ 공유 가능 SQL

SQL을 찾기 위한 키 값은 SQL 문 그 자체이다.

아래의 SQL은 의미적으로 모두 같으나, 실행할 때 각각 최적화를 진행하고 라이브러리 캐시에서 별도 공간을 사용한다.

SELECT * FROM EMP WHERE EMPNO = 7900;
SELECT * FROM EMP where EMPNO = 7900;
SELECT /* COMMENT */ * FROM EMP WHERE EMPNO = 7900;

 

 

String SQLStmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = '" + login_id + "'"

위와 같은 모듈을 쓰는 쇼핑몰 행사로 서버에 과한 접속이 발생할 경우.
DBMS에 발생하는 부하는 대개 과도한 I/O가 원인인데, 이날은 I/O가 거의 발생하지 않음에도 불구하고 CPU 사용률이 급격히 올라가는데 이는 각 고객에 대해 동시다발적으로 발생하는 SQL 하드파싱 때문이다.
그 순간 라이브러리 캐시(V$SQL)를 조회해 보면, 아래와 같은 SQL로 가득 차 있다.

SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'ORAKING'
SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'TOMMY'
SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'JAKE'

로그인 프로그램을 이렇게 작성하면 고객이 로그인할 때마다 아래와 같이 DBMS 내부 프로시저를 하나씩 만들어 라이브러리 캐시를 적재하는 셈이다.

CREATE PROCEDURE LOGIN_ORAKING() {.....}
CREATE PROCEDURE LOGIN_TOMMY() {.....}
CREATE PROCEDURE LOGIN_JAKE() {.....}

위 프로시저의 내부 처리 루틴은 모두 같다.

그렇다면 프로시저를 여러 개 생성할 것이 아니라 아래처럼 로그인ID를 파라미터로 받는 프로시저 하나를 공유하면서 재사용하는 것이 마땅하다.

create procedure LOGIN (login_id in varchar2) {....}

이처럼 파라미터 Driven 방식으로 SQL을 작성하는 방법이 제공되는데, 바인드 변수가 바로 그것이다.

 

 

String SQLStmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = ?"

로그인 프로그램을 위와 같이 수정하고 라이브러리 캐시를 조회에 보면, 로인과 관련한 SQL은 아래 하나만 발견된다.

 

SELECT * FROM CUSTOMER WHERE LOGIN_ID = :1

이 SQL에 대한 하드파싱은 최초 한 번만 일어나고, 캐싱된 SQL을 100만 고객이 공유하면서 재사용한다.