Optimizer, Profile และ Baseline
บทความนี้เน้นความแตกต่างระหว่าง SQL profile และ baselines ใน Oracle® และอธิบายว่าทำงานอย่างไรเมื่อปรับแต่งคิวรี
Optimizer, Profile และ Baseline
ในระดับสูง สองสิ่งนี้ทำงานร่วมกันดังนี้:
-
Query optimizer ใช้ข้อมูลเช่น system statistics, bind variables, และ compilations เพื่อหาแผนที่ดีที่สุดสำหรับการรันคิวรี อย่างไรก็ตาม บางครั้งข้อบกพร่องของอินพุตนำไปสู่แผนที่ไม่เหมาะสม
-
SQL profiles มีข้อมูลเสริมที่ช่วยลดปัญหานี้ พวกเขาลดข้อผิดพลาดเหล่านี้และช่วยให้ optimizer เลือกแผนที่ดีที่สุด
-
SQL plan baselines สำหรับ SQL statements ประกอบด้วยชุดของแผนที่ยอมรับ หลังจาก parsing statement optimizer จะเลือกแผนที่ดีที่สุดจากชุดแผนที่ยอมรับ หาก cost optimizer พบแผนที่ดีอีกแผนหนึ่ง จะเพิ่มแผนใหม่เข้าแผนประวัติ แต่ optimizer จะไม่ใช้แผนใหม่จนกว่าจะตรวจสอบว่าแผนใหม่นั้นจะทำงานดีกว่าแผนที่ยอมรับอยู่หรือไม่
คิดได้ว่า: SQL profiles ให้ข้อมูลแก่ optimizer เพื่อช่วยเลือกแผนที่ดีที่สุด แต่ไม่บังคับให้ optimizer เลือกแผนใดแผนหนึ่ง SQL plan baselines จำกัดการเลือกแผนของ optimizer ให้อยู่ในชุดแผนที่ยอมรับ หากต้องการพิจารณาแผนที่คำนวณจาก cost ให้นำแผนเหล่านั้นเข้าสู่ชุด baseline ที่ยอมรับ
ใช้ SQL profiles หากต้องการให้ optimizer ใช้แผน least-cost และต้องการใช้สถิติล่าสุด ใช้ baselines เมื่อต้องการใช้หนึ่งในชุดแผนเฉพาะ หาก SQL plan baselines ไม่สามารถหาแผนที่ดีที่สุดจากชุดที่ยอมรับได้ ให้ใช้ SQL profile แทน
SQL Plan Management
SQL Plan Management (SPM) มีส่วนประกอบดังนี้:
- Plan capture
- Plan selection
- Plan evolution
SPM Plan Capture
เมื่อคุณรัน statement ใดๆ ระบบจะ hard parse และสร้าง cost plan ตาม SQL profile ที่มีอยู่ หลังจากเลือก cost-based plan แล้ว จะเปรียบเทียบกับแผนที่มีใน SQL plan baselines หาก cost-based generated plan ตรงกับหนึ่งในแผนที่ยอมรับ สามารถใช้แผนนั้นได้ หากแผนไม่ตรงกัน ระบบจะเพิ่มเข้า baselines เป็นแผนที่ยังไม่ได้ยอมรับ
SPM Plan Selection
เมื่อรัน SQL statement ด้วย baseline plan จะเลือกแผนที่ดีที่สุดสำหรับ SQL นั้น Optimizer ใช้กระบวนการเดียวกัน SQL profile ที่มีอยู่ก็ส่งผลต่อ estimated cost ของแต่ละแผนและเลือกแผนตามนั้น
SPM Plan Evolution
ส่วนสุดท้ายของ SPM คือ演化ของแผนที่ยังไม่ได้ยอมรับ ซึ่งทดสอบแผนที่ยังไม่ได้ยอมรับกับแผนที่ยอมรับ กระบวนการนี้ประเมินแผนที่ดีที่สุด โดยพิจารณาจากเวลาที่คิวรีใช้และทรัพยากร CPU ที่ต้องการ ยอมรับแผนที่ดีที่สุดตาม cost ของคิวรี หากมี SQL profile จะส่งผลต่อ estimated cost
การเปรียบเทียบ Profile และ Baseline
ตารางต่อไปนี้ จาก https://www.cnblogs.com/princessd8251/articles/3637461.html เปรียบเทียบ SQL profiles และ SQL plan baselines:
สถาปัตยกรรม
Image Source: https://ittutorial.org/sql-plan-management-using-sql-plan-baselines-in-oracle-oracle-database-performance-tuning-tutorial-14/)
โหลด SQL Plan Baselines
ภาพต่อไปนี้แสดง 2 วิธีในการโหลด SQL plan baselines:
Image Source: https://ittutorial.org/sql-plan-management-using-sql-plan-baselines-in-oracle-oracle-database-performance-tuning-tutorial-14/
ด้วยวิธีแรก คุณสามารถตั้งค่าพารามิเตอร์ initialization OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES เป็น TRUE เพื่อใช้ automatic plan capture พารามิเตอร์นี้เป็น FALSE โดยค่าเริ่มต้น ดังนั้นให้ตั้งเป็น TRUE ตามตัวอย่างต่อไปนี้:
ด้วยวิธีที่สอง คุณสามารถใช้แพ็กเกจ DBMS_SPM เพื่อจัดการ SQL plan baselines แบบมืออาชีพ โหลดแผนจาก SQL tuning set ตามตัวอย่างต่อไปนี้:
โหลด SQL Plan Baseline แบบมืออาชีพ
ใช้คำสั่งต่อไปนี้เพื่อโหลดแผน baseline แบบมืออาชีพ:
ตรวจสอบการใช้งาน SQL Plan Baseline
หลังจากโหลด SQL plan baseline แล้ว คุณต้องรัน SQL เพื่อดูว่า optimizer ใช้ SQL plan baseline หรือไม่ คุณสามารถสอบถาม SQL plan baselines โดยใช้ SQL_TEXT และชื่อแผน ดังนี้:
แสดง SQL Plan Baselines
รันคิวรีต่อไปนี้เพื่อแสดง SQL plan baselines:
ลบ SQL Plan Baseline
เพื่อลบ SQL plan baseline ให้รันคิวรีต่อไปนี้เพื่อตรวจสอบว่า optimizer ใช้ SQL plan 哪个:
จากนั้น หลังจากได้แผนที่ใช้งานอยู่ ให้รันคำสั่งต่อไปนี้เพื่อลบ baseline:
Oracle SQL Profile
SQL tuning advisor ซึ่งคุณเรียกใช้ผ่าน Oracle Enterprise Manager (OEM) หรือโดยใช้คิวรี command-line สามารถสร้าง SQL profile สำหรับ SQL statement ได้ Profile นี้รวมข้อมูลเพิ่มเติมเกี่ยวกับ statement
ตัวอย่าง
ในตัวอย่างนี้ คุณจะรัน SQL tuning advisor ต่อ sql_id ก่อน แล้วจึงดำเนินการกับ SQL profile:
1. รัน SQL Tuning Advisor
รันโค้ด SQL tuning advisor ต่อไปนี้สำหรับ sql_id 6dkrnbx1zdwy38:
รัน DBMS_SQLTUNE.report_tuning_task ต่อไปนี้เพื่อขอคำแนะนำ:
2. ยอมรับ sql_profile
รันโค้ดต่อไปนี้เพื่อยอมรับ sql_profile:
3. ตรวจสอบชื่อ sql_profile
ใช้คิวรีต่อไปนี้เพื่อตรวจสอบชื่อ sql_profile:
4. ปิดใช้งาน sql_profile
รันโค้ดต่อไปนี้เพื่อปิดใช้งาน sql_profile:
เพื่อเปิดใช้งาน ให้เปลี่ยนค่าจาก DISABLED เป็น ENABLED
5. ลบ sql_profile
รันโค้ดต่อไปนี้เพื่อลบ sql_profile:
สรุป
เมื่อคุณรัน SQL statement ใดๆ Optimizer จะสร้าง execution plan เพื่อ parse คิวรี ดึงข้อมูลจากฮาร์ดดิสก์ และวางไว้ในหน่วยความจำ SQL profile และ baselines ช่วยให้ optimizer เลือกแผนที่มีต้นทุนน้อยที่สุดในแง่ของเวลาและ CPU cost แผน SQL ที่ดีจะรันคิวรีได้อย่างมีประสิทธิภาพและให้ผลลัพธ์ที่ต้องการเร็วขึ้น
เรียนรู้เพิ่มเติมเกี่ยวกับบริการฐานข้อมูลของเรา
ใช้แท็บ Feedback เพื่อแสดงความคิดเห็นหรือถามคำถาม คุณยังสามารถเริ่มสนทนากับเราได้
