[SQL 튜닝] SQL 공유 및 재사용(라이브러리 캐시, SQL캐싱)

라이브러리 캐시

- SQL파싱, 최적화, 로우 소스 생성을 통해 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱을 해두는 메모리 공간을 라이브러리 캐시라고 함

- 라이브러리 캐시는 SGA의 구성요소

- SGA : System Global Area, 서버 프로세스와 백그라운드 프로세스가 공통으로 접근하는 데이터와 제어 구조를 캐싱하는 메모리 공간

 

소프트/하드 캐싱

- 사용자가 SQL을 전달하면 DBMS는 파싱 후에 해당 SQL이 라이브러리 캐시에 존재하는지를 먼저 확인

- 캐시에 있으면 바로 실행 단계로 : 소프트 캐싱

- 캐시에 없으면 최적화 및 로우 소스 생성 후에 실행 단계로 : 하드 캐싱

 

하드 캐싱

- SQL 최적화 과정은 하드한 작업임

- 옵티마이저의 SQL최적화 작업은 매우 많은 일을 수행함

- JOIN순서, JOIN방법, 테이블 스캔 방법, 인덱스 스캔 방법 등 옵티마이저가 계산해야 하는 연산이 엄청나게 많음

- 하나의 쿼리 수행을 위해서 후보가 되는 무수히 많은 실행 경로를 만들고, 딕셔너리와 통계정보를 가지고 효율성을 판단하는 최적화 작업은 하드 한 작업임

- 데이터베이스의 처리는 대부분 I/O 작업이지만, 하드 파싱은 CPU 자원까지 사용하는 무거운 작업임

- 이렇게 하드 파싱으로 만들어낸 내부 프로시저를 한 번만 사용하고 버리는 것은 비효율적이며, 그래서 라이브러리 캐시를 사용하여 재활용함

 

SQL캐싱

- 함수, 프로시저, 트리거 등은 고유의 이름을 가지며, 컴파일 상태로 딕셔너리에 영구 보관됨

- 실행할 때 라이브러리 캐시에 적재하여 여러 사용자가 공유하여 사용함

- SQL은 이름이 따로 없어서, 전체 SQL텍스트가 이름의 역할을 함

- 처음 SQL을 실행할 때 최적화 과정을 거쳐 생성한 내부 프로시저를 라이브러리 캐시에 적재하여, 그다음부터 재사용함, 캐시 공간이 부족하면 버려졌다가 다음에 다시 실행할 때 똑같은 최적화 과정을 거쳐 캐시에 적재됨

 

SQL영구 저장

- DB2는 SQL도 함수, 프로시저처럼 영구로 저장을 하지만, 다른 DBMS는 그렇게 하지 않음

- SQL은 이름이 따로 없고, 텍스트 자체가 이름이기 때문에 텍스트의 작은 하나라도 변경이 되면 그 순간 다름 객체임

- DBMS에서 수행되는 일회성, 무효화된 SQL까지 모두 영구 저장하면 많은 공간이 필요하고, 그만큼 캐시에서 SQL을 찾는 속도도 느려짐