Sunday, September 20, 2009

การตั้งค่าใหักับ PGA Memory

ข้อเขียนนี้ช่วยฉัน: 
1. ตั้งค่า WORKAREA_SIZE_POLICY = AUTO
2. ตั้งค่า PGA_AGGREGATE_TARGET = จำนวน Memory ที่เหลือทั้งหมด

หลังจากที่เราทำการ Config Memory ส่วนสำคัญ ๆ เช่น SGA ซึ่งประกอบด้วย Buffer Cache, Log Buffer, Shared+Large+Java Poolไปแล้ว Memory ส่วนที่เหลือเราจะกำหนดให้กับ PGA Memory ซึ่ง Oracle ใช้ในการ Sort (เช่นการใช้ Memory ที่เกิดจากการใช้คำสั่ง ORDER BY, คำสั่งที่เป็น Analytical, DISTINCT, GROUP BY หรือการทำ Hash Join ของ Oracle ฯลฯ) ซึ่ง Operation ต่าง ๆ เหล่านี้จะใช้ Memory ส่วนที่เรียกว่า PGA ซึ่งเมื่อ Memory ส่วนนี้ไม่เพียงพอที่จะทำงาน (เช่นการ Order By) Oracle จะย้าย (Swap) ข้อมูลไปทำใน Temporary Tablespace (Temporary Tablespace จึงทำหน้าที่คล้าย ๆ Virtual Memory ในกรณีนี้) แล้วย้ายกลับเข้ามาใน PGA

Automatic Memory Management
สิ่งที่ยุ่งยากมากอันหนึ่งของ DBA คือการกำหนดพื้นที่ในการทำงานให้กับ User แต่ละคนที่ล็อกอินเข้ามาใช้งานฐานข้อมูล เราสามารถกำหนดแบบ Manual ได้โดยการทำผ่านพารามิเตอร์ชื่อ SORT_ARE_SIZE และ HASH_AREA_SIZE แต่สิ่งที่ยากก็คือไม่รู้ว่าจะเซ็ทให้ใหญ่หรือเล็กแค่ไหน และที่ซับซ้อนกว่านั้นคือความต้องการใช้พื้นที่ของ User จะเปลี่ยนไปตามเรื่อย ๆ เช่นตอนเช้า 8:00 มีเพียง 2 User ต่อมาตอน 14:00 อาจจะมี 100 User เป็นต้น

การกำหนดแบบ Automatic Memory Management จะทำให้งานเหล่านี้ง่ายขึ้น โดยการเซ็ทพารามิเตอร์
1. WORKAREA_SIZE_POLICY = AUTO
2. PGA_AGGREGATE_TARGET = Memory ที่เหมาะสม

โดย Memory ที่เหมาะสม คือ (Physical) Memory ส่วนที่เหลือที่จะให้ Oracle จัดการ Assign ใหั User แต่ละคนเองโดยอัตโนมัติ ซึ่งการใช้ Automatic Memory Management จะทำให้งานการกำหนดพื้นที่ในการทำงานให้กับ User แต่ละคนทำได้ง่ายกว่าการกำหนดเองผ่าน SORT_AREA_SIZE และ HASH_AREA_SIZE โดยเฉพาะในกรณีที่ความต้องการเหล่านี้เปลี่ยนแปลงไปเรื่อย ๆ

ในอดีตหลังจากกำหนด Memory ส่วนหลัก ๆ (Buffer Cache, Log Buffer และ Pools ต่าง ๆ)ไปแล้ว DBA จะกำหนด Memory ส่วนที่เหลือให้กับ PGA โดยกำหนดผ่าน SORT_AREA_SIZE สมมติว่ามีการ Order By พร้อม ๆ กัน 10 Order By, Oracle อาจจะใช้ Memory ประมาณ 10 * SORT_AREA_SIZE แต่ถ้ามี 100 Order By พร้อม ๆ กันก็อาจจะใช้ Memoryได้ถึง 100 * SORT_AREA_SIZE และเมื่อรวมถึงการที่มีงานอื่น ๆ จะต้องใช้พื้นที่ของ PGA อีก ทำให้การควบคุมขนาดของ PGA ให้เหมาะสมตลอดเวลา ทำได้ยุ่งยากมาก

