공유 풀 내용
공유 풀은 2개의 메인 구조와  제3의 구조를 포함하고 있습니다:

공유 글로벌 영역 튜닝
데이터 딕셔너리 캐쉬 또는 라이브러리 캐쉬 상에서의 캐쉬 실패(cache miss)는 데이터베이스 버퍼 캐쉬 상에서의 실패보다 훨씬 비용이 많이 듭니다. 캐쉬 실패 시, 공유 풀 튜닝이 우선됩니다.
공유 풀을 튜닝할 때, 오라클 알고리즘이 메모리에서 라이브러리 캐쉬 데이터 보다 딕셔너리 데이터를 좀 더 오래 유지하고 있기 때문에, DBA는 주로 라이브러리 캐쉬에 관심이 있을 것입니다. 따라서, 수용할 수 있는 캐쉬 적중율로 라이브러리 캐쉬를 튜닝함으로써, 데이터 딕셔너리 캐쉬 적중률 또한 수용할 수 있게 됩니다.공유 풀이 너무 작을 경우, 서버는 자원을 전용하여 한정된 공간을 관리해야 합니다. 이때, CPU 자원의 소모로 경합이 발생합니다.

공유 풀의 크기
공유 풀의 크기를 init.ora 파라미터 SHARED_POOL_SIZE로 설정합니다. 디폴트는 3,500,000 바이트입니다.

라이브러리 캐쉬
라이브러리 캐쉬는 공유 SQL과 PL/SQL 영역(PL/SQL 블록과 SQL 문의 완전 구문분석된 표시 또는 컴파일된 표시)을 포함합니다:

PL/SQL 블록에는 다음이 포함되어 있습니다:

데이터 딕셔너리 캐쉬
데이터 딕셔너리 캐쉬는 메모리 내에 딕셔너리 객체의 정의를 보관합니다

SQL 과 PL/SQL 저장장소
오라클 서버는 SQL문과 PL/SQL 블록을 저장하기 위해 라이브러리 캐쉬를 사용합니다. 캐쉬를 관리하는 데에는 LRU(Least Recently Used, 최근최저사용빈도) 알고리즘이 사용됩니다.

사용자가 이미 캐쉬에 존재하는 문장을 실행시킬 경우, 오라클 서버는 문장의 구문을 재분석하지 않고 캐쉬에 저장된 버전을 사용할 수 있습니다.

문장이 이미 캐쉬에 저장되어 있는가를 찾기 위하여, 오라클 서버는 다음을 수행합니다:

  1. ASCII 텍스트의 수치로 문장을 줄임
  2. 이 수치에 해시 함수 사용

첫번째 튜닝 목표
구문분석을 최소로 유지하여 실패를 감소시킵니다:

두 번째 튜닝 목표
다음을 통해 단편화를 피하십시오:

3개의 키워드
V$LIBRARYCACHE 내의 각 행은 라이브러리 캐쉬에서 유지되는 한 유형의 항목에 대한 통계를 포함합니다. 각 행에 의해 설명된 항목은 NAMESPACE 열의 값에 의해 구별됩니다. 다음의 NAMESPACE 값을 가진 테이블의 행은 SQL 문과 PL/SQL 블록에 대한 라이브러리 캐쉬 활동을 반영합니다:

SQL AREA, TABLE/PROCEDURE, BODY, TRIGGER

기타의 NAMESPACE 값을 가진 행은 오라클이 종속성 유지를 위해 사용하는 객체 정의에 대한 라이브러리 캐쉬 활동을 반영합니다:

INDEX, CLUSTER, OBJECT, PIPE

NAMESPACE와 관련된 3개의 키워드는 다음과 같습니다.

뷰의 설명

커서가 공유되고 있는가?

주의: OEM 진단 팩 애플리케이션:
Performance Manager -> Memory -> 라이브러리 캐쉬 상세 내역(Library Cache Details)

주의: OEM 진단 팩 애플리케이션:
Performance Manager -> Memory -> SQL 영역(SQL AREA)

재로드 대 핀 비율 계산 방법

주의: OEM 진단 팩 애플리케이션:
Performance Manager -> Memory -> Library Cache Hit %

