Monday, February 14, 2011

Shared Pool Memory Structure


Oracle Shared Pool เป็นคุณสมบัติสำคัญที่ช่วยในการแชร์ให้ User กลุ่มใหญ่สามารถใช้ Object ที่ซับซ้อนร่วมกันได้ ในอดีตบ่อยครั้งที่ DBA ใช้เวลาส่วนใหญ่เรียนรู้เกี่ยวกับการจัดการหน่วยความจำ Shared Pool   และตั้งค่าตลอดจนปรับแต่ง (Tune) การใช้งาน Shared Pool ต่อมาจึงมีคุณสมบัติที่เรียกว่า Automatic Shared Memory Management (ASMM) ที่เริ่มมีใน Oracle 10gR1 ซึ่งน่าจะสามารถช่วยในการแก้ปัญหานี้ โดยได้สร้างกลไกในการปรับแต่งตัวเองแบบอัตโนมัติในการตั้งค่าส่วนความจำ SGA ซึ่งรวมถึง Buffer Cache และ Shared Pool

วัตถุประสงค์ของ Shared Pool
Shared Pool ได้ถือกำเนิดขึ้นโดยเป็นคุณสมบัติหนึ่งของ Oracle นับแต่ Oracle version 7 โดยวัตถุประสงค์เริ่มแรกคือเป็นที่เก็บหน่วยความจำสำหรับ SQL และ PL/SQL เพื่อเอาไว้ใช้ร่วมกัน ซึ่งต่อมามันได้พัฒนามารองรับหน้าที่หลักในการเป็นที่พัก (Cache) สำหรับ Cursors ที่ใช้ร่วมกันระหว่าง Session ที่เชื่อมต่อกับฐานข้อมูล Oracle อีกด้วย
กล่าวโดยหน่วยที่พื้นฐานที่สุด   Shared Pool คือ ที่พักของ Metadata โดยที่ Buffer Cache เป็นที่สำหรับพักของข้อมูล, Shared Pool เป็นที่สำหรับพัก Object อันซับซ้อนที่จะบอกว่าข้อมูลถูกเก็บไว้ที่ใด และข้อมูลนั้นมีความสัมพันธ์กับข้อมูลอื่นอย่างไร และจะดึงออกมาใช้อย่างไร แบบไหน
การใช้ Shared Pool ส่วนใหญ่ก็เพื่อใช้ในการ Execute คำสั่ง SQL และ Package PL/SQL ที่ใช้ร่วมกัน แต่เพื่อที่จะสร้าง Cursor หรือคอมไพล์ PL/SQL Procedure ระบบจะต้องรู้ข้อมูลเกี่ยวกับ Object ที่ถูกอ้างถึงใน SQL หรือ PL/SQL ที่กำลังจะถูกคอมไพล์ เช่น เพื่อที่จะสร้าง Cursor จากคำสั่ง Select ง่าย ๆ จากหนึ่งตาราง เราจำเป็นต้องรู้ Metadata เกี่ยวกับตารางเช่น ชื่อคอลัมน์, ชนิดของข้อมูล, อินเด็กซ์ และข้อมูลสถิติที่ใช้กับ Optimizer,  ข้อมูล Metadata เหล่านี้จะถูกพักไว้ใน Shared Pool โดยแยกอิสระออกจาก Cursor และส่วนที่ใช้เก็บ Program (เช่น Package เป็นต้น)

องค์ประกอบของ Shared Pool
โดยทั่วไปสิ่งที่ DBA มักจะสนใจเป็นพิเศษคือการจัดสรรหน่วยความจำซึ่งเป็นที่พักสำหรับของ Object ต่าง ๆ ดังได้กล่าวมาแล้ว เนื่องจากการสร้าง Object ใหม่ใน Shared Pool นั้น สิ้นเปลืองทรัพยากร และกระทบต่อขนาดและประสิทธิภาพการทำงานของระบบ   การจัดสรรหน่วยความจำให้กับ Object ซึ่งสามารถถูกสร้างใหม่ได้นั้นบางครั้งเราใช้คำว่า ‘Recreatable’

