자동 sql 실행 메모리에 대해

9I) 자동 SQL 실행 메모리 : PGA_AGGREGATE_TARGET, WORKAREA_SIZE_POLICY

제품 : ORACLE SERVER (9I) 자동 SQL 실행 메모리 : PGA_AGGREGATE_TARGET, WORKAREA_SIZE_POLICY ====================================================================== PURPOSE ------- Oracle 9i의 새 기능인 자동화된 SQL 실행 메모리 관리(Automated SQL Execution Memory Management) 기능에 대하여 알아본다. Explanation ----------- 한 프로세스 당 하나만 할당되는 PGA 메모리 영역은 SGA 영역과는 달리 다른 프로세스와 공유되지 않는, 각 프로세스가 독립적으로 사용하는 non-shared 메모리 영역이다. PGA 메모리에는 다음과 같은 정보를 저장한다. - session의 variable, array - cursor 실행 메모리 - persistent 영역 - 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 실행 메모리 관리와 개념에 대하여 알아보기로 한다. 1. WORKAREA_SIZE_POLICY = AUTO Oracle 9i에서는 자동화된 SQL 실행 메모리 관리 기능을 위해 PGA run-time 영역 할당을 하는 데 있어 automatic 모드와 manual 모드를 제공한다. 모드 선택은 WORKAREA_SIZE_POLICY 파라미터를 통해 지정할 수 있다. 이 파라미터는 세션 및 시스템 레벨에서 다음과 같이 동적으로 변경할 수 있다. ALTER {SYSTEM|SESSION} SET WORKAREA_SIZE_POLICY={AUTO|MANUAL}; Manual 모드는 단지 Oracle 9i 이전 버젼과 호환을 위해 제공하는데, 이 모드가 default이다. 이 모드로 설정되면, *_area_size 파라미터들을 이용해서 메모리 튜닝이 가능하다. Auto 모드에서는 SQL 실행 메모리 관리가 자동적으로 이루어지는데, 이 모드에서 Oracle 서버는 전체적인 시스템 성능을 극대화할 수 있도록 메모리 관리를 수행한다. Note : WORKAREA_SIZE_POLICY 를 AUTO로 하기 위해서는 아래에 설명하게 될 PGA_AGGREGATE_TARGET 의 값을 반드시 먼저 지정해야 한다. 2. 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 3. Oracle 서버 튜닝 목표 DBA가 PGA_AGGREGATE_TARGET 값을 설정해서 auto 모드로 동작하는 경우, 오라클 서버는 내부적으로 다음과 같은 세 가지 목표를 달성할 수 있도록 튜닝 가능 메모리의 크기를 동적으로 조절하게 된다. 1) 튜닝 가능 메모리의 양을 조절해서 PGA 메모리의 크기가 PGA_AGGREGATE_TARGET을 넘지 않도록 한다. 2) 튜닝 가능 메모리의 양을 조절해서 특정 DB 프로세스가 out of memory 에러를 유발하지 않도록 한다. 3) 튜닝 가능 메모리의 양을 시스템 전체 throughput과 response time을 최적화할 수 있도록 조절한다. 4. 관련 V$ 뷰 자동화된 SQL 실행 영역 메모리 관리 기능과 관련해서 Oracle 9i에서는 DBA가 PGA 메모리의 사용 현황을 쉽게 파악할 수 있게 하기 위해 다음과 같은 기능이 추가되었다. 기존 V$SESSTAT과 V$SYSSTAT에 새로운 statistics를, V$PROCESS에 새로운 컬럼을 추가했고, 새로운 V$ 뷰 - V$SQL_WORKAREA, V$SQL_WORKAREA_ACTIVE - 를 제공한다. 1) 기존 뷰에 관련 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 0 end) * 100 / 5 (sum(case when name like 'workarea executions - optimal' 6 then value else 0 end) 7 + 8 sum(case when name like 'workarea executions - onepass' 9 then value else 0 end) 10 + 11 sum(case when name like 'workarea executions - multipass' 12 then value else 0 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 메모리 크기 2) 새로 도입한 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 문장 등을 알아볼 수 있다. Example ------- none