1. HOME
  2. [종료]코어 오라클 데이터베이스 스터디
  3. 5차 코어 오라클 데이터베이스 스터디
  4. direct path read temp, direct path write temp

direct path read temp, direct path write temp (by xsoft) [2011.01.17]

1. 개요

1) 이벤트 원인

  • 정렬작업을 위해 임시 영역을 읽고 쓰는 경우 나타남.
  • 오라클 10g 이후로 분화된 것으로, 오라클 9i 까지는 'direct path read', 'direct path write' 이벤트로 관찰됨.
  • 정렬 세그먼트에서의 'direct path I/O'는 정렬해야 할 데이터가, 정렬을 위해 할당된 PGA 메모리 영역보다 큰 경우 발생됨.

2) 해결방법

  • 이 이벤트가 광범위하게 관찰될 경우 정렬작업을 위한 메모리 영역을 추가로 할당해야 함.
  • 아래에서는 어플리케이션과 메모리 레이어 영역에서 해결방안을 살펴본다.

2. 어플리케이션 레이어

1) 해결방법

  • 정렬이 필요한 SQL 문장들이 최적화되어 있는지 검토해 보아야 함.
  • 불필요한 정렬 작업은 CPU, PGA 영역, 디스크 I/O 낭비를 초래함.
  • 또한 UNION ALL을 사용이 가능함에도 불구하고, UNION을 사용하여 Sort를 발생시키는 것을 막아야 함.

3. 오라클 메모리 레이어

1) Work Area

  • PGA가 Sort, Hash Join, Bitmap 연산 등의 수행을 위한 메모리 영역을 'Work Area'라고 함.
  • 오라클 8i까지는 아래의 파라미터를, 관리자가 직접 수동으로 개별 작업공간의 크기를 조정했음.

SELECT A.KSPPINM  NAME,
       B.KSPPSTVL VALUE,
       B.KSPPSTDF DEF_YN,
       A.KSPPDESC DESCRIPTION
FROM   X$KSPPI  A,
       X$KSPPSV B
WHERE  A.INDX = B.INDX
AND    LOWER(A.KSPPINM) IN (TRIM(LOWER('sort_area_size')),
                            TRIM(LOWER('hash_area_size')),
                            TRIM(LOWER('bitmap_merge_area_size')),
                            TRIM(LOWER('create_bitmap_area_size'))
                            )
ORDER  BY 1
;

NAME                     VALUE    DEF_YN   DESCRIPTION                                    
------------------------ -------- -------- -----------------------------------------------
bitmap_merge_area_size   1048576  TRUE     maximum memory allow for BITMAP MERGE          
create_bitmap_area_size  8388608  TRUE     size of create bitmap buffer for bitmap index  
hash_area_size           131072   TRUE     size of in-memory hash work area               
sort_area_size           65536    TRUE     size of in-memory sort work area
  • 하지만, 9i 이후에 추가된 'PGA_AGGREGATE_TARGET' 파라미터를 통해, 작업 공간의 크기를 동적으로 관리 가능하게 됨.

SELECT A.KSPPINM  NAME,
       B.KSPPSTVL VALUE,
       B.KSPPSTDF DEF_YN,
       A.KSPPDESC DESCRIPTION
FROM   X$KSPPI  A,
       X$KSPPSV B
WHERE  A.INDX = B.INDX
AND    LOWER(A.KSPPINM) IN (TRIM(LOWER('pga_aggregate_target'))
                            )
ORDER  BY 1
;

NAME                  VALUE        DEF_YN  DESCRIPTION                                                       
--------------------- ------------ ------- ------------------------------------------------------------------
pga_aggregate_target  10737418240  FALSE   Target size for the aggregate PGA memory consumed by the instance 

SELECT *
FROM   V$PGASTAT
;