V$SGASTAT
วิธีที่ง่ายที่สุดในการตรวจสอบสิ่งที่อยู่ใน Shared Pool ณ ขณะใดขณะหนึ่งคือการคิวรี Fixed View ชื่อ V$SGASTAT เนื่องจาก Oracle จะคอยรันเก็บจำนวนของการจัดสรรหน่วยความจำทั้งหมดใน Shared Pool เอาไว้ การคิวรีข้อมูลจากวิวนี้จึงไม่ได้ใช้ทรัพยากรมากและไม่มีผลกระทบต่อระบบงานบนเครื่อง Production เท่าไรนัก ใน Oracle10gR2 วิวตัวนี้ได้รับการปรับปรุงให้แสดงผลได้เป็นหน่วยละเอียดขึ้น
ตัวอย่าง:

SELECT * FROM(SELECT NAME, BYTES/(1024*1024) MB FROM V$SGASTAT WHERE POOL = 'shared pool' ORDER BY BYTES DESC)WHERE ROWNUM <= 10;

NAME                               MB
-------------------------- ----------
ktcmvcb                    6856.96056
KGH: NO ACCESS             1610.02487
sql area                    571.81263
free memory                502.999672
CCursor                    301.799118
PCursor                     176.69886
library cache               112.56636
kglsim object batch        77.3775787
gcs resources              75.5597076
sql area:PLSQL             51.2854691


การ Select จาก V$SGASTAT หลายครั้ง แสดงให้เห็นว่าองค์ประกอบบางตัวจะมีขนาดที่คงที่

Cache Dynamics
ขนาดขององค์ประกอบเหล่านี้จะเคลื่อนไหวขึ้น ๆ ลง ๆ ตามการสร้าง SQL และ PL/SQL ใหม่ ๆ ภายในหน่วยความจำ Cache ซึ่งแปรผันตามความเปลี่ยนแปลงของ Application องค์ประกอบเหล่านี้แต่ละตัวจะมีส่วนเกี่ยวข้องกับกระบวนการสร้าง SQL (และ PL/SQL) นี้ในระดับหนึ่ง โดยที่ในบางขณะองค์ประกอบจะคืนหน่วยความจำออกมาเพื่อที่องค์ประกอบอื่นสามารถนำไปใช้ต่อ
shared_pool_comp

วิวในระดับ Object (Object-level View)
ดังกล่าวมาข้างต้นจะเห็นได้ชัดว่าแต่ละองค์ประกอบมี Object อาศัยอยู่ได้เป็นร้อย ๆ อย่างเช่นใน Shared Pool อาจจะมี Cursor หนึ่งตัว, ตารางหนึ่งตาราง, วิว หรือหนึ่ง Package
ปกติแล้ว Objects เหล่านี้มีขนาดไม่ใหญ่นัก แต่ก็สามารถที่จะโตได้ไปจนถึงหลาย MB ขึ้นอยู่กับประเภทของ Objects กับ Attribute ที่กำหนด (เช่นถ้ากำหนดคอลัมน์ให้เป็น CHAR ขนาดใหญ่ ก็จะกินพื้นที่มากเป็นต้น) ไม่น่าแปลกใจเลยที่ Package Body ที่เก็บ Procedure จำนวนมากจะกินพื้นที่ใน Shared Pool มากกว่าคำสั่ง Select ง่าย ๆ
ขนาดและรายละเอียดอื่น ๆ ของ Object แต่ละชิ้นใน Shared Pool สามารถหาได้จากการคิวรี Fixed View ชื่อ V$DB_OBJECT_CACHE (10g ขึ้นไป – หรือจากวิวที่เกี่ยวข้องเช่น V$SQL และ V$SQLAREA) คิวรีข้างล่างนี้จะแสดง Object ที่ใหญ่ที่สุดสองตัวแรกจากแต่ละ Namespace ใน V$DB_OBJECT_CACHE

