เพิ่มประสิทธิภาพฐานข้อมูล: เทคนิค Performance Tuning จากผู้เชี่ยวชาญ

การทำ performance tuning บนฐานข้อมูลของคุณเป็นกิจกรรมที่พบบ่อยที่สุดและสำคัญที่สุดที่ทุก database administrator (DBA) ต้องทำอย่างต่อเนื่องและเป็นประจำ ผู้เชี่ยวชาญแนะนำว่าคุณควรปรับแต่งฐานข้อมูลเป็นประจำเพื่อประสิทธิภาพที่เพิ่มประสิทธิภาพ การปรับแต่งช่วยให้ผู้ใช้รันรายงานและคิวรีได้เร็วขึ้นและได้ผลลัพธ์เร็วขึ้น บทความนี้แบ่งปันเทคนิคบางส่วนสำหรับการ troubleshooting และ tuning ฐานข้อมูล Oracle®

ขั้นตอนการปรับแต่ง

เพื่อเริ่มต้น คุณต้องระบุพื้นที่ที่ปัญหากำลังเกิดขึ้น พื้นที่ที่เป็นไปได้ที่สามารถทริกเกอร์ปัญหาได้รวมถึงระบบปฏิบัติการ ฐานข้อมูล ขาดแคลนหน่วยความจำ และอื่นๆ หลังจากที่คุณระบุพื้นที่ปัญหาแล้ว คุณสามารถดำเนินการปรับแต่งพื้นที่นั้นเพื่อประโยชน์สูงสุด บทความนี้เน้นเฉพาะ performance tuning

Oracle ให้ DBAs เครื่องมือไม่มากน้อยเพื่อช่วยในการวินิจฉัยปัญหาและ troubleshooting ปัญหาประสิทธิภาพ เครื่องมือเหล่านี้รวมถึง Automatic Database Diagnostic Monitor (ADDM) และรายงาน Automatic Workload Repository (AWR) ซึ่งคุณสามารถสร้างสำหรับช่วงเวลาที่มีปัญหาและใช้ในการวิเคราะห์ของคุณ คุณควรใช้เครื่องมือเพื่อค้นหาองค์ประกอบที่ skewed หรือ tunable

Top Timed Events ในรายงาน AWR

AWR เป็นเครื่องมือที่คุณสามารถสร้างรายงานที่สรุปการใช้หน่วยความจำในรูปแบบสถิติโดยอินสแตนซ์เฉพาะ เพื่อสร้างรายงาน AWR ให้รันไฟล์ต่อไปนี้ใน sqlplus prompt:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql 

สิ่งนี้สร้างรายงาน AWR ในรูปแบบ HTML โดยใช้เทมเพลตที่เก็บไว้ล่วงหน้า หลังจากที่คุณสร้างรายงาน AWR แล้ว คุณสามารถเห็นส่วน Top Timed Events ในรายงาน AWR ได้อย่างรวดเร็ว ซึ่งระบุพื้นที่ปัญหา ตามภาพด้านล่าง:

ADDM tuning Session

ปกติแล้ว เมื่อมีปัญหาถูกรายงาน DBA อาจ troubleshoot เฉพาะพื้นที่นั้นหรือแก้ไขปัญหาเฉพาะนั้น อย่างไรก็ตาม หากปัญหาไม่ได้รับการระบุและแก้ไขอย่างถูกต้อง อาจนำไปสู่ปัญหาที่ใหญ่กว่าในอนาคต DBA อาจพลาดภาพใหญ่ที่นี่ เพื่อช่วยให้ DBAs ได้ภาพใหญ่และครบถ้วนเกี่ยวกับปัญหาฐานข้อมูล Oracle ได้จัดเตรียมเครื่องมืออีกตัวหนึ่งสำหรับ DBAs คือ ADDM ADDM tuning session ปฏิบัติตามขั้นตอนที่คล้ายกับ manual tuning session ภาพต่อไปนี้แสดงการเปรียบเทียบ:

แหล่งภาพ: Oracle 11G Performance Tuning training manual

ดึงรายงาน ADDM โดยใช้ SQL หรือผ่าน Oracle Enterprise Manager (OEM) ภาพต่อไปนี้แสดง SQL ตัวอย่าง:

Bad SQL และ execution plans

หลังจากรายงาน AWR หรือ ADDM ระบุส่วนของ bad SQL/SQLID คุณสามารถใช้ DBMS_XPLAN เพื่อรวบรวมข้อมูลเพิ่มเติมเกี่ยวกับพวกเขา แพ็กเกจ DBMS_XPLAN จัดเตรียม table functions ต่อไปนี้ที่คุณสามารถใช้เพื่อดึงและแสดง execution (exec) plan:

DISPLAY DISPLAY_AWR select plan_table_output from table (DBMS_XPLAN.DISPLAY_AWR('fs22b3fgfh8xc')); DISPLAY_CURSOR 

