[SQLP] 1-3. SQL 공유 및 재사용

🧷 SQL 공유 및 재사용

🖇️ 소프트 파싱 VS 하드 파싱

SGA(System Global Area)는 서버 프로세스와 백그라운드 프로세스가 공통적으로 액세스 하는 데이터와 제어구조를 캐싱하는 메모리 공간이다. SGA 구성요소 중에 SQL파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 공간을  라이브러리 캐시(Library Cache) 이라고 한다.

 

 

• 소프트 파싱(Soft Parsing) : SQL 파싱 후 라이브러리 캐시에 해당 SQL이 존재하면 바로 실행한다.
• 하드 파싱(Hard Parsing) : 라이브러리 캐시에 SQL이 존재하지 않으면 최적화 및 로우 생성 과정을 거친다.

하드 파싱이 느린 이유 

 

 하드 파싱(Hard Parsing)은 옵티마이저가 최적화하는 동안 많은 연산을 수행하여 실행 경로를 결정해야 하므로 CPU 소모가 크다. 이를 위해 다음 정보를 가지고 고려한다.

 

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

이 과정을 최적화하기 위해 데이터베이스는 라이브러리 캐시에서 내부 프로시저를 재사용하여 효율성을 높인다.

 

🖇️ 라이브러리 캐시

Shared Pool 내에 위치하고, SQL 공유 커서, PL/SQL 오브젝트(컴파일을 거친 프로시저, 함수 등) 및 데이터베이스 오브젝트(테이블, 인덱스 등)에 대한 정보를 캐싱하는 메모리 공간이다. 코드 캐시라고도 부른다.

1. 캐시 정보의 종류와 분류 기준

(1) 라이브러리 캐시 오브젝트(LCO)

LCO : 라이브러리 캐시 내 저장되는 정보 단위

• 실행가능 LCO : SQL 커서와 PL/SQL 오브젝트처럼 실행 가능한 오브젝트
• 오브젝트 LCO : 참조하는 테이블, 인덱스, 클러스터 같은 데이터베이스 정보들도 하나의 오브젝트로서 라이브러리 캐시에 저장
• LCO 각각에는 자신을 참조하는 다른 실행가능 LCO 목록을 갖는다.
  • 스키마 오브젝트 정보의 경우, 딕셔너리 캐시에도 캐싱되어 있음에도 LCO 간 의존성을 관리하기 위해 중복 저장 관리한다.

(2) 보관 기간

• Stored Object : 생성 후 Drop 하기 전까지 데이터 베이스에 영구적으로 보관되는 오브젝트 정보
  • 예 : 테이블, 인덱스, 클러스터, 뷰, 트리거, 패키지, 사용자 정의 함수/프로시저 등
  • 생성될 때부터 이름을 가지는 것이 특징
• Transient Object : 실행시점에 생성돼서 인스턴스가 떠있는 동안에만 존재하는 일시적인 오브젝트 정보 (ex, Anonymous)
  • 예 : 커서, Anonymous PL/SQL문
  • 이름을 따로 지정하지 않으며, 문장을 구성하는 전체 문자열이 이름 역할을 한다.

2. 캐싱 관리 방법

(1) LRU 알고리즘

라이브러리 캐시는 Shared Pool 내에 위치하기 때문에 LRU 알고리즘을 통해 관리되고 있다. (Shared Pool도 DB 버퍼 캐시처럼 LRU 알고리즘 이용) 사용빈도가 낮은 SQL은 캐시에서 밀어냄으로써 새로운 SQL을 캐싱할 수 있도록 공간을 확보한다.

(2) 해시구조

라이브러리 캐시도 DB 버퍼 캐시처럼 해시 구조로 관리된다. 즉, 해시 버킷에 LCO 핸들(LOC을 식별하고 힙을 포인트 하는 데 사용)이 체인으로 연결돼 있고, 핸들을 통해 LCO Heap을 찾아가는 구조다. 해시 함수를 통해 리턴된 해시 값을 가지고 해시 버킷을 할당한다.

(3) 커서