SELECT * FROM (SELECT ROW_NUMBER () over (PARTITION BY NAMESPACE ORDER BY SHARABLE_MEM DESC) ROW_within , NAMESPACE, SHARABLE_MEM, SUBSTR(NAME,1,40) NAME FROM V$DB_OBJECT_CACHE ORDER BY SHARABLE_MEM DESC) WHERE ROW_WITHIN <= 2 ORDER BY NAMESPACE, ROW_WITHIN;

ROW_WITHIN NAMESPACE           SHARABLE_MEM NAME
---------- ------------------- ------------ -----------------------
1          CLUSTER                     2794 C_OBJ#_INTCOL#
2          CLUSTER                     1684 SMON_SCN_TO_TIME
1          RSRC PLAN                   5117 SYS_GROUP
2          RSRC PLAN                      0 OTHER_GROUPS
1          RULESET                    34367 ALERT_QUE_R


Memory Heaps
มีความเชื่อผิด ๆ ยอดนิยมอันหนึ่งว่าเราจะเจอปัญหาถ้าเราพยายามจัดสรรหน่วยความจำให้กับ Object ขนาดใหญ่เหล่านี้ ในกรณีสุดโต่งก็อาจจะเป็นจริงได้ แต่ก็ไม่เสมอไป ทำไมหรือ? เราต้องเข้าใจก่อนว่าแต่ละ Object ไม่ได้ประกอบด้วยการใช้หน่วยความจำขนาดใหญ่ก้อนเดียว แต่จะถูกแบ่งออกหน่วยความจำที่เป็นอิสระจากกันขนาดเล็กกว่าเป็นส่วน ๆ เรียกว่า Heap   จำนวนของ Heap ต่อ หนึ่ง Object ขึ้นอยู่กับประเภทของ Object เช่น Cursor ของ SQL ตัวหนึ่งมี 2 Heap: Heap ตัวเล็กสำหรับ Library Cache Metadata (ปกติเรียก Heap 0) และตัวที่ใหญ่กว่าเก็บรูปแบบการรัน  (Executable Representation) ของ Cursor (ปกติเรียก SQLAREA) ความเข้าใจนี้มีประโยชน์ต่อการเข้าใจการทำงานภายในของพารามิเตอร์ init.ora บางตัวเช่น SESSION_CACHED_CURSORS
แม้ว่าตัวของ Heap เองอาจจะมีขนาดค่อนข้างใหญ่ แต่ตัวของมันเองก็ยังประกอบด้วยหน่วยความจำหนึ่งก้อน (Chunk) หรือมากกว่า แผนภูมิต่อไปนี้แสดงเลย์เอาท์ของ Heap สำหรับ Shared Pool Object ส่วนใหญ่

shared pool comp2

การใช้พื้นที่ใน Shared Pool (Space Allocation in the Shared Pool)
สิ่งแรกที่ต้องเข้าใจคือ Oracle ต้องการหน่วยความจำที่ติดกันต่อเนื่องเพื่อนำมาใช้งาน เช่น ถ้ามีความต้องการใช้หน่วยความจำก้อนขนาด 4K, Heap Manager (ตัวที่คอยจดบัญชีพื้นที่ว่างใน Shared Pool) ไม่สามารถที่จะเอาหน่วยความจำก้อนขนาด 3K มาหนึ่งก้อน แล้วเอาอีกหนึ่งก้อนที่มีขนาด 1K มาได้ ถ้าไม่มีหน่วยความจำก้อนขนาดที่ต้องการ,  Process จะเริ่มคืนกลุ่ม (Batch) ของ Heap จาก Shared Pool และคอยเช็คดูหลังจากคืนทีละกลุ่ม Batch จนกว่าหน่วยความจำที่คืนมาจะมีขนาดใหญ่เพียงพอ
แต่ถ้าท้ายที่สุด Shared Pool ก็ยังไม่มีพื้นที่เหลือพอตามที่ต้องการ ก็จะเกิดข้อผิดพลาด ORA-4031  อย่างไรก็ตาม ก่อนจะขึ้นข้อผิดพลาด Heap Manager จะพยายามคืนหน่วยความจำให้มากเท่าที่จะทำได้ก่อนที่จะเลิก; ทำซ้ำ ๆ กันไปตลอดทั้ง Shared Pool LRU (LRU – Least Recently Used ปลายของหน่วยความจำด้านที่มีการเรียกใช้ซ้ำน้อยที่สุด) เป็นจำนวน 5 ครั้งเพื่อคืนหน่วยความจำและมอบก้อนข้อมูลที่ต่อเนื่องกันตามที่ถูกร้องขอมา ก่อนที่จะขึ้น 4031  ส่วนหน่วยความจำที่คืนมาจะถูกรวมกันเป็นก้อน (Coalesced) ถ้าสามารถทำได้โดยอัตโนมัติ

