JayK

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로 구성

-- 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(+);

1. 일반적인 Oracle server 구성방식

* Process : A는 작업장1로 복사해와서 작업을하고, B는 작업장2로 복사를 해와서 작업을 하며 저장을 database에 한다. 이렇게 instance와 database 사이를 왔다 갔다 하면서 작업을 해주는 구성요소

  - server process

  - backgroud process

■ Oracle Server의 구성 방식

  - single server 구성 : 하나의 Database에 하나의 instance가 할당되는 구성

==> 일반적으로 DB서버 구현시 1개의 서버를 사용하는데, 이런 경우 istance 역할을 하는 서버에 장애 발생했을 때 storage에 저장된 데이터를 사용할 수 없게 되는 위험이 존재

  - OPS(8i버전까지) 또는 RAC(9i버전부터) : 하나의 database에 여러 개의 instance로 구성하는 방식

2. HA 구성 (High Availability = 고가용성)

  똑같은 장비 두개를 구축해서, 하나는 실제 서비스(active)를 하고, 한대는 대기상태(standby)로 두는 서버구성방식 그래서 active상태였던 서버가 고장나면, standby상태의 서버가 즉시 active 상태로 바뀌어서 투입되는 서비스 중단이 발생하지 않도록 조치되는 구성

※ 문제점

1. 비용이 많이 듬

2. 데이터 동기화 문제 : active 상태의 node에서 작업을 하다가 db가 죽으면, standby 상태의 node로 작업을 할 수는 있다고 쳐도, 데이터가 동기화 되지 않았으므로, 데이터는 이미 날라간 상태

  ==> 미러링을 어떻게 해 줄거냐에 따라서 성능이 결정

       * 미러링 : 장비가 고장나는 사고 생겼을때, 데이터가 손실되는것을 막기위해 데이터를 하나 이상의 장치 중복저장하는 것

  ==> DG(data guard) (HA구성 방식으로 사용시, 데이터 동기화 문제를 해결하기 위해 오라클에서 제공하는 무료 프로그램)

       : 운영 db를 백업받는 용도로 규칙적으로 동기화 ( 별 쓸모 없음 )

** 24X7X365(무정지) : 하루 24시간 7일 365일 내내 stop 하는 시간으로 -> 0이 될 수록 좋음.

3. OPS(Oracle Parallel Server)

  하나의 storage에 두 개의 instance가 연결되어 있는 구성으로, 사용자가 각각 다른 instance에 접속을 해도 storage가 하나이므로, 같은 데이터를 조회, 변경할 수 있음.

※ 장점

만약 1개의 instance에서 장애가 발생할 경우 남아 있는 다른 instance를 통해 storage에 접근가능하기 때문에 서비스가 중단되는 경우를 예방할 수 있다. (데이터가 날라가지 않음. 그 데이터 그대로 다른 instance에서 쓸 수 있음)

** 문제점 : RAC ping

[현재 상황] : storage에 저장된 홍길동 데이터를 A사용자가 instance1로 접속해서 조회하고, B사용자는 instance2에 접속한 후 조회.

                   이후에 A사용자가 홍길동을 일지매로 update한 후 commit까지 완료

[문제 발생] : 이때 instance2에 접속해 있는 B사용자가 홍길동 데이터를 조회할 경우 일지매라는 데이터가 보여야하는데,

                    instance1에서 변경완료후 commit된 일지매 데이터를 instance2로 가져오기 위해서 storage에 우선 저장 후 instance2로 가져와야 함

* RAC Ping 이란 Instance 1 에서 변경 완료 된 데이터를 Instance 2로 가져오기 위해서 우선 디스크에 저장을 한 후 해당 데이터를 Instance 2 로 복사해 오는 작업. 이런 과정이 디스크를 사용해서 시간이 오래 걸리기 때문에 OPS 환경에서 RAC Ping 작업은 성능 저하의 주요 원인으로 지적됨

4. HA와  OPS의 비교

