[ORACLE] Library cache 역할 및 확인

Oracle Library Cache

Library Cache는 Oracle Instance SGA Shared Pool 영역에 존재한다. 존재의 궁극적인 목표는 Oracle의 성능을 높여주기 위함이다. 사용자가 SQL 문장을 질의 했을때 Oracle 내부에서는 Parse - Execute - Fetch 단계를 진행하여 결과를 반환해주는데 Library Cache에 저장되어 있는 정보에 따라 Parse 단계를 거치지 않고 메모리에 저장된 정보를 그대로 사용할 수 있다.

 

 

Library Cache 내부에는 공유 SQL 영역(Shared SQL Area)과 공유 PL/SQL 영역(Shared PL/SQL Area)이 존재하는데, 파스 과정이 완료되면 사용자가 실행한 SQL 문장과 실행계획이 저장되는 공간이다. Shared Pool에 존재하여 같은 인스턴스를 사용하는 사용자들에 의해 지속적으로 공유가 가능하다.

 

 

Library Cache 저장 과정

질의한 쿼리문장은 Parsing 작업을 통해서 Library Cache에 저장됩니다. 사용자가 질의한 문장이 해당 인스턴스를 통해 한번도 Oracle에 요청되지 않았다면 Hard Parse 작업을 진행하여 Library Cache에 저장 합니다. 만약 이미 Library Cache에 문장이 저장되어 있다면 Soft Parse를 진행하여 바로 메모리에서 읽어와 Parse 작업이 필요 없으므로 소요되는 자원을 최소화 시켜줄 수 있어 데이터 처리속도를 높여줄 수 있습니다.

 

Hard Parsing을 통해 Library Cache에 SQL 문장과 실행계획이 저장되는 과정을 단계별로 확인해보겠습니다.

 

 

1. 사용자 질의

SQL> SELECT DEPTNO, DNAME FROM SCOTT.DEPT;

 

위와 같은 질의를 사용자가 했을 때, 가장먼저 Library Cache를 확인하여 동일 한 문장과 실행계획이 있는지 확인합니다. Hard Parse는 해당 문장이 Library Cache에 없어 Syntax와 Semantic check 단계로 넘어갑니다.

 

2. Syntax and Semantic check

Syntax와 Semantic check는 해당 문장의 유효성 검사를 하는 단계입니다.

Syntax : 오타 혹은 문법에 대한 확인입니다.

Semantic : SQL 문장에 정의된 테이블, 칼럼명이 존재하는지 그리고 질의하는 사용자의 권한은 충분한지 등 Data Dictionary를 확인하여 정보의 불일치가 없는지 확인하는 단계입니다.

 

3. 쿼리 변환

Oracle 내부적으로 SQL문장을 더 효율적으로 수행할 수 있도록 문장을 변환하는 작업입니다.

 

4. 실행 계획 생성

어떤 방식으로 실행할 것인지 Optimizer가 실행계획을  생성합니다.

 

 

이러한 단계를 통해 SQL 문장과 실행계획을 생성하여 Library Cache에 저장하여 이후에 같은 인스턴스를 사용하는 사용자들에게 공유됩니다.

 

 

Library Cache 저장 관리

사용자들의 질의가 parsing 작업을 통해 계속 Library Cache에 저장되다 보면 어느순간, 한정되어있는 Library Cache의 크기를 넘어 더 많은 공간이 필요한 경우가 생깁니다.  그래서 Oracle은 LRU(Least Recently Used) 알고리즘을 통해 자주 사용되지 않는 문장을 후 순위로 두고 자주 실행되는 SQL 문장들만 보관하는 방법으로 관리가 됩니다.

 

 

 

Library Cache SQL 확인

SELECT SQL_ID, SQL_TEXT, IS_SHAREABLE FROM V$SQL WHERE SQL_TEXT LIKE '%TESTING%';

V$SQL이라는 view를 통해서 Library Cache에 저장되어 있는 SQL 문장을 확인할 수 있습니다. 위와 같이 현재 질의한 Query에 대해서 저장 정보를 확인할 수 있습니다. TESTING은 검색 편의를 위해서 Query에 힌트를 넣어 작업하기 위해 LIKE 조건을 넣었습니다.

 

SELECT /* TESTING */ DEPTNO, DNAME FROM SCOTT.DEPT;

 

SCOTT 계정에 DEPT 테이블을 조회하는 Query 입니다.

 

SELECT SQL_ID, SQL_TEXT, IS_SHAREABLE FROM V$SQL WHERE SQL_TEXT LIKE '%TESTING%';

DEPT 테이블을 조회한 Query가 Library Cache에 존재하는지 확인하기 위해서 다시 V$SQL View를 조회했습니다.

결과로 위에서 조회한 내용이 확인됩니다.

 

Parsing되어 Library Cache에 저장되는 Query는 공백, 대소문자 등 모든 형태가 같아야 Soft Parse를 진행합니다. 즉, 토씨하나 틀리지 않고 들어오는 질의에 대한 내용만 Parse 단계를 거치지 않고 메모리에 저장된 내용을 그대로 사용할 수 있습니다.

 

Oracle Database가 서비스되는 Application 단에서 3 tier 이상의 형태로 시스템이 구축되어 있으면 동일한 Query가 들어오기 때문에 공백이나 대문자에 대한 문제는 해결할 수 있지만, WHERE절의 찾고자하는 데이터의 조건의 변동이 있을 시에는 새로운 실행계획을 작성하고 Hard Parse를 진행해야합니다. 이러한 문제는 BIND변수를 사용하면 Library Cache에 저장된 하나의 질의와 하나의 실행계획으로 여러 조건의 질의를 Parsing 작업 없이 수행이 가능합니다.

 

 

Library Cache 정리

-Library Cache는 SGA Shared Pool에 존재

-Hard Parse를 통해 Library Cache에 SQL 문장과 실행계획이 저장

-이미 Library Cache에 저장되어 있는 SQL 문장을 실행할 경우, Parse 작업을 거치지 않아 데이터 처리 속도를 높여줄 수 있음

-LRU 알고리즘을 통한 메모리 관리