[스크랩] 오라클 메모리(SGA,PGA)

 

http://mes2good.egloos.com/viewer/762022

 

오라클은 유저가 변경한 data들을 메모리로 저장하였다 가공 이 끝난 후 DISK 에 내려쓴다. 또한 이 반대로 저장된 data 들을 다시 메모리 영역으로 읽어서 화면에 print 하기도하고 유저에 의해 가공되어 다시 DISK 에 저장이 된다.

이러한 오라클의 메모리의 종류에는 SGA(System Global Area) 와 PGA(Program Global Area)가 있다.

SGA 의 개념

- SGA(System Global Area) – 모든 사용자가 공유하여 사용이 가능

SGA 는 아래의 그림으로 간단하게나마 설명할 수 있다.

http://www.oracleclub.com/lecture/1854

오라클의 SGA에는 위에서 보는 그림과 같이

- Share pool(공유풀) - Permanent Area

- Dinamic Area – Library cache

- Dictionary cache

- Database Buffer Cache(데이터 베이스 버퍼 캐쉬)

- Redo log Buffer (리두로그 버퍼)

- Large pool(대형 풀)

- Java pool(자바 풀)

USER1111

이 있다.

 

ORCL USER2 USERn   SGA SGA 는 공용 메모리 영역이므로 같은 DB 에 접속하는 모든 사용자는 그림과 같이 동일 SGA를 쓰게 된다.

SGA 의 확인

- show sga

Total System Global Area 285,212,672 bytes Fixed Size 1,218,992 bytes Variable Size 88,082,000 bytes Database Buffers 192,937,984 bytes Redo Buffers 2,973,696 bytes

- select * from v$sga;

- select * from v$sgastat àiSQL*plus 로 확인 해 볼 것.

- select * from v$sga_dinamic_components; àiSQL*plus 로 확인 해 볼 것

위의 command 들로 SGA 확인

SGA 변경

[형식] alter system set 파라메터 명 = 변경 값;

이와 같은 형식으로 파라메터의 값을 변경할 수있다.

ex)

SQL> show parameter db_cache

NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ db_cache_advice string ON db_cache_size big integer 4M

SQL> alter system set db_cache_size = 24M;

SQL> show parameter db_cache

NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ db_cache_advice string ON db_cache_size big integer 24M

SQL> alter system set shared_pool_size = 28M;

SQL> show parameter shared_pool NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ shared_pool_reserved_size big integer 3984588 shared_pool_size big integer 28M

Shared Pool(공유 풀)

여러영역으로 나뉜 Oracle 메모리영역에서 Shared Pool은 또 한번 Permanent Area 와 Dynamic Area 로 나뉘어진다.

- Permanent Area(고정영역) – 오라클이 SGA를 관리하는 메커니즘 및 오라클의 파라메터 정보가 저장이 되며 위의 SGA 확인명령들로 확이이 가능하다(Fixed Size).

(!! 고정영역의 크기는 파라메터 설정값 등을 고려하여 자동으로 할당 되며 사용자가 지정 할수 없다….. 고하는데 파라메터 설정값 등을 변경하여 할당 할수 있지 않을까??)

- Dynamic Area(동적영역) – 이 영역은 Library Cache와 Data Dictionary Cache로 구분되며 크기는 위의 ‘alter system set shared_pool_size = 28M;’ 명령으로 변경할 수 있다.

1) Library Cache – SQL 쿼리, 오라클의 recursive SQL, SQL 의 Pase Tree 및 Execution Plan 이 저장된다.

2) Data Dictionary Cache – Low Cache 라고도 불리우며 테이블, 인덱스 함수 및 트리거 등 오라클 오브젝트 정보 및 권한 등의 정보가 저장된다.

공유풀의 활용

오라클은 질의된 SQL 문의 문법이 올바른지 검색하고 문법이 맞다면 위와 같이 사원테이블에 대한 정보를 Data dictionary cache 로 저장하고 SQL 문, 분석정보 및 실행 계획을 Library cache 에 저장한다.

공유 풀에 저장 된 정보는 유저가 같은 SQL 을 다시 실행 하였을 경우 해당정보를 재사용 할 수 있게 한다.(단!!! 구문분석(parsing)은 대소문자를 구별하고 오브젝트 소유자가 일치 하여야 한다. 조건이 일치 하지 않을 경우에는 Hard parsing이 일어나게 되어 DISK 를 읽게 되므로 성능에 영향을 주게 된다.)

