공유 풀은 오라클의 SGA를 구성하는 요소 중에 데이터베이스 버퍼 캐시와 함께 인스턴스의 성능을 결정하는
중요한 메모리 영역으로 라이브러리 캐시(Library Cache), 딕셔너리 캐시(Dictionary Cache), 컨트롤 구조로 구성 되어
있습니다.
공유 풀의 크기는 초기 파라미터인 SHARED_POOL_SIZE의 값에 의해 결정 되는데 기본값은 32Bit Flatform에서는 8MB, 64Bit 환경에서는 64MB 입니다.
공유풀은 사용자가 새로운 SQL문을 오라클에 보낼 때 먼저 Library Cache내에 동일한 SQL 문장이 있는지 검사 합니다.문장이 이미 존재 한다면 구문 분석없이 이미 사용했던 실행 계획에 따라 데이터를 추출 하고 만약 없다면 구문 분석을 수행 후 DB내의 객체의 구조를 저장하고 관리하는 데이터 딕셔너리 정보를 검색하여 현재 사용자를 확인하고 그 사용자가 접근 하려는 객체를 검사합니다.
그리고 사용자가 요구한 데이터를 가장 빠른 검색 경로를 통하여 추출 하려는 실행 계획을 세우게 됩니다.
참고로 공유 풀과 init.ora 파일의 ,SHARED_POOL_SIZE 변수는 일치하지 않을 수 있습니다.
SQL> select sum(bytes) from v$sgastat
2 where pool = 'shared pool';
SUM(BYTES)
----------
67108864
SQL> show parameter shared_pool_size;
NAME TYPE VALUE
------------------------------------ ----------- ---------
shared_pool_size big integer 50331648
공유 풀은 대응하는 init.ora 변수에 의해서 지정하는 영역밖에 존재하는 많은 다른 구조를 가지므로 sum(bytes) from v$sgastat 값은 항상 parameter shared_pool_size 값보다 크게 됩니다. SHARED_POOL_SIZE 변수는 공유 풀에 많은 영향을 주지만 유일한 결정자는 아닌것 입니다.
라이브러리 캐시(Library Cache)
라이브러리 캐시는 사용자가 요청한 질의 문장을 Server Process가 여러 단계를 거쳐 작업할 때 사용하는 작업 공간으로서 공유 SQL 영역과 개별 SQL 영역, PL/SQL 영역으로 나누어 볼 수 있습니다. 공유 SQL 영역에는 SQL문에 대한 텍스트, 파스 트리, 실행 계획등을 저장 하고 있으며 Shared PL/SQL영역은 최근에 실행한 PL/SQL 문장과 파싱되고 컴파일된 프로그램 Unit들, Procedure등을 저장 합니다. 개별 SQL 영역은 MTS(Multiple Transaction Server)에서 사용 합니다.
Library Cache의 가장 중요한 목적은 Library Cache안에 저장된 Object를 가장 빠르게 찾고 저장하는 기법을 제공하는 것으로 오라클의 모든 동적 메모리를 관리하는 Heap Manager와 Library Cache Manager에 의해 관리 되어집니다.
이러한 목적을 위해 Library Cache Manager도 Hashing 기법을 이용하여 Object에 대한 이름을 갖는 핸들을 찾고 이 핸들은 결국 Object를 가리키고 있는 겁니다. Hash Table은 Hash Bucket으로 구성 되어 있으며 이 Burcket들은 여러 개의 Library Cache Handle List를 가지고 있습니다.
Library Cache Handle은 결국 Library Cache Object를 가리키고 있으며 Library Cache Object의 이름, namespace, timespace, reference list, object를 locking하고 있는 lock list, pinning하고 있는 pin list를 포함하고 있습니다.
Library Cache에는 아래와 같은 Library Objects들이 있습니다.
SQL AREA(SQL CURSOR)
TABLE/PROCEDURE(VIEW, SYNONYM, SEQUENCE 등 포함)
BODY
TRIGGER
INDEX
CLUSTER
OBJECT(PL/SQL Anonymous BLOCK)
PIPE
이번에는 Library Cache Manager가 Object를 찾는 절차에 대해 보도록 하겠습니다.
1. 주어진 Object의 Namespace, Object Name, Owner, DataBase Link값을 hash function을 적용하여 Object가 존재하는 hash bucket을 찾습니다.
2. hash bucket을 찾은 다음 linked list를 따라 Object가 존재하는 지를 check 합니다.
3. 만약 Object가 존재 한다면 9로 가고 아니면 Library Cache Manager는 주어진 이름으로 Empty Object를 생성 합니다.
4. 3에서 생성된 Empty Object를 Hash Table에 포함 시킵니다.
5. Client에게 Object를 로드 하도록 요청 합니다.
6. Client가 디스크에서 읽어 Object를 찾습니다.
7. Heap Manager에게 Memory를 할당 하도록 요청 합니다.
8. Object를 로드 합니다.
9. 찾은 Object를 사용 합니다.
오라클의 모든 SQL은 공유 SQL 영역과 개별 SQL 영역에서 수행 됩니다. 만약 두명의 사용자가 같은 SQL문을 사용 할 경우 공유 SQL 영역을 재사용 하여 자원을 절약 합니다.그러나 사용자는 개별 SQL 영역에 복사본을 보유하게 됩니다.
라이브러리 캐시에는 SQL 문장을 하나씩 실행 할 때 마다 사용된 SQL 문장이 저장되며 User가 이전과 동일한 SQL 문장을 실행하면 Parse Time이 줄어들어 성능이 향상 됩니다. 동일한 문장이란… 대.소문자.공백을 포함하여 문자열이 동일 해야 하며 참조된 Object가 동일 해야 하며 또한 변수 유형과 이름, 사용자가 동일 해야 합니다. 결국 100% 같은 문장만이 동일한 문장으로 인식한다는것 꼭 기억 하시기 바랍니다.
1.공유 SQL 영역 : 주어진 SQL 문장에 대해 Parse Tree와 실행 계획을 가지고 있습니다. 오라클은 여러번 걸쳐 사용되는 SQL문장을 하나의 공유 SQL 영역에 저장하여 관리 합니다. 만약 전체 공유 풀이 모두 사용되어 꽉 차게 되면 LRU 알고리즘에 따라 메모리 사용 빈도가 낮은 SQL을 제거 하게 됩니다.
2.PL/SQL 프러시저와 패키지 : SQL을 처리하는 것과 같이 PL/SQL 프로그램 Unit(Function, Procedure, Package, Anonymous Block)을 처리 합니다. SQL과 마찬가지로 구문 분석 후 컴파일 된 형태로 공유 SQL 영역에 저장 됩니다. 오라클은 프로그램 단위로 실행되는 세션에 할당된 값을 저장하기 위해 개별 SQL 영역에 메모리를 할당 합니다. 여기에는 지역변수, 전역변수, 패키지변수, 실행된 SQL에 대한 버퍼가 포함되며 이렇게하여 개별 사용자는 자신의 개별 SQL 영역에 분리된 복사본을 가지게 됩니다. PL/SQL 프로그램의 구문분석 정보와 실행 계획은 공유 SQL 영역에서 사용 되며 SQL의 실행은 각 세션에 대한 개별 영역에 저장 됩니다.
데이터 딕셔너리 캐시(Data Dictionary Cache)
공유 풀의 크기는 초기 파라미터인 SHARED_POOL_SIZE의 값에 의해 결정 되는데 기본값은 32Bit Flatform에서는 8MB, 64Bit 환경에서는 64MB 입니다.
공유풀은 사용자가 새로운 SQL문을 오라클에 보낼 때 먼저 Library Cache내에 동일한 SQL 문장이 있는지 검사 합니다.문장이 이미 존재 한다면 구문 분석없이 이미 사용했던 실행 계획에 따라 데이터를 추출 하고 만약 없다면 구문 분석을 수행 후 DB내의 객체의 구조를 저장하고 관리하는 데이터 딕셔너리 정보를 검색하여 현재 사용자를 확인하고 그 사용자가 접근 하려는 객체를 검사합니다.
그리고 사용자가 요구한 데이터를 가장 빠른 검색 경로를 통하여 추출 하려는 실행 계획을 세우게 됩니다.
참고로 공유 풀과 init.ora 파일의 ,SHARED_POOL_SIZE 변수는 일치하지 않을 수 있습니다.
SQL> select sum(bytes) from v$sgastat
2 where pool = 'shared pool';
SUM(BYTES)
----------
67108864
SQL> show parameter shared_pool_size;
NAME TYPE VALUE
------------------------------------ ----------- ---------
shared_pool_size big integer 50331648
공유 풀은 대응하는 init.ora 변수에 의해서 지정하는 영역밖에 존재하는 많은 다른 구조를 가지므로 sum(bytes) from v$sgastat 값은 항상 parameter shared_pool_size 값보다 크게 됩니다. SHARED_POOL_SIZE 변수는 공유 풀에 많은 영향을 주지만 유일한 결정자는 아닌것 입니다.
라이브러리 캐시(Library Cache)
라이브러리 캐시는 사용자가 요청한 질의 문장을 Server Process가 여러 단계를 거쳐 작업할 때 사용하는 작업 공간으로서 공유 SQL 영역과 개별 SQL 영역, PL/SQL 영역으로 나누어 볼 수 있습니다. 공유 SQL 영역에는 SQL문에 대한 텍스트, 파스 트리, 실행 계획등을 저장 하고 있으며 Shared PL/SQL영역은 최근에 실행한 PL/SQL 문장과 파싱되고 컴파일된 프로그램 Unit들, Procedure등을 저장 합니다. 개별 SQL 영역은 MTS(Multiple Transaction Server)에서 사용 합니다.
Library Cache의 가장 중요한 목적은 Library Cache안에 저장된 Object를 가장 빠르게 찾고 저장하는 기법을 제공하는 것으로 오라클의 모든 동적 메모리를 관리하는 Heap Manager와 Library Cache Manager에 의해 관리 되어집니다.
이러한 목적을 위해 Library Cache Manager도 Hashing 기법을 이용하여 Object에 대한 이름을 갖는 핸들을 찾고 이 핸들은 결국 Object를 가리키고 있는 겁니다. Hash Table은 Hash Bucket으로 구성 되어 있으며 이 Burcket들은 여러 개의 Library Cache Handle List를 가지고 있습니다.
Library Cache Handle은 결국 Library Cache Object를 가리키고 있으며 Library Cache Object의 이름, namespace, timespace, reference list, object를 locking하고 있는 lock list, pinning하고 있는 pin list를 포함하고 있습니다.
Library Cache에는 아래와 같은 Library Objects들이 있습니다.
SQL AREA(SQL CURSOR)
TABLE/PROCEDURE(VIEW, SYNONYM, SEQUENCE 등 포함)
BODY
TRIGGER
INDEX
CLUSTER
OBJECT(PL/SQL Anonymous BLOCK)
PIPE
이번에는 Library Cache Manager가 Object를 찾는 절차에 대해 보도록 하겠습니다.
1. 주어진 Object의 Namespace, Object Name, Owner, DataBase Link값을 hash function을 적용하여 Object가 존재하는 hash bucket을 찾습니다.
2. hash bucket을 찾은 다음 linked list를 따라 Object가 존재하는 지를 check 합니다.
3. 만약 Object가 존재 한다면 9로 가고 아니면 Library Cache Manager는 주어진 이름으로 Empty Object를 생성 합니다.
4. 3에서 생성된 Empty Object를 Hash Table에 포함 시킵니다.
5. Client에게 Object를 로드 하도록 요청 합니다.
6. Client가 디스크에서 읽어 Object를 찾습니다.
7. Heap Manager에게 Memory를 할당 하도록 요청 합니다.
8. Object를 로드 합니다.
9. 찾은 Object를 사용 합니다.
오라클의 모든 SQL은 공유 SQL 영역과 개별 SQL 영역에서 수행 됩니다. 만약 두명의 사용자가 같은 SQL문을 사용 할 경우 공유 SQL 영역을 재사용 하여 자원을 절약 합니다.그러나 사용자는 개별 SQL 영역에 복사본을 보유하게 됩니다.
라이브러리 캐시에는 SQL 문장을 하나씩 실행 할 때 마다 사용된 SQL 문장이 저장되며 User가 이전과 동일한 SQL 문장을 실행하면 Parse Time이 줄어들어 성능이 향상 됩니다. 동일한 문장이란… 대.소문자.공백을 포함하여 문자열이 동일 해야 하며 참조된 Object가 동일 해야 하며 또한 변수 유형과 이름, 사용자가 동일 해야 합니다. 결국 100% 같은 문장만이 동일한 문장으로 인식한다는것 꼭 기억 하시기 바랍니다.
1.공유 SQL 영역 : 주어진 SQL 문장에 대해 Parse Tree와 실행 계획을 가지고 있습니다. 오라클은 여러번 걸쳐 사용되는 SQL문장을 하나의 공유 SQL 영역에 저장하여 관리 합니다. 만약 전체 공유 풀이 모두 사용되어 꽉 차게 되면 LRU 알고리즘에 따라 메모리 사용 빈도가 낮은 SQL을 제거 하게 됩니다.
2.PL/SQL 프러시저와 패키지 : SQL을 처리하는 것과 같이 PL/SQL 프로그램 Unit(Function, Procedure, Package, Anonymous Block)을 처리 합니다. SQL과 마찬가지로 구문 분석 후 컴파일 된 형태로 공유 SQL 영역에 저장 됩니다. 오라클은 프로그램 단위로 실행되는 세션에 할당된 값을 저장하기 위해 개별 SQL 영역에 메모리를 할당 합니다. 여기에는 지역변수, 전역변수, 패키지변수, 실행된 SQL에 대한 버퍼가 포함되며 이렇게하여 개별 사용자는 자신의 개별 SQL 영역에 분리된 복사본을 가지게 됩니다. PL/SQL 프로그램의 구문분석 정보와 실행 계획은 공유 SQL 영역에서 사용 되며 SQL의 실행은 각 세션에 대한 개별 영역에 저장 됩니다.
데이터 딕셔너리 캐시(Data Dictionary Cache)
구로디지털단지역
오엔제이프로그래밍 실무학원
(Java ,
Oracle, SQL, Oracle Tuning, BackUP& Recovery, ASP.NET, C#, C#Network
,채용확정
무상교육)
데이터 딕셔너리 캐시는 데이터베이스 테이블과 뷰에 대한 정보, 구조, 사용자등에 대한 정보가 포함된다 SQL 구문을 구문분석하는 동안 자주 데이터 딕셔너리에 접근하게 됩니다. 이런 접근은 필수적이지만 접근하는 빈도가 높기 때문에 데이터 딕셔너리 캐시라는 메모리에 한번 접근한 딕셔너리 데이터를 저장하여 속도를 높일 필요가 있는 것입니다.
여기에는 두개의 영역이 있는데, 하나는 데이터 딕셔너리 캐시로서 Row Cache라고 불리기도 합니다. 전체 블록대신 레코드 단위로 저장 됩니다. 또 다른 하나는 라이브러리 캐시 이고 모든 오라클 사용자는 데이터 딕셔너리 정보에 접근하기 위해 이 두가지 캐시를 공유하여 사용 합니다.
DML중 SELECT 처리 과정중 오라클 서버가 전달받은 SQL문의 테이블의 컬럼 정보, 사용자 권한등을 점검하게 되는데 이대 오라클 서버 프로세스가 참조하는 영역이 데이터 딕셔너리 캐시 입니다. 이 데이터 딕셔너리 캐시에는 모든 사용자 정보와 권한을 저장하는 테이블과 뷰, 데이터베이스 내에 존재하는 테이블과 뷰이름, 테이블의 컬럼명과 타입등의 정보가 저장되어 있습니다.
데이터 딕셔너리 캐시 내부에 저장되어 있는 데이터 딕셔너리는 4가지로 분류가 가능 합니다.
1.ALL_ : 현재 사용자가 Access 가능한 Object에 관한 정보
2.USER_ : 현재 사용자가 소유한 Object에 관한 정보
3.DBA_ : DBA만이 Access 가능한 뷰로서 데이터베이스의 모든 정보를 제공
4.V$ : DATABASE 내부의 Architecture 및 성능 정보를 보는 Performance View
공유풀에서 SQL 영역의 제거
1.ANALYZE문이 테이블, 클러스터, 인덱스에 대한 통계 정보를 생성하고 삭제되는데 이용 될 때, 분석된 스키마 객체를 참조하고 있는 SQL문을 포함한 공유 SQL 영역은 공유 풀에서 제거 됩니다.
2.SQL문에 의하여 참조된 스키마 객체의 구조가 변경 된다면 공유 SQL 영역은 INVALID 상태가 됩니다.
3.DB의 이름을 바꾸면 공유 풀의 모든 정보는 제거 됨니다.
4.DBA는수동으로 공유 풀의 모든 정보를 제거 할 수 있습니다.
(Alter System Flush Shared_Pool)
댓글 없음:
댓글 쓰기