다른 명령
SGA
- 10g부터 SGA구성 요소 중 Shared pool, DB buffer cache, Large pool, Java pool, Streams Pool 메모리 자동 관리.
- MMAN 프로세스가 5분 마다 주기적으로 수집한 작업부하(Workload) 정보를 바탕으로 동적으로 구성, 메모리는 필요한 곳으로 동적으로 할당.
ASMM 사용 조건
라지 풀 (Large Pool)
- 대용량 풀의 크기를 결정하는 파라미터 LARGE_POOL_SIZE이며 단위는 바이트.
- 아래 목적으로 대용량 풀을 사용
- MTS(Multi Threaded Server) - SGA영역에서 UGA를 할당 하기 위해
- 오라클 백업과 복원
- 패러럴(병렬) 실행
- RMAN 백업, 복구할 때 사용
- 대용량 풀에는 LRU 리스트는 없으며 MTS를 사용하는 경우라면 유지하기를 권고
- 만약 대용량 풀 없이 MTS를 사용 한다면 모든 메모리 할당이 공유 풀에서 나오므로 이는 성능 저하 발생
자바 풀(Java Pool)
- 데이터베이스에서 자바 수행 지원
- 만약 자바로 저장 프로시저를 만들고 EJB를 데이터베이스에 두면 오라클은 이것의 처리를 위해 자바 풀을 이용
- Oracle JVM에 접속해 있는 모든 세션에서 사용하는 자바코드가 사용하는 메모리 영역을 말함
- 만약 사용자가 자바 내장 프로시저(Java Stored Procedure)를 컴파일하여 오라클 JVM에 로딩하는 작업에 오라클 JVM만을 사용 한다면 20MB 정도의 메모리면 충분 합니다.
- 오라클 공유 서버와 함께 EJB, CORBA를 운영한다면 1GB정도의 메모리가 할당 되어야 합니다.
- 자바 풀의 사이즈를 결정하는 파라미터는 JAVA_POOL_SIZE
- SQL 또는 PL/SQL 의 SQL Statement 및 파싱정보를 보관하고 있는 공간
- SQL, PL/SQL 의 SQL Statement 및 파싱정보를 저장하는 Library Cache 영역
- 오라클 딕셔너리(Dictionary) 정보를 저장하는 Row Cache 영역
스트림 풀(streams pool)
- 파라미터 : streams_pool_size
- 데이터펌프(export,import) , 데이터 백업과 관련 있음 , size가 작으면 데이터 펌프시 에러 발생되거나 수행이 되지 않을수 있음.(alertlog 확인 할것)
- 버퍼링 된 큐 메시지 저장하거나 캡처 프로세스 및 적용 프로세스를 위한 메모리 공간 중 하나
관련 파라미터
SGA_TARGET = 8G DB_CACHE_SIZE =0 JAVA_POOL_SIZE =0 LARGE_POOL_SIZE=0 SHARED_POOL_SIZE = 0 STREAMS_POOL_SIZE = 0
- 위와 같이 설정 시 최소 값은 기본 값으로 구성되며 최대 8G
DB_CACHE_SIZE = 1G 로 설정시 최소 값은 1G 유지하고, 최대 8G
SGA_MAX_SIZE
- SGA 전체의 최대 크기, SGA관련 파라미터들은 SGA_MAX_SIZE 한도 내에서 파라미터의 크기를 변경
- Oracle 9i 이후부터는 DBA가 ALTER SYSTEM 명령을 이용해서 SGA 파라미터의 크기를 동적으로 변경 'Dynamic SGA'라고 함.
- 데이터베이스를 shutdown/startup 없이 작업이 가능
- Oracle 10g 부터 Dynamic SGA를 사용할 수 있는 SGA 관련 파라미터는
- DB_CACHE_SIZE
- LOG_BUFFER
- SHARED_POOL_SIZE
- LARGE_POOL_SIZE
- JAVA_POOL_SIZE
- SGA 파라미터의 크기를 늘려주기 위해서는 필요한 만큼의 Free Granule이 존재해야만 하며, 현재 사용하고 있는 SGA의 크기가 SGA_MAX_SIZE보다 작아야 합니다.
SGA 와 PGA 비율
디폴트 SGA 6 : PGA 4
SGA + PGA 메모리 조회
SELECT NAME , DISPLAY_VALUE FROM V$PARAMETER WHERE NAME IN ( 'pga_aggregate_target','sga_target' );
AMM(Auto Memory Management)
- 오라클에는 SGA(System Global Area), PGA(Program Global Area) 메모리 영역이 존재
- 11g 부터 AMM 관리 기법은 SGA+PGA를 자동으로 관리하는 방식
- 이때 전체 메모리를 설정하는 memory_max_target, memroy_target 파라미터를 설정해 주어야 하는데 리눅스 운영체제의 /dev/shm 크기가 최대치가 된다.
- 리눅스는 기본적으로 전체 메모리의 50%를 ‘/dev/shm’에 할당하는 데 필요한 경우 이 영역을 변경하고 오라클 파라미터를 설정해야 오류가 나지 않는다.
- AMM 기능을 사용하면 SGA_TARGET, PGA_AGGREGATE_TARGET의 값을 0으로 지정하는 것이 좋다.
- 0이 아닌 경우 그 값을 최솟값으로 인식하기 때문이다.
su - oracle sqlplus /nolog
- 시스템 리소스 변경 적용시 DB기동이 실패할 경우를 대비해서 항상 pfile을 백업 받아두는게 좋다.
SQL> conn / as sysdba SQL> show parameter target # 현재 설정되어 있는 memory_max_target, memory_target, pga_aggregate_target, sga_target 값을 확인한다. -- AMM 설정 SQL> alter system set memory_max_target=6G scope=spfile; SQL> alter system set memory_target=6G scope=spfile; SQL> alter system set sga_target=0 scope=spfile; SQL> alter system set pga_aggregate_target=0 scope=spfile; SQL> shutdown immediate; SQL> startup; SQL> show parameter target SQL> show parameter memory_max_target SQL> show parameter memroy_target SQL> exit
- pfile을 이용하여 기동한경우 spfile로 변경 해줘야 한다. 오라클은 이제는 기본이 spfile이다.
SELECT DISTINCT(NLS_CHARSET_NAME(CHARSETID)) CHARACTERSET, DECODE(TYPE#, 1, DECODE(CHARSETFORM, 1, 'VARCHAR2', 2, 'NVARCHAR2', 'UNKOWN'), 9, DECODE(CHARSETFORM, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'), 96, DECODE(CHARSETFORM, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'), 112, DECODE(CHARSETFORM, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN FROM SYS.COL$ WHERE CHARSETFORM IN (1,2) AND TYPE# IN (1, 9, 96, 112);
SGA 정보
SQL*Plus에서 SHOW SGA 명령어
SQL> SHOW SGA; Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145750508 bytes Database Buffers 25165824 bytes Redo Buffers 262144 bytes
Total System Global Area
SGA_MAX_SIZE =[ Fixed Size + Variable Size +Database Buffers + Redo Buffers ]
- SGA를 구성하는 영역 크기의 합계로 SGA_MAX_SIZE 파라미터로부터 영향 받음.
Fixed Size
- - 데이터베이스나 인스턴스의 상태를 저장하는 영역으로, 백그라운드 프로세스가 액세스 하는 영역
- - 사용자 데이터는 저장되지 않고, 일반적으로 100k 미만의 영역을 차지함
Variable Size
[ SHARED_POOL_SIZE +JAVA_POOL_SIZE +LARGE_POOL_SIZE + ?? ]
- 이 영역의 크기는 SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE 파라미터로부터 영향을 받음
Database Buffers
[ DB_CACHE_SIZE + DB_nK_CACHE_SIZE +DB_KEEP_CACHE_SIZE +DB_RECYCLE_CACHE_SIZE ]
- 데이터파일로 부터 읽어 들인 데이터 블록 내용을 저장하는 영역으로 DB_CACHE_SIZE 파라미터로부터 영향
Redo Buffers
- 데이터베이스에 가해진 모든 변경 사항에 대한 내역을 저장하는 Redo Log Buffer의 크기로 LOG_BUFFER 파라미터로부터 영향
V$SGAINFO 뷰 조회
SQL> SELECT * FROM V$SGAINFO; NAME BYTES RES -------------------------------- ---------- --- Fixed SGA Size 2057528 No Redo Buffers 14716928 No Buffer Cache Size 1593835520 Yes Shared Pool Size 469762048 Yes Large Pool Size 16777216 Yes Java Pool Size 16777216 Yes Streams Pool Size 33554432 Yes Granule Size 16777216 No Maximum SGA Size 2147483648 No Startup overhead in Shared Pool 335544320 No Free SGA Memory Available 0
- Internal SGA overhead 크기가 10G부터 shared pool size에 포함되었으며 자동 memory 관리 기능 사용하지 않고 수동 크기 설정 시 최소한 overhead 크기보다는 커야 합니다.
- 그렇지 않으면 startup 시 ORA-00371에러가 발생하며 startup 실패 합니다.
- 첨부파일 Metalink Doc ID 351018.1 : “Minimum for SHARED POOL SIZE.docx” 참조.
[ORA-00371] 00371, 00000, "not enough shared pool memory, should be atleast %s bytes" Cause: Init.ora parameter shared_pool_size is too small Action: Increase the parameter value
SGA 변경 이력 정보 조회
V$SGA_RESIZE_OPS
- SGA 메모리 변경이력을 확인하여 최대로 증가한 값을 기준으로 수동 세팅 설정
- SHRINK : 감소 - GROW : 증가
SELECT COMPONENT , OPER_TYPE , FINAL_SIZE/1024/1024/1024 FINAL_SIZE_GB , TO_CHAR(START_TIME,'YYYYMMDD HH24:MI:SS') STARTED FROM V$SGA_RESIZE_OPS WHERE STATUS='COMPLETE' ORDER BY STARTED DESC, COMPONENT;
V$MEMORY_RESIZE_OPS
- SGA TARGET,PGA TARGET 증가/감소 이력 조회
SELECT COMPONENT , OPER_TYPE , OPER_MODE , PARAMETER , ROUND(INITIAL_SIZE/1024/1024/1024,2) INITIAL_SIZE_GB , ROUND(TARGET_SIZE/1024/1024/1024 ,2) TARGET_SIZE_GB , ROUND(FINAL_SIZE/1024/1024/1024,2) FINAL_SIZE_GB , START_TIME , END_TIME , CON_ID FROM V$MEMORY_RESIZE_OPS ORDER BY start_time , component;
SGA 메모리 사이즈 변경
- 11g 의 경우 SGA_TARGET 파라미터를 설정하면 나머지 값들을 적절하게 자동으로 설정해줌 (ASMM)
-- 메모리 증가 설정 변경 alter system set memory_max_target=4G scope=spfile; alter system set memory_target=4G scope=spfile; -- DB 재기동 shutdown immediate; startup;
- 만약 MEMORY_MAX_TARGET을 설정하지 않고 MEMORY_TARGET을 설정했다면, 데이터베이스는 자동으로 MEMORY_MAX_TARGET값을 MEMORY_TARGET 값으로 설정.
- 반대의 경우라면, MEMORY_TARGET은 0으로 설정되고 DB가 기동된 이후에 유동적으로 MEMORY_TARGET값을 변경할 수 있으며, 그 값은 MEMORY_MAX_TARGET 을 넘어선 안 된다.
-- 재부팅 필요없이 바로 적용 됨 alter system set sga_max_size=2G scope=spfile; alter system set sga_target=2G scope=spfile;
ASMM 미사용시
[SGA Size 산정] DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE+ SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + LOG_BUFFER + 1MB
※ 대상 초기화파라미터는 SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, DB_CACHE_SIZE, STREAMS_POOL_SIZE 가 있다.
sga_target = 0 => ASMM 를 사용하지 않겠다는 의미
SQL> alter system set LARGE_POOL_SIZE='32M' SCOPE=spfile;
SQL> alter system set JAVA_POOL_SIZE='50M' SCOPE=spfile;
SQL> ALTER SYSTEM SET DB_CACHE_SIZE=2000M;
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=500M;
- ASSM 수동 작동시 최적화된 값 구하기
select lower('SGA_TARGET=0') SGA_MENUAL from dual union all select case component when 'java pool' then lower('JAVA_POOL_SIZE=') when 'large pool' then lower('LARGE_POOL_SIZE=') when 'shared pool' then lower('SHARED_POOL_SIZE=') when 'DEFAULT buffer cache' then lower('DB_CACHE_SIZE=') end||decode(current_size,granule_size,0,trunc(current_size/1024/1024,0)) ||decode(current_size,granule_size,'','M') from V$SGA_DYNAMIC_COMPONENTS where component in ('shared pool','large pool','java pool','DEFAULT buffer cache');
현재 SGA SIZE 확인
- V$SGA
- V$SGAINFO
select * from v$sga; NAME VALUE -------------------- ---------- Fixed Size 744168 ==> 백그라운드 프로세스 액세스 하는 영역 Variable Size 520093696 ==> SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE Database Buffers 2147483648 ==> DB_CACHE_SIZE Redo Buffers 1323008 ==> Redo Log Buffer
select * from v$sgainfo;
- SGA 영역 확인 (parameter)
SELECT NAME , DISPLAY_VALUE FROM V$PARAMETER WHERE NAME IN ( 'pga_aggregate_target' , 'db_cache_size' , 'shared_pool_size' , 'shared_pool_reserved_size' , 'log_buffer' , 'java_pool_size' , 'large_pool_size' , 'sga_max_size' );
PGA
PGA 정보
WORKAREA_SIZE_POLICY = AUTO/MANUAL; -- 관리방식
MANUAL 방식
AUTO 방식
PGA관련 WAIT EVENT
- 정렬작업을 위해 임시 영역을 읽고 쓰는 경우에는 direct path read temp, direct path write temp 이벤트를 대기.
- Direct Path I/O는 정렬해야 할 데이터가 정렬을 위해 할당된 PGA 메모리 영역보다 큰 경우에 발생.
- 정렬작업 시 direct path read temp, direct path write temp 대기가 광범위하게 나타난다면 PGA 메모리 영역의 추가 할당
- PGA_AGGREGATE_TARGET
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=10G SCOPE=BOTH;
- _PGA_MAX_SIZE
ALTER SYSTEM SET "_PGA_MAX_SIZE"=1G SCOPE=BOTH;
SQL 메모리 정렬방식
- - Optimal Pass Sort
- 모든 작업이 메모리상에서 이루어지는 경우
- - One Pass Sort
- 프로세스에 할당된 작업공간의 크기 내에서 디스크상으로 한 번에 정렬이 이루어지는 경우
- - Multi Pass Sort
- 정렬작업 중간에 정렬결과를 TEMP, TBS 내 정렬 세그먼트(Sort Segment) 영역에 읽고 쓰는 작업이 여러 번 발생하는 경우
- PGA 관련 대기 이벤트에서 Multi Pass Sort 작업 중 direct path read temp, direct path write temp 대기 이벤트가 광범위하게 발생하는 경우 PGA_AGGREGATE_TARGET의 값을 증가 해야함.
PGA SIZE 권장 사항 및 설정 방법
PGA 파라미터 정보조회
SQL> show parameter pga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 1073741824
파라미터(V$PARAMETER)
SELECT NAME , DISPLAY_VALUE FROM V$PARAMETER WHERE NAME IN ( 'pga_aggregate_target' );
PGA_AGGREGATE_TARGET 설정 샘플
- - For OLTP
- PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
- - For DSS
- PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=655M SCOPE=BOTH;