03 라이브러리 캐시 구조
-
Shared Pool 내에 위치, SQL 공유 커서 및 데이터베이스 오브젝트(테이블, 인덱스 등)에 대한 정보를 관리
-
여기에 저장되는 정보의 단위를 라이브러리 캐시 오브젝트(LCO)라고 부른다.
-
SQL 커서 뿐만 아니라, 컴파일을 거친 프로시저, 함수, 패키지, 트리거 등 PL/SQL 프로그램을 담는 PL/SQL Area도 라이브러리 캐시에 저장한다.
-
실행 가능 LCO: SQL 커서와 PL/SQL 오브젝트처럼 실행 가능한 오브젝트
-
오브젝트 LCO: 실행가능 LCO 외에도 데이터베이스 오브젝트 정보들도 동등하게 하나의 오브젝트로서 관리됨
-
스키마 오브젝트 정보는 데이터 딕셔너리 캐시에도 캐싱되어 있는데, 이를 읽어 라이브러리 캐시에 중복 저장하는 이유 : LCO간 의존성을 관리하려는 데 목적이 있다.
-
LCO 각각에는 자신을 참조하는 다른 실행 가능 LCO(커서, 함수, 프로시저, 패키지 등) 목록을 갖는다.
dba_dependencies
오라클 데이터베이스 객체들 간의 의존성 정보를 확인할 수 있는 뷰.
주로 객체 변경이나 삭제 시 영향을 받는 다른 객체를 파악하는 데 사용된다.
dba_constraints
데이터베이스 테이블에 정의된 제약 조건(constraints) 정보를 확인하는 뷰이다.
테이블에 설정된 PK, FK, Unique, Check, Not Null 제약 조건 -
라이브러리 캐시에 어떤 유형의 오브젝트들이 적재되는지 확인
SQL> select namespace, gets, pins, reloads, invalidations from v$librarycache;
NAMESPACE GETS PINS RELOADS INVALIDATIONS
__________________ ________ __________ __________ ________________
SQL AREA 78237 1079464 3520 4109
TABLE/PROCEDURE 51874 123420 3686 0
BODY 2623 9543 3 0
TRIGGER 1150 1150 0 0
INDEX 10440 9447 756 0
CLUSTER 1923 1989 0 0
PIPE 3 3 0 0
DIRECTORY 16 19 0 0
QUEUE 6 8 0 0라이브러리 캐시에 캐싱되는 정보
-
생성 후 Drop 하기 전까지 데이터베이스에 영구적으로 보관되는 오브젝트(Stored Object) 정보
테이블, 인덱스, 클러스터, 뷰, 트리거, 패키지, 사용자 정의 함수/프로시저
이들 오브젝트는 생성될 때부터 이름을 갖는 것이 특징 -
실행시점에 생성돼서 인스턴스가 떠 있는 동안에만 존재하는 일시적인 오브젝트(Transient Object) 정보
커서, Anonymous PL/SQL 문이 대표적
이름을 따로 지정하지 않으며, 문장을 구성하는 전체 문자열 그대로가 이름 역할을 수행
- 라이브러리 캐시는 데이터 딕셔너리 캐시와 함께 Shared Pool에 할당된 메모리 공간을 사용
- Shared Pool에서 특정 오브젝트 정보 또는 SQL 커서를 위한 Free Chunk를 할당 받으려 할 때, 필요한 래치가 shared pool 래치
- 예전에는 하나의 shared pool 래치로 전체를 관리하였으나, 9i부터 shared pool을 여러 개 Sub pool로 나누어 관리할 수 있게 되면서 래치도 7개까지 사용할 수 있게 됨
ORA-04031 ERROR
SQL> select child#, gets, misses, sleeps, immediate_gets, immediate_misses
2 from v$latch_children
3 where name='shared pool'
4* order by child#;
CHILD# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
1 1424417 2479 45 0 0
2 21 0 0 0 0
3 21 0 0 0 0
4 21 0 0 0 0
5 21 0 0 0 0
6 21 0 0 0 0
7 21 0 0 0 07 rows selected.
- 라이브러리 캐시도 DB 버퍼 캐시처럼 해시 구조로 관리된다.
- 즉 해시 버킷에 LCO 핸들(-> LCO를 식별하고 힙을 포인팅하는 데 사용)이 체인으로 연결되어 있고, 핸들을 통해 LCO에 힙(Heap)을 찾아가는 구조.
- DB 버퍼 캐시와 마찬가지로 해시 함수를 통해 리턴된 해시값을 가지고 해서 버킷을 할당
-
SQL 문장이 100% 동일한대도 커서를 공유하지 못하고, 커서를 별도로 생성해야 할 때가 있는데, 오라클은 그럴 때 다중 Child 커서를 사용한다.
-
DB 버퍼 캐시에서 체인에 연결된 리스트 구조를 보호하기 위해 cache buffers chains 래치를 사용하는 것처럼, 라이브러리 캐시 체인을 탐색하고 변경하려면, 먼저 library cache 래치를 획득해야 한다.
-
이에 대한 경합이 발생할 때, latch: library cache 대기 이벤트가 발생
-
DB 버퍼 캐시에서 버퍼 자체를 보호하려 버퍼 Lock을 사용한 것처럼, LCO를 보호하기 위해 오라클은 라이브러리 캐시 Lock 과 라이브러리 캐시 Pin을 사용한다.
-
그러고 나서 LCO의 실제 내용이 담긴 힙(heap)에서 정보를 읽거나, 변경할 때는 Pin을 걸어 두어야 한다.
-
LCO를 읽고, 쓰고, 실행하는 동안 다른 프로세스에 의해 정보가 변경되거나 캐시에서 밀려나는 것을 방지
-
shared pool 래치, library cache 래치 경합: 소프트/하드 파싱을 동시에 심하게 일으킬 때 발생
-
library cache lock, library cache pin 대기 이벤트: 주로 SQL 수행 도중 DDL을 날릴 때 발생
(트랜잭션 활발한) 주간에 DDL문을 날릴 경우 라이브러리 캐시에 심한 부하를 유발
라이브러리 캐시 최적화를 위한 3가지
커서를 공유할 수 있는 형태로 SQL을 작성
- 바인드 변수를 사용한 형태의 SQL에 대한 반복적인 하드파싱이 일어나지 않도록 해야 함
세션 커서 캐싱 기능을 이용해 라이브러리 캐시에서 SQL 찾는 비용을 줄인다. - Softer soft parse (PGA)
애플리케이션 커서 캐싱을 이용해 Parse Call 발생량을 줄임
04 커서 공유
커서 (Cursor)
공유 커서(shared cursor): 라이브러리 캐시에 공유돼 있는 Shared SQL Area
세션 커서(session cursor): Private SQL Area에 저장된 커서
애플리케이션 커서(application cursor): 세션 커서를 가리키는 핸들
공유 커서
- Java, VB, Pro*C, PL/SQL 등에서 SQL을 수행하면, 서버 프로세스는 해당 SQL이 라이브러리 캐시에 공유돼 있는지를 먼저 확인한다.
-없으면, 최적화 과정을 통해 실행계획을 만들고, 라이브러리 캐시에 공유 - 라이브러리 캐시에 공유되어 있는 Shared SQL Area를 커서라고 부른다.
세션 커서
-
라이브러리 캐시에 공유돼 있는 커서를 실행할 때 우선 PGA 영역에 메모리를 할당: Private SQL Area
Persistent Area, Runtime Area -
Persistent Area: Bind 변수 등을 저장. 실행이 종료된 후 커서가 닫힐 때 해제
-
Runtime Area: select문은 모든 레코드를 fetch 완료하거나, 실행을 취소할 때 해제되지만, insert, update, delete는 실행이 종료됨과 동시에 해제됨
-
커서를 실행하기 위한 이런 준비과정을 “커서를 오픈한다”고 표현
-
PGA에 저장된 커서 정보(즉, 파싱된 SQL문과 문장을 수행하는 데 필요한 기타 정보)를 또한 커서라 부른다.
-
커서 오픈, 즉 라이브러리 캐시에 공유돼 있는 커서를 PGA로 인스턴스화하는 것.
-
Cursor c = new Cursor();
애플리케이션 커서
- PGA에 있는 커서를 핸들링하려면, Java, VB, Pro*C, PL/SQL 같은 클라이언트 애플리케이션에도 리소스를 할당.
커서 공유
- 만약 메모리에서 찾으면 곧바로 실행단계로 넘어가는 것을 소프트 파싱(Soft Parsing)이라고 한다. 커서 공유라고도 한다.
- 세션 커서, 애플리케이션 커서를 다른 프로세스와 공유할 수 없다.
v$sql
parse_calls: 라이브러리 캐시에서 SQL 커서를 찾으려는 요청 횟수
loads: 하드 파싱을 거친 SQL 실행 계획을 라이브러리 캐시에 적재한 횟수
executions: SQL을 수행한 횟수
invalidations: 커서가 무효한 횟수, 커서가 참조하고 있는 오브젝트에 중요한 변화가 일어났음을 의미함.
BEGIN
DBMS_STATS.GATHER_TABLE_STATS
(ownname => USER,
tabname => ‘EMP’,
no_invalidate => FALSE); → LCO를 바로 무효화 하겠다
END;
/
TRUE라면, 5시간 안에 Random한 시간에 무효화됨
커서 무효화가 일괄적으로 발생하면, 대량의 하드 파싱이 동시에 발생하여 CPU 부하와 성능 저하를 일으킬 수 있다.
- 커서가 공유되려면 커서를 식별하는 키 값이 같아야 하는데, 라이브러리 캐시에서 커서를 식별하기 위해 사용되는 키 값은 SQL문장 그 자체다.
sql_id와 sql_fulltext는 1:1로 대응된다.
SQL마다 하나의 Parent 커서를 가지며, Child 커서는 여러개일 수 있다.
실제 수행에 필요한 정보는 Child 커서에 담기므로 적어도 한 개의 Child 커서를 갖는다.
파싱 스키마에 따라 다른 오브젝트를 참조하는 상황에서 Child 커서가 필요해진다.
하나의 SQL문장이 여러 개 Child 커서를 갖게 되는 이유
1. SQL에서 참조하는 오브젝트명이 같지만, SQL을 실행한 사용자에 따라 다른 오브젝트를 가리킬 때
2. 참조 오브젝트가 변경돼 커서가 무효화되면, 이후 그 커서를 처음 사용하려는 세션에 의해 다시 하드파싱 돼야 하는데, 특정 세션이 아직 기존 커서를 사용 중(Pin)일 때
3. 옵티마이저 모드를 비롯해 옵티마이저 관련 파라미터가 다를 때
4. 입력된 바인드 값의 길이가 크게 다를 때
5. NLS 파라미터를 다르게 설정했을 때
6. SQL 트레이스를 활성화했을 때
