home

หน้าแรก
 Computer >> บทช่วยสอนคอมพิวเตอร์ >  >> การเขียนโปรแกรม >> ฐานข้อมูล

ใช้ SQL Plan Baselines เพื่อเพิ่มประสิทธิภาพคิวรีข้ามสภาพแวดล้อม

คิวรี SQL 特定อาจทำงานได้ไม่ดีบนฐานข้อมูลหนึ่ง (เช่น production) แต่ทำงานได้ดีบนอีกฐานข้อมูลหนึ่ง (เช่น development) สถานการณ์นี้อาจเกิดขึ้น หากคิวรีเดียวกันมีแผนการรัน (execution plan) ที่ต่างกันบนแต่ละอินสแตนซ์ บทความนี้แสดงวิธีถ่ายโอนแผนการรันจากอินสแตนซ์ที่คิวรีทำงานดี ไปยังอีกอินสแตนซ์ที่ทำงานแย่ โดยใช้ฟีเจอร์ SQL plan baseline ที่ Oracle® Database® นำมาใช้ครั้งแรกในเวอร์ชัน 11g

บทนำ SQL Plan Management

Oracle SQL Plan Management (SPM) เป็นฟีเจอร์ใน Oracle Database ที่จับแผนการรันในอดีตทั้งหมดสำหรับคิวรี ด้วยนั้น คุณสามารถสร้าง baseline สำหรับแผนที่ดีจากแผนการรันที่มีอยู่ใน SPM และเปิดใช้งาน baseline นั้น เพื่อให้ระบบเลือกแผนที่ดีจาก baseline เท่านั้น

เพื่อใช้ประโยชน์จากฟีเจอร์นี้ คุณต้องระบุ sql_id ของคิวรีที่ทำงานดีบนอินสแตนซ์หนึ่งและทำงานแย่บนอีกอินสแตนซ์ คุณต้องจับ execution plan ID ที่ดี คือ plan_hash_value ของคิวรีบนอินสแตนซ์ที่ทำงานดี

คัดลอก SQL base plan จากอินสแตนซ์หนึ่งไปอีกอินสแตนซ์

ใช้ขั้นตอนต่อไปนี้เพื่อคัดลอก SQL base plan จาก source instance ไป target instance:

  1. รันคิวรีบน source instance ที่คิวรีทำงานดี เพื่อให้คิวรีอยู่ใน cursor cache
  2. บน source instance โหลดแผนการรัน SQL สำหรับคิวรีจาก cursor cache ไป SPM เป็น baseline
  3. สร้าง staging table บน source instance ตารางนี้ใช้สำหรับย้ายแผนการรันจาก source ไป target instance
  4. Pack source execution plan หรือ baseline ลงใน staging table บน source instance
  5. ถ่ายโอน staging table จาก source instance ไป target instance โดยใช้ export/import utility
  6. Unpack SQL plan บน target instance จาก staging table ไป SPM
  7. ตรวจสอบว่า baseline ที่สร้างบน target instance เป็น fixed และ accepted สำหรับคิวรีในการเลือกในครั้งต่อไป
  8. ทดสอบ SQL ที่มีปัญหาประสิทธิภาพบน target instance และตรวจสอบว่าเลือก baseline ที่ถ่ายโอนมา

ตัวอย่างการรัน

การรันขั้นตอนข้างต้นจะได้ output คล้ายกับตัวอย่างต่อไปนี้

ขั้นตอนที่ 1: รันคิวรีบน source instance

รัน SQL บน source instance และระบุ sql_id และ plan_hash_value ตรวจสอบ cursor cache เพื่อรับค่า ในกรณีนี้คือค่าต่อไปนี้:

  • sql_id: 9xva48wpnsmp6
  • plan_hash_value: 1572948408)

รันคิวรีต่อไปนี้บน source instance:

SQL> select distinct plan_hash_value from v$sql where sql_id='9xva48wpnsmp6'; PLAN_HASH_VALUE --------------- 1572948408

