PGA_AGGREGATE_LIMIT พารามิเตอร์สุดเข้มใน Oracle 12c

photo-1429051781835-9f2c0a9df6e4

สวัสดีครับ สวัสดีในรอบ 4 เดือนเลยทีเดียว หายไปนานมากเลย แต่ยังไงวันนี้ก็มีความรู้ใหม่มาฝากเพื่อนๆ สาวก Oracle DBMS นั่นคือพารามิเตอร์ตัวใหม่ที่ในที่สุด Oracle ก็สร้างมันออกมาเพื่อแก้ปัญหาที่มีมายาวนานกับการกินหน่วยความจำจนหมดเครื่องจากการจัดการ PGA ที่ค่อนข้างยากและท้าทายมากสำหรับ DBA ในเวอร์ชั่นเก่าๆ พารามิเตอร์ที่ผมเอามาฝากวันนี้คือ PGA_AGGREGATE_LIMIT ครับ

PGA_AGGREGATE_LIMIT ทำงานอย่างไร?

พารามิเตอร์ตัวนี้เป็นที่ได้รับความสนใจมากสำหรับเหล่า DBA อย่างเราๆ จนถึงเวอร์ชั่น 11g ก็ยังไม่มีทางที่เราจะทำการจำกัดการใช้งาน PGA บน Oracle ได้ แต่ก็ยังมีพารามิเตอร์ที่ชื่อ _pga_max_size (hidden parameter)  ซึ่งสามารถนำมาใช้เพื่อทำการจำกัดการใช้งานหน่วยความจำต่อหนึ่งโพรเซส และ SGA_TARGET ที่สามารถนำมาใช้นำการจำกัดการใช้หน่วยความจำของ Oracle SGA แต่เจ้าตัว PGA_AGGREGATE_TARGET นั้นสามารถควบคุมได้แค่การใช้งานหน่วยความจำของ PGA บนแต่ละโพรเซสผ่านการคิดคำนวนของ Oracle (แต่ละโพรเซสจะถูกจำกัดไว้ที 200MB เป็นค่า default) ซึ่งคนส่วนใหญ่จะเข้าใจผิดว่า pga_aggregate_target เป็นพารามิเตอร์ที่จำกัดการใช้งานหน่วยความจำของ PGA ได้แต่ว่ามัน “ผิด” นะครับ

เนื่องจากในหลายๆ ครั้งเรามักพบกับปัญหาที่เกี่ยวกับหน่วยความจำที่ถูกใช้งานจนหมดเครื่องและตามมาด้วยการใช้งาน CPU ที่สูงมากเนื่องจากหน่วยความจำไม่พอจนมันลงไปใช้ Swap area สาเหตุนั้นมาจากปัญหาเกี่ยวกับการจองหน่วยความจำมากเกินความจำเป็นของ PGA

จนมาถึงในเวอร์ชั่นล่าสุด 12c ได้ก่อกำเนิดพารามิเตอร์น้องใหม่ที่ชื่อ PGA_AGGREGATE_LIMIT ซึ่งเป็นพารามิเตอร์ที่กำหนดขอบเขตและจำกัดการใช้งานหน่วยความจำของ PGA ได้ แต่การทำงานก็ยังคงแตกต่างกับ SGA_TARGET กล่าวคือเมื่อเรากำหนดค่าให้กับพารามิเตอร์ตัวนี้แล้วจะทำให้การใช้งานหน่วยความจำของแต่ละโพรเซสน้อยลงแต่อย่างใด แต่จะเป็นการทำให้โพรเซสหรือเซสชั่นใดๆ ถูกยกเลิกการทำงานแทน

ในเอกสารของ Oracle ได้กล่าวถึงการยกเลิกเซสชั่นไว้ดังนี้

  • เซสชั่นใดๆ ก็ตามที่มีการใช้งาน PGA มากที่สุดจะถูกยกเลิกการทำงาน
  • ถ้าการใช้งาน PGA ในขณะนั้นๆ ยังคงมากกว่า PGA_AGGREGATE_LIMIT เมื่อนั้นเซสชั่นและโพรเซสที่กำลังใช้งานหน่วยความจำ PGA มากที่สุดจะถูกยกเลิกการทำงาน (ต่างกันยังไง???)

PGA_AGGREGATE_LIMIT จะสามารถตั้งค่ามากกว่าหรือเท่ากับได้ตามที่กำหนดดังต่อไปนี้

  • 2GB (default value)
  • 200% ของ PGA_AGGREGATE_TARGET
  • 3MB * PROCESSES parameter
  • น้อยกว่า 2 X RAM – SGA

เพื่อที่จะทดสอบพารามิเตอร์ตัวนี้ ผมได้ทำการทดสอบโดยการใช้ตารางขนาดใหญ่ (โค้ดดิ้งจาก Asktom)