ภาพต่อไปนี้แสดงตัวอย่าง output จากคำสั่ง EXPLAIN PLAN และระบุว่าคิวรีใช้ full table scan หรือ narrowing down ข้อมูลโดยใช้ indexes บางอย่าง:

รายงาน ADDM สะท้อนว่าคุณสามารถบรรลุประโยชน์ด้านประสิทธิภาพได้โดยการสร้าง indexes ใหม่บางส่วน คุณยังสามารถรัน SQL Tuning Advisor จาก OEM เพื่อปรับแต่งคิวรีให้ละเอียด และอาจใช้ execution plan ที่ดีกว่า ได้ส่วนใหญ่ การใช้แผนที่ดีกว่าสำหรับ SQL ที่มีปัญหาแก้ไขปัญหาประสิทธิภาพหลัก

SQL Tuning Advisor

SQL Tuning Advisor ช่วยวิเคราะห์ SQL statements และได้คำแนะนำด้านประสิทธิภาพโดยใช้ SQL authorization ID (SQLID) เพื่อวิเคราะห์และเสนอโซลูชัน SQL Tuning Advisor วิเคราะห์แหล่งต่อไปนี้:

• Top activity: วิเคราะห์ top SQL statements ที่ active อยู่
• SQL tuning sets: วิเคราะห์ชุดของ SQL statements ที่คุณให้
• Historical SQL (AWR): วิเคราะห์ SQL statements จาก statements ที่เก็บโดย AWR snapshots

ภาพหน้าจอต่อไปนี้แสดงตัวอย่างบางส่วน:

Long running requests

ในฐานข้อมูล Oracle eBusiness Suite ปัญหา long running requests ส่วนใหญ่เกิดขึ้นที่ concurrent requests น้อยๆ ที่ทำงานต่อเนื่อง เพื่อแก้ไข คุณต้องรวบรวมข้อมูลเพิ่มเติมเกี่ยวกับ database session ที่เกี่ยวข้องกับ concurrent requests ภาพต่อไปนี้แสดงขั้นตอนในการรวบรวมข้อมูลนี้

information:

พารามิเตอร์หน่วยความจำฐานข้อมูล Oracle

หลังจากทบทวนรายงาน AWR DBA สามารถระบุ cache ที่ต้องการปรับแต่งได้ง่าย เพราะ cache hit ratio ต่ำกว่า cache นั้นเมื่อเทียบกับ cache อื่นๆ ภาพต่อไปนี้แสดงพารามิเตอร์ฐานข้อมูลชั้นนำบางส่วนที่คุณต้องพิจารณาสำหรับ instance-wide memory tuning

แหล่งภาพ: https://ora-performance-tuning.blogspot.com/2014/02/automatic-shared-memory-management.html

Wait events ที่พบบ่อย

ตารางต่อไปนี้แสดง wait events ที่พบบ่อยและสาเหตุที่เป็นไปได้:

