แบบสอบถาม SQL เฉพาะอาจทำงานได้ไม่ดีในฐานข้อมูลหนึ่ง (เช่น การผลิต) แต่ทำงานได้ดีบนฐานข้อมูลอื่น (เช่น การพัฒนา) สถานการณ์นี้อาจเกิดขึ้นหากแบบสอบถามเดียวกันมีแผนการดำเนินการที่แตกต่างกันในแต่ละอินสแตนซ์ บล็อกนี้แสดงวิธีถ่ายโอนแผนการดำเนินการจากอินสแตนซ์ที่การสืบค้นทำงานได้ดี ไปยังอินสแตนซ์อื่นซึ่งการสืบค้นทำงานไม่ดี โดยใช้คุณสมบัติพื้นฐาน SQLplan ที่ Oracle® Database® เปิดตัว inversion 11g เป็นครั้งแรก
ข้อมูลเบื้องต้นเกี่ยวกับการจัดการแผน SQL
Oracle SQL Plan Management (SPM) เป็นคุณลักษณะใน Oracle Database ที่รวบรวมแผนการดำเนินการที่ผ่านมาทั้งหมดสำหรับการสืบค้น ด้วยเหตุนี้ คุณสามารถสร้างข้อมูลพื้นฐานสำหรับแผนที่ดีจากแผนการดำเนินการที่มีอยู่ใน SPM และเปิดใช้งานข้อมูลพื้นฐานนั้นเพื่อให้แน่ใจว่าระบบจะเลือกเฉพาะแผนที่ดีจากพื้นฐาน
หากต้องการใช้ประโยชน์จากคุณลักษณะนี้ คุณต้องระบุ sql_id ของแบบสอบถามที่ทำงานได้ดีในอินสแตนซ์หนึ่งและประสิทธิภาพต่ำในอีกอินสแตนซ์ คุณต้องบันทึก ID แผนปฏิบัติการที่ดี ซึ่งก็คือ plan_hash_value ,ของแบบสอบถามในอินสแตนซ์ที่ทำงานได้ดี
คัดลอกแผนฐานข้อมูล SQL จากอินสแตนซ์หนึ่งไปยังอีกอินสแตนซ์หนึ่ง
ใช้ขั้นตอนต่อไปนี้เพื่อคัดลอกแผนฐาน SQL จากอินสแตนซ์ต้นทางไปยังอินสแตนซ์เป้าหมาย:
- เรียกใช้การสืบค้นบนอินสแตนซ์ต้นทาง ซึ่งการสืบค้นทำงานได้ดี เพื่อให้การสืบค้นอยู่ในแคชเคอร์เซอร์
- บนอินสแตนซ์ต้นทาง โหลดแผนการดำเนินการ SQL สำหรับแบบสอบถามจากแคชเคอร์เซอร์ไปยัง SPM เป็นพื้นฐาน
- สร้างตารางการจัดเตรียมบนอินสแตนซ์ต้นทาง ตารางนี้ใช้เพื่อย้ายแผนการดำเนินการจากอินสแตนซ์ต้นทางไปยังอินสแตนซ์เป้าหมาย
- แพ็คแผนการดำเนินการต้นทางหรือข้อมูลพื้นฐานในตารางการจัดเตรียมบนอินสแตนซ์ต้นทาง
- โอนตารางการจัดเตรียมจากอินสแตนซ์ต้นทางไปยังอินสแตนซ์เป้าหมายโดยใช้ยูทิลิตี้ส่งออก/นำเข้า
- แกะแผน SQL บนอินสแตนซ์เป้าหมายจากตาราง staging ไปยัง SPM
- ตรวจสอบว่าเส้นฐานที่สร้างบนอินสแตนซ์เป้าหมายได้รับการแก้ไขและยอมรับสำหรับการค้นหาเพื่อเลือกในการเรียกใช้ครั้งต่อไป
- ทดสอบ SQL ที่มีปัญหาด้านประสิทธิภาพในอินสแตนซ์เป้าหมายและตรวจสอบว่าเลือกพื้นฐานที่โอนมาหรือไม่
ตัวอย่างการดำเนินการ
การดำเนินการตามขั้นตอนก่อนหน้าจะทำให้ได้ผลลัพธ์ที่คล้ายกับตัวอย่างต่อไปนี้
ขั้นตอนที่ 1:เรียกใช้การสืบค้นบนอินสแตนซ์ต้นทาง
เรียกใช้ SQL บนอินสแตนซ์ต้นทางและระบุ sql_id และ plan_hash_value . ตรวจสอบแคชเคอร์เซอร์เพื่อรับค่า ในกรณีนี้ เป็นค่าต่อไปนี้:
sql_id:9xva48wpnsmp6plan_hash_value:1572948408)
ดำเนินการค้นหาต่อไปนี้ในอินสแตนซ์ต้นทาง:
SQL> select distinct plan_hash_value from v$sql where sql_id='9xva48wpnsmp6';
PLAN_HASH_VALUE
---------------
1572948408
ขั้นตอนที่ 2:โหลดแผนไปยัง SPM
ดำเนินการแบบสอบถามต่อไปนี้เพื่อโหลดแผนการดำเนินการแบบสอบถามที่ดีนี้จากแคชเคอร์เซอร์ไปยัง SPM เป็นพื้นฐาน:
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 บนอินสแตนซ์ต้นทาง โปรดสังเกตรายละเอียดต่อไปนี้เพื่อใช้อ้างอิงในภายหลัง
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:สร้างตารางการจัดเตรียมบนอินสแตนซ์ต้นทาง
ดำเนินการค้นหาต่อไปนี้เพื่อสร้างตารางการจัดเตรียมบนอินสแตนซ์ต้นทาง:
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:บรรจุข้อมูลพื้นฐาน
ดำเนินการแบบสอบถามต่อไปนี้เพื่อแพ็คข้อมูลพื้นฐานในตารางการจัดเตรียมบนอินสแตนซ์ต้นทาง:
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:โอนตารางการจัดเตรียมจากต้นทางไปยังอินสแตนซ์เป้าหมาย
ดำเนินการคำสั่งต่อไปนี้เพื่อทำการสำรองข้อมูลการส่งออกของตารางการจัดเตรียมบนอินสแตนซ์ต้นทาง:
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.
ตอนนี้ ให้รันคำสั่งต่อไปนี้บนอินสแตนซ์เป้าหมายเพื่อถ่ายโอนข้อมูลสำรองของตารางการจัดเตรียมไปยังโฮสต์ของอินสแตนซ์เป้าหมายและนำเข้าตารางในอินสแตนซ์เป้าหมาย:
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:แกะเส้นฐาน
ดำเนินการคำสั่งต่อไปนี้เพื่อแกะ baseline จาก staging table ไปยัง SPM ของอินสแตนซ์เป้าหมาย ในตัวอย่างต่อไปนี้ ให้นับก่อนที่จะแกะเส้นฐานเพื่อยืนยันว่าเส้นฐานถูกนำเข้าอย่างถูกต้องบนเป้าหมาย
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:ตรวจสอบข้อมูลพื้นฐาน
รันคำสั่งต่อไปนี้บนอินสแตนซ์เป้าหมายเพื่อตรวจสอบว่า baselineis ได้รับการยอมรับและแก้ไขแล้ว
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>
ผลลัพธ์ก่อนหน้าแสดงว่ามีการนำเข้าข้อมูลพื้นฐานบนอินสแตนซ์เป้าหมายแต่ไม่ได้รับการแก้ไข เรียกใช้แบบสอบถามต่อไปนี้เพื่อแก้ไขพื้นฐานและเปิดใช้งานเครื่องมือเพิ่มประสิทธิภาพเพื่อเลือกแผนนี้เท่านั้น
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 บนอินสแตนซ์เป้าหมาย
ดำเนินการคำสั่งต่อไปนี้บนอินสแตนซ์เป้าหมายเพื่อตรวจสอบว่าได้รับข้อมูลพื้นฐานใหม่หรือไม่:
SQL> select SQL_PLAN_BASELINE from v$sql where sql_id='9xva48wpnsmp6';
SQL_PLAN_BASELINE
------------------------------
SQL_PLAN_d6j5asfazky54868c96c3
วิธีการเลือกแผน SQL
รูปภาพต่อไปนี้แสดงวิธีการเลือกแผน SQL เมื่อมีแผนพื้นฐาน:
แหล่งที่มาของรูปภาพ :Metalink Note พื้นฐานแผน SQL อัตโนมัติ (Doc ID 1930525.1)
บทสรุป
ใช้ขั้นตอนในโพสต์นี้หากคุณต้องโอนข้อมูลพื้นฐานสำหรับการค้นหาเดี่ยว คุณยังสามารถสร้างบรรทัดฐานของ SQL สำหรับการสืบค้นทั้งหมดสำหรับการอัปเกรด การย้ายข้อมูล และอื่นๆ ใช้พื้นฐานแผน SQL เพื่อมีแผนการดำเนินการ SQL ที่สอดคล้องกันและเพื่อหลีกเลี่ยงปัญหาด้านประสิทธิภาพ
ใช้แท็บคำติชมเพื่อแสดงความคิดเห็นหรือถามคำถาม
เรียนรู้เพิ่มเติมเกี่ยวกับบริการฐานข้อมูลและบริการแอปพลิเคชัน Rackspace