create table big_table tablespace data_4m
as
select rownum id,
 OWNER, OBJECT_NAME, SUBOBJECT_NAME,
 OBJECT_ID, DATA_OBJECT_ID,
 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
 TIMESTAMP, STATUS, TEMPORARY,
 GENERATED, SECONDARY
 from all_objects a
 where 1=0
/
 
alter table big_table nologging;
--Load the table
declare
 l_cnt number;
 l_rows number := &1;
begin
 insert /*+ append */
 into big_table
 select rownum,
 OWNER, OBJECT_NAME, SUBOBJECT_NAME,
 OBJECT_ID, DATA_OBJECT_ID,
 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
 TIMESTAMP, STATUS, TEMPORARY,
 GENERATED, SECONDARY
 from all_objects a
 where rownum <= &1;
l_cnt := sql%rowcount;
commit;
while (l_cnt < l_rows)
 loop
 insert /*+ APPEND */ into big_table
 select rownum+l_cnt,
 OWNER, OBJECT_NAME, SUBOBJECT_NAME,
 OBJECT_ID, DATA_OBJECT_ID,
 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
 TIMESTAMP, STATUS, TEMPORARY,
 GENERATED, SECONDARY
 from big_table
 where rownum <= l_rows-l_cnt; 
 l_cnt := l_cnt + sql%rowcount; 
 commit; 
 end loop; 
 end; 
 / 
 --gather stats 
 exec dbms_stats.gather_table_stats ( ownname => 'TEST',tabname => 'BIG_TABLE' )

เรามาเช็คขนาดของตารางที่สร้างกันครับ

select owner,table_name,num_rows from dba_tables where table_name='BIG_TABLE';
 
OWNER            TABLE_NAME         NUM_ROWS
-------------------- -------------------- ----------
TEST                 BIG_TABLE         110000000

PGA_AGGREGATE_LIMIT จะถูกตั้งค่าให้เท่ากับ 2GB (default value) และจะไม่สามารถตั้งค่าให้ต่ำกว่าที่กำหนดไว้ได้ (ดังที่กล่าวไว้ข้างต้น)

SQL> alter system set pga_aggregate_limit=1g;
alter system set pga_aggregate_limit=1g
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00093: pga_aggregate_limit must be between 2048M and 100000G

ก่อนที่เราจะทำการทดสอบ ผมได้ทำการตั้งค่า PGA_AGGREGATE_TARGET ไว้ที่ 30GB ซึ่งนั่นทำให้โพรเซสสามารถจองหน่วยความจำบน PGA ได้มากขึ้น

สร้าง Shell file ด้วยโค้ดด้านล่างนี้

cat 1.sh
modname=$1
sqlplus -s amit/amit <<EOF
set pages 1000 lines 100
set autot traceonly
exec dbms_application_info.set_module(module_name=>'${modname}',action_name=>'')
 select * from big_table t1,big_table t2 where t1.object_id =t2.object_id;
exit
EOF

ด้วยโค้ดด้านบนนี้เราจะทำการรันด้วยลูปจำนวน 20 เซสชั่น

for ((i=1;i&lt;=20;i++))
do
nohup sh 1.sh PGA_LIMIT${i} &amp;
done

และในเซสชั่นที่แยกออกมา เราจะรันคิวรี่ข้างล่างนี้เพื่อเช็คการใช้งานหน่วยความจำ

set pages 1000 lines 200
col event for a30
col module for a30
select s.sid,s.serial#,p.spid,p.pga_used_mem/1024/1024 PGA_MB,s.status,s.event,s.module from v$session s ,v$process p where s.paddr=p.addr and s.module like 'PGA_LIMIT%' and wait_class<>'Idle' order by module
/
select sum(pga_used_mem)/1024/1024 pga_used_mb from v$process;

ผลลัพธ์ที่ได้จะเป็นดังข้างล่างนี้ครับ

SQL> @pga
 
       SID    SERIAL# SPID             PGA_MB STATUS   EVENT              MODULE