Pasing(구문분석)에 대한 내용은 text 105p 구문분석 참조

공유풀의 관리

오라클은 Shared pool의 Library cache 관리를 위해 LRU (Least Recently Used)알고리즘을 사용한다.

프로세스 스케줄링 기법(※ text 106p 참고… 프로세스 스케줄링 기법을 모두 적으려했으나…. OTL… ㅠ.ㅠ 기법은 너무 아서 text 에 적힌 것만)

LRU – 이상한 카드놀이?

FIFO – Que

LIFO – Stack 등과 비슷

Ora-4031 à이 에러를 내고 싶다면 http://ukja.tistory.com/239 블로그 참고(누구 블로그 인줄 다아시지요 ^^;;)

공유 풀 예약공간

공유풀에 미리 공간을 예약함으로 인해 Ora -4031 에 대한 SQL 실행실패를 예방 할 수있다.

Alter system set shared_pool_size = 100M;

Alter system set shared_pool_reserved_size = 10M;

총 공유 풀 공간을 100M 로 설정하고 그중에 예약공간을 10M 로 설정

(show parameter shared_pool 로 공유풀과 공유 풀 예약공간을 볼 수 있다.)

위와 같이 공유풀 예약공간을 설정 하면

1^stSQL 은 공유풀 예약 공간의 메모리를 사용하게 되며 2^ndSQL은 부터는 공유풀을 사용하게 됨으로 써 Ora-4031 에러를 감소 시킬수 있다.(물론 이보다 더 큰 메모리를 요하는 SQL 같은경우에는 어쩔수 없다.. OTL 이다.. 이때는 공유풀의 내용을 초기화 하는수밖에..ㅠ.ㅠ)

- 공유 풀의 예약공간을 설정하지 않았다면 자동으로 공유풀의 5% 크기의 예약공간이 설정된다.

- 공유 풀 예약공간은 공유 풀 크기의 50% 이상 설정할 수 없다.

- Ora-4031 에러를 감소시키기 위한 방법으로 공유풀의 예약공간을 크게 한다면 메모리의 낭비를 초래 하게 되어 성능에 영향을 미치게 된다.

Data buffer cache

데이터 버퍼 캐쉬 – 데이터를 읽고 가공하기 위해 디스크에 존재하는 데이터를 읽어 저장하는 메모리공간 이다. 이 데이터 버퍼 캐쉬 또한 LRU 알고리즘에 의해 관리된다.(이거 보시면 아시겠지요.. ㅎㅎ) 이것 또한 LRU 알고리즘에 의해 관리되다 보니 데이터 버퍼 캐쉬에 데이터가 없으면 DISK 에서 데이터를 읽어 가공을 한다. 하지만 Disk 보다 메모리가 더 빠르다는 것은 다들 알 것이다. 그렇기 때문에 필요한 데이터를 가급적 메모리에서 읽어오게 하는 것이 좋다.

많은 데이터를 가급적 메모리에서 읽어오도록 하기 위해서는 어떻게 하는 것이 좋은가.

오라클은 데이터를 읽거나 쓸 때 블록단위로 실행한다.

많은 데이터를 읽기오기 위해서는 블록을 크게 하면 된다.

(OTL.. 워드에서는 ‘블럭’ 이 블록으로 변환되네요.. 알아서 보시길.. ^^;;)

하지만!!!! 하나의 DB에 여러 user 가 질의를 하고 있다고 가정할 때 단순히 블록의 크기만 크게 하게 되면 그 블록에 경합이 발생을 하게 된다. 단순히 몇 명 뿐이면 이해가 쉽게 가지 않을 수 있다. 만약 우연히 증권을 거래하는 HTS에 같은 application 을 최소 몇 만명에서 1000만 이상 쓰게 되었을 때 같은 블록내의 데이터를 보려고 SQL로 질의 하게 된다면. 될 것인가… 결과는 불을 보듯 뻔하다… 시스템이.. 멎던가… 아니면.. 경합으로 인하여 조금 오래 걸릴 것이다. 하지만 이런 결과를 조금이나마 해소하기 위하여 오라클은 9i 부터 여러 개의 데이터 블록의 크기를 설정할 수 있게 되었다.

Text의 115p 처럼 Show parmeter cache_size 명령으로 볼수있고 또한 116p 처럼 테이블 스페이스에 블럭 size 를 설정할 수 있다.

