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) นี้ในระดับหนึ่ง โดยที่ในบางขณะองค์ประกอบจะคืนหน่วยความจำออกมาเพื่อที่องค์ประกอบอื่นสามารถนำไปใช้ต่อ
วิวในระดับ 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 (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
