PGA, 자동 PGA 메모리 관리

■ PGA 
- SQL 수행 도중 데이터 정렬이 필요할 떄 오라클은 PGA 메모리에 sort area 를 할당하며 완료여부에 따라 두 가지 유형으로 수행된다.
1) 메모리 소트(in memory sort): 전체 데이터의 정렬 작업을 메모리 내에서 완료. internal sort
2) 디스크 소트 (to dist sort): 할당받은 sort area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용. external sort 

#) sort area 내에서 데이터 정렬을 마무리 하는 것이 최적이나(optimal), 양이 많을 때는 정렬된 중간 결과 집합(sort run이라고도 한다)을 temporary tablesapce의 temp segment 에 임시 저장한다.

#1) optimal sort: sort 작업을 PGA 메모리 내에서 sort_area_size 안에서 이루어지는 것
#2) oneoptimal sort: sort 대상 집합(=: sort run)이 디스크에 한번만 쓰여지는 것
#3) multipass sort: sort 대상 집합(=: sort run)이 디스크에 여러번 쓰여짐으로써 디스크 i/o가 많이 발생하여 성능 저하된다. 
-> 성능 저하: 문장 튜닝 -> 메모리 튜닝 순으로 성능 저하를 해결한다.

■ 자동 PGA 메모리 관리 
- pga_aggregate_target parameter 를 기반으로 작업영역에 할당되는 PGA 메모리 양이 동적으로 조정된다. 
- 기본값 : 10MB 또는 SGA 크기의 20% 중에 더 큰 값으로 설정.(해석: 별도의 설정이 되어 있지 않는다면 기본값에서 큰 값으로 설정된다.)

alter session set workarea_size_policy = auto;

- 수동관리 (8i 버전) 설정 방법

alter session set workarea_size_policy = manual;
alter session set sort_area_size = 1M;
alter session set hash_area_size = 1M;
alter session set create_bitmap_area_size = 1M;
alter session set create_bitmap_area_size = 1M;

#) sort_area_size 확인

<sys sess>

select a.ksppinm name , b.ksppstvl value
from x$ksppi a, x$ksppsv b
where a.indx = b.indx
and lower(a.ksppinm) in ('workarea_size_policy', 'sort_area_size', '_smm_max_size');

※ 각 서버 프로세스가 사용 가능한 work area 최대 크기는  _smm_max_size 파라미터로 확인(kb 단위)

<hr sess>

create table emp_temp
as
select *
from employees, (select rownum no from dual connect by level <= 1000);

select count(*) from emp_temp;

 

#) 실행 계획 확인

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

 

#) 힌트 사용

select /*+ gather_plan_statistics */ * 
from (select rownum as id, employee_id from emp_temp order by employee_id) 
where id = 1;

 

#) 실행 계획

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

#) 설명
1) OMem: optimal sort에 필요한 예상 정렬 작업 영역의 크기
2) 1Mem: one pass sort에 필요한 예상 정렬 작업 영역의 크기
3) Used-Mem: 실제로 사용된 정렬 작업 영역의 크기
4) Used-Temp: 메모리가 부족하여 temporary space 를 사용된 공간크기(kb 단위)

5) Writes: 각 Operation이 disk에 write한 block한 수
6) Reads: 각 Operation이 disk에 read한 block한 수
7) Buffers: 각 Operation이 disk에 메모리에서 읽은 block한 수
8) A-Time: 실제 실행 시간
9) A-Rows: 각 Operation이 끝났을때 return되는 건수(실제)
10) E-Rows: 각 Operation이 끝났을때 return되는 건수(예상치)

---

<시나리오>

#) 사이즈 변경 

- workarea_size_policy manual 및 sort_area_size 사이즈 변경

alter session set workarea_size_policy = manual;
alter session set sort_area_size = 1048576;

#) select 문에 힌트 사용 및 실행 계획 확인 

select /*+ gather_plan_statistics */ * 
from (select rownum as id, employee_id from emp_temp order by employee_id) 
where id = 1;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

 

---

<시나리오>

#) 사이즈 = 0으로 설정 했으나, 오라클에서는 자체적으로 사이즈를 최저로 조절한다. 

alter session set sort_area_size = 0;

select /*+ gather_plan_statistics */ * 
from (select rownum as id, employee_id from emp_temp order by employee_id) 
where id = 1;

 

#) 실행 계획 확인

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

 

#) 동일한 sql_id 로 child_number가 다른 경우
<sys sess>

select sql_id, child_number
from v$sql
where sql_text like '%gather_plan_statistics%'
and sql_text not like '%v$sql%';

#) LCO의 child_number 확인.

- 같은 sql_id 로 여러개의 child 가 생성된 것을 확인할 수 있다.

select child_number, last_execution, last_memory_used, last_tempseg_size
from v$sql_workarea
where sql_id = '536t1zcg4gs6u';

 

---

<시나리오>

- trace 로 확인해보기 

#) hr sess

alter session set sort_area_size = 0;
alter session set tracefile_identifier = 'sort';
alter session set events '10046 trace name context forever, level 8';
select * from (select rownum as id, employee_id from emp_temp order by employee_id) where id = 1;
alter session set events '10046 trace name context off';

[oracle1@oracle ~]$ cd /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace
[oracle1@oracle trace]$ ls *sort.trc
ora11g_ora_5470_sort.trc
[oracle1@oracle trace]$ tkprof ora11g_ora_5470_sort.trc ex_sort01.txt sys=no

[oracle1@oracle trace]$ vi ex_sort01.txt
...
direct path write temp:  => sort 때문에 발생하는 대기 이벤트
direct path read temp: => sort 때문에 발생하는 대기 이벤트