http://blog.naver.com/sanfriend?Redirect=Log&logNo=40005040783

자동 SQL 실행 메모리 : PGA_AGGREGATE_TARGET, WORKAREA_SIZE_POLICY#

목적#

Oracle 9i의 새 기능인 자동화된 SQL 실행 메모리 관리(Automated SQL Execution Memory Management) 기능에 대하여 알아본다.

설명#

한 프로세스 당 하나만 할당되는 PGA 메모리 영역은 SGA 영역과는 달리 다른 프로세스와 공유되지 않는, 각 프로세스가 독립적으로 사용하는 non-shared 메모리 영역이다. PGA 메모리에는 다음과 같은 정보를 저장한다.
  1. session의 variable, array
  2. cursor 실행 메모리
  3. persistent 영역
  4. run-time 영역

특히, run-time 영역에는 SQL 문을 실행하는 중에 사용하는 정보를 포함하게 되는데, SQL 문의 복잡도, 처리해야 할 데이타의 양 등에 따라 이 영역의 크기가 달라진다. 이 영역은 SQL 문의 수행이 끝나면 OS로 메모리가 반환된다.

DSS(Decision Support System) 분야에서 사용되는 복잡한 SQL 문의 경우, 이 runtime 영역의 많은 부분이, 메모리를 많이 필요로 하는 row source들에 의해 사용된다. 이러한 row source의 예로는 sort row source, hash-join row source 등을 들 수 있다. 일반적으로 이 row source들을 위한 메모리가 많이 할당될 수록 질의 처리 속도가 현저히 빨라진다.

Oracle 8i 버젼까지는 DBA가 sort_area_size, hash_area_size, bitmap_merge_area_size, create_bitmap_area_size 등의 다양한 initSID.ora 파라미터를 이용해서 이들 run-time 영역의 메모리를 제어할 수 있었다.

그런데, 현재 버젼(8i)까지의 방식은 다음과 같은 문제점이 있다.

  1. 이들 파라미터는 관련 SQL 문들의 수행 빈도와 동시성 등에 의존하기 때문에 경험이 풍부한 DBA라 하더라도 이들 파라미터를 튜닝하기가 쉽지 않다.
  2. 현재 방식은 특정 질의가 사용할 최대 메모리 크기를 제어하지 않기 때문에 특정 사용자가 메모리를 과다하게 사용하게 되므로 오히려 성능 저하를 유발할 수 있다.
  3. 필요 이상으로 과다하게 할당된 파라미터의 경우 메모리 낭비를 일으킨다.

이러한 이유로 인하여 Oracle 9i에서는 자동화된 SQL 실행 메모리 관리 기능이 새롭게 추가되었다.

Oracle 9i에서 자동화된 SQL 실행 메모리 관리#

이와 같은 Oracle 8i에서 SQL 실행 메모리 관리의 문제점을 극복하기 위해서 Oracle 9i부터는 자동화된 SQL 실행 메모리 관리 기능을 제공한다. 즉, SQL 실행 메모리의 크기를 DBA가 initSID.ora 파라미터로 지정하는 대신에, 질의 수행 시에 Oracle 서버가 자동적으로, 그리고 동적으로 조절하는 기능을 제공한다.

이와 같이 자동화된 SQL 실행 메모리 관리 기능은 다음과 같은 장점을 제공한다.

  1. 메모리 튜닝의 편이성
  2. 메모리 튜닝에 사용되는 시간의 단축
  3. 메모리 사용 효율성 증대로 인한 시스템 throughput 향상(특히 동시 사용자가 많을 때)
  4. 개별 질의에 대한 response time 향상

이 문서에서는 자동화된 SQL 실행 메모리 관리와 개념에 대하여 알아보기로 한다.

WORKAREA_SIZE_POLICY = AUTO#

Oracle 9i에서는 자동화된 SQL 실행 메모리 관리 기능을 위해 PGA run-time 영역 할당을 하는 데 있어 automatic 모드와 manual 모드를 제공한다. 모드 선택은 WORKAREA_SIZE_POLICY 파라미터를 통해 지정할 수 있다. 이 파라미터는 세션 및 시스템 레벨에서 다음과 같이 동적으로 변경할 수 있다.