SQL & PL/SQL ใน Shared Pool
ทุก ๆ SQL Cursor และ PL/SQL Package ถูกจัดสรรหน่วยความจำเป็นก้อน ๆ ให้ โดยมีขนาดก้อนละประมาณ 4K  ยิ่ง Cursor มีขนาดใหญ่ จำนวนของการใช้ก้อนหน่วยความจำ (4K) ก็จะมากขึ้นด้วย และมักไม่ค่อยจะมีขนาดก้อนใหญ่กว่า 4K นอกจากว่ามีความต้องการพิเศษจาก SQL Optimizer หรือจาก PL/SQL runtime engine
เนื่องจากเมื่อตอนจัดสรรหน่วยความจำ จะจัดเป็น 4K เท่า ๆ กัน ดังนั้นตอนจะคืนหน่วยความจำก็จะคืนเป็นหน่วยของ 4K เช่นกัน ดังนั้นจึงไม่น่าจะเป็นปัญหาถ้าจะสร้าง Cursor หรือ PL/SQL Package ตัวใหม่ ถ้ามี Cursor หรือ Package เก่า ๆ พร้อมที่จะถูกคืนออกมา

การแปล (Parsing)
เนื่องจากเรากำลังพูดถึงเรื่องการจัดสรรหน่วยความจำให้กับ SQL และ PL/SQL สิ่งที่จะข้ามไปเสียมิได้คือเรื่องของการแปล (Parsing) เพราะถ้าปราศจากการแปลใหม่ (Hard Parsing) ก็ไม่จำเป็นจะต้องมีการจัดสรรหน่วยความจำจาก Shared Pool เลย การแปลใหม่มีผลกับระบบในหลายระดับ แม้แต่ก่อนที่จะมีการสร้าง Cursor, SQL จะต้องถูกแปล และทำให้อยู่ในรูปที่มีประสิทธิภาพในการรันสูงสุด (Optimized) กระบวนการนี้ต้องใช้ทรัพยากรใน Library Cache และ Row Cache (Dictionary Cache) และอาจจะทำให้เกิดการโหลด Object ใน Library Cache และ Row Cache ซ้ำแล้วซ้ำอีก ซึ่งก่อให้เกิดภาระกับ Library Cache, Row Cache และองค์ประกอบใน Shared Pool และเป็นตัวอธิบายว่าทำไมอัตราการแปลใหม่ (Hard Parse) จึงมักจะมาพร้อม ๆ กับ Latch Contention
เมื่อเปรียบเทียบกันการแปลทบทวน (Soft Parse) จะกินทรัพยากรน้อยกว่ามาก อย่างไรก็ตามแม้แต่แปลทบทวน (Soft Parse) ก็ไม่ได้หมายความว่าจะไม่ได้กินทรัพยากรเลย เนื่องจากการแปลทบทวน (Soft Parse) ระบบจะต้องมองหาคำสั่ง SQL ใน Shared Pool, ทำการล๊อค และตรึง (Pin) Object ที่จำเป็น และตอนรัน Cursor นั้นต้องมีการทำงานของ Latch มาเกี่ยวข้องด้วยเสมอ และแม้ว่าค่าใช้จ่ายของ Soft Parse จะน้อยกว่า Hard Parse หลายเท่า แต่ความถี่ในการใช้มักจะมากกว่าหลายเท่าเช่นกัน นี่เป็นเหตุผลว่าทำไมจึงมีออปชั่นในการเก็บ (Cache) SQL เหล่านี้ไว้ในหน่วยความจำทั้งบนฝั่ง Server และ Client เพื่อช่วยให้ระบบที่มีการส่งผ่านข้อมูลจำนวนมาก สามารถทำงานได้มากขึ้นหรือใหญ่ขึ้น