ขั้นตอนที่ 2: โหลดแผนไป SPM

รันคิวรีต่อไปนี้เพื่อโหลดแผนการรันคิวรีที่ดีจาก cursor cache ไป SPM เป็น baseline:

SQL> set serveroutput on SQL> declare 2 ret binary_integer; l_sql_id varchar2(13); 3 4 l_plan_hash_value number; 5 l_fixed varchar2(3); 6 l_enabled varchar2(3); 7 Begin 8 l_sql_id := '&&sql_id'; 9 l_plan_hash_value := to_number('&&plan_hash_value'); 10 l_fixed := 'Yes'; 11 l_enabled := 'Yes'; 12 ret := dbms_spm.load_plans_from_cursor_cache( 13 sql_id=>l_sql_id, 14 plan_hash_value=>l_plan_hash_value, 15 fixed=>l_fixed, 16 enabled=>l_enabled); 17 end; 18 / Enter value for sql_id: 9xva48wpnsmp6 old 8: l_sql_id := '&&sql_id'; new 8: l_sql_id := '9xva48wpnsmp6'; Enter value for plan_hash_value: 1572948408 old 9: l_plan_hash_value := to_number('&&plan_hash_value'); new 9: l_plan_hash_value := to_number('1572948408'); PL/SQL procedure successfully completed.

รันคิวรีที่กำหนดไว้ต่อไปนี้เพื่อตรวจสอบว่าคุณสร้าง SQL baseline บน source instance แล้ว สังเกตรายละเอียดต่อไปนี้เพื่ออ้างอิงภายหลัง

SQL> select count(*) from dba_sql_plan_baselines ; COUNT(*) -------- 1 SQL> select SQL_HANDLE, PLAN_NAME from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME ------------------------------ ------------------------------ SQL_d344aac395f978a4 SQL_PLAN_d6j5asfazky54868c96c3

ขั้นตอนที่ 3: สร้าง staging table บน source instance

รันคิวรีต่อไปนี้เพื่อสร้าง staging table บน source instance:

SQL> sho user USER is "SYS" SQL> BEGIN DBMS_SPM.CREATE_STGTAB_BASELINE( table_name => 'SPM_STAGETAB', table_owner => 'APPS', tablespace_name => 'SYSAUX'); END; 2 3 4 5 6 7 8 / PL/SQL procedure successfully completed.

ขั้นตอนที่ 4: Pack baseline

รันคิวรีต่อไปนี้เพื่อ pack baseline ลงใน staging table บน source instance:

SQL> DECLARE 2 my_plans number; 3 BEGIN 4 my_plans := DBMS_SPM.PACK_STGTAB_BASELINE( table_name => 'SPM_STAGETAB', enabled => 'yes', 5 6 7 table_owner => 'APPS', 8 plan_name => 'SQL_PLAN_d6j5asfazky54868c96c3', 9 sql_handle => 'SQL_d344aac395f978a4'); 10 END; 11 / PL/SQL procedure successfully completed.

ขั้นตอนที่ 5: ถ่ายโอน staging table จาก source ไป target instance

รันคำสั่งต่อไปนี้เพื่อสำรองข้อมูล (export) staging table บน source instance:

