※PGA는 프로세스 메모리를 담고 있으며, UGA도 함께 포함할 수 있다. PGA 메모리의 다른 영역은 일반적으로 in-memory 정렬, 비트맵 머징(merging) 그리고 해싱과 같은 작업에 사용된다.
※오라클9i에서 shared server 커넥션 방식을 사용하면 수동PGA메모리 관리방법만 사용(10gr1부터 가능)
1. 수동 PGA 메모리 관리
세션에 할당된 메모리 외에 PGA의 크기에 가장 큰 영향을 주는 파라미터
SORT_AREA_SIZE - SORT_AREA_RETAINED_SIZE = PGA에 할당
SORT_AREA_RETAINED_SIZE = UGA에 할당
EX1) SORT_AREA_SIZE(512KB), SORT_AREA_RETAINED_SIZE(256KB) 일 경우
-> 서버 프로세스는 최초 쿼리의 데이터 정렬시 512KB를 사용하고, 정렬작업 완료 시 정렬 영역은 256KB로 줄어들고,
256KB를 초과하는 영역은 템포러리 테이블스페이스에 기록된다.
EX2) HASH_AREA_SIZE 는 커다란 집한과 또 다른 집합을 조인할때 사용하는 해시 조인에서 많이 활용 된다.
-> 작은쪽이 메모리에 해쉬되고, 해쉬영역을 초과하는 부분은 템포러리 테이블스페이스에 저장 될 것이다.
스크립트
--1.정렬 영역의 크기는 64KB, 1MB, 1GB 를 사용하며, 아래의 스크립트를 run_query.sql 로 저장하여 사용한다
--run_query.sql
connect /
set serveroutput off
set echo on
column sid new_val SID
select sid from v$mystat where rownum = 1;
alter session set workarea_size_policy=manual;
alter session set sort_area_size = &1;
prompt run @reset_stat &SID and @watch_stat in another session here!
pause
set termout off
select * from t order by 1,2,3,4;
set termout on
prompt run @watch_stat in another session here!
Pause
and then run:
--2.관찰을 위한 작은 테이블을 리셋하고 SQL*Plus 변수에 SID를 설정
--reset_stat.sql
drop table sess_stats;
create table sess_stats
( name varchar2(64), value number, diff number);
variable sid number
exec :sid :=&1
--3.스크립트에 세션의 통계값을 INSERT하고 이후에 통계값을 갱신하기 위해
MERGE SQL문을 생성
--watch_stat.sql
merge into sess_stats
using
(
select a.name, b.value from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = :sid
and (a.name like '%ga %' or a.name like '%direct temp%')
) curr_stats
on (sess_stats.name = curr_stats.name)
when matched then
update set diff = curr_stats.value - sess_stats.value,
value = curr_stats.value
when not matched then
insert (name, value, diff)
values (curr_stats.name, curr_stats.value, null)
/
select name,
case when name like '%ga %'
then round(value/1024,0)
else value
end kbytes_writes,
case when name like '%ga %'
then round(diff / 1024,0)
else value
end diff_kbytes_writes
from sess_stats
order by name;
1-0. ALL_OBJECTS 테이블의 복사본을 생성
drop table t;
create table t as select * from all_objects;
exec dbms_stats.gather_table_stats( user, 'T');
test 1-1. <세션1>
scott@BWD> @run_query 65536 --정렬 영역의 크기를 64KB로 설정
Connected.
scott@BWD> column sid new_val SID
scott@BWD> select sid from v$mystat where rownum = 1;
SID
---------- -- sid가 174임을 확인
174
scott@BWD> alter session set workarea_size_policy=manual; --PGA메모리 관리방법을 수동으로 설정
Session altered.
scott@BWD> alter session set sort_area_size = &1;
old 1: alter session set sort_area_size = &1
new 1: alter session set sort_area_size = 65536
Session altered.
scott@BWD> prompt run @reset_stat &SID and @watch_stat in another session here! --또다른 세션에서 두가지 다른 스크립트를 실행하라고 지시
run @reset_stat 174 and @watch_stat in another session here!
scott@BWD> pause
1-2. <세션2>
scott@PRD> @reset_stat 174
Table dropped.
Table created.
PL/SQL procedure successfully completed.
scott@PRD> @watch_stat
6 rows merged.
NAME KBYTES_WRITES DIFF_KBYTES_WRITES
---------------------------------------------------------------- ------------- ------------------
physical reads direct temporary tablespace 0 0
physical writes direct temporary tablespace 0 0
session pga memory 621
session pga memory max 621
session uga memory 284
session uga memory max 284
6 rows selected.
-- 이스크립트를 시작하기 전 UGA에는 약 284KB의 데이터가 있고 PGA에는 621KB의 데이터가 있음을 알 수 있다.
– dedicated server 방식에서는 UGA가 완전히 PGA에 포함되기 때문에 PGA + UGA = 621KB를 사용하는 것이 된다.
– shared server 방식에서는 UGA가 SGA로부터 할당되며, PGA는 shared server 프로세스 안에 있다. 그래서 위의 쿼리에서 마지막 로우를 얻고 난 직후 shared server 프로세스가 다른 사람이 점유하여 사용하고 있을 수도 있다.(정확한 테스트가 불가능하다)
– shared server 방식에서 PGA는 더는 특정 사용자의 것이 아니므로 기술적으로 284KB의 메모리를 사용하고 있을을 알수있다.(예외적으로, 실제 그 쿼리가 실행중이라면 그때는 PGA사용량과 UGA사용량을 합한 905KB만큼이 사용되고 있는것이다.)
1-3. <세션1>
scott@BWD> pause
--엔터
scott@BWD> set termout off
scott@BWD> prompt run @watch_stat in another session here!
run @watch_stat in another session here!
1-4. <세션2>
scott@PRD> @watch_stat
6 rows merged.
NAME KBYTES_WRITES DIFF_KBYTES_WRITES
---------------------------------------------------------------- ------------- ------------------
physical reads direct temporary tablespace 0 0
physical writes direct temporary tablespace 0 0
session pga memory 1389 768
session pga memory max 21741 21120
session uga memory 1051 767
session uga memory max 19063 18779
--session xxx memory 값은 방금 사용한 메모리가 얼마나 되는지를 나타낸다.
--session xxx memory max 값은 세션이 쿼리를 처리하는 동안에 사용한 최고값을 나타낸다.
--메모리 사용량이 증가하였다.
--많은 양의 데이터 정렬작업을 포함하는 쿼리를 처리하는동안 UGA는 284KB => 348KB로 64KB증가하였다.
test 2-1. <세션1>
@run_query 1048576
Connected.
scott@PRD> column sid new_val SID
scott@PRD> select sid from v$mystat where rownum = 1;
SID
----------
174
scott@PRD> alter session set workarea_size_policy=manual;
Session altered.
scott@PRD> alter session set sort_area_size = &1;
old 1: alter session set sort_area_size = &1
new 1: alter session set sort_area_size = 1048576
Session altered.
scott@PRD> prompt run @reset_stat &SID and @watch_stat in another session here!
run @reset_stat 174 and @watch_stat in another session here!
scott@PRD> pause
2-2. <세션2>
scott@PRD> @reset_stat 174
Table dropped.
Table created.
PL/SQL procedure successfully completed.
scott@PRD> @watch_stat
6 rows merged.
NAME KBYTES_WRITES DIFF_KBYTES_WRITES
---------------------------------------------------------------- ------------- ------------------
physical reads direct temporary tablespace 0 0
physical writes direct temporary tablespace 0 0
session pga memory 621
session pga memory max 621
session uga memory 284
session uga memory max 284
6 rows selected.
2-3. <세션1>
엔터
2-4. <세션2>
scott@PRD> @watch_stat
6 rows merged.
NAME KBYTES_WRITES DIFF_KBYTES_WRITES
---------------------------------------------------------------- ------------- ------------------
physical reads direct temporary tablespace 0 0
physical writes direct temporary tablespace 0 0
session pga memory 749 128
session pga memory max 21805 21184
session uga memory 412 128
session uga memory max 19063 18779
6 rows selected.
3-1. <세션1>
scott@PRD> @run_query 1073741820
scott@PRD> connect scott/tiger
Connected.
scott@PRD> set serveroutput off
scott@PRD> set echo on
scott@PRD> column sid new_val SID
scott@PRD> select sid from v$mystat where rownum = 1;
SID
----------
174
scott@PRD> alter session set workarea_size_policy=manual;
Session altered.
scott@PRD> alter session set sort_area_size = &1;
old 1: alter session set sort_area_size = &1
new 1: alter session set sort_area_size = 1073741820
Session altered.
scott@PRD> prompt run @reset_stat &SID and @watch_stat in another session here!
run @reset_stat 174 and @watch_stat in another session here!
scott@PRD> pause
3-2. <세션2>
scott@PRD> @reset_stat 174
Table dropped.
Table created.
PL/SQL procedure successfully completed.
scott@PRD> @watch_stat
6 rows merged.
NAME KBYTES_WRITES DIFF_KBYTES_WRITES
---------------------------------------------------------------- ------------- ------------------
physical reads direct temporary tablespace 0 0
physical writes direct temporary tablespace 0 0
session pga memory 621
session pga memory max 621
session uga memory 284
session uga memory max 284
6 rows selected.
3-3. <세션1>
--엔터
scott@PRD> set termout off
scott@PRD> prompt run @watch_stat in another session here!
run @watch_stat in another session here!
scott@PRD> pause
3-4. <세션2>
scott@PRD> @watch_stat
6 rows merged.
NAME KBYTES_WRITES DIFF_KBYTES_WRITES
---------------------------------------------------------------- ------------- ------------------
physical reads direct temporary tablespace 0 0
physical writes direct temporary tablespace 0 0
session pga memory 749 128
session pga memory max 21741 21120
session uga memory 412 128
session uga memory max 19063 18779
세션에 할당한 정렬영역의 크기를 증가시키고 커다란 정렬작업을 수행하면 세션이 사용하는 메모리의 양은 증가할 것인데,
PGA 메모리가 증가했다가 감소하거나 혹은 시간이 지나도 변함 없이 일정한 수준을 유지하는 것을 확인할 수있다.
9i와 10g에서는 운영체제 고유의 메모리 할당 call을 사용하는 새로운 방법으로 필요로 하는 작업 영역을 덧붙이고 해제한다.
8i는 PGA메모리 크기가 줄어들지 않는다.(SESSION PGA MEMORY는 항상 SESSION PGA MEMORY MAX와 같다.)
SORT_AREA_SIZE를 10MB 로 설정 했다면, 한명당 최대 10MB까지 정렬작업시 메모리를 사용할수 있다. 100명의 사용자가 동일 작업을
한다면 1,000MB 까지 정렬작업으로 메모리를 사용 할 것이다. 이때 가용메모리가 500MB라면 한 명의 사용자가 사용할때는 충분하지만,
100명의 사용자가 작업을 한다면 훨씬 적은 메모리를 각각 사용 할 수 밖에 없다.
TEST <1>
@stats.sql -- watch_stat.sql을 조금 수정 하여 인스턴스 전체 통계와 하나의 세션을 위한 세션 통계 저장
drop table sess_stats;
create table sess_stats
as
select name, value, 0 active
from
(
select a.name, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = (select sid from v$mystat where rownum=1)
and (a.name like '%ga %'
or a.name like '%direct temp%')
union all
select 'total: ' || a.name, sum(b.value)
from v$statname a, v$sesstat b, v$session c
where a.statistic# = b.statistic#
and (a.name like '%ga %'
or a.name like '%direct temp%')
and b.sid = c.sid
and c.username is not null
group by 'total: ' || a.name
);
NAME : V$SESSTAT의 PGA와 UGA에 대한 정보 및 템포러리 테이블스페이스에 기록하는 것과 마찬가지로 데이터베이스 인스턴스에 대한 메모리 정보를 담고 있다.
VALUE : 결과값
ACTIVE : 실제 활동하고 있는 다른 세션의 수.
<2>
@single_load --해당 세션이 수행한 디스크에서 수행한 정렬작업과 함께 PGA/UGA메모리 설정에 대한 모든 사항에 대해 결과를 수집한다.
session uga memory
session uga memory max
session pga memory
session pga memory max
physical reads direct temporary tablespace
physical writes direct temporary tablespace
total: physical reads direct temporary tablespace
total: physical writes direct temporary tablespace
total: session pga memory
total: session pga memory max
total: session uga memory
total: session uga memory max
<3>
scott@PRD> @report
scott@PRD> column active format 999
scott@PRD> column pga format 999.9
scott@PRD> column "tot PGA" format 999.9
scott@PRD> column pga_diff format 999.99
scott@PRD> column "temp write" format 9,999
scott@PRD> column "tot writes temp" format 99,999,999
scott@PRD> column writes_diff format 9,999,999
scott@PRD> select active,
2 pga,
3 "tot PGA",
4 "tot PGA"-lag( "tot PGA" ) over (order by active) pga_diff,
5 "temp write",
6 "tot writes temp",
7 "tot writes temp"-lag( "tot writes temp" ) over (order by active) writes_diff
8 from (
9 select *
10 from (
11 select active,
12 max( decode(name,'session pga memory',val) ) pga,
13 max( decode(name,'total: session pga memory',val) ) as "tot PGA",
14 max( decode(name,'physical writes direct temporary tablespace',val) ) as "temp write",
15 max( decode(name,'total: physical writes direct temporary tablespace',val) ) as "tot writes temp"
16 from (
17 select active,
18 name,
19 case when name like '%ga mem%' then round(value/1024/1024,1) else value end val
20 from sess_stats
21 where active < 225
22 )
23 group by active
24 )
25 )
26 order by active
27 /
ACTIVE PGA tot PGA PGA_DIFF temp write tot writes temp WRITES_DIFF
------ ------ ------- -------- ---------- --------------- -----------
0 2.2 25.7 0 0
1 22.8 48.8 23.10 0 0 0
<1>
#sh gen_load.sh
for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
sqlplus scott/tiger @gen_load.sql &
done
<2>
@single_load
@single_load
<3>
@report
ACTIVE PGA tot PGA PGA_DIFF temp write tot writes temp WRITES_DIFF
------ ------ ------- -------- ---------- --------------- -----------
0 2.2 25.7 0 0
1 22.8 48.8 23.10 0 0 0
2 21.6 68.9 20.10 0 0 0
29 2.4 163.5 94.60 0 0 0
--몇번수행
<1><2><3>
# sh gen_load.sh
@single_load
@report --Active로 ordery by
ACTIVE PGA tot PGA PGA_DIFF temp write tot writes temp WRITES_DIFF
------ ------ ------- -------- ---------- --------------- -----------
0 2.2 25.7 0 0
1 22.8 48.8 23.10 0 0 0
2 21.6 68.9 20.10 0 0 0
29 2.4 163.5 94.60 0 0 0
40 1.6 580.4 416.90 0 5,426 5,426
41 1.7 582.4 2.00 0 18,342 12,916
43 21.1 223.7 -358.70 0 0 -18,342 ==>TEMP TABLESPACE를 사용하지않고 ORA-01652 발생
46 7.3 192.0 -31.70 0 0 0
정렬작업이 아닌 다른작업(단지 로그인 만 했을 때도 )들도 PGA 메모리를 사용한다.
PGA_AGGREGATE_TARGET을 사용한 메모리 제어
PGA메모리의 총량을 제어하기 위해서 PGA_AGGREGATE_TARGET을 사용,
그러나 메모리의 총량보다 넘게 사용 될 수도 있다.
작업영역이 PGA메모리만으로 이루어지는 것이 아니기 때문
scott@PRD> create or replace package demo_pkg
2 as
3 type array is table of char(2000) index by binary_integer;
4 g_data array;
5 end;
6 /
현재 PGA/UGA에서 얼마나 많은 메모리를 사용하고있는지 측정
scott@PRD> select a.name, to_char(b.value, '999,999,999') bytes,
2 to_char(round(b.value/1024/1024,1), '99,999.9' ) mbytes
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 and a.name like '%ga memory%';
NAME BYTES MBYTES
---------------------------------------------------------------- ------------ ---------
session uga memory 618,040 .6
session uga memory max 2,187,832 2.1
session pga memory 1,029,336 1.0
session pga memory max 2,995,416 2.9
--이쿼리를 수행한 결과를 담기위해 PGA메모리를 2.9정도 사용하였다.
--초기메모리 현황과 비교하기위해 256MB의 PGA_AGGREGATE_TARGET크기안에서 T테이블 쿼리 실행
scott@PRD> select * from t order by 1,2,3,4;
181574 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2236 consistent gets
0 physical reads
0 redo size
10503386 bytes sent via SQL*Net to client
133632 bytes received via SQL*Net from client
12106 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
181574 rows processed
NAME BYTES MBYTES
---------------------------------------------------------------- ------------ ---------
session uga memory 1,337,528 1.3
session uga memory max 22,202,680 21.2
session pga memory 5,289,176 5.0
session pga memory max 26,850,520 25.6 --25.6MB의 실제 메로리를 사용
--메모리에서 정렬 결과를 기록
--패키지 안에있는 CHAR배열을 채우는 작업
scott@PRD> begin
2 for i in 1 .. 200000
3 loop
4 demo_pkg.g_data(i) := 'x';
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
scott@PRD> select a.name, to_char(b.value, '999,999,999') bytes,
2 to_char(round(b.value/1024/1024,1), '99,999.9' ) mbytes
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 and a.name like '%ga memory%';
NAME BYTES MBYTES
---------------------------------------------------------------- ------------ ---------
session uga memory 623,760,056 594.9
session uga memory max 623,825,464 594.9
session pga memory 628,995,288 599.9
session pga memory max 628,995,288 599.9
--PGA_AGGREGATE_TARGET의 최대값을 초과
--디스크에서 정렬 결과를 기록
scott@PRD> set autotrace traceonly statistics;
select * from t order by 1,2,3,4;scott@PRD>
181574 rows selected.
Statistics
----------------------------------------------------------
2380 recursive calls
302 db block gets
3328 consistent gets
2286 physical reads
41512 redo size
10503386 bytes sent via SQL*Net to client
133632 bytes received via SQL*Net from client
12106 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
181574 rows processed
set autotrace off
수동/자동 메모리관리기법의 선택
자동PGA메모리 관리를 기본으로 사용하는 것을 선호
상황에따라 ALTER SESSION명령어를 사용하여 가능한 모든 자원을 사용.
PGA_AGGREGATE_TARGET는 실제 메모리의 40%가 오라클의 권고 사항