การตั้งค่า Shared Pool
อย่างไรก็ตามการกำหนดค่าตั้งต้นของ Shared Pool มักจะเป็นเรื่องยากเนื่องมาจากธรรมชาติที่เปลี่ยนแปลงไปอยู่เรื่อย ๆ ของ Workload ของ Application และการจะกำหนดหน่วยความจำที่จะใช้ก็ยากที่จะกำหนดตายตัวลงไป แม้แต่เมื่อเราได้ระดับประสิทธิภาพของ Shared Pool ที่ยอมรับได้แล้ว ณ ขณะหนึ่งกับค่าที่ตั้งไว้ แต่เมื่อเวลาผ่านไป การเปลี่ยนแปลง Application, การเปลี่ยนแปลงจำนวนผู้ใช้งาน และการเปลี่ยนแปลงของค่าพารามิเตอร์เริ่มต้น (Initialization Parameters) ต่างก็สามารถส่งผลในทางลบกับระบบได้ทั้งสิ้น
สมมติว่าระบบหนึ่งได้รันมาจนอยู่ตัวแล้วในระดับหนึ่ง ตอนต่อไปนี้จะให้ทิปสำคัญว่าด้วยควรจะดูอะไรเพื่อประเมินความต้องการ  SGA
การกำหนดขนาดของ Shared Pool และการ Tune เมื่อเกิดปัญหาขึ้นควรจะถูกจัดการต่างกันโดย การ Tune นั้นควรจะถูกขับเคลื่อนด้วยคอขวดที่ใหญ่ที่สุดในระบบ ซึ่งกำหนดได้จากการตรวจสอบเหตุการณ์รอคอย (Wait Events) ร่วมกับ ข้อมูล Time Model (V$SYS_TIME_MODEL) ข้อมูลนี้สามารถใช้กำหนดบริเวณที่เป็นปัญหาใหญ่ที่สุดซึ่งควรจะสนใจเป็นอันดับแรก

การกำหนดขนาดของ Shared Pool ที่เล็กเกินไป
การมี Shared Pool ที่มีขนาดเล็กเกินไป อาจทำให้เกิดปัญหาดังต่อไปนี้
  • ORA-04031 out of shared pool memory
  • Library cache lock and pin contention (มีการ Reload ของ Library Cache เป็นจำนวนมาก)
  • Row cache enqueue contention (มีการ Reload ของ Row Cache เป็นจำนวนมาก)
  • Latch Contention (shared pool, library cache, row cache)
เราจะมาขยายความกันตามลำดับดังนี้

ORA-04031
ข้อผิดพลาดตัวนี้ไม่ควรจะปรากฎใน Application Log, alert log หรือ Trace Files ใด ๆ ข้อผิดพลาด 4031 จะปรากฎใน Alert Log เมื่อมันมีผลต่อการทำงานของ Background Process (เช่น PMON), 4031 ไม่ใช่ข้อผิดพลาดภายใน (Internal Errors) ดังนั้นจึงสามารถถูกดักจับ และจัดการโดย Application ได้ (แม้ว่าจะไม่แนะนำ)
นับแต่ Oracle10gR1 เป็นต้นมา Trace File 4031 จะถูกเขียนลงไดเรคทอรี USER_DUMP_DEST หรือ BACKGROUND_DUMP_DEST, Trace File เหล่านี้มีประโยชน์ในการวิเคราะห์ธรรมชาติของปัญหา