오브젝트 LCO(테이블 정보)와 실행가능 LCO(SQL 커서)가 라이브러리 캐시에 함께 적재되어 있다. 커서는 Parent 커서 밑에 Child 커서가 연결되는 구조를 갖는다. SQL이 동일해도 커서를 공유하지 못하고 별도로 생성해야 할 때가 있는데, 그럴 때 다중 Child 커서를 사용한다.

3. Latch

Latch : 메모리 내 자원에 대해 동시 접근 할 때, 조율하고 데이터의 일관성을 유지하도록 도와주는 일종의 잠금장치

(1) Shared Pool 래치

Shared Pool에서 특정 오브젝트 정보 또는 SQL 커서를 위한 Free Chunk를 할당받으려 할 때 필요한 래치가 Shared Pool 래치이다. 이전에는 래치 1개로 관리하였지만, 9i부터는 7개까지 사용할 수 있다. 만약, 동시 사용자가 순간적으로 과도한 하드 파싱 부하를 일으킨다면 Shared Pool 래치에 대한 경합현상이 발생할 수 있다.

(2) Library Cache 래치

DB 버퍼 캐시에서 체인에 연결된 리스트 구조를 보호하기 위해 Cache Buffers Chains 래치를 사용하는 것처럼 라이브러리 캐시 체인을 탐색하고 변경하려면 먼저 Library Cache 래치를 획득해야 한다. 이에 대한 경합이 발생할 때, latch : library cache 대기 이벤트가 발생한다.

 

Library Cache가 많지 않기 때문에, 하드 파싱뿐만 아니라 소프트 파싱이 많이 발생해도 래치에 대한 경합이 증가하게 된다.

(3) 라이브러리 캐시 Lock / Pin

DB 버퍼 캐시에서 버퍼 자체를 보호하려고 버퍼 Lock을 사용하는 것처럼 LCO를 보호하기 위해 라이브러리 캐시 Lock / Pin을 사용한다. LCO에 접근할 때는 먼저 핸들에 대한 Lock을 획득해야 한다. 그러고 나서, LCO의 실제내용이 담긴 힙에서 정보를 읽거나 변경할 때는 Pin을 걸어두어야 한다. 즉, 라이브러리 캐시 힙을 Pin 하려면 먼저 라이브러리 캐시 Lock을 획득해야 한다.

 

Lock을 통해 LCO 핸들을 보호하고, Pin은 LCO에 실제로 담긴 Heap을 보호한다. 이를 통해 LCO을 읽고, 쓰고, 실행하는 동안 다른 프로세스에 의해 정보가 변경되거나 캐시에 밀려나는 것을 방지한다.

(4) 경합현상

• Shared Pool 래치 / Library Catch 래치 경합은 소프트/하드 파싱을 동시에 심하게 일으킬 때 발생한다.
• Library Cache Lock / Pin 대기 이벤트는 주로 SQL 수행 도중 DDL을 날릴 때 발생한다.

4. 라이브러리 캐시 최적화

1. 커서를 공유할 수 있는 형태로 작성
   • 특히, 바인드 변수를 사용해 같은 형태의 SQL에 대한 반복적인 하드 파싱이 일어나지 않도록 처리
2. 세션 커서 캐싱 기능을 이용해 라이브러리 캐시에서 SQL 찾는 비용 감소 처리
3. 애플리케이션 커서 캐싱을 이용해 Parse Call 발생량을 감소 처리

 

🖇️ 커서 공유

1. Cursor란?

공유 커서 (Shared cursor) : 라이브러리 캐시에 공유돼 있는 Shared SQL Area

 

JAVA, PL/SQL 등에서 SQL을 수행하면, 서버 프로세스는 해당 SQL이 라이브러리 캐시에 공유돼 있는지를 먼저 확인한다.
 없으면, 최적화 과정을 통해 실행계획을 만들고, 라이브러리 캐시에 공유한다. 그렇게 라이브러리 캐시에 공유돼 있는 Shared SQL Area를 '커서'라고 부른다.

세션 커서 (Session cursor) : Private SQL Area에 저장된 커서

 