สิ่งที่เราต้องการคือให้หน่วยความจำส่วนนี้สามารถใช้งานได้อย่างยืดหยุ่น กล่าวคือถ้ามีผู้ใช้พร้อมกันจำนวนมาก แต่ละผู้ใช้งานก็จะได้ Memory ขนาดเล็ก ในขณะที่เมื่อจำนวนผู้ใช้งานลดลง แต่ะละคนก็ควรจะได้พื้นที่ใน PGA จำนวนมากขึ้นตามสัดส่วน ซึ่งการตั้งค่า WORKAREA_SIZE_POLICY = AUTO และกำหนด Memory ส่วนที่เหลือใหักับ PGA_AGGREGATE_TARGET ทั้งหมด จะเป็นการมอบหมายใหั Oracle จัดการหน่วยความจำ PGA ให้ตามแนวคิดนี้โดยอัตโนมัติ ซึ่งขนาดของ PGA_AGGREGATE_TARGET ที่เหมาะสมนี้เราจำต้องเป็นผู้กำหนดให้กับ Oracle ซึ่งเราอาจจะหาค่าที่เหมาะสมนี้ได้จาก Oracle Enterprise Manager (PGA Advisory) หรือ Statspack ซึ่งจะแนะนำว่าขนาดของ PGA_AGGREGATE_TARGET ที่เหมาะสม (ที่จะทำให้การใช้ Temporary Tablespace น้อยที่สุด) ณ ขณะนั้นคืออะไร

แล้วมีกรณีใดบ้างที่เราจะไม่ต้องการใช้ WORKAREA_SIZE_POLICY และ PGA_AGGREGATE_TARGET เพื่อให้ Oracle จัดการหน่วยความจำส่วนของ User ให้?

โดยปกติการจัดการที่ Oracle ทำให้โดยอัดโนมัติจะกระจายหน่วยความจำให้กับแต่ละ User เท่า ๆ กัน ซึ่งจะจำกัดขนาดของหน่วยความจำเป็นสัดส่วนของ PGA_AGGREGATE_TARGET แต่ในบางกรณีเช่นการ Batch Process ตอนกลางคืน ที่ไม่มีผู้ใช้คนอื่นอีกมีแต่ Batch Process ที่ต้องการใช้พื้นที่บน PGA ในกรณีนี้เราสามารถใช้ SORT_AREA_SIZE ผ่านคำสั่ง alter session ในการกำหนดขนาดของ PGA และให้ Memory ทั้งหมดกับ Batch Process ได้, คำสั่ง alter session นี้จะ Override การจัดการหน่วยความจำ PGA อัตโนมัติของ Oracle สำหรับเฉพาะ Session และเมื่อ Session นั้นปิดไป Session ใหม่ที่เปิดมาก็จะใช้การจัดการหน่วยความจำ PGA แบบอัตโนมัติตามเดิม

กล่าวโดยสรุปเกี่ยวกับการกำหนดค่าใหักับหน่วยความจำ PGA
ใช้วิธีการ Automatic Memory Management โดย
1. ตั้งค่า WORKAREA_SIZE_POLICY = AUTO
2. ตั้งค่า PGA_AGGREGATE_TARGET = จำนวน Memory ที่เหลือทั้งหมด
หากต้องการ PGA Memory ขนาดใหญ่พิเศษเป็นครั้งคราว
3. ใช้คำสั่ง alter session set PGA_AGGREGATE_TARGET = จำนวน Memory ที่เหลือทั้งหมด

2 comments:

Strange Scholar said...

My Master :D

พี่กรสวัสดีครับ ตอนนี้กำลังจะ tuning TOPSERV performance เปิดมาเจอเรื่อง PGA พอดี ได้ความรู้มากมายครับพี่ -/|\-

Nut

Palmmie Triya said...

ขอบคุณมากค่ะ มีประโยชน์มากๆ

Post a Comment