무효가 발생할 때
스키마 객체가 SQL 문에서 참조되고 그 객체가 나중에 어떠한 방법으로든 수정될 경우, 공유 SQL 영역은 무효로 되고(무효로 표시됨) 문장은 다음에 실행될 때 구문이 재분석되어, 재로드됩니다.

예: 테이블, 시퀀스, 동의어, 또는 뷰는 재생성되거나 변경되거나 삭제되어, 아니면, 프로시저 또는 패키지 사양이 재컴파일되면, 모든 종속 공유 SQL 영역이 무효로 됩니다.

주의: OEM 진단 팩 애플리케이션:
Performance Manager -> Memory -> Library Cache Details

애플리케이션 테스트
기존 애플리케이션에 대해, 테스트를 설정하고 동적인 뷰를 사용하여, 사용되는 메모리 양을 찾아야 합니다. SHARED_POOL_SIZE를 매우 큰 값으로 설정(필요하면 다른 구조를 희생해서라도)한 다음, 애플리케이션을 실행시키십시오.

사용된 공유가능한 메모리 계산

애플리케이션은 위의 수를 총합한 것 만큼의 라이브러리 캐쉬 이외에, 동적인 SQL을 위한 약간의 여유가 있는 것이 이상적입니다.


공유 풀에 공간을 예약하는 이유
DBA는 PL/SQL 컴파일과 트리거 컴파일 같은 작업이 수행되는 동안 대용량 메모리 할당을 만족시키기 위해 공유 풀 내에 메모리를 예약할 수 있습니다. 크기가 더 작은 객체들은 예약된 목록을  단편화하지 않기 때문에, 예약된 목록이 큰 연속 메모리 부분을 차지하도록 보장하는데 도움이 됩니다. 예약 목록으로부터 할당된 메모리가 비워지면, 예약 목록으로 반환됩니다.

초기화 파라미터
예약 목록으로부터 할당될 수 있는 객체의 최소 크기는 물론 예약 목록의 크기는 다음 2개의 초기화 파라미터에 의해 제어됩니다:

  • SHARED_POOL_RESERVED_SIZE: 대규모 할당을 위해 예약된 SHARED_POOL_SIZE의 양 제어 (초기 값을 SHARED_POOL_SIZE의 10%로 설정).
  • SHARED_POOL_RESERVED_MIN_ALLOC: 예약된 메모리에 대한 할당 제어 (예약 목록을 생성하기 위해, SHARED_POOL_RESERVED_SIZE는 SHARED_POOL_RESERVED_MIN_ALLOC 보다 커야 합니다. 충분한 크기의 메모리 부분이 공유 풀의 예약 목록에서 발견되지 않을 경우, SHARED_POOL_RESERVED_MIN_ALLOC 보다 큰 할당 만이 예약 목록의 공간을 할당할 수 있습니다. 대부분의 경우, 디폴트 값이 적당합니다.)

V$SHARED_POOL_RESERVED 뷰
이 뷰는 공유 풀 내의 예약 풀과 공간을 튜닝하는데 도움이 됩니다.
이 뷰의 행들은 SHARED_POOL_RESERVED_SIZE 파라미터가 유효한 값으로 설정된 경우에만 유효합니다.
   SQL> desc V$SHARED_POOL_RESERVED
   Name                        Null?    Type
   --------------------------- -------  -------
   FREE_SPACE                           NUMBER
   AVG_FREE_SIZE                        NUMBER
   FREE_COUNT                           NUMBER
   MAX_FREE_SIZE                        NUMBER
   USED_SPACE                           NUMBER
   AVG_USED_SPACE                       NUMBER
   USED_COUNT                           NUMBER
   MAX_USED_SIZE                        NUMBER
   REQUESTS                             NUMBER
   REQUEST_MISSES                       NUMBER
   LAST_MISS_SIZE                       NUMBER     
   MAX_MISS_SIZE                        NUMBER

여기에서:

FREE_SPACE

예약 목록 내의 총 여유 공간

AVG_FREE_SIZE

예약 목록 상에서 여유 메모리의 평균 크기