HA  

  - standby 상태의 서버는 장애를 대비하여 대기만하고, 실제 서비스에는 전혀 도움을 주지 않음.

  - Active 상태의 서버 장애 발생시, 해당 서버에 접속해 있던 연결들은 모두 접속 종료 후 standby서버가 가동되면서 다시 접속됨

    즉, active상태였던 서버에서 하던 작업들은 모두 취소

  - 비용이 많이 들고 굉장히 비 효율적

  - 각 서버 별로 Storage를 별도로 가지고 있기 때문에 active상태였던 서버에서 변경된 작업이 standby상태 서버에 반영되지 못할 경우

    데이터 불일치 현상이 생길 수 있음.(데이터 동기화 문제) 

  - 두 개의 서버로만 구성할 수 있음

OPS

  - 두 노드 모두가 Active상태로 동작하기에 이론적으로는 부하가 50%로 분산될 수 있고, 서비스 속도도 두 배 빨리 짐

  - OPS의 경우에는 CTF나 TAF라는 설정이 되어있을 경우 기존 서버에 장애가 발생했을 경우 작업을 그대로 다른 서버로 이전시킬 수 있음

    (단, 수행 중이던 작업의 종류에 따라 다름)

  - 1개의 storage를 공유하므로 한 서버에서 변경된 작업을 다른 서버에서도 그대로 반영

  - OPS나 RAC는 이론적으로 서버수의 제한이 없이 확장이 가능함

  - Down Time을 획기적으로 줄일 수 있음

  - RAC ping이라는 현상으로 심각한 성능저하 발생

5. RAC(Real Application Cluster) (9i버전부터)

  OPS의 RAC ping문제가 개선되어 성능이 크게 향상 된 것으로, oracle9i버전부터는 서로 다른 instance에서 변경된 데이터를 디스크를 거치지 않고 바로 instance로 가져올 수 있는 기능인 Cache Fusion(캐쉬 퓨젼)이라는 기능이 사용됨.

* public network (public 망) : ip 3개 중 관리자가 유지,보수 할 때 쓰는 것으로 public 망에 붙어쓰는게 vip인데, service ip임

* inter connect = private network (private 망) : instance1과 instacne2를 연결하는 망

* cache fusion : instance1에 있는 데이터와 instance2에 있는 데이터를 서로 즉시 볼 수 있고, 어떤 물리적인 instance에서 작업을

                       하든지 내용 구분없이 섞여 있으므로 cache fusion이라고 부름

■ 클러스터용 소프트웨어

CRS (10g R1) --> clusterware (10g R2) --> Grid(11g)

10g R1버전부터 클러스터용 프로그램을 오라클에서 직접 만들어 제공하기 시작. 10g R2버전부터 클러스터웨어라는 용어로 부름.

11g에서는 ASM기능이 통합되어 grid라는 명칭으로 변경. Grid는 여러개의 local lock 떨어져 있는 것을 하나로 묶어 줌

■ clusterware (10g R2)

동일한 데이터를 동시에 변경을 하게 되는 문제가 생기지 않도록 관리해주는 역할

■ ASM기반

  - 10g R2까지는 OCR과 Vote disk를 ASM storage에 저장할 수 없기 때문에 별도의 Raw device를 구성해서 저장해야 함

  - 11g부터는 OCR이나 vote disk가 전부 ASM에 들어감

* OCR (Oracle Cluster Repository) : RAC 구성의 전체 정보를 저장하고 있는 디스크로, RAC에서의 핵심역할을 함

  - RAC를 시작할 때 OCR에 저장되어 있는 정보를 보고 RAC를 구성해야 하는데, 10g RAC까지는 RAC 시작 후 ASM instance를 시작하기 때문에

    OCR을 ASM에 저장할 경우 RAC를 시작할 수 없게 되는데, 그래서 별도의 Raw device에 저장 (최소크기 100MB)

