SQL योजना आधार रेखा का उपयोग करके SQL योजनाओं को स्थानांतरित करें

एक विशेष SQL क्वेरी एक डेटाबेस (जैसे उत्पादन) पर खराब प्रदर्शन कर सकती है लेकिन दूसरे डेटाबेस (जैसे विकास) पर ठीक काम करती है। यह स्थिति हो सकती है यदि एक ही क्वेरी में प्रत्येक उदाहरण पर एक अलग निष्पादन योजना है। यह ब्लॉग दिखाता है कि एक उदाहरण से निष्पादन योजना को कैसे स्थानांतरित किया जाए, जहां क्वेरी ठीक काम करती है, दूसरे उदाहरण में, जहां क्वेरी खराब व्यवहार करती है, SQLplan बेसलाइन सुविधा का उपयोग करके, जिसे Oracle® Database® ने पहली बार उलटा 11g पेश किया था।

एसक्यूएल प्लान मैनेजमेंट का परिचय

Oracle SQL प्लान मैनेजमेंट (SPM) Oracle डेटाबेस में एक विशेषता है जो एक क्वेरी के लिए सभी ऐतिहासिक निष्पादन योजनाओं को कैप्चर करता है। इसके साथ, आप एसपीएम में उपलब्ध निष्पादन योजनाओं से अच्छी योजना के लिए आधार रेखा बना सकते हैं और उस आधार रेखा को सक्षम कर सकते हैं ताकि यह सुनिश्चित हो सके कि सिस्टम बेसलाइन से केवल अच्छी योजना चुनता है।

इस सुविधा का लाभ उठाने के लिए, आपको sql_id . की पहचान करनी होगी क्वेरी का जो एक उदाहरण पर अच्छा प्रदर्शन कर रहा है और दूसरे उदाहरण पर खराब प्रदर्शन कर रहा है। आपको अच्छी निष्पादन योजना आईडी, plan_hash_value . को भी कैप्चर करना होगा , उस उदाहरण पर क्वेरी का जहां यह अच्छी तरह से काम करता है।

SQL बेस प्लान को एक इंस्टेंस से दूसरे इंस्टेंस में कॉपी करें

SQL बेस प्लान को सोर्स इंस्टेंस से टारगेट इंस्टेंस में कॉपी करने के लिए निम्न चरणों का उपयोग करें:

क्वेरी को सोर्स इंस्टेंस पर चलाएं, जहां क्वेरी अच्छी तरह से काम करती है, ताकि क्वेरी कर्सर कैश में मौजूद रहे।
स्रोत उदाहरण पर, क्वेरी के लिए SQL निष्पादन योजना को कर्सर कैश से SPM में बेसलाइन के रूप में लोड करें।
सोर्स इंस्टेंस पर एक स्टेजिंग टेबल बनाएं। इस तालिका का उपयोग निष्पादन योजना को स्रोत उदाहरण से लक्ष्य उदाहरण में माइग्रेट करने के लिए किया जाता है।
स्रोत निष्पादन योजना या आधार रेखा को स्रोत उदाहरण पर स्टेजिंग तालिका में पैक करें।
निर्यात/आयात उपयोगिता का उपयोग करके स्टेजिंग तालिका को स्रोत उदाहरण से लक्ष्य उदाहरण में स्थानांतरित करें।
स्टेजिंग टेबल से SPM तक टारगेट इंस्टेंस पर SQL प्लान को अनपैक करें।
सत्यापित करें कि लक्ष्य उदाहरण पर बनाई गई आधार रेखा तय है और अगले रन पर इसे चुनने के लिए क्वेरी के लिए स्वीकार की गई है।
उस SQL का परीक्षण करें जिसमें लक्ष्य उदाहरण पर एक प्रदर्शन समस्या थी और सत्यापित करें कि यह स्थानांतरित आधार रेखा को चुनता है।

उदाहरण निष्पादन

पिछले चरणों को निष्पादित करने से निम्न उदाहरणों के समान आउटपुट प्राप्त होता है।

चरण 1:स्रोत इंस्टेंस पर क्वेरी चलाएँ

स्रोत इंस्टेंस पर SQL चलाएँ और sql_id . की पहचान करें और plan_hash_value मान प्राप्त करने के लिए कर्सर कैश की जांच करें। इस मामले में, वे निम्नलिखित मान हैं:

sql_id :9xva48wpnsmp6
plan_hash_value :157294808)

स्रोत उदाहरण पर निम्न क्वेरी निष्पादित करें:

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:बेसलाइन को अनपैक करें

बेसलाइन को स्टेजिंग टेबल से टारगेट इंस्टेंस के एसपीएम तक अनपैक करने के लिए निम्न कमांड निष्पादित करें। निम्नलिखित उदाहरण में, बेसलाइन को अनपैक करने से पहले यह सत्यापित करने के लिए एक गिनती लें कि बेसलाइन को लक्ष्य पर ठीक से आयात किया गया था।

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:आधार रेखा सत्यापित करें

यह सत्यापित करने के लिए लक्ष्य उदाहरण पर निम्न आदेश चलाएँ कि आधार रेखा स्वीकृत और स्थिर है।

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 योजना का चयन कैसे किया जाता है:

SQL योजना आधार रेखा का उपयोग करके SQL योजनाओं को स्थानांतरित करें

छवि स्रोत :मेटलिंक नोट स्वचालित SQL प्लान बेसलाइन (डॉक्टर आईडी 1930525.1)

निष्कर्ष

इस पोस्ट में दिए गए चरणों का उपयोग करें यदि आपको किसी एकल क्वेरी के लिए आधार रेखा को स्थानांतरित करना है। आप अपग्रेड, माइग्रेशन आदि के लिए सभी प्रश्नों के लिए SQL बेसलाइन भी जेनरेट कर सकते हैं। लगातार SQL निष्पादन योजनाओं के लिए और किसी भी प्रदर्शन समस्या से बचने के लिए SQL योजना आधार रेखा का उपयोग करें।

कोई टिप्पणी करने या प्रश्न पूछने के लिए प्रतिक्रिया टैब का उपयोग करें।

हमारी डेटाबेस सेवाओं और रैकस्पेस एप्लिकेशन सेवाओं के बारे में अधिक जानें।