MAX_FREE_SIZE

예약 목록 상에서 메모리의 가장 큰 여유 부분의 크기

REQUEST_MISSES

주어진 목록에 요청을 수행하기 위한 여유 메모리 부분이 없을 경우, LRU 목록에서 객체를 삭제하기 시작한 횟수

 

뷰 내의 다음 열들에는 파라미터가 설정되지 않은 경우에도 유효한 값들이 포함되어 있습니다:

  • REQUEST_FAILURES
  • LAST_FAILURE_SIZE
  • ABORTED_REQUEST_THRESHOLD
  • ABORTED_REQUESTS
  • LAST_ABORTED_SIZE

여기에서:

REQUEST_FAILURES

요청을 수행하기 위한 어떠한 메모리도 발견되지 않는 경우의 횟수

LAST_FAILURE_SIZE

마지막으로 실패한 요청의 크기

V$SHARED_POOL_RESERVED 뷰를 통한 진단
V$SHARED_POOL_RESERVED 뷰의 통계는 파라미터 튜닝에 도움이 될 수 있습니다. SGA를 증가시키는 데에 충분한 여유 메모리가 있는 시스템에서, 목표는 REQUEST_MISSES = 0 입니다.

ABORTED_REQUEST_THRESHOLD 프로시저를 통한 진단
DBMS_SHARED_POOL 패키지 내의 ABORTED_REQUEST_THRESHOLD 프로시저는 ORA-4031 오류를 보고하기 이전에 비워야 할 공유 풀의 양을 제한하는 것을 허용합니다. 따라서, 큰 객체로 인해 발생할 수 있는 extent(한번에 비워지는)를 제한할 수 있습니다.

파라미터 설정을 위한 Guidelines
시스템이 OS 메모리에 대해 한정되어 있더면, 목표는 다음과 같습니다:

  1. REQUEST_FAILURES = 0 또는 증가되지 않음
  2. LAST_FAILURE_SIZE > SHARED_POOL_RESERVED_MIN_ALLOC
  3. AVG_FREE_SIZE > SHARED_POOL_RESERVED_MIN_ALLOC

두 번째 목표나 세 번째 목표가 달성되지 않으면, SHARED_POOL_RESERVED_SIZE와 SHARED_POOL_SIZE를 같은 양으로 증가시키십시오.

SHARED_POOL_RESERVED_SIZE가 너무 작을 때의 Guidelines
REQUEST_FAILURES > 0 이고 적어도 다음 중 하나가 사실이면, 예약 풀이 너무 작은 것입니다:

이러한 경우에:

SHARED_POOL_RESERVED_SIZE가 너무 클 때의 Guidelines
다음과 같은 경우, 너무 많은 메모리가 예약 목록에 할당되었을 수 있습니다:

이러한 경우에:

SHARED_POOL_SIZE가 너무 작을 때의 Guidelines
이것은 다음과 같은 경우일 것입니다:

이러한 경우에:

객체 보관 이유 및 시기
큰 객체를 로드하는 것이 단편화를 유발시키는 주요 원인입니다. 크기는 작지만 수가 많은 객체들이 오래되었을 때 여유 공간을 만들기 위하여 공유 풀에서 삭제되기 때문에, 사용자의 응답시간이 영향을 받게 됩니다. 이러한 상황을 막기 위하여, 이들 큰 객체나 자주 요구되는 객체는 공유 풀에 보관하여 오래되어도 공유 풀에서 결코 삭제되지 않도록 하십시오.

객체 보관 방법
제공된 DBMS_SHARED_POOL 패키지와 KEEP 프로시저를 사용하십시오.
패키지를 생성하기 위해서는 dbmspool.sql 스크립트를 실행시키십시오. prvtpool.plb 스크립트는 앞의 스크립트가 종료될 때 자동으로 실행됩니다. 이들 스크립트는 catproc.sql로 실행되지 않습니다.
UNKEEP 프로시저를 사용하여, 공유 풀에서 고정된 객체를 삭제하십시오.

큰 익명의 PL/SQL 블록을 제거하기 위한 2가지 해결방안