Private SQL Area은 라이브러리 캐시에 공유돼 있는 커서를 실행할 때, PGA 영역에 할당되는 메모리를 말한다. Shared SQL Area를 읽어 커서를 실행하는 데 필요한 정보들을 Private SQL Area에 담고, 공유 커서를 가리키는 포인터를 유지할 뿐만 아니라 커서의 상태 정보(open, bound, execute, close 등)도 관리한다. 커서를 오픈하면 라이브러리 캐시에 공유돼 있는 커서를 인스턴스화함으로써 PGA에 커서를 위한 메모리 공간을 할당하고, 실제 데이터 추출을 시작할 수 있도록 준비한다.

애플리케이션 커서 (Application Cursor) : 세션 커서를 가리키는 핸들

 

PGA에 있는 커서를 핸들링하려면 JAVA, PL/SQ 같은 클라이언트 애플리케이션에도 리소스를 할당해야 하는데 이를 커서라고 한다.

2. 커서 공유

세션 커서, 애플리케이션 커서를 다른 프로세스와 공유할 수 없기 때문에 "커서를 공유한다."의 커서는 라이브러리 캐시의 공유 커서를 의미한다.

 

라이브러리 캐시에 공유돼 있는 커서의 수행 통계는 V$SQL을 통해 조회할 수 있다.

• parse_calls : 라이브러리 캐시에서 SQL 커서를 찾으려는 요청 횟수
• loads : 하드파싱을 거친 SQL 실행계획을 라이브러리 캐시에 적재한 횟수
• executions : SQL을 수행한 횟수
• invalidations : 커서가 무효화된 횟수
  위의 정보를 통해 해당 SQL이 몇 번 하드파싱되었는지, 소프트 파싱되었는지 알 수 있다. 즉, 공유된 하나의 커서를 몇 번 재사용했는지 확인할 수 있다.

3. 공유 불가

(1) Child cursor 공유 불가

SQL 문장이 100% 동일한대도 SQL 커서를 공유하지 못할 경우, 별도의 Child 커서를 생성한다. SQL 커서처럼 실행시점에 생성돼서 인스턴스가 떠있는 동안에만 존재하는 Transient Object는 이름을 따로 지정하지 않고 문장을 구성하는 전체 문자열 그대로가 LCO를 식별하는 이름 역할을 한다. 그래서 전체 문자열은 같지만 다른 방식으로 실행해야 하거나, 파싱 스키마에 따라 다른 오브젝트를 참조하는 상황에서 Child 커서가 필요하게 된다.

 

즉, SQL마다 하나의 Parent 커서를 가지며, Child 커서는 여러 개 일 수 있다(다중 Child 커서). 그러나 version count 수치가 높은 SQL 일수록 커서를 탐색하는 데 더 많은 시간을 소비하므로 library cache 래치에 대한 경합 발생 가능성을 증가시킨다. 많은 SQL 커서를 갖는 구조일수록 라이브러리 캐시 효율이 나빠진다.

하나의 SQL 문장이 여러 개 Child 커서를 갖게 되는 이유 

• SQL에서 참조하는 오브젝트 명이 같지만, SQL을 실행한 사용자에 따라 다른 오브젝트를 가리킬 때
• 참조 오브젝트가 변경돼 커서가 무효화되면 이후 그 커서를 처음 사용하려는 세션에 의해 다시 하드파싱돼야 하는데, 특정 세션이 아직 기존 커서를 사용 중(Pin) 일 때
• 옵티마이저 모드를 비롯해 옵티마이저 관련 파라미터가 다를 때
• 입력된 바인드 값의 길이가 크게 다를 때
• NLS 파라미터를 다르게 설정했을 때
• SQL 트레이스를 활성화했을 때

(2) Parent cursor 공유 불가

의미적으로 같고 실행환경이 같은데도 커서를 공유하지 못해 Parent 커서 자체가 여러 개 생성되는 경우 

1. 공백 문자 또는 줄 바꿈
2. 대소문자 구분
3. 테이블 Owner 명시
4. 주석
5. 옵티마이저 Hint
6. 조건절 비교 값 (바인드 변수를 사용하지 않고, Literal 값으로 문자열을 대체하는 경우)

다른 경우는 라이브러리 캐시 효율이 크게 나빠지지 않지만, 6번의 경우 좋은 성능을 보장할 수 없다. 이를 해결하기 위해 바인드 변수를 사용해서 처리한다.

 

 

---

Reference

• 친절한 SQL 튜닝
• 오라클 성능고도화 원리와 해법 1