따라서 데이터 버퍼 캐쉬 설정에는 정해진 크기가 없다.. 각 시스템 마다 알아서 설정을 해줘야 하는 것이다.

또한 이러한 경합을 줄이기 위해 8i 부터 다중 데이터 버퍼 캐쉬를 설정하는 것이 가능해졌다.

다중 데이터 버퍼 캐쉬란

파라미터에서 설정할 수 있는 일반 데이터 버퍼 캐쉬에서 재활용이 많이 되는 캐쉬 의 내용을 LRU 리스트에서 조차 삭제 하지 못하도록 하는 Keep 할수 있는 기능과 재활용이 거의 되지 않는다고 판단되어 짧은 시간안에 제거되게 만드는 Rcycle 기능 이 있다.

데어터 버퍼 캐쉬의 Keep 과 recycle에 대한 설정은 text 120p에 잘 나타나있다.

리두 로그 버퍼

오브젝트가 변경되거나 DML 작업에 의해 데이터가 변경될 때 변경에 대한 로그를 저장하는 SGA메모리 공간이다. (이 에대한 자세한 사항은 8장 리두로그 파일과 아카이브 로그파일을 발표하는 사람에게.. ㅋㅋ^^;;)

오라클의 로그 기법은 선로그 기법을 쓴다.(Log Ahread 기법)

변경전 데이터를 리두로그 버퍼에 미리 저장을 하여 변경시 장애가 생겨도 해당 작업에 수행을 할수 있도록 하는 기법

리두로그 버퍼의 크기 는 1~10M 이내이면 운영하는데 이상이 없다고 한다.

(리두로그 버퍼를 설정한 이후 V$SYSTEM_EVENT 데이터 딕셔너리 뷰를 통해 Log Buffer Space 대기 이벤트가 많이 발생한다면 리두로그 버퍼를 크게 설정 해 주어야 하며 그렇지 않다면 더 이상 크게 설정 할 필요는 없을 것이다.)

Large pool (대형 풀)

SGA 영역중 Shared pool, Data buffer cache, redo log buffer 는 필수 SGA 영역이며 Large pool 은 Optional 이다.

MTS(공유 환경) 환경에서는 UGA 가 공유 풀을 사용하는데 이를 방지 하기 위해서 대형 풀을 설정한다.

RMAN 사용시 파라미터 설정으로 대형 풀을 설정하게 되며 대형풀의 공간이 부족하다면 경고 메세지를 alret log 에 저장 하며 백업 및 복구시 I/O 슬레이브 프로세스를 사용하지 않게 된다.( p123)

병렬 프로세스의 정보저장을 하는데 쓰이며 설정을 하지 않으면 공유 풀을 사용한다.

I/O 슬레이브 프로세서의 정보저장 (p123 의 설명과 앞의 RMAN 의 상관관계 가 있다.)

(여기 까지 봤을 때…. 대형 풀에 대한 나의 생각은.. 대형 풀은….. 필수 SGA 영역이다… OTL 설정안 하면 ORA-4031 에러 나온다고 협박 당한 느낌….;; 꼭 설정하도록 하자…)

앞과 마찬가지로 alter system set large_pool_size = 10M; 이렇게 간단히 설정할 수있다.

JAVA pool(자바 풀)

자바풀 또한 필수 SGA 영역은 아니다. Optional 한 영역인 것이다. 하지만 JAVA 명령을 Parsing 할 경우 지정이 되어 있어야 하며 필요 없을 시 size 를 0 로 하여 효율을 높일 수 있다.

JAVA_POOL_SIZE 조정 자바풀은 정적 파라미터로 크기 변경 후 DB를 재기동 해야 한다. SQL>alter system set large_pool_size=24M scope=spfile;[출처] java_pool_size 조정|작성자 곰탱

참고 - http://blog.naver.com/luckij?Redirect=Log&logNo=50080316073

오라클 메모리 관리

공유 메모리 자동관리 (ASMM)

(튜닝시간에 배울 듯 하므로 일단 교재의 내용을 거의 그대로 베꼈음… OTL.. ㅠ.ㅠ)

- 오라클 10g 의 새로운 기능으로 옹라클이 업무 부하를 판단하여 공유풀, default 데이터 버퍼, 대형 풀 및 자바 풀의 크기를 조정하는 기능이다.