(

แหล่งตาราง: Oracle Performance Tuning 11G OCP training manual, chapter 20, page 24

Top 10 tuning issues

ลูกค้ามักเผชิญกับ top 10 tuning issues ต่อไปนี้:

• Bad connection management: นักพัฒนาเขียนโค้ดเพื่อเชื่อมต่อกับฐานข้อมูลในแอปพลิเคชันหรือรันคิวรีเพื่อดึงข้อมูลจากฐานข้อมูล หลังจากที่ข้อมูลถูกดึงมาแล้วและไม่ต้องการอะไรเพิ่มเติม โค้ดควรปิดการเชื่อมต่อกับฐานข้อมูล อย่างไรก็ตาม สิ่งนี้มักไม่เกิดขึ้น ซึ่งนำไปสู่จำนวน inactive sessions ที่เพิ่มขึ้นในฐานข้อมูล เซสชันเหล่านี้ใช้ทรัพยากรที่มีค่าที่อาจถูกใช้สำหรับ active connections อื่นๆ

• Bad use of cursors and shared pool: Cursor เป็นเครื่องมือในอาวุธของ Developer ที่ส่วนใหญ่ไม่ได้ถูกใช้งาน โดยไม่มี cursors Oracle ต้อง hard parse โค้ดทุกครั้งที่รัน สิ่งนี้ส่งผลกระทบอย่างมากต่อประสิทธิภาพของ SQL queries ที่รันซ้ำๆ DBA สามารถระบุปัญหานี้ได้โดยดูที่ Instance Efficiency Percentages - Execute to Parse %. ส่วนของรายงาน AWR

• Bad SQL: วิธีที่ SQL query ถูกเขียน (ด้วย join conditions เพื่อดึงข้อมูล) ส่งผลอย่างมากต่อประสิทธิภาพของ SQL นั้นหลังจากรันแล้ว คุณควรหลีกเลี่ยง full table scans สำหรับตารางขนาดใหญ่ หลังจากเตรียม SQL แล้ว นักพัฒนาและ DBAs ควรรัน explain plan บน SQL นั้นเพื่อเข้าใจต้นทุนของการรันในฐานข้อมูล โดยใช้ cursors, bind variables และ indexes คุณสามารถเพิ่มประสิทธิภาพได้

• Use of nonstandard initialization parameters: DBAs ควรใช้พารามิเตอร์ initialization มาตรฐานหรือที่แนะนำเท่านั้น ใช้ non-standard initialization parameters เฉพาะเมื่อถูกแนะนำโดย Oracle Service Request (SR)

• Getting database I/O wrong: เมื่อ DBA เลือกฮาร์ดแวร์ฐานข้อมูล ควรพยายามกระจายฐานข้อมูลบนหลายดิสก์ และเกี่ยวข้องทีมเครือข่ายในการอภิปรายเกี่ยวกับความเร็วที่ข้อมูลจะเดินทางไปยังผู้ใช้ปลายทางจากเซิร์ฟเวอร์ฐานข้อมูลและกลับมา DBA ควรพิจารณาความเร็วของ network switches และ routers เพื่อหลีกเลี่ยง bottleneck หรือปัญหาประสิทธิภาพใดๆ

• Redo log setup problems: Redo logs จำเป็นต้องจัดเก็บข้อมูลจาก redo buffer เพื่อที่ Oracle จะสามารถ redo ธุรกรรมได้ในกรณีของ crash ใดๆ หากขนาด redo log ไม่เพียงพอ multiple switches อาจเกิดขึ้นในฐานข้อมูลซึ่งทำให้เกิดปัญหาประสิทธิภาพ นี่ยังเพิ่มภาระบนการสร้าง archive

• Serialization of data blocks in the buffer cache: สิ่งนี้เกิดขึ้นเนื่องจากขาดแคลน free list groups หรือ undo segments สถานการณ์นี้เกิดขึ้นสำหรับ insert-heavy databases ที่มีฐานผู้ใช้งาน active ใหญ่ แต่มี undo segments น้อย ซึ่งในที่สุดนำไปสู่ปัญหาประสิทธิภาพ

• Full table scans: ตรวจสอบ full table scans ในคิวรีโดยการรัน explain plan ปกติคิวรีที่ทำ full table scans สะท้อน bad SQL design ซึ่งสามารถแก้ไขได้โดยใช้ indexes และ narrowing ข้อมูลที่ต้องการ ในบางกรณี full table scans อาจมีประโยชน์ โดยเฉพาะในกรณีของตารางเล็กๆ

• Recursive SQL: Recursive SQL อาจเป็นประโยชน์ต่อนักพัฒนาถ้าใช้อย่างถูกต้อง แต่สามารถเป็นดาบสองคมได้ ทำถูกต้อง มันให้ output อย่างมีประสิทธิภาพ มิฉะนั้น มันส่งผลกระทบอย่างมากต่อประสิทธิภาพของฐานข้อมูล

• In-disk sorting: In-disk sorting เป็นงานที่มีต้นทุนสูงมากสำหรับฐานข้อมูล มันบ่งชี้ poor SQL design และ bad optimization คุณสามารถระบุปัญหาในส่วน Instance Activity Stats – Sorts (disk) ของรายงาน AWR

สรุป

ในขณะที่ DBAs ต้องพิจารณาพื้นที่ต่างๆ สำหรับ performance tuning ในโลกฐานข้อมูล performance tuning เริ่มต้นจากขั้นตอนการออกแบบฐานข้อมูลและแอปพลิเคชัน ฐานข้อมูลและแอปพลิเคชันที่ออกแบบมาพร้อมมุมมอง performance tuning มีความ scalable มากกว่าแอปพลิเคชันที่ออกแบบโดยไม่คำนึงถึง performance tuning

จุดการปรับแต่งประสิทธิภาพที่ครอบคลุมในบทความนี้เพียงแค่ปลายของ iceberg ฉันแนะนำให้ DBA เพื่อนๆ อ่านเพิ่มเติมเกี่ยวกับหัวข้อนี้เพื่อดูแลประสิทธิภาพฐานข้อมูลโดยรวม

ใช้แท็บ Feedback เพื่อแสดงความคิดเห็นหรือถามคำถาม

เรียนรู้เพิ่มเติมเกี่ยวกับบริการฐานข้อมูลของเรา

Rackspace มีความรู้อย่างกว้างขวางเกี่ยวกับผลิตภัณฑ์ Oracle เรียนรู้เพิ่มเติมเกี่ยวกับวิธีที่เราสามารถเพิ่มประสิทธิภาพการลงทุน Oracle ของคุณได้