ALTER {SYSTEM|SESSIONSET WORKAREA_SIZE_POLICY={AUTO|MANUAL};

Manual 모드는 단지 Oracle 9i 이전 버젼과 호환을 위해 제공하는데, 이 모드가 default이다. 이 모드로 설정되면, *_area_size 파라미터들을 이용해서 메모리 튜닝이 가능하다.

Auto 모드에서는 SQL 실행 메모리 관리가 자동적으로 이루어지는데, 이 모드에서 Oracle 서버는 전체적인 시스템 성능을 극대화할 수 있도록 메모리 관리를 수행한다.

Note : WORKAREA_SIZE_POLICY 를 AUTO로 하기 위해서는 아래에 설명하게 될 PGA_AGGREGATE_TARGET 의 값을 반드시 먼저 지정해야 한다.

PGA_AGGREGATE_TARGET#

자동화된 실행 메모리 관리 기능이 도입됨에 따라, PGA 메모리 영역은 크게 튜닝 가능 메모리와 튜닝 불가능 메모리로 구분할 수 있다. 튜닝 가능 메모리는 실제 SQL 실행 시 사용되는 메모리 영역이고, 튜닝 불가능 메모리는 PGA의 나머지 영역을 일컫는다. DSS와 같은 분야에서는 대용량의 데이타에 대한 sort, hash join, bitmap index 관련 연산 등으로 인해 튜닝 가능 메모리가 PGA 영역의 대부분(90% 이상)을 차지하는 반면, OLTP 분야의 경우에는 일반적으로 PGA 메모리의 1% 이하가 튜닝 가능 메모리이다.

자동화된 SQL 실행 메모리 관리 기능과 관련해서 Oracle 9i에서는 PGA_AGGREGATE_TARGET 이라는 initSID.ora 파라미터를 제공해서 DBA가 전체 PGA 메모리를 명시적으로 제어할 수 있게 해준다. 즉, DBA가 PGA_AGGREGATE_TARGET을 설정하면, Oracle 서버는 다음 조건을 만족시키는 범위에서 SQL 처리를 수행한다. (참고로 DBA가 PGA_AGGREGATE_TARGET을 명시적으로 설정하면, default로 auto모드로 동작한다.)

튜닝 가능 메모리 + 튜닝 불가능 메모리 <= PGA_AGGREGATE_TARGET

이 파라미터를 사용하게 되면 DBA가 PGA SQL 실행 영역의 *_area_size를 일일이 설정해줄 필요 없이 이 범위 내에서 메모리를 최대한 효율적으로 사용할 수 있도록 동작하게 된다.

Note : 이 파라미터는 ALTER SYSTEM을 이용해서 시스템 레벨에서 동적으로 변경할 수 있다.

SQL> alter system set pga_aggregate_target=20000000;

시스템이 변경되었습니다.

SQL> alter system set workarea_size_policy=AUTO;

시스템이 변경되었습니다.

SQL> show parameter pga;

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
pga_aggregate_target                 big integer
20000000

SQL> show parameter workarea

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
workarea_size_policy                 string
AUTO                                          

Oracle 서버 튜닝 목표#

DBA가 PGA_AGGREGATE_TARGET 값을 설정해서 auto 모드로 동작하는 경우, 오라클 서버는 내부적으로 다음과 같은 세 가지 목표를 달성할 수 있도록 튜닝 가능 메모리의 크기를 동적으로 조절하게 된다.
  1. 튜닝 가능 메모리의 양을 조절해서 PGA 메모리의 크기가 PGA_AGGREGATE_TARGET을 넘지 않도록 한다.
  2. 튜닝 가능 메모리의 양을 조절해서 특정 DB 프로세스가 out of memory 에러를 유발하지 않도록 한다.
  3. 튜닝 가능 메모리의 양을 시스템 전체 throughput과 response time을 최적화할 수 있도록 조절한다.

! 관련 V$ 뷰 자동화된 SQL 실행 영역 메모리 관리 기능과 관련해서 Oracle 9i에서는 DBA가 PGA 메모리의 사용 현황을 쉽게 파악할 수 있게 하기 위해 다음과 같은 기능이 추가되었다. 기존 V$SESSTAT과 V$SYSSTAT에 새로운 statistics를, V$PROCESS에 새로운 컬럼을 추가했고, 새로운 V$ 뷰 - V$SQL_WORKAREA, V$SQL_WORKAREA_ACTIVE - 를 제공한다.

기존 뷰에 관련 row 및 column 추가#

V$SESSTAT과 V$SYSSTAT 뷰에 다음 네 가지 statistics가 추가되었다.
  • workarea memory allocated : 특정 세션이나 시스템에 할당된 전체 PGA 메모리 크기
  • workarea executions - optimal : SQL 실행 메모리가 최적의 크기를 가졌던 누적 횟수. 여기서 메모리가 최적의 크기를 갖는다는 것은 예를 들어, disk에 write하지 않고 sort 작업을 수행하는 경우를 의미한다.
  • workarea executions - onepass : SQL 실행 메모리가 1 pass의 크기를 가졌던 누적 횟수. 여기서 메모리가 1 pass의 크기를 갖는다는 의미는, 예를 들어 sort의 경우 disk에 임시 결과를 한번은 저장하고 결과를 merge해서 sort 작업을 마치는 경우를 의미한다.
  • workarea executions - multipass : SQL 실행 메모리가 2 pass 이상의 크기를 가졌던 누적 횟수.

이들 뷰를 이용해서 SGA가 아닌 PGA 메모리의 사용 현황을 모니터링할 수 있다. 예를 들어, 다음은 시스템 전체에 걸쳐 SQL 실행 영역이 최적의 크기를 가졌던 비율을 구하는 SQL 문이다.

SQL> SELECT
  2      trunc (
  3           sum(case when name like 'workarea executions - optimal'
  4                   then value else end100 /
  5          (sum(case when name like 'workarea executions - optimal'
  6                   then value else end)
  7           +
  8           sum(case when name like 'workarea executions - onepass'
  9                   then value else end)
 10           +
 11           sum(case when name like 'workarea executions - multipass'
 12                   then value else end)
 13          )
 14       optimal_percent
 15  FROM v$sysstat
 16  WHERE name like 'workarea executions - %';

OPTIMAL_PERCENT
---------------
            100       

그리고, V$PROCESS 뷰에는 다음 컬럼들이 추가되었는데, 이들 정보를 이용해서 할당된 PGA 메모리의 양과 실제로 Oracle 서버에 의해 사용된 메모리 양을 파악할 수 있다.

  • PGA_USED_MEM : 해당 프로세스에 의해 현재 사용되는 PGA 메모리 크기
  • PGA_ALLOC_MEM : 해당 프로세스에 의해 할당된 현재 PGA 메모리 크기(OS에 return되지 않고 free 상태로 할당된 메모리 영역도 포함)
  • PGA_MAX_MEM : 해당 프로세스에 할당된 최대 PGA 메모리 크기

새로 도입한 V$뷰#

자동화된 SQL 실행 메모리 관리 기능을 위해 추가로 도입된 V$ 뷰는 V$SQL_WORKAREA, V$SQL_WORKAREA_ACTIVE 등이 있다.
  • V$SQL_WORKAREA : 각 SQL 커서에 의해 사용되는 SQL 실행 영역 정보
  • V$SQL_WORKAREA_ACTIVE : 현재 시스템에 의해 할당되어 있는 SQL 실행 영역들 정보

아래의 예를 통해서 이들 각각의 뷰에서 유용한 정보를 추출하는 방법을 알아보기로 한다.

예) V$SQL_WORKAREA

V$SQL_WORKAREA 뷰를 통해서 다음과 같이 SQL 실행 메모리를 가장 많이 필요로 하는 top 10 실행 영역을 구할 수 있다.

SELECT *
FROM SELECT workarea_address, operation_type, policy, estimated_optimal_size
       FROM v$sql_workarea
       ORDER BY estimated_optimal_size DESC )
WHERE ROWNUM <= 10

예) V$SQL_WORKAREA_ACTIVE