장점으로는 - 데이터 베이스 관리자가 매번 수행해야 했던 SGA 구성요소의 크기를 조정을

오라클이 자동으로 수행함으로써 업무 부하에 따라 동적으로 SGA 구성요소의 크기가 동적으로 조정 되기 때문에 메모리 사용 효율 증가와 이에 따라 메모리 부족현상이 감소 하였다고 한다.

구성방법으로 써는 http://rhrlfdud.tistory.com/entry/자동-공유-메모리-관리-ASMM의-사용-방법 을 보는 것이 가장 빠를 것이다.

정적 파라미터파일과 동적 파라미터 파일

데이터 베이스가 재 시작하게 되면 파라미터 파일(Pfile, initdb명.ora)에 설정된 값을 재적용 하게 되는데 이때 변경 값을 유지하기 위해서 동적 파라미터(Spfile, Spfiledb명) 파일을 쓰게 된다. 그이유는 정적 파라미터 파일인 pfile은 machine 운영 중 적용한 변경 값이 oracle 메모리에만 적용이되고 파라미터 파일에 적용이 되지 않으므로 초기 파라메터 그대로 남아있다. 하지만 동적파라미터 파일인 spfile 은 machine 운영 중에 적용한 변경 값이 scope=BOTH 옵션을 추가 하게 되면 변경 된 값이 메모리와 동적 파라미터 파일에 동시에 적용되므로 DB 재시작후에도 변경된 값이 적용된다.(!!!만약 machine이 정적 파라미터로 운영중이면 파라메터 값이 SGA 에 적용이 되기 때문에 몇 개월 후 DB를 재 시작 했을 때 초기의 파라메터로 돌아갈 수 있다. ※ 정적 파라메터로 시스템을 운영 하다 정기 점검등의 이유로 인하여 오라클을 내렸다가 올렸을 때를 생각 해보라. ㅎㅎ;; 지옥이 보일것이다.. ^^;;)

공유 메모리 자동 관리 사용시 고려사항

1. MMAN 백그라운드 프로세스가 관리하지 않는 SGA 구성요소

- 공유 풀의 고정영역

- 기본 데이터 버퍼캐쉬를 제외한 Db_2k_cache_size 와 같은 파라메터로 지정되는 다중 블록 데이터 버퍼 캐쉬

※ show parameter cache_size 명령으로 볼 수 있다. (기본 데이터 버퍼 캐쉬인db_cache_size 를 제외한 모두라고 보면 된다.)

- Db_keep_cache_size 또는 db_recycle_cache_size 로 지정되는 고정 또는 재사용 데이터 버퍼 캐쉬

- 리두 버퍼 캐쉬

공유 메모리 자동관리는 위와 같이 MMAN 백그라운드 프로세스 비관리 메모리에 대해서는 자동관리를 하지 않는다. (이 메모리를 제외한 나머지 메모리(SHARED POOL, LARGE POOL, JAVA POOL, DB CACHE (DB_BLOCK_SIZE)에 대해서만 자동으로 관리를 한다.)

2. 사용자가 MMAN 백그라운드 프로세스에 의해 관리되는 SGA 영역의 값 설정

- 만약 ASMM을 사용시 사용자가 MMAN 백그라운드 프로세가 관리하는 메모리들(SHARED POOL, LARGE POOL, JAVA POOL, DB CACHE (DB_BLOCK_SIZE))에 대해 값을 0 으로 설정 할 경우 MMAN 백그라운드 프로세가 자유롭게 값을 증가시키고 감소 시킬 수 있다. 4가지 메모리영역을 설정하지 않아도 0 이 된다. 따라서 설정하지 않아도 ASMM 이 구성되는 것이다.

- 만약 사용자가 최소한 어떠한 영역에 어느 정도의 메모리를 써야 한다면 사용자가 그 값을 지정해 주게 되면 지정한 값 이하로는 감소하지 않고 그 값보다 조금 더 큰 범위에서 놀게 되는 것이다.

 

 

http://mes2good.egloos.com/viewer/762042

오라클 메모리(PGA)

 

- PGA (Program Global Area)

pga 란 서버 프로세스에 생성되며 오라클에서 사용하는 메모리 영역이다.
여러 유저가 db에 접속할시 하나의 유저당 서버 프로세스가 하나씩 생기듯이 pga도
하나의 유저당 한개가 생긴다.그리고 해당 유저가 접속을 끊으면 해당 pga는 해지된다.

 

-- shard sever 에서는 pga사용이 필요없다 했는데 이는 더 알아보고 올려드리겠습니다.

 

pga의 구조는 정렬 공간, 세션 정보, 커서 상태 정보, 변수 저장 공간이 있다.

 

1) 정렬 공간 : order by 또는 group by 등 정렬을 수행하기 위한 공간이며, pga 내에서
정렬이 되면 메모리 정렬이라하고, 메모리 공간이 부족할시 disk에서
공간을 이용한다.

2) 세션 정보 : 유저 프로세스의 세션 정보를 저장한다.

3) 커서 상태 정보 : SQL파싱 정보가 저장되어있는 주소를 저장한다.

4) 변수 저장 공간 : SQL문장에 바인드변수를 사용할시 해당 바인드변수를 저장 한다.

 

 

- PGA 관련 파라메터

 

parameter 확인 : show parameter parametername
parameter 값 변경하기 : alter system set parametername = value;

 

1) 수동으로 PGA 사이즈를 관리하는데 필요한 파라메터들

 

sort_area_size
- 소트를 위해 오라클 프로세스가 최대로 사용할 수 있는 메모리 영역

 

sort_area_retained_size
- 소트가 완료된 후 유저 프로세스에게 result set을 리턴하기 전에 유지되는 variable UGA 영역.

hash_area_size
- 해쉬 조인을 위해 오라클 프로세스가 최대로 사용할 수 있는 메모리 영역

 

bitmap_merge_area_size
- 비트맵들의 병합을 위해 오라클 프로세스가 최대로 사용할 수 있는 메모리 영역

 

create_bitmap_area_size
- 비트맵 인덱스를 생성할 때 오라클 프로세스가 최대로 사용할 수 있는 메모리 영역

 

2) 자동으로 PGA 사이즈를 관리하는데 필요한 파라메터들

 

workarea_size_polycy
- PGA 메모리 할당 방식을 정한다. 자동으로 할려면 AUTO, 수동으로 할려면 MANUAL
로 설정해주면 된다.

 

pga_aggregate_target
- 모든 인스턴스에 접속된 모든 서버 프로세스들의 가용한 PGA크기

 

_pga_max_size
- 하나의 프로세스당 최대로 사용할수있는 PGA 메모리 크기
*하나의 프로세스는 복수의 work area를 가잘수있다.

 

_smm_max_size
- serial(직렬) operation을 위한 최대 work area 크기
*이 파라메터는 pga_aggregate_taget & _pga_max_size에 의해 자동계산된다.

 

_smm_px_max_size
- parallel(병렬) operation을 위한 최대 work area 크기
기본이 pga_aggregate_target의 30%이다.
parallel 오퍼레이션은 parallel힌트, alter table xxx parallel,
parallel_automatic_turning=true로 활성화 시킬 수 있다.
*이 파라메터는 pga_aggregate_taget & _pga_max_size에 의해 자동계산된다.

 

/* sort_area_size가 5MB로 설정되어있고, pga_aggregate_target 사이즈는 50MB라고 하자.

이때 한유저가 40MB의 데이터 정렬이 필요하다하면 디스크의 I/O가 8번 일어나게된다.

하지만 workarea_size_policy 파라메터를 auto로 설정하면, pga_aggregate_target size가

50MB로 해놨기 때문에 디스크의 I/O없이 정렬 작업이 된다. 이는 자동 pga 조정이라하는데,

이는 auto extent와 비슷하다 고 생각한다. 하지만 max사이즈를 50MB로 해놨기때문에
50MB를 다 써버리게 되면 다음 유저는 에러가 발생하여 접속을 할수없게된다.

 

SELECT pga_used_mem, -- 프로세스가 현재 사용하는 PGA 크기
pga_alloc_mem, -- 프로세스에 할당된 PGA의 크기(사용 완료 후
-- 시스템 메모리에 반환하지 않는 메모리 포함)
pga_max_mem -- 프로세스가 사용한 최대 PGA 크기
FROM V$PROCESS;

위를 조회 하게되면 해당 DB에 접속하는 세션수와 해당 세션이 사용하는 pga크기가

나오기때문에 적절하게 값을 설정해주면된다. 하지만 pga_aggregate_target가 너무크면

해당 용량을 다 쓰기도 전에 시스템이 죽는수가 있기 때문에 적절하게 설정해야한다. */