초기화 파라미터
다음의 파라미터는 라이브러리 캐쉬에 영향을 미칩니다.

2개의 키워드

목표
일부 경우에 데이터 딕셔너리 캐쉬에서의 실패가 예상됩니다. 인스턴스가 시작하자마자, 데이터 딕셔너리 캐쉬에는 어떠한 데이터도 포함되어 있지 않습니다. 따라서 실행된 어떠한 SQL 문이라도 캐쉬 실패를 초래할 수 있습니다. 더 많은 데이터가 캐쉬로 읽혀질수록, 캐쉬 실패의 가능성은 감소합니다. 결국, 데이터베이스는 가장 자주 사용되는 딕셔너리 데이터가 캐쉬에 저장되는 “안정된 상태”에 도달해야 합니다. 이러한 점에서, 캐쉬 실패는 거의 발생해서는 안됩니다. 캐쉬를 튜닝하기 위해, 애플리케이션이 실행된 후에만 활동을 검사하십시오.

딕셔너리 캐쉬 감시
V$ROWCACHE 뷰를 사용하십시오. 가장 관심있는 열은 다음 다음과 같습니다.

 열

 설명

 PARAMETERS

 데이터 딕셔너리 항목의 범주

 GETS

 범주에 관한 정보 요청

 GETMISSES

 캐쉬 실패를 초래하는 요청


크기 조정
SHARED_POOL_SIZE 파라미터를 사용하여 간접적으로만 딕셔너리 캐쉬의 크기를 조정할 수 있습니다. 공유 풀 공간을 할당하기 위한 알고리즘은 딕셔너리 캐쉬를 선호합니다.

적절한 비율을 위한 목표
정상적인 실행동안 모든 GETMISSES의 총합과 GETS의 총합의 비율은 15%이하이어야 합니다. 이보다 높은 경우, SHARED_POOL_SIZE를 증가시킬 것을 고려하십시오.
시작한 후 서버가 객체 정의를 처음 필요로 할 때, 객체 정의가 캐쉬로 로드되어야 하기 때문에 GETMISSES값이 0이 되기를 기대할 수 없습니다. 

Report.txt 출력 결과의 비율
Report.txt 출력 결과가 항목 수에 대해  높은 GET_MISS/GET_REQ 비율을 나타내면, 이것은 SHARED_POOL_SIZE를 증가시켜야 한다는 것을 의미합니다.

주의: OEM 진단 팩 애플리케이션:
Performance Manager -> Memory -> Data Dictionary Cache Hit %

사용자 글로벌 영역
다중 쓰레드 서버를 사용할 경우, 사용자 세션과 커서 상태 정보는 개별(private) 사용자 메모리에 저장되는 것이 아니라 공유 풀에 저장됩니다. 정렬 영역과 개별(private) SQL 영역은 세션 정보에 포함됩니다. 이것은 공유 서버가 문장 당 작업을 하기 때문에 어떠한 서버도 모든 사용자의 정보에 액세스할 필요가 있기 때문입니다. 공유 풀의 이 부분은 사용자 글로벌 영역(User Global Area, UGA)이라고 합니다.

다중 쓰레드 서버에 대한 총 메모리 요구량은 전용 서버를 쓰는 경우 정도입니다. SHARED_POOL_SIZE를 증가시킬 필요가 있겠지만, 개별(private) 사용자 메모리는 더 적게 필요합니다.

필요한 공간 측정
모든 다중 쓰레드 연결에 대해, 공유 풀에 세션 메모리를 놓기 위하여 모든 공유 서버 사용자를 위한 필요 공간의 양을 계산할 필요가 있습니다.

주의: OEM 진단 팩 애플리케이션:
Performance Manager -> Memory -> Memory Allocated

대용량 풀의 존재

Oracle8은 SGA에 공유 풀과 유사한 새로운 영역을 도입했습니다. 대용량 풀은 할당되지 않으므로 필요할 경우 명시적으로 구성해야 합니다. 대용량 풀의 메모리는 공유 풀에서 할당되는 것이 아니라 SGA에서 직접 할당되므로 Oracle 서버 시작 시 인스턴스에 필요한 공유 메모리의 양에 대용량 풀의 메모리를 추가합니다.

