Direct path read/write temp

EXEM Knowledge Base

(Direct path read temp에서 넘어옴)
Jump to: navigation, 찾기

목차

[편집] Basic Info

정렬작업을 위해 임시 영역을 읽고 쓰는 경우에는 direct path read temp, direct path write temp 이벤트를 대기한다. 이 대기이벤트들은 오라클 10g 이후에 분화된 것으로 오라클 9i까지는 direct path read, direct path write 대기로 관찰되었다. 정렬 세그먼트에서의 direct path I/O는 정렬해야 할 데이터가 정렬을 위해 할당된 PGA 메모리 영역보다 큰 경우에 발생한다. 따라서 정렬 작업시 direct path read temp, direct path write temp 대기가 광범위하게 나타난다면 정렬작업을 위한 메모리 영역을 추가로 할당함으로써 해소가 가능하다.

[편집] 어플리케이션 레이어

정렬이 필요한 SQL 문장들이 최적화되어 있는지 검토해보아야 한다. 불필요한 정렬 작업은 CPU 낭비, PGA 영역낭비, 디스크 I/O 낭비를 초래한다. UNION과 UNION ALL의 성능차이에서 알 수 있듯이 불필요한 정렬작업을 줄이는 것만으로 많은 문제를 해결할 수 있다.

[편집] 오라클 메모리 레이어

PGA는 정렬, 해시 조인(Hash Join), 비트맵(Bitmap) 연산 등의 수행을 위한 메모리 영역을 가지고 있는데, 이것을 작업공간(Work Area)이라고 부른다.

오라클 8i까지는 SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, CREATE_BITMAP_AREA_SIZE와 같은 파라미터를 이용해서 관리자가 직접 개별 작업공간의 크기를 조정해야 했다. 하지만 오라클 9i 이후에 추가된 PGA_AGGREGATE_TARGET 파라미터를 이용하면 이러한 작업 공간의 크기를 동적으로 관리할 수 있다. 오라클은 PGA_AGGREGATE_TARGET의 값을 세션마다 골고루 사용할 수 있게 분배한다.
모든작업이 메모리상에서 이루어지는 경우를 Optimal pass sort 라고 한다. 프로세스에 할당된 작업공간의 크기 내에서 디스크 상에서 한번에 정렬이 이루어지는 것을 One pass sort라고 부른다. 그 반대의 경우를 Multi pass sort라고 하는데, Multi pass sort가 발생하는 경우에는 정렬작업 중간에 정렬결과를 정렬 세그먼트(Sort Segment)영역에 읽고 쓰는 작업이 발생하게 되고 이로 인해 direct path read temp, direct path write temp 대기가 발생하게 된다. 만일 해당 대기가 광범위하게 발생한다면 PGA_AGGREGATE_TARGET의 값을 적절히 늘려줌으로써 문제를 해소할 수 있다.
PGA_AGGREGATE_TARGET 값을 설정할 때 한가지 주의할 것은 서버 프로세스가 실제 사용 가능한 메모리의 크기에 제한이 있다는 것이다. 즉 PGA_AGGREGATE_TARGET에 지정된 값의 일부분만을 개별 세션이 사용 가능하다. 오라클은 PGA_AGGREGATE_TARGET에 지정된 값에 따라 개별 프로세스에 할당가능한 최대 메모리영역을 계산하는데, 그 값은 히든 파라미터인 _SMM_MAX_SIZE에 저장되며 단위는 Kbyte이다.
_SMM_PX_MAX_SIZE는 Parallel Query 수행시 전체 슬레이브 세션들이 사용 가능한 최대 메모리 크기이다. V$SESSTAT 뷰에서 "session pga memory max" 값을 조회하면 세션이 실제로 사용한 최대 메모리 영역을 조회할 수 있다.

[편집] Parameter & Wait Time

[편집] Wait Parameters

direct path read/write temp 대기이벤트의 대기파라미터는 다음과 같다.

  • P1 : Absolute File#
  • P2 : Starting Block#
  • P3 : 블록수

[편집] Wait Time

I/O관련 이벤트이므로 타임아웃이 발생하지 않으며, I/O 가 완료될 때까지 소요된 시간을 나타낸다.

[편집] Check Point & Solution

[편집] Event Tip

[편집] PGA_AGGREGATE_TAREGET과 direct I/O

실제 PGA_AGGREGATE_TAREGET을 설정한 경우 개별 세션의 PGA값이 어떻게 지정되는지 확인해보자.

SQL> alter system set pga_aggregate_target = 200M;

SQL> @show_param max_size
_smm_max_size (Kbyte단위)
 40960

_smm_px_max_size
102400

-- 인덱스를 생성한다. 인덱스 생성시 내부 정렬작업이 발생한다.
SQL> create index pdml_idx on pdml_test(id);

SQL> @sesstat
stat_name 값을 입력하시오: 'session pga memory max'
==> 44920200

_SMM_MAX_SIZE * 1024 = 41943040로 session pga memory max 통계값과 거의 동일함을 알 수 있다. 비록 시스템 전체의 PGA 작업공간은 200M이지만, 개별 프로세스가 실제로 사용가능한 메모리는 40M로 제한된다. 만일, PGA_AGGREGATE_TARGET을 그대로 사용하면서 특정 세션에 대해서만 작업 공간의 크기를 크게 주고 싶다면, 해당 세션의 PGA 관리정책만을 변경하면 된다. 즉, alter session set workarea_size_policy = manual 로 변경한 후, alter session set sort_area_size = ... 을 이용해 필요한 만큼 큰 값을 주면 된다. 오라클 튜닝 가이드에서는 다음과 같이 PGA_AGGREGATE_TARGET의 값을 설정할 것을 권고하고 있다.