다음 SQL 문은 V$SQL_WORKAREA_ACTIVE, V$SQL_WORKAREA, V$SQL 뷰를 이용하여 현재 시스템에서 할당된 실행 영역 중 top 10 영역을 찾는 예를 보여준다.

SQL> SELECT c.sql_text, w.operation_type, top_ten.wasize
  2  FROM SELECT *
  3         FROM SELECT workarea_address, actual_mem_used wasize
  4                FROM v$sql_workarea_active
  5                ORDER BY actual_mem_used desc)
  6         WHERE ROWNUM <= 10 top_ten,
  7         v$sql_workarea w, v$sql c
  8  WHERE w.workarea_address = top_ten.workarea_address
  9        AND c.address = w.address
 10        AND c.child_number = w.child_number
 11        AND c.hash_value = w.hash_value;   

SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE                               WASIZE
---------------------------------------- ----------
SELECT c.sql_text, w.operation_type, top_ten.wasize FROM SELECT *        FROM
SELECT workarea_address, actual_mem_used wasize               FROM v$sql_worka
rea_active               ORDER BY actual_mem_used desc)        WHERE ROWNUM <= :
"SYS_B_0" top_ten,        v$sql_workarea w, v$sql c WHERE w.workarea_address =
 top_ten.workarea_address       AND c.address = w.address       AND c.child_numb
er = w.child_number       AND c.hash_value = w.hash_value
SORT                                         196608   

결론#

이 내용은 Oracle 9i에서 새로이 추가된 Automated SQL Execution Memory 기능에 대한 내용이다. PGA_AGGREGATE_TARGET 값을 적절한 값으로 정의하고, WORKAREA_SIZE_POLICY를 AUTO로 지정하면 기존의 sort_area_size, hash_area_size, bitmap_merge_area_size, create_bitmap_area_size 등의 값을 일일이 지정할 필요 없이 서버가 알아서 필요한 메모리를 할당하게 된다. 보통, DSS 환경에서 튜닝 가능 메모리 크기의 비율이 크기 때문에 OLTP 환경보다는 DSS 환경에서 튜닝 작업의 단순화, 메모리 사용 throughput 향상 등에 있어 더 큰 효과를 볼 수 있을 것이다.

V$SESSTAT과 V$SYSSTAT의 새로운 통계를 활용해서 가급적이면, workarea가 optimal한(disk i/o 없이) 상태 혹은 disk에 대한 1 pass만으로 가능하도록 PGA_AGGREGATE_TARGET 값을 지정하는 것이 바람직하며, V$SQL_WORKAREA_ACTIVE, V$SQL_WORKAREA, V$SQL 뷰를 통해서 실제 사용된 메모리에 대한 top 10 SQL 문장 등을 알아볼 수 있다.

Add new attachment

Only authorized users are allowed to upload new attachments.
« This page (revision-1) was last changed on 06-Jan-2008 16:30 by DongGukLee