NAME                                        VALUE UNIT   
-------------------------------------- ---------- -------
aggregate PGA target parameter         1.0737E+10 bytes  
aggregate PGA auto target              9178684416 bytes  
global memory bound                    1073735680 bytes  
total PGA inuse                         539396096 bytes  
total PGA allocated                     624038912 bytes  
maximum PGA allocated                  3557746688 bytes  
total freeable PGA memory                53477376 bytes  
process count                                  72        
max processes count                           203        
PGA memory freed back to OS            1.0131E+10 bytes  
total PGA used for auto workareas          351232 bytes  
maximum PGA used for auto workareas    2983875584 bytes  
total PGA used for manual workareas             0 bytes  
maximum PGA used for manual workareas     1075200 bytes  
over allocation count                           0        
bytes processed                        2.0335E+11 bytes  
extra bytes read/written               2700260352 bytes  
cache hit percentage                        98.68 percent
recompute count (total)                     19747

2) One Pass & Multi Pass

  • One Pass
    • 프로세스에 할당된 작업공간의 크기 내에서 한 번에 정렬이 이루어지는 것
  • Multi Pass
    • 정렬작업 중간에 정렬결과를 'Sort Segment' 영역에 읽고 쓰는 작업이 발생하게 되고, 이로 인해 'direct path read temp', 'direct path write temp' 대기가 발생함.
    • 만약 이 이벤트가 광범위하게 보일 경우 'PGA_AGGREGATE_TARGET' 값을 적절히 늘려주면 해소 가능.

3) PGA_AGGREGATE_TARGET 주의사항

  • 'PGA_AGGREGATE_TARGET'을 통해 전체 Session이 Sort 공간을 공유하지만, 지정된 값의 일부분만을 개별 Session이 사용 가능.
  • 오라클은 'PGA_AGGREGATE_TARGET'에 지정된 값에 따라 개별 프로세스에 할당가능한 최대 메모리영역을 계산하며, 히든 파라미터인 '_SMM_MAX_SIZE'에 저장되고 단위는 Kbytes

SELECT A.KSPPINM  NAME,
       B.KSPPSTVL VALUE,
       B.KSPPSTDF DEF_YN,
       A.KSPPDESC DESCRIPTION
FROM   X$KSPPI  A,
       X$KSPPSV B
WHERE  A.INDX = B.INDX
AND    LOWER(A.KSPPINM) IN (TRIM(LOWER('_smm_max_size')))
ORDER  BY 1
;

NAME          VALUE   DEF_YN DESCRIPTION                                       
------------- ------- ------ --------------------------------------------------
_smm_max_size 1048570 TRUE   maximum work area size in auto mode (serial)
  • 대부분 '_SMM_MAX_SIZE' 값은 'PGA_AGGREGATE_TARGET' 값의 20%이며, 이는 최대 하나의 Session에서 사용 가능한 Sort 영역을 의미함.
  • 만약 'PGA_AGGREGATE_TARGET' 값을 그대로 사용하면서 특정 Session에 대해서만 작업 공간의 크기를 크게 해주고 싶다면, 해당 세션의 PGA 관리정책을 'AUTO'에서 'MANUAL'로 변경해주면 됨.
    {info}
  • 오라클 튜닝 가이드에서 제안하는 'PGA_AGGREGATE_TARGET' 값
OLTPPGA_AGGREGATE_TARGET(total_mem * 80%) * 20%DSSPGA_AGGREGATE_TARGET(total_mem * 80%) * 50%
  • OS 레벨에서 20% 정도의 메모리를 사용한다고 가정하고, OLTP는 여유메모리의 20%, DSS는 50% 정도를 사용하라는 의미
    {info}
  • 주의할점은, 물리적인 메모리 크기를 초과하게끔 PGA 메모리 영역의 크기를 할당하면 Page Out / Page In이 발생할 확률이 높아짐.

문서에 대하여

  1. HOME
  2. [종료]코어 오라클 데이터베이스 스터디
  3. 5차 코어 오라클 데이터베이스 스터디
  4. direct path read temp, direct path write temp