Step 1: RUN QUERY ON THE SOURCE Database
Run the SQL on the source database and get the sql_id and plan_hash_value
sql_id: 5qw0w762nh6sq
plan_hash_value: 1822515825
Execute the below query on the source database:
SQL> select distinct plan_hash_value from v$sql where sql_id='5qw0w762nh6sq';
PLAN_HASH_VALUE
---------------
1822515825
Step 2: LOAD THE PLAN TO SPM
Execute the below query to load this good query execution plan from the cursor cache to SPM as a baseline:
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: 5qw0w762nh6sq
old 8: l_sql_id := '&&sql_id';
new 8: l_sql_id := '5qw0w762nh6sq';
Enter value for plan_hash_value: 1822515825
old 9: l_plan_hash_value := to_number('&&plan_hash_value');
new 9: l_plan_hash_value := to_number('1822515825');
PL/SQL procedure successfully completed.
Execute the below queries to verify that you created the SQL baseline on the source database.
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_e455afc395f678a8 SQL_PLAN_e6j5adacky46872d84a4
Step 3: CREATE A STAGING TABLE ON THE SOURCE database
Execute the below query to create a staging table on the source database:
SQL> sho user
USER is "SYS"
SQL> SQL> BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'SPM_BASETAB',
table_owner => 'Admin',
tablespace_name => 'SYSAUX');
END;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
Step 4: PACK THE BASELINE
Execute the below query to pack the baseline in the staging table on the source database:
SQL> DECLARE
2 my_plans number;
3 BEGIN
4 my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => 'SPM_BASETAB',
enabled => 'yes',
7 table_owner => 'Admin',
8 plan_name => 'SQL_PLAN_e6j5adacky46872d84a4',
9 sql_handle => 'SQL_e455afc395f678a8 ');
10 END;
11 /
PL/SQL procedure successfully completed.
Step 5: TRANSFER THE STAGING TABLE FROM THE SOURCE TO THE TARGET database
Execute the below command to take an export backup of the staging table on the source database:
#-> expdp Admin@PDBISTDB directory=Load dumpfile=SPM_BASETAB.dmp logfile=SPM_BASETAB.log tables='Admin.SPM_BASETAB' cluster=n
Export: Release 12.1.0.2.0 - Production on Wed Jul 8 14:40:10 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Starting "Admin"."SYS_EXPORT_TABLE_01": Admin/********@PDBISTDB directory=Load dumpfile=SPM_BASETAB.dmp logfile=SPM_BASETAB.log tables=Admin.SPM_BASETAB cluster=n
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 512 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "Admin"."SPM_BASETAB" 78.88 KB 11 rows
Master table "Admin"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for Admin.SYS_EXPORT_TABLE_01 is:
/oracle/app/orawork/load/SPM_BASETAB.dmp
Job "Admin"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jul 8 14:40:54 2020 elapsed 0 00:00:35
Now, execute the below command on the target database to transfer the export backup of the staging table to the target database's host
and import the table in the target database:
#-> impdp Admin@PDBFINDB directory=Load dumpfile=SPM_BASETAB.dmp logfile=SPM_BASETAB.log cluster=n
Import: Release 12.1.0.2.0 - Production on Wed Jul 8 14:44:21 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Master table "Admin"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "Admin"."SYS_IMPORT_FULL_01": Admin/********@OTM4P directory=Load dumpfile=SPM_BASETAB.dmp logfile=SPM_BASETAB.log cluster=n
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "Admin"."SPM_BASETAB" 78.88 KB 11 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "Admin"."SYS_IMPORT_FULL_01" successfully completed at Wed Jul 8 14:44:39 2020 elapsed 0 00:00:13
Step 6: UNPACK THE BASELINE
Execute the below commands to unpack the baseline from staging table to the target database's SPM.
In the below 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(*)
--------
1
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_BASETAB',
6 table_owner => 'Admin');
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(*)
--------
2
Step 7: VERIFY THE BASELINE
Run the following commands on the target database 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_e455afc395f678a8 SQL_PLAN_e6j5adacky46872d84a4 YES YES NO MANUAL-LOAD
The output shows that the baseline was imported on target database but that it is not fixed.
Run the below query to fix the baseline and enable the optimizer to pick only this plan.
SQL> DECLARE
2 l_plans_altered PLS_INTEGER;
3 BEGIN
4 l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
5 sql_handle => 'SQL_e455afc395f678a8 ',
6 PLAN_NAME => 'SQL_PLAN_e6j5adacky46872d84a4',
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_e455afc395f678a8 SQL_PLAN_e6j5adacky46872d84a4 YES YES YES MANUAL-LOAD
Step 8: TEST THE SQL QUERY ON THE TARGET database
Execute the below command on the target database to verify that it picks up the new baseline:
SQL> select SQL_PLAN_BASELINE from v$sql where sql_id='5qw0w762nh6sq';
SQL_PLAN_BASELINE
------------------------------
SQL_PLAN_e6j5adacky46872d84a4
2 comments
Click here for commentsEasy steps to follow
ReplyGood steps
ReplyConversionConversion EmoticonEmoticon