Computer >> Máy Tính >  >> Lập trình >> Cơ sở dữ liệu

Chuyển các kế hoạch SQL bằng cách sử dụng đường cơ sở 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 (chẳng hạn như sản xuất) nhưng hoạt động tốt trên cơ sở dữ liệu khác (chẳng hạn như phát triển). Tình huống này có thể xảy ra nếu cùng một truy vấn có một kế hoạch thực thi khác nhau trên mỗi phiên bản. Thisblog chỉ ra cách chuyển kế hoạch thực thi từ một phiên bản, nơi truy vấn hoạt động tốt, sang một phiên bản khác, nơi truy vấn hoạt động kém, bằng cách sử dụng tính năng cơ sở SQLplan mà Oracle® Database® lần đầu tiên giới thiệu đảo ngược 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 Cơ sở dữ liệu Oracle thu thập tất cả các kế hoạch thực thi lịch sử cho một truy vấn. Cùng với đó, bạn có thể tạo đường cơ sở 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 đường cơ sở đó để đảm bảo rằng hệ thống chỉ chọn kế hoạch tốt từ đường cơ sở.

Để 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 phiên bản và hoạt động kém trên trường không nhanh. Bạn cũng phải nắm bắt được ID kế hoạch thực thi tốt, plan_hash_value , của truy vấn trên phiên bản mà nó hoạt động tốt.

Sao chép sơ đồ cơ sở SQL từ phiên bản này sang phiên bản khác

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

  1. Chạy truy vấn trên phiên bản nguồn, nơi truy vấn hoạt động tốt, để truy vấn tồn tại trong bộ đệm con trỏ.
  2. Trên phiên bản nguồn, tải kế hoạch thực thi SQL cho truy vấn từ bộ đệm con trỏ tới SPM làm đường cơ sở.
  3. Tạo một bảng dàn trên phiên bản nguồn. Bảng này được sử dụng để di chuyển kế hoạch thực thi từ bản sao nguồn sang bản sao đích.
  4. Đóng gói kế hoạch thực thi nguồn hoặc đường cơ sở trong bảng tổ chức trên phiên bản nguồn.
  5. Chuyển bảng dàn từ bản sao nguồn sang bản sao đích bằng cách sử dụng tiện ích xuất / nhập.
  6. Giải nén gói SQL trên phiên bản đích từ bảng dàn thành SPM.
  7. Xác minh rằng đường cơ sở được tạo trên phiên bản đích đã được sửa và được chấp nhận để truy vấn chọn nó trong lần chạy tiếp theo.
  8. Kiểm tra SQL có vấn đề về hiệu suất trên phiên bản đích và xác minh rằng nó chọn đường cơ sở đã chuyển.

Ví dụ về thực thi

Việc thực hiện các bước trước sẽ cho kết quả tương tự như các ví dụ sau.

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

Chạy SQL trên phiên bản nguồn và xác định sql_id và plan_hash_value .Kiểm tra bộ đệm con trỏ để 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 phiên bản nguồn:

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

PLAN_HASH_VALUE
---------------
1572948408

Bước 2:Tải gói lên SPM

Thực thi truy vấn sau để tải kế hoạch thực thi truy vấn tốt này từ bộ đệm con trỏ tới SPM làm đường cơ sở:

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.

Thực thi các truy vấn được xác định sau để xác minh rằng bạn đã tạo cơ sở SQL trên phiên bản 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 bảng dàn trên phiên bản nguồn

Thực thi truy vấn sau để tạo bảng dàn trên phiên bản 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 đường cơ sở

Thực thi truy vấn sau để đóng gói đường cơ sở trong bảng dàn trên phiên bản 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 bảng dàn từ nguồn sang phiên bản đích

Thực thi lệnh sau để tạo một bản sao lưu xuất của bảng dàn trên phiên bản 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ờ, hãy thực hiện lệnh sau trên phiên bản đích để chuyển bản sao lưu xuất khẩu của bảng dàn sang máy chủ của phiên bản đích và nhập bảng vào phiên bản đí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 đường cơ sở

Thực thi các lệnh sau để giải nén đường cơ sở từ bảng dàn sang SPM của cá thể mục tiêu. Trong ví dụ sau, hãy đếm trước khi giải nén đường cơ sở để xác minh rằng đường cơ sở đã được nhập đúng vào mục tiêu. 

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 đường cơ sở

Chạy các lệnh sau trên phiên bản đích để xác minh rằng đường cơ sở được chấp nhận và đã sửa. 

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 rằng đường cơ sở đã được nhập trên phiên bản đích nhưng nó không được cố định. Chạy truy vấn sau để sửa đường cơ sở và cho phép trình tối ưu hóa 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 phiên bản đích

Thực thi lệnh sau trên cá thể đích để xác minh rằng nó có lấy đường cơ sở mới:

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

SQL_PLAN_BASELINE
------------------------------
SQL_PLAN_d6j5asfazky54868c96c3

Cách chọn gói SQL

Hình ảnh sau đây cho thấy cách một kế hoạch SQL được chọn khi một kế hoạch cơ sở tồn tại:

Chuyển các kế hoạch SQL bằng cách sử dụng đường cơ sở kế hoạch SQL

Nguồn hình ảnh :Metalink Lưu ý Đường cơ sở kế hoạch SQL tự động (Doc ID 1930525.1)

Kết luận

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

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 của chúng tôi và các dịch vụ Ứng dụng Rackspace.