OS 레벨에서 20% 정도의 메모리를 사용한다고 가정하고, OLTP 시스템의 경우에는 여유메모리의 20% 정도를, DSS 시스템은 정렬작업이 많으므로 50% 정도를 사용하라는 의미이다. 위의 계산법은 절대적인 것은 아니지만, 메모리용량 산정이 기본 정책으로 활용 가능하다. 한가지 조심할 것은 불필요하게 많은 메모리를 할당하는 것은 바람직하지 않다는 것이다. 특히 물리적인 메모리 크기를 초과하게끔 PGA 메모리영역의 크기를 할당하면 페이지 아웃/페이지 인이 발생할 확률이 높아지게 된다. 페이징 현상이 자주 발생하면 시스템 성능이 크게 저하되는 현상이 생길 수 있다.

PGA_AGGREGATE_TARGET 파라미터의 값을 적절히 변경하는 경우 direct path I/O 대기가 얼마나 줄어드는지 테스트해보자.

-- PGA 전체크기를 10M로 변경
SQL> alter system set pga_aggregate_target = 10M;

-- 정렬 작업 수행
SQL> create index pdml_idx on pdml_test(id);
Index created.
Elapsed: 00:00:40.07

SQL> @my_sess_event
EVENT                                     TOTAL_    WAITS TIME_WAITED
-----------------------------------  -----------  --------------------------
SQL*Net message from client             22        3251
direct path read temp                     4593           7
log file sync                                        2           4
direct path write temp                      684           1
SQL*Net message to client                 23           0
direct path write                                 4           0
events in waitclass Other                     1           0

-- PGA 전체 크기를 1G로 늘린후 재수행한다. '
SQL> alter system set PGA_AGGREGATE_TARGET = 1G;
SQL> create index pdml_idx on pdml_test(id);
Index created.
Elapsed: 00:00:28.11

SQL> @my_sess_event
-- PGA_AGGREGATE_TARGET 값을 늘린 결과 direct path I/O가 사라졌다.
EVENT                                      TOTAL_WAITS  TIME_WAITED
------------------------------------  -----------------  ----------------
SQL*Net message from client             20                  8268
log file switch completion                     4                      33
log file sync                                        2                       8
SQL*Net message to client                 21                       0
events in waitclass Other                      1                      0
direct path write                                  4                       0

PGA_AGGREGATE_TARGET 값을 적절하게 설정해주는 경우, direct path I/O가 사라지고 이로 인해 direct path read temp, direct path write temp 대기현상이 완전히 사라지게 된다. 더불어 성능도 크게 개선되는 것을 확인할 수 있다.

[편집] Analysis Case

[편집] PGA_AGGREGATE_TARGET 정합성 분석

문제 분석기간 동안, 특정 SQL문이 동시에 여러 세션에서 수행되는 경우, 다음과 같이 direct path read/write 이벤트가 다량 발생하였다. 일반적으로 세션은 SQL문을 수행하면서 sorting, hash 조인 처리시 세션의 PGA 메모리 공간을 할당받아 처리하게 된다. 이번 사례의 경우, DB의 pga_aggregate_target 파라미터 값이 20M 로 작게 설정되어 있어 세션당 1M 밖에 할당받지 못해 대부분의 작업이 PGA 메모리 공간을 활용하지 못하고 temp segment 에서 이루어져 direct i/o 발생 빈도가 높게 나타났다.

그림:4_5_1.jpg

적정한 pga_aggregate_target 파라미터 값을 할당하기 위해서, v$pga_target_advice 뷰를 활용해 본다. 현재, 파라미터 값은 20M인데, 이 값으로는 PGA 메모리공간 hit율은 60% 이하로 매우 저조하다.

그림:4_5_2.jpg

PGA 공간을 증가 시켜서 문제 상황을 해결하였다. ( 20 M → 2048 M )

alter system set pga_aggregate_target = 2048m scope = both ;    


파라미터 변경 절차

1. pga_aggregate_target 파라미터를 2 Gbytes 정도로 설정한다.

2. _pga_max_size 값은 현재 설정값 (200 MB) 그대로 둔다.

3. _smm_max_size 값이 100 MB 로 자동 설정됨.

  _pga_max_size (200 MB) > 5% of pga_aggregate_target (100 MB) 일경우 에는 
  pga_aggregate_target 설정 값의 5%로 자동 설정됨. ( 2048 MB * 0.05 ) = 100 MB

4. _smm_px_max_size 값이 614 MB 로 자동 설정됨.

  _ pga_max_size (200 MB) > 5% of pga_aggregate_target (100 MB) 일경우 에는
  pga_aggregate_target 설정 값의 30%로 자동 설정됨. (2048 MB * 0.3 ) = 614 MB

5. 파라미터 값이 DB에 정상적으로 적용 되었는지 확인 한다.


파라미터 확인 스크립트

-- pga_aggregate_target 설정확인 script

select sid,name,value,DISPLAY_VALUE 
from v$spparameter
where name like '%pga%' ;

-- _smm_max_size , _smm_px_max_size, _pga_max_size 설정확인 script

select a.ksppinm, b.ksppstvl, a.ksppdesc
from x$ksppi a, x$ksppsv b
where a.indx = b.indx
 and a.inst_id = b.inst_id
 and a.ksppinm in ( '_smm_max_size','_smm_px_max_size','_pga_max_size')
order by ksppinm ;