Computer >> Hướng Dẫn Máy Tính >  >> Lập Trình >> Cơ Sở Dữ Liệu

Tận Dụng SQL Plan Baselines Để Tối Ưu Truy Vấn Trên Các Môi Trường

Giới Thiệu Về Quản Lý Kế Hoạch SQL

Một truy vấn SQL cụ thể có thể hoạt động kém trên một cơ sở dữ liệu (như production) nhưng hoạt động tốt trên cơ sở dữ liệu khác (như development). Tình huống này có thể xảy ra nếu cùng một truy vấn có kế hoạch thực thi khác nhau trên từng instance. Blog này cho thấy cách chuyển kế hoạch thực thi từ một instance, nơi truy vấn hoạt động tốt, sang instance khác, nơi truy vấn hoạt động kém, bằng cách sử dụng tính năng SQL plan baseline mà Oracle® Database® lần đầu tiên giới thiệu trong phiên bản 11g.

Giới Thiệu Về Quản Lý Kế Hoạch SQL

Oracle SQL Plan Management (SPM) là một tính năng trong Oracle Database thu thập tất cả các kế hoạch thực thi lịch sử cho một truy vấn. Với điều đó, bạn có thể tạo baseline cho kế hoạch tốt từ các kế hoạch thực thi có sẵn trong SPM và kích hoạt baseline đó để đảm bảo hệ thống chỉ chọn kế hoạch tốt từ baseline.

Để tận dụng tính năng này, bạn phải xác định sql_id của truy vấn đang hoạt động tốt trên một instance và hoạt động kém trên instance khác. Bạn cũng phải nắm bắt ID kế hoạch thực thi tốt, plan_hash_value, của truy vấn trên instance nơi nó hoạt động tốt.

Sao Chép Kế Hoạch Cơ Sở SQL Từ Instance Này Sang Instance Khác

Sử dụng các bước sau để sao chép kế hoạch cơ sở SQL từ instance nguồn sang instance đích:

  1. Chạy truy vấn trên instance nguồn, nơi truy vấn hoạt động tốt, để truy vấn tồn tại trong cursor cache.
  2. Trên instance nguồn, tải kế hoạch thực thi SQL cho truy vấn từ cursor cache vào SPM làm baseline.
  3. Tạo staging table trên instance nguồn. Bảng này được dùng để di chuyển kế hoạch thực thi từ instance nguồn sang instance đích.
  4. Đóng gói kế hoạch thực thi nguồn, hoặc baseline, trong staging table trên instance nguồn.
  5. Chuyển staging table từ instance nguồn sang instance đích bằng tiện ích export/import.
  6. Giải nén kế hoạch SQL trên instance đích từ staging table vào SPM.
  7. Xác minh baseline được tạo trên instance đích là fixed và accepted để truy vấn chọn nó ở lần chạy tiếp theo.
  8. Kiểm tra SQL gặp vấn đề hiệu suất trên instance đích và xác minh rằng nó chọn baseline đã chuyển.

Ví Dụ Thực Thi

Thực thi các bước trước đó dẫn đến đầu ra tương tự như các ví dụ sau.

Bước 1: Chạy truy vấn trên instance nguồn

Chạy SQL trên instance nguồn và xác định sql_id và plan_hash_value. Kiểm tra cursor cache để lấy các giá trị. Trong trường hợp này, chúng là các giá trị sau:

  • sql_id: 9xva48wpnsmp6
  • plan_hash_value: 1572948408)

Thực thi truy vấn sau trên instance nguồn:

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

Bước 2: Tải kế hoạch vào SPM

Thực thi truy vấn sau để tải kế hoạch thực thi truy vấn tốt này từ cursor cache vào SPM làm baseline:

SQL> set serveroutput on
SQL> declare
2 ret binary_integer;
l_sql_id varchar2(13);
3
l_plan_hash_value number;
l_fixed varchar2(3);
l_enabled varchar2(3);
4 Begin
5 l_sql_id := '&&sql_id';
6 l_plan_hash_value := to_number('&&plan_hash_value');
7 l_fixed := 'Yes';
8 l_enabled := 'Yes';
9 ret := dbms_spm.load_plans_from_cursor_cache(
10 sql_id=>l_sql_id,
11 plan_hash_value=>l_plan_hash_value,
12 fixed=>l_fixed,
13 enabled=>l_enabled);
14 end;
15 /
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.

Thực thi các truy vấn được định nghĩa sau để xác minh bạn đã tạo SQL baseline trên instance nguồn. Lưu ý các chi tiết sau để tham khảo sau.

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

Bước 3: Tạo staging table trên instance nguồn

Thực thi truy vấn sau để tạo staging table trên instance nguồn:

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.

Bước 4: Đóng gói baseline

Thực thi truy vấn sau để đóng gói baseline trong staging table trên instance nguồn:

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.

Bước 5: Chuyển staging table từ instance nguồn sang instance đích

Thực thi lệnh sau để lấy export backup của staging table trên instance nguồn:

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.

Bây giờ, thực thi lệnh sau trên instance đích để chuyển export backup của staging table sang host của instance đích và import bảng trong instance đích:

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.

Bước 6: Giải nén baseline

Thực thi các lệnh sau để giải nén baseline từ staging table vào SPM của instance đích. Trong ví dụ sau, lấy count trước khi giải nén baseline để xác minh baseline được import đúng cách trên instance đích.

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

Bước 7: Xác minh baseline

Chạy các lệnh sau trên instance đích để xác minh baseline được accepted và 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>

Đầu ra trước đó cho thấy baseline đã được import trên instance đích nhưng nó không fixed. Chạy truy vấn sau để fix baseline và cho phép optimizer chỉ chọn kế hoạch này.

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>

Bước 8: Kiểm tra truy vấn SQL trên instance đích

Thực thi lệnh sau trên instance đích để xác minh rằng nó chọn baseline mới:

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

Cách Kế Hoạch SQL Được Chọn

Hình ảnh sau cho thấy cách kế hoạch SQL được chọn khi baseline plan tồn tại:

Tận Dụng SQL Plan Baselines Để Tối Ưu Truy Vấn Trên Các Môi Trường

Nguồn hình ảnh: Metalink Note Automatic SQL Plan Baselines (Doc ID 1930525.1)

Kết Luận

Sử dụng các bước trong bài viết này nếu bạn phải chuyển baseline cho một truy vấn đơn lẻ. Bạn cũng có thể tạo SQL baseline cho tất cả các truy vấn cho nâng cấp, di chuyển, v.v. Sử dụng SQL plan baseline để có kế hoạch thực thi SQL nhất quán và tránh bất kỳ vấn đề hiệu suất nào.

Sử dụng tab Phản hồi để đưa ra bất kỳ nhận xét hoặc đặt câu hỏi nào.

Tìm hiểu thêm về các dịch vụ cơ sở dữ liệu và dịch vụ ứng dụng Rackspace của chúng tôi.