Use the following steps to copy the SQL base plan from the source databaseto the target instance:
1) Run the query on the source instance, where the query works well, so that the query exists in the cursor cache.
2) On the source instance, load the SQL execution plan for the query from the cursor cache to SPM as a baseline.
3) Create a staging table on the source instance. This table is used to migrate the execution plan from the source instance to the target instance.
4) Pack the source execution plan, or baseline, in the staging table on the source instance.
5) Transfer the staging table from the source instance to the target instance by using the export/import utility.
6) Unpack the SQL plan on the target instance from the staging table to SPM.
7) Verify that baseline created on target instance is fixed and accepted for the query to select it on next run.
8) Test the SQL that had a performance issue on the target instance and verify that it picks the transferred baseline.
Example execution
Executing the preceding steps results in output similar to the following examples.
STEP 1: RUN QUERY ON THE SOURCE INSTANCE
Run the SQL on the source instance and identify the sql_id and plan_hash_value. Examine the cursor cache to get the values. In this case, they are the following values:
sql_id: 9dtxw5f7qdnvg
plan_hash_value: 3505580018
SQL> select distinct plan_hash_value from v$sql where sql_id='9dtxw5f7qdnvg';
PLAN_HASH_VALUE
---------------
3505580018
STEP 2: LOAD THE PLAN TO SPM
Execute the following query to load this good query execution plan from the cursor cache to SPM as a baseline:
SQL> set serveroutput on
SQL> declare
ret binary_integer;
l_sql_id varchar2(13);
l_plan_hash_value number;
l_fixed varchar2(3);
l_enabled varchar2(3);
Begin
l_sql_id := '&&sql_id';
l_plan_hash_value := to_number('&&plan_hash_value');
l_fixed := 'Yes';
l_enabled := 'Yes';
ret := dbms_spm.load_plans_from_cursor_cache(
sql_id=>l_sql_id,
plan_hash_value=>l_plan_hash_value,
fixed=>l_fixed,
enabled=>l_enabled);
end;
/
nter value for sql_id: 9dtxw5f7qdnvg
old 8: l_sql_id := '&&sql_id';
new 8: l_sql_id := '9dtxw5f7qdnvg';
Enter value for plan_hash_value: 3505580018
old 9: l_plan_hash_value := to_number('&&plan_hash_value');
new 9: l_plan_hash_value := to_number('3505580018');
PL/SQL procedure successfully completed.
Execute the following defined queries to verify that you created the SQL baseline on the source instance. Note the following details for later reference.
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
STEP 3: CREATE A STAGING TABLE ON THE SOURCE INSTANCE
Execute the following query to create a staging table on the 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;
/
PL/SQL procedure successfully completed.
STEP 4: PACK THE BASELINE
Execute the following query to pack the baseline in the staging table on the source instance:
SQL> DECLARE
my_plans number;
BEGIN
my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => 'SPM_STAGETAB',
enabled => 'yes',
table_owner => 'APPS',
plan_name => 'SQL_PLAN_d6j5asfazky54868c96c3',
sql_handle => 'SQL_d344aac395f978a4');
END;
/
PL/SQL procedure successfully completed.
STEP 5: TRANSFER THE STAGING TABLE FROM THE SOURCE TO THE TARGET INSTANCE
Execute the following command to take an export backup of the staging table on the 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.
Now, execute the following command on the target instance to transfer the export backup of the staging table to the target instance's host and import the table in the 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.
STEP 6: UNPACK THE BASELINE
Execute the following commands to unpack the baseline from staging table to the target instance's SPM. In the following example, take a count before unpacking the baseline to verify that the baseline was imported properly on the target.
SQL> select count(*) from dba_sql_plan_baselines;
COUNT(*)
--------
0
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
STEP 7: VERIFY THE BASELINE
Run the following commands on the target instance to verify that the baseline is accepted and 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>
The preceding output shows that the baseline was imported on target instance but that it is not fixed. Run the following query to fix the baseline and enable the optimizer to pick only this plan.
SQL> DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => 'SQL_d344aac395f978a4',
PLAN_NAME => 'SQL_PLAN_d6j5asfazky54868c96c3',
ATTRIBUTE_NAME => 'fixed',
attribute_value => 'YES');
DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/
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>
STEP 8: TEST THE SQL QUERY ON THE TARGET INSTANCE
Execute the following command on the target instance to verify that it picks up the new baseline:
SQL> select SQL_PLAN_BASELINE from v$sql where sql_id='9dtxw5f7qdnvg';
SQL_PLAN_BASELINE
------------------------------
SQL_PLAN_d6j5asfazky54868c96c3
No comments:
Post a Comment