---------- ---------- ------------------------ ---------- -------- ------------------------------ ------------------------------
       262    243 20941            130.878675 ACTIVE   direct path write temp     PGA_LIMIT1
       390     77 20957            130.878675 ACTIVE   direct path write temp     PGA_LIMIT10
       154     11 20938            130.816335 ACTIVE   direct path write temp     PGA_LIMIT11
    35     77 20921            130.944379 ACTIVE   direct path write temp     PGA_LIMIT12
       266     35 20906            130.878675 ACTIVE   direct path write temp     PGA_LIMIT13
       141     17 20917            130.878675 ACTIVE   direct path write temp     PGA_LIMIT14
       263     59 20945            130.878675 ACTIVE   direct path write temp     PGA_LIMIT15
    25     55 20943            130.878675 ACTIVE   direct path write temp     PGA_LIMIT16
       135    119 20942            130.878675 ACTIVE   direct path write temp     PGA_LIMIT17
       383    217 20923            130.878675 ACTIVE   direct path write temp     PGA_LIMIT18
       371    391 20937            130.816335 ACTIVE   direct path write temp     PGA_LIMIT19
       382    163 20955            130.878675 ACTIVE   direct path write temp     PGA_LIMIT2
    29     35 20954            130.816335 ACTIVE   direct path write temp     PGA_LIMIT20
       144    103 20947            130.878675 ACTIVE   direct path write temp     PGA_LIMIT3
       136     15 20952            130.878675 ACTIVE   direct path write temp     PGA_LIMIT4
    38      5 20926            130.882039 ACTIVE   direct path write temp     PGA_LIMIT5
       257    139 20902            130.878675 ACTIVE   direct path write temp     PGA_LIMIT6
       260     73 20939            130.816335 ACTIVE   direct path write temp     PGA_LIMIT7
       381     13 20951            130.878675 ACTIVE   direct path write temp     PGA_LIMIT8
    23    101 20915            130.878675 ACTIVE   direct path write temp     PGA_LIMIT9
 
20 rows selected.
 
Elapsed: 00:00:00.00
 
PGA_USED_MB
-----------
 2684.47882

จากผลลัพธ์ที่ได้จะเห็นว่า PGA ถูกใช้งานไปมากกว่า 2GB และไม่พบ error แต่อย่างใด เหตุผลก็คือเราได้ทำการตั้งค่า pga_aggregate_target ไว้ที่ 30GB เมื่อทำการเช็คค่าของ pga_aggregate_limit ยังแสดงค่าอยู่ที่ 2GB แต่มันดูเหมือนจะถูกเพิ่มค่าไปยังค่าที่สูงกว่า 2GB แล้ว (ตั้งแต่เราทำการตั้งค่า pga_aggregate_target ไว้ที่ 30GB) และต่อมาผมได้ทำการลดค่า pga_aggregate_target กลับมาอยู่ที่ 500MB ทันใดนั้นดูเหมือนว่าบางเซสชั่นบนฐานข้อมูลจะถูกทำการยกเลิกการทำงานโดยอัตโนมัติ ซึ่งจะตี error ออกมาดังตัวอย่างข้างล่าง

  
ALTER SYSTEM SET pga_aggregate_target=500M SCOPE=BOTH;
2013-07-07 11:27:34.015000 +00:00
Errors in file /home/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_20923.trc  (incident=12476):
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
Errors in file /home/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_20939.trc  (incident=12433):
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

ลองกลับมาเช็คข้อมูลจาก V$session เพื่อคอนเฟิร์มผลลัพธ์อีกรอบนึง

      
SID    SERIAL# SPID              PGA_MB STATUS   EVENT              MODULE
---------- ---------- ------------------------ ---------- -------- ------------------------------ ------------------------------
       262    243 20941            130.878675 ACTIVE   direct path write temp     PGA_LIMIT1
       390     77 20957            130.878675 ACTIVE   direct path write temp     PGA_LIMIT10
       266     35 20906            130.878675 ACTIVE   direct path write temp     PGA_LIMIT13
       141     17 20917            130.878675 ACTIVE   direct path write temp     PGA_LIMIT14
    25     55 20943            130.878675 ACTIVE   direct path write temp     PGA_LIMIT16
       135    119 20942            130.878675 ACTIVE   direct path write temp     PGA_LIMIT17
       382    163 20955            130.878675 ACTIVE   direct path write temp     PGA_LIMIT2
       144    103 20947            130.878675 ACTIVE   direct path write temp     PGA_LIMIT3
       136     15 20952            130.878675 ACTIVE   direct path write temp     PGA_LIMIT4
       257    139 20902            130.878675 ACTIVE   direct path write temp     PGA_LIMIT6
       381     13 20951            130.878675 ACTIVE   direct path write temp     PGA_LIMIT8
    23    101 20915            130.878675 ACTIVE   direct path write temp     PGA_LIMIT9
 
12 rows selected.
 
PGA_USED_MB
-----------
 1637.30552

สรุป

ตั้งแต่ pga_aggregate_limit ถูกตั้งค่าไว้ที่ 2GB มันก็จะทำการ terminate บางเซสชั่นโดยอัตโนมัติ และในที่สุดเราก็ได้ค้นพบพระเอกคนใหม่สำหรับ Oracle DBMS แล้วครับผม

Enjoy your DBA life สวัสดีครับ

Credit: AskDba.org Weblog

Advertisements

ใส่ความเห็น

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / เปลี่ยนแปลง )

Twitter picture

You are commenting using your Twitter account. Log Out / เปลี่ยนแปลง )

Facebook photo

You are commenting using your Facebook account. Log Out / เปลี่ยนแปลง )

Google+ photo

You are commenting using your Google+ account. Log Out / เปลี่ยนแปลง )

Connecting to %s