exp file=SPM_STAGETAB.dmp tables=APPS.SPM_STAGETAB log=SPM_STAGETAB.log compress=n Export: Release 11.2.0.4.0 - Production on Sun Jun 3 13:14:50 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: system/******* Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set About to export specified tables via Conventional Path ... Current user changed to APPS . . exporting table SPM_STAGETAB 1 rows exported Export terminated successfully without warnings.

ตอนนี้ รันคำสั่งต่อไปนี้บน target instance เพื่อถ่ายโอนไฟล์สำรอง export ของ staging table ไปยังโฮสต์ของ target instance และ import ตารางใน target instance:

imp system file=SPM_STAGETAB.dmp log=imp_SPM_STAGETAB.log fromuser=apps touser=apps Import: Release 11.2.0.4.0 - Production on Sun Jun 3 14:16:25 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set . importing APPS's objects into APPS . . importing table "SPM_STAGETAB" 1 rows imported Import terminated successfully without warnings.

ขั้นตอนที่ 6: Unpack baseline

รันคำสั่งต่อไปนี้เพื่อ unpack baseline จาก staging table ไป SPM ของ target instance ในตัวอย่างต่อไปนี้ ให้นับจำนวนก่อน unpack เพื่อตรวจสอบว่า baseline ถูก import อย่างถูกต้องบน target

SQL> select count(*) from dba_sql_plan_baselines; COUNT(*) -------- 2 SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 l_plans_unpacked PLS_INTEGER; 3 BEGIN 4 l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline( 5 table_name => 'SPM_STAGETAB', 6 table_owner => 'APPS'); 7 8 DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked); 9 END; 10 / Plans Unpacked: 1 PL/SQL procedure successfully completed. SQL> select count(*) from dba_sql_plan_baselines; COUNT(*) -------- 3

ขั้นตอนที่ 7: ตรวจสอบ baseline

รันคำสั่งต่อไปนี้บน target instance เพื่อตรวจสอบว่า baseline เป็น accepted และ fixed

SQL> SELECT sql_handle, plan_name, enabled, accepted, fixed, origin FROM dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN --------------------- ------------------------------ --- --- --- ------------ SQL_d344aac395f978a4 SQL_PLAN_d6j5asfazky54868c96c3 YES YES NO MANUAL-LOAD SQL>

เอาต์พุทข้างต้นแสดงว่า baseline ถูก import บน target instance แต่ไม่ใช่ fixed รันคิวรีต่อไปนี้เพื่อ fix baseline และให้ optimizer เลือกแผนนี้เท่านั้น

SQL> DECLARE 2 l_plans_altered PLS_INTEGER; 3 BEGIN 4 l_plans_altered := DBMS_SPM.alter_sql_plan_baseline( 5 sql_handle => 'SQL_d344aac395f978a4', 6 PLAN_NAME => 'SQL_PLAN_d6j5asfazky54868c96c3', 7 ATTRIBUTE_NAME => 'fixed', 8 attribute_value => 'YES'); 9 10 DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered); 11 END; 12 / PL/SQL procedure successfully completed. SQL> SELECT sql_handle, plan_name, enabled, accepted, fixed, origin FROM dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN --------------------- ------------------------------ --- --- --- ------------ SQL_d344aac395f978a4 SQL_PLAN_d6j5asfazky54868c96c3 YES YES YES MANUAL-LOAD SQL>

ขั้นตอนที่ 8: ทดสอบคิวรี SQL บน target instance

รันคำสั่งต่อไปนี้บน target instance เพื่อตรวจสอบว่าเลือก baseline ใหม่:

SQL> select SQL_PLAN_BASELINE from v$sql where sql_id='9xva48wpnsmp6'; SQL_PLAN_BASELINE ------------------------------ SQL_PLAN_d6j5asfazky54868c96c3

วิธีการเลือก SQL plan

ภาพต่อไปนี้แสดงวิธีการเลือก SQL plan เมื่อมี baseline plan:

ใช้ SQL Plan Baselines เพื่อเพิ่มประสิทธิภาพคิวรีข้ามสภาพแวดล้อม

แหล่งภาพ: Metalink Note Automatic SQL Plan Baselines (Doc ID 1930525.1)

สรุป

ใช้ขั้นตอนในบทความนี้หากคุณต้องถ่ายโอน baseline สำหรับคิวรีเดียว คุณยังสามารถสร้าง SQL baselines สำหรับคิวรีทั้งหมดสำหรับการอัปเกรด การย้ายถิ่น ฯลฯ ใช้ SQL plan baseline เพื่อให้มีแผนการรัน SQL ที่สอดคล้องกัน และหลีกเลี่ยงปัญหาประสิทธิภาพ

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

เรียนรู้เพิ่มเติมเกี่ยวกับบริการฐานข้อมูลและบริการแอปพลิเคชันของ Rackspace