Library Cache Reloads
Shared Pool ควรจะมีขนาดใหญ่พอ เพื่อหลีกเลี่ยง Overhead จากการ Reload Object เดิม ๆ อยู่บ่อย ๆ
ก่อนการกำหนดขนาดของ Shared Pool (เพื่อหลีกเลี่ยงการ Reload) คุณควรจะเช็คว่าการ Reload ไม่ได้เป็นผลมาจากการ Invalidation (การที่ Object เกิดข้อผิดพลาดบางประการทำให้ต้องโหลดใหม่) ถ้าจำนวนของ Invalidation มีมากจนมีนัยสำคัญ (มากกว่า 20%) เมื่อเทียบกับจำนวน Reload ทั้งหมด ก็ให้ตรวจสอบสาเหตุและแก้ปัญหาการ Invalidation ก่อน คิวรีต่อไปนี้แสดงว่ามีการ Reload และ Invalidation จำนวนเท่าใดตั้งแต่ Start Instance ขั้นมา

SELECT NAMESPACE, PINS, PINS-PINHITS LOADS, RELOADS,INVALIDATIONS, 100 * (RELOADS - INVALIDATIONS) / (PINS-PINHITS) "%RELOADS" FROM V$LIBRARYCACHE WHERE PINS >0 ORDER BY NAMESPACE;

NAMESPACE             PINS      LOADS    RELOADS INVALIDATIONS   %RELOADS
 
--------------- ---------- ---------- ---------- ------------- ----------
BODY             104188273       1052        603             0 57.3193916
CLUSTER               5613        140         79             0 56.4285714
INDEX             20675002       6151       4369             0 71.029101
SQL AREA         627459674    9140453     238610        175744 .687777728
TABLE/PROCEDURE  265612275      86391      37095             0 42.9385005
TRIGGER             483898        316        232             0 73.4177215


แม้ว่าจะไม่มีคำแนะนำที่แน่นอนตายตัว, Library Cache Reloads ควรจะมีปริมาณคิดเป็นเปอร์เซ็นต์เพียงเล็กน้อย (ประมาณ 10%) เมื่อเทียบกับจำนวนการโหลดทั้งหมด

Row Cache Misses
ถ้า Library Cache มีจำนวน Reloads สูง (หรือเรียกว่ามี Library Cache Misses สูงนั่นเอง) มักจะส่งผลให้ Dictionary Cache Misses สูงเช่นกัน ลองดูคิวรีบน V$ROWCACHE เพื่อดูข้อมูล Dictionary Cache Misses

SELECT PARAMETER , SUM(GETS) GETS , SUM(GETMISSES) GETMISSES, SUM("COUNT") NUM, SUM(USAGE) USAGE FROM V$ROWCACHE WHERE GETMISSES > 0 GROUP BY PARAMETER ORDER BY PARAMETER;

PARAMETER                    GET   GETMISSES        NUM      USAGE
 
---------------------- ---------- ---------- ---------- ----------
dc_awr_control              16460        415          1          1
dc_constraints               2001        677          0          0
dc_database_links        11383620         89          5          5
dc_files                     4248       1313          0          0
dc_global_oids             513894       1084         34         34
dc_hintsets                     1          1          0          0
dc_histogram_data       213983893      87858       5959       5959
dc_histogram_defs        61303102      97242       3983       3983
dc_object_grants         13188017       1827        250        250
dc_object_ids           301224033      22093        930        930
dc_objects               73246110      30001       1089       1089
dc_profiles              16354351         20          3          3
dc_rollback_segments     45923272        644        584        584
dc_segments              30937535      34097       1230       1230
dc_sequences                13754       3036         11         11
dc_table_scns            46760420    1505551          1          1
dc_tablespace_quotas        12121       1225         22         22
dc_tablespaces           70787039        103        102        102
dc_usernames             66902952        306         24         24
dc_users                413738271       1048        103        103
kqlsubheap_object               1          1          1          1
outstanding_alerts         314186     306164          5          5


แปลจาก "Shared Pool Memory Structures", http://wiki.oracle.com