장점

대용량 풀은 다음 작업에 사용할 세션 메모리에 대용량 메모리를 할당할 때 사용됩니다.

작업 방식

LARGE_POOL_SIZE 초기화 매개변수가 설정되어 있지 않으면 Oracle 서버는 SGA의 공유 풀에서 공유 메모리 버퍼 할당을 시도합니다. LARGE_POOL_SIZE가 설정되어 있으나 그 값이 충분히 크지 않은 경우에는 할당에 실패하며 버퍼를 요청한 Oracle 서버 구성 요소는 다음을 수행합니다.

대용량 풀에는 LRU 목록이 없습니다. 대용량 풀은 공유 풀에서 할당된 다른 메모리와 동일한 LRU 목록을 사용하는 공유 풀의 예약 공간과는 다릅니다.

대용량 풀 구성 방법

LARGE_POOL_SIZE 매개변수가 설정되어 있지 않은 경우에는 대용량 풀이 없습니다. 지정한 크기의 메모리는 SGA에서 할당됩니다.

PARALLEL_AUTOMATIC_TUNING이 TRUE로 설정되어 있으면 LARGE_POOL_SIZE가 자동으로 계산됩니다. LARGE_POOL_SIZE 값을 수동으로 설정하려면 V$SGASTAT 뷰를 질의하여 필요에 맞게 LARGE_POOL_SIZE 값을 늘리거나 줄입니다.

예를 들어, 시작 시 다음 오류가 발생할 수 있습니다.

   ORA-27102: out of memory

   SVR4 Error: 12: Not enough space

이 경우 데이터베이스가 시작될 수 있도록 LARGE_POOL_SIZE 값을 충분히 낮출 것을 고려해 봅니다. LARGE_POOL_SIZE 값을 낮춘 후에도 다음 오류가 표시될 수 있습니다.

   ORA-04031: unable to allocate 16084 bytes of shared memory ("large pool","unknown object","large pool hea","PX msg pool")

이런 경우 다음 질의를 실행하여 16,084바이트를 할당할 수 없었던 이유를 확인합니다.

   SQL> SELECT NAME, SUM(BYTES) FROM V$SGASTAT

     2  WHERE POOL='LARGE POOL' GROUP BY ROLLUP (NAME);

Oracle은 다음과 유사한 출력으로 응답합니다.

   NAME                          SUM(BYTES)
   --------------------------    ----------
   PX msg pool                     1474572
   free memory                      562132
                                   2036704

   3 rows selected.

이 문제를 해결하려면 LARGE_POOL_SIZE 값을 늘립니다. 이 예제의 LARGE_POOL_SIZE 값은 약 2MB입니다. 사용 가능한 메모리의 크기에 따라 LARGE_POOL_SIZE 값을 4MB로 늘려 데이터베이스 시작을 시도할 수 있습니다.

 

 문맥

 참조

 초기화 파라미터

 SHARED_POOL_SIZE
 OPEN_CURSORS
 SESSION_CACHED_CURSORS
 CURSOR_SPACE_FOR_TIME
 CLOSE_CACHED_OPEN_CURSORS
 SHARED_POOL_RESERVED_SPACE
 SHARED_POOL_RESERVED_MIN_ALLOC

 동적인 성능 뷰

 V$SGASTAT
 V$LIBRARYCACHE
 V$SQLAREA
 V$SQLTEXT
 V$DB_OBJECT_CACHE
 V$ROWCACHE
 V$SHARE_POOL_RESERVED
 V$STATNAME
 V$SESSTAT
 V$MYSTAT

 데이터 딕셔너리 뷰

 None

 명령어

 None

 패키지된 프로시저 및 함수

 dbms_shared_pool.keep
 dbms_shared_pool.unkeep
 dbms_shared_pool.aborted_request_threshold

 스크립트

 dbmspool.sql
 prvtpool.plb

 진단 팩 애플리케이션

 Performance Manager

X 정답:A


O


O


X 정답:A


O


X 정답:C


X 정답:ABC


X 정답:A


X 정답:D


O


X 정답:D