* VoteDisk : 각 Node들이 장애가 있는지 없는지를 구분하기 위해서 사용하는 일종의 출석부 같은 역할을 하며,

                  cssd가 보내는 heartbeat에 응답을 보내면서 매 초마다 vote disk에도 자신이 정상적으로 동작하고 있다는 표시를 해둠

  - RAC를 구성하는 프로세스 중 cssd라는 프로세스가 각 Node들이 정상적으로 작동하고 있는지 interconnect를 통해 매 초마다

    핑(heartbeat)을 보내고 각 node들은 그에대한 응답을 다시 보내어 자신이 정상적으로 동작하고 있다는 것을 알려줌.

    ==> 만약 어떤 이유로 특정 node가 heartbeat에 응답하지 못했을 때, cssd는 2차로 vote Disk를 확인. 그 vote disk를 확인해보고

         그곳에서 해당 node의 표시가 없다면 해당 node가 장애가 생겼다고 가정하고 해당 node를 cluster에서 제외하고 재부팅한다.

         만약 heartbeat에응답이 없었는데, votedisk에 해당 node의 표시가 있다면, node가 바빴다고 생각하고 넘어간다.

         (최소크기 20MB, 3개로 다중화해서 구성하기를 권장)

6. Cache Fusion

  1) Cache Fusion 개념

     : 물리적으로 떨어져 있어도 하나로 만들어 주는 것

RAC 에서는 어떤 Instance 에서 작업을 해도 하나의 서버에서 작업을 하는 것과 같은 효과를 내게 되는데 이런 것을 가능하게 해 주는 RAC의 대표먹인 서비스 두가지가 GES , GCS 

GES (Global Enqueue Service) 란 어떤 Instance 에서 데이터 변경작업 ( 트랜잭션 ) 을 하든 하나의 Instance 에서 데이터 변경 작업을 하는것과 같이 관리하는 기능

GCS (Global Cache Fusion) 란 Cache Fusion 기능이 구현되기 위한 필수 서비스로서 어떤 사용자가 자신의 Instance 에서 원하는 데이터를 찾지 못해서 다른 Instance 에 있는 데이터를 요청했을 때 Insterconnect 를 통해서 데이터를 전달해 주는 서비스를 말함

GCS 에서는 데이터 블록을 전송하고 관리할 때 아래의  3가지 모드로 구분함 

  - Null (N) 모드 : 해당 블록을 사용중인 사용자가 없다는 것을 뜻함

  - Share (S) 모드 : 해당 블록을  select 하고 있는 세션이 있다는 뜻함

  - Exclusive (X) 모드 : 해당 블록을 누군가가 변경하고 있다는 뜻함

3. Interconnnect ( 인터커넥트 ) 란?

  - 앞에서 살펴본 예에서 알 수 있듯이  RAC의 핵심 기능인 Cache Fusion이라는 특징 때문에 블록들이 이동하는 길인  

    Interconnect 가 RAC 전체 성능에 아주 중요한 역할

  - Interconnect  를 통해 이동하는  정보는  크게  Clusterware  가  Cluster 를  유지하고  운영하기 위해 사용하는 정보와 실제 데이터 블록 ,

    Parallel Query 관련 정보들이 있음 

  - 일반적으로  Cluster 를 유지하고 운영하는 정보인  GCS/GES 관련 정보는  256 bytes 정도로 아주 작지만  실제  데이터  블록은  DB_BLOCK_SIZE   

    ( 10g/11g  기준으로  기본  크기는  8k )  나  Non-Standard Block Size 의 크기로 GCS/GES 정보에 비해 아주 큼

  - Parallel Query 관련 정보는  Parallel_Execution_message_size 에 의해 크기가 결정되는 데 기본크기는  8k 로 설정

  - 이런 내용들이 얼마나 자주 왕래하느냐가 성능에 아주 중요한 영향을 주게 되며 당연히 가급적이면 이동하는 양을 줄이는 것이 튜닝에 아주

      핵심적인 관점 

  -  RAC 튜닝에서  Interconnect 의 사용량을 측정하여 튜닝하는 것이 아주 자주 언급

즉,  Interconnect 를 사용하는 량을 줄이거나 혹은  Interconnect 의 속도를 높이는 것이  RAC 튜닝 에서 아주 중요하다는 뜻