Oracle Architecture

Oracle Architecture

oracle sever = 인스턴스 + 데이터베이스

인스턴스 : 메모리 상에 생성되는 구조

SGA(System Global Area)

백그라운드 프로세스

데이터베이스 : 디스크 영역에 저장되는 구조

Data files : 데이터가 저장되는 영역

Control files : DB전체의 관리정보가 들어있는 영역

Redo log files : 장애 복구 시에 사용되는 영역

SGA란?

-오라클서버의 메모리 영역

-Oracle 인스턴스에 대한 데이터와 제어정보를 가지는 공유 메모리 영역의 집합

-오라클 파라미터 파일(pfile,spfile)의 조정으로 SGA의 각 부분의 크기를 조절

initSID.ora : pfile (정적파라미터변경; 서버를 내리고 해야함)

spfileSID.ora : spfile (동적파라미터변경;)

-Oracle 9i부터 동적으로 변경 가능 (SGA_MAX_SIZE내)

-Shared Pool,DataBase Buffer Cache, Redo LogBuffer와 Large Pool과 Java Pool로 구성

 

1.Shared Pool

- Shared Pool은 Library Cache와 Data Dictionary Cache로 구성

- Shared Pool은 하나의 데이터베이스에 행해지는 모든 SQL문을 처리하기 위하여 사용

- Shared Pool은 문장을 실행하기 위해 그 문장과 관련된 실행 계획과 구문분석 정보가 들어있음

- SHARED_POOL_SIZE 파라미터 값으로 결정

1) Library Cache

- 가장 최근에 사용된 SQL문장의 명령문과, 구문 분석 트리, 실행계획 정보를 가지고 있음

- LRU(Least Recently Used) 알고리즘으로 관리

- 동일한 문장이 다음 번에 실행되면 Shared SQL 영역에 저장되어 있는 실행계획과 파싱 트리를 그대로 이용하기 때문에 SQL 문장의 처리 속도가 향상(Soft Parsing) <> Hard Parsing : Library Cache가 없는 것

*라이브러리캐시 히트레시오 : 소프트 파싱이 잘되는 rate (보통 90퍼이상)

 

-- TEST SQL 실행

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 60;
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 60;
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 50;

 

-- plan hash value 값 확인

SELECT SQL_ID, SQL_TEXT, HASH_VALUE, PlAN_HASH_VALUE

FROM V$SQL

WHERE SQL_TEXT LIKE '%employees where department_id%'

OR SQL_TEXT LIKE '%EMPLOYEES WHERE DEPARTMENT_ID%';

 

 

 

literal SQL 변수를 바인드 처리해줌( 캐시에 불필요한 sql을 담기 때문 )--> 성능 향상

ex) select empno from emp where empno=:a

* 여러 empno를 조회하더라도 하나의 sql으로 보기 때문에 불필요한 메모리 낭비를 줄일 수 있다.

 

EXACT_MATCHING_SIGNATURE은 다르나 FORCE_MATCHING_SIGNATURE가 같으면 literal sql 변수

 

2) Data Dictionary Cache

- Object(테이블, 컬럼, 사용자 이름, 사용 권한) 같은 가장 최근에 사용된 데이터 사전의 정보를 저장

- 구문 분석 단계에서 서버 프로세스는 SQL문에 지정된 오브젝트 이름을 찾아내고 접근 권한을 검증하기 위해 Dictionary Cache의 정보를 찾아봄

 

3) Shared Pool Size

- 확인

SQL) show parameter shared_pool_size; <프롬프트창>

 

 

SQL) select * from v$parameter where name='shared_pool_size'; <접속툴:Orange>

 

 

- 변경

SQL) alter system set shared_pool_size=10m;

 

select * from v$sgastat;  - sga를 구성하는 4가지 pool의 크기를 이곳에서 조회할 수 있다.

select * from v$parameter  -  현재 세션의 현재 파라미터의 값 표시

 

SELECT * FROM V$PARAMETER

WHERE NAME = 'shared_pool_size';

 

 

 

SQL) alter system set shared_pool_size=13m;

 

 

==> 분명 10M, 13M로 변경하였는데 16M로 바뀌어있다. Granule사이즈 때문인데 아래에서 살펴보자

 

4) Granule

- Granule은 가상 메모리 상의 연속된 공간으로, dynamic SGA 모델에서 할당할 수 있는 최소 단위

- 이 Granule의 크기는 SGA_MAX_SIZE에 따라 다음과 같이 구분

    if estimated SGA size is < 1G 4MB

    else 16MB

- Dynamic SGA를 사용할 수 있는 SGA관련 파라미터는 이 Granule 단위로 늘어나거나 줄어듬

 

2. Database Buffer Cache (Buffer Cache)

- 가장 최근에 사용된 데이터를 저장하는 메모리 공간

- DB Buffer Cache 용량이 적을 경우 디스크 I/O가 많아져 부하가 생겨 성능 저하

- 이 버퍼는 아직까지 디스크에 완전히 쓰여지지 않는 수정된 데이터를 보유할 수도 있음

- LRU 알고리즘에 의하여 가장 오래 전에 사용된 것은 디스크에 저장하고 메모리에는 가장 최근에 사용된 데이터를 저장함으로, 디스크 입 출력이 줄어들고, 데이터베이스 시스템의 성능이 증가됨

- 데이터를 조회 -> 해당 데이터를 먼저 Database Buffer Cache에서 찾고 있으면 반환(Logical Read),

없으면 Database Buffer Cache의 Free Buffer를 일단 확보 후 Disk에서 데이터를 읽어 들여 cache하여 반환 (Physical Read)

 

□ Database Buffer Cache Block의 상태

- Pinned Buffer : 다른 사용자가 이미 사용하고 있는 Buffer Block으로 사용할 수 없음

- Dirty Buffer : 현재 작업은 진행되지 않지만 다른 사용자가 내용을 변경한 후 아직 데이터 파일에 변경된 내용을 저장 하지 않은 Buffer

- Free Buffer : 이 Buffer는 사용되지 않았거나(Unused) 또는 Dirty Buffer였다가 디스크로 저장이 되고 다시 재사용 가능하게 된 Block

 

□ LRU List : Buffer Block들의 상태를 관리하고 있는 list

- 수많은 사용자가 동시에 Physical Read를 하여 동시에 Database Buffer Cache의 Free Buffer를 찾으려고 할 때 이 LRU List를 참조 -> 동시성 관리를 위해 순번제공 (Latch) -> 본인 순번이 올 때까지 대기

 

-확인

show parameter db_cache_size; <프롬프트창>

select * from v$parameter

where name ='db_cache_size';

 

-변경

SQL> alter system set db_cache_size=10m;

 

 

==> 이또한 Granule 사이즈 영향을 받는다.

 

3. Redo Log Buffer

· Redo 로그 버퍼는 데이터베이스에서 일어난 모든 변화를 저장하는 메모리 공간

· 장애 발생 시 복구를 위해 모든 변경사항 저장

· Redo Log Buffer에 기록하지 않는 경우도 있음

- Direct Load(SQL Loader, insert /+*append*/)

- table 이나 index의 nologging 옵션의 경우

- 단, table nologging 시 DML작업의 경우 제한적으로 Redo log에 기록

· DB에서 발생한 모든 변화는 LGWR에 의해 Redo 로그 파일에 저장

· LOG_BUFFER로 크기 지정

· 동적으로 Size 변경 불가능

 

4. Large Pool ( 위에 3까지가 중요하고 다음은 0으로 설정해도 무관 )

- Oracle 백업 및 복원 작업에 대한 대용량 메모리 할당, I/O 서버 프로세스 및 다중 스레드 서버와 Oracle XA에 대한 세션 메모리를

    제공하는 SGA의 선택적인 영역

      · RMAN으로 백업 및 복구를 할 경우 RMAN이 사용하는 I/O용 Buffer

      · Parallel Execution 작업을 할 경우 각 프로세스 간 Message Buffer

      · Shared Server Mode로 Oracle Server 운영 시 UGA 

- LARGE_POOL_SIZE 파라미터로 관리되며, 기본크기는 0

 

5. Java Pool

- 자바로 작성된 프로그램을 실행할 때 실행 계획을 저장하는 영역

- JAVA_POOL_SIZE 파라미터로 관리되며, 기본크기 24MB가 할당

 

6. Streams Pool

- 10g New Feature

- Stream 기능 사용할 경우에만 사용되어 기본 값은 0임

- 오라클 내부적으로 redolog 변경된 내용을 추적하여 타 DB로 복제

 

7. Fixed SGA

- Oracle이 내부적으로 사용하기 위해 생성시키는 공간

- 주로 백그라운드 프로세스들이 필요한 database 전반적인 공유 정보나 각 프로세스들끼리 공유해야 하는 lock 정보 같은 내용들이 저장

- Oracle이 시작될 때 자동으로 설정되며 사용자나 관리자가 임의로 변경 할 수 없음

 

SGA 사이즈 확인

- Total System Global Area : 전체 SGA 영역

- Fixed Size : 백그라운드 프로세스들이 사용하는 예약된 공간

- Variable Size : Shared Pool, Large Pool, Java Pool 공간

- Database Buffers : Database Buffer Cache 영역

- Redo Buffers : Redo log Buffer 영역

SQL > show SGA

 

 

Dynamic SGA 기능

- Oracle 9i 이후부터 등장하게 된 SGA 동적 관리 기법

- 파라미터의 크기를 Oracle Instance 재 기동 없이 변경할 수 있음

- Dynamic SGA를 사용할 수 있는 SGA 관련 파라미터는 DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE,

 

JAVA_POOL_SIZE

- 각 SGA 파라미터의 합은 SGA_MAX_SIZE보다 작아야 함

- SGA_MAX_SIZE는 동적으로 변경 불가능

- 파라미터 사이즈 할당 시 Granule 단위로 할당됨

 

PGA(Program Global Area)

- 하나의 단일 프로세스에 대한 데이터와 제어 정보를 가지고 있는 메모리 공간을 USER PROCESS가 Oracle Database에 접속하고

Session이 생성될 때 Oracle에 의해 할당(1:1)

- 각 SERVER PROCESS에 하나만 할당되는 PGA 메모리 영역은 SGA영역과 달리 다른 프로세스와 공유되지 않는, 각 프로세스가

독립적으로 사용하는 non-shared 메모리 영역

- PGA는 프로세스가 생성될 때 할당되며 프로세스가 종료될 때 해제됩니다.

- PGA_AGGREGATE_TARGET라는 값을 통해 사이즈 설정

- 메모리 가득 차면 temp tablespace로 감

  1) Private SQL Area

· User Process로부터 전달받은 SQL의 Bind 변수 값 보관

· Query의 실행 상태 정보 및 Query의 임시 정보 저장

 

2) SQL Work Area

· Sort나 Hash관련 작업을 수행하는 공간

· order by나 union과 같은 정렬작업을 필요로 하는 SQL 구문 실행 시 1차적으로 해당 공간에서 작업 수행

· INDEX 생성 작업 역시 정렬이 필요하므로 해당 영역에서 1차적 작업 수행

· WORKAREA_SIZE_POLICY

- PGA의 각 영역의 메모리 할당 방식을 지정해 주는 파라미터

- MANUAL과 AUTO(default)관리 방식으로 나뉨

- 세션 별, 시스템 별 설정 가능

SQL> alter session | system set WORKAREA_SIZE_POLICY = AUTO | MANUAL

- PGA_AGGREGATE_TARGET=0 이면 자동으로 WORKAREA_SIZE_POLICY = MANUAL로 설정되면서

각 PGA의 개별 구성요소 값을 수동으로 설정 해야 함

- PGA_AGGREGATE_TARGET > 0 이면 자동으로 WORKAREA_SIZE_POLICY = AUTO로 설정되면서

각 PGA의 개별 구성요소 값을 오라클 서버가 자동으로 설정

 

SQL> select * from v$PARAMETER

WHERE NAME in ('pga_aggregate_target'

,'workarea_size_policy'

,'sort_area_size'

,'hash_area_size'

,'bitmap_merge_area_size'

,'create_bitmap_area_size');

 

 

--PGA_AGGREGATE_TARGET의 사이즈가 0일 때 SGA 사이즈의 20% SGA*0.2나 10M중 최대 값 으로 설정

--> 그래서 WORKAREA_SIZE_POLICY가 AUTO설정

SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY=AUTO;

SQL> ALTER SYSTEM SET PGA_AGGREGATE_TARGET=110M;

 

--세션 별 PGA SIZE확인

SELECT S.SID, S.SERIAL#, P.SPID AS "OS PID", S.USERNAME, S.MODULE, S.TERMINAL, S.SQL_ID, S2.SQL_TEXT, P.PGA_USED_MEM/1024/1024 AS "SIZE(MB)"

FROM V$PROCESS P, V$SESSION S, V$SQL S2

WHERE S.PADDR = P.ADDR

AND S.SQL_ID = S2.SQL_ID(+);