[메모리 관리]
library hit ratio 85%이상 권고
library hit ratio 가 너무 낮게 나오면 = hard parsing이 많다면 문제점이 2가지 있다.
1. 메모리 문제 : 사이즈가 작으면 수행되는 SQL의 실행계획을 충분히 저장하지 못해서 hard parsing이 많이 발생할 수 있다. 해결하려면 shared pool size를 늘린다. ASMM환경이면 sga_target(sga 영역의 총 크기)의 size를 증가시킨다.
2. literal SQL 문제 : 변수처리되지 않고 상수항을 그대로 노출시킨 형태의 SQL을 말함. cursor_sharing mode 가 exact 인 경우 상수항까지 모두 일치해야 실행계획을 공유하므로 literal SQL은 hard parsing을 유발함. 해결방법은 literal SQL 사용금지(개발자의 소스 변경), 아니면 cursor_sharing mode 를 force로 변경.
[동적 메모리 관리 (Dynamic SGA)]
DB online 중에 메모리의 일부 영역을 변경할 수 있는 기능.
pfile, spfile 환경 모두 가능.
1) pfile : 현재 메모리에만 변경 가능, restart 시 원복. pfile에서는 scope 로 옵션 지정하는 그런 거 없음.
2) spfile : 현재 메모리에만 변경 가능, 파라미터 파일에도 기록 가능.(scope=[spfile|both])
alter system set shared_pool_size = 200m;memory_target, sga_target, pga_aggregate_target 모두 동적 변경 가능.
* redo log buffer size 같은 파라미터들은 동적 변경이 불가.
[자동 메모리 관리]
1. ASMM(Automatic Shared Memory Management)
– SGA내 메모리 영역들을 자동으로 관리
– PGA 자동 관리
– sga_target으로 할당되는 SGA 전체 사이즈를 관리
– pga_aggregate_target으로 PGA 전체 사이즈를 관리
** 관련 파라미터
1) sga_target : 자동으로 관리하는 sga 전체 사이즈
2) sga_max_size : sga_target의 최대 사이즈 limit
3) pga_aggregate_target : 전체 pga 사이즈
4) _pga_max_size : 각 세션별 최대 pga 사이즈. 정렬을 수행할 때 pga에서 우선 정렬해보고 공간 모자르면 그때 temp tbs에서 수행하는데 그러면 속도가 많이 느려진다. 그래서 현업에서는 딱히 최대 pga size를 지정하지 않는다. 그리고 파라미터 앞에 _ 붙어있는 거는 오라클에서 별로 권장하지 않는 기능이다.
2. AMM(Automatic Memory Management)
– 전체 메모리를 자동으로 관리
– SGA와 PGA를 하나의 메모리 영역으로 보고 자동 관리
– memory_target으로 전체 메모리 사이즈를 관리
– memory_max_target보다 memory_target이 더 작아야 함. limit 느낌. 더 큰 값으로 변경하려고 하면 안 바뀐다.
– memory_target 변경 시 memory_max_target값도 확인해야 한다.
– max가 붙은 모든 파라미터는 동적 변경 불가해서 재기동해야만 반영됨.
– target이 붙은 모든 파라미터는 동적 변경 가능하다.
** 관련 파라미터
1) memory_target
2) memory_max_target
[실습]
1. 파라미터 조회
select name, value, display_value
from v$parameter
where name in ('memory_target','memory_max_target','sga_target','pga_aggregate_target');
-- 출력 --
sga_target 0 0
memory_target 1258291200 1200M
memory_max_target 1258291200 1200M
pga_aggregate_target 0 0
-- memory_target=0이면 ASMM환경
-- memory_target!=0이면 AMM환경
-- memory_target, sga_target, pga_aggregate_target 모두 동적 변경 가능2. AMM 환경일 경우 memory_target size 증가
alter system set memory_target=900m;
-- 난 잘 됐음. 왜냐면 내 memory_max_target이 1200m이어서. 다른 사람들은 원래 700언저리여서 에러났음.
-- 만약 에러나는 경우여도 scope=spfile; 추가하면 변경은 된다. max 파라미터 값도 똑같이 키워주고 재기동해야 반영됨.
alter system set memory_max_target=900m scope=spfile;[memory_target만 변경해서 DB 안 올라오는 경우]
memory_target만 spfile을 수정하고 memory_max_target를 수정 안 한채로 재기동하면 DB 안 올라옴. 아예 nomount 단계도 못 감.
-- 1. pfile 생성
create pfile from spfile;
-- 2. vi initdb1.ora
*.memory_target을 수정하든지
*.memory_max_target을 수정하든지 함.
-- 3. spfile 지우기
rm spfiledb1.ora
-- 4. db startup -> pfile 환경으로 startup
-- 5. spfile 생성
create spfile from pfile;
-- 6. db 재기동 -> spfile 환경으로 만들기 위함.
shutdown immediate
startup[AMM 환경에서 값 설정]
sga_target, pga_aggregate_target 값은 각 영역에 대한 최소 limit을 설정하는 것.
1. AMM/ASMM 환경일 때 sga, pga 할당 사이즈 조회
select component, current_size/1024/1024
from v$memory_dynamic_components
where component in ('SGA Target','PGA Target');
-- SGA = 540m, PGA = 360m** memory_target = sga_target + pga_aggregate_target
memory_target은 sga_target + pgs_aggregate_target 이상으로 설정 가능.
2. 수동으로 sga_target 수정
sga_target limit 값을 조절하는 것.
alter system set sga_target = 700m;
-- 다시 조회
select component, current_size/1024/1024
from v$memory_dynamic_components
where component in ('SGA Target','PGA Target');
-- SGA = 700m, PGA = 200m[각 메모리 영역 설정]
자동 메모리 관리 환경(AMM, ASMM)일 경우 수동으로 파라미터 조절하는 것의 의미는 ‘최소값 지정’이다.
하지만 시스템이 판단하기에 의미 없는 설정일 경우 반영되지 않을 수 있다.
[shared pool size 변경]
1. 현재 설정값 조회
select name, value/1024/1024
from v$parameter
where name in ('sga_target',
'sga_max_size',
'shared_pool_size',
'memory_target',
'pga_aggregate_target');
-- 출력 --
sga_max_size 900
shared_pool_size 0
sga_target 700
-- shared_pool_size = 0 : 자동 관리 중이라는 의미2. 할당값 조회
select component, current_size/1024/1024
from v$memory_dynamic_components
where component in ('shared pool');
-- 2083. 최소 할당값 변경
alter system set shared_pool_size = 209m;
-- 확인
select component, current_size/1024/1024
from v$memory_dynamic_components
where component in ('shared pool');
-- 212로 나옴
-- 기존 할당값에 +1한 size으로 변경시도해도 4씩(granule(메모리 변경 단위)) 늘어난다.** granule : 메모리 변경 단위
if SGA < 1GB then 4MB else 16MB
[ASMM 환경]
SGA와 PGA를 각각 별도의 메모리 영역으로 보고 각각 관리.
SGA 내 메모리 공간을 자동으로 관리.
자동 메모리 관리 시 하위 파라미터 고정 가능(최소 limit size 지정의 의미를 가짐)
sga_target 내에서 sga 하위 영역을 자동으로 관리
pga_aggregate_target 내에서 각 세션들의 pga를 관리
sga_max_size 보다 큰 값으로 sga_target 설정 불가
[실습 : AMM -> ASMM 환경으로 변경]
1. 메모리 확인
select name, value, display_value
from v$parameter
where name in ('sga_max_size',
'sga_target',
'pga_aggregate_target',
'memory_target');2. 메모리 변경
-- sga_target, pga_aggregate_target을 먼저 수정해줘야 memory_target을 수정할 수 있다.
alter system set sga_max_size=700m scope=spfile;
alter system set sga_target=700m;
alter system set pga_aggregate_target=200m;
alter system set memory_target=0m;3. db 재기동
[sga_target을 잘못 변경한 경우]
sga_target 변경 시 sga_max_size 이하값으로 변경해야 함.
sga_target 값을 sga_max_size 값보다 큰 값으로 변경 후 (scope=spfile) restart 시 장애 발생
1. sga_target 변경
alter system set sga_target=712m scope=spfile;2. DB 재기동
3. 복구
-- 1.
create pfile from spfile;
-- 2.
vi initdb1.ora
sga_max_size 를 712m 로 변경
-- 3.
rm spfiledb1.ora
-- 4.
startup
-- 5.
create spfile from pfile;
-- 6.
shutdown immediate
-- 7.
startup
show parameter spfile[pfile 환경일 때 메모리 동적 변경]
1. pfile 환경으로 변경
shutdown immediate
create pfile from spfile;
rm spfiledb1.ora
startup2. 메모리 사이즈 확인
select name, value/1024/1024
from v$parameter
where name in ('shared_pool_size');
-- 212m3. shared pool size 변경
alter system set shared_pool_size = 208m;
select name, value/1024/1024
from v$parameter
where name in ('shared_pool_size');
-- 208m4. restart
shutdown immediate
startup
select name, value/1024/1024
from v$parameter
where name in ('shared_pool_size');
-- 변경 반영 안 되고 다시 원래 212m로 돌아감5. restart 후에도 변경 유지시키기
shutdown immediate
vi initdb1.ora
208m으로 수정.
startup
select name, value/1024/1024
from v$parameter
where name in ('shared_pool_size');
-- 208m 출력됨.