Create Baseline to change execution plan hash value

Issue:

When a query is having a new plan which is worst and you found by SQL tuning Advisor there is another plan which is having better execution time.














       

There is a better plan in AWR as in above Fig.

Steps to change plan hash value.

Step1:  Create SQL tuning set with SQL_ID attached.

Drop if same name exists before
 exec dbms_sqltune.DROP_SQLSET('sqlid_1vktnmmbp6cz3');

Create new one as follows.


 exec DBMS_SQLTUNE.CREATE_SQLSET('sqlid_1vktnmmbp6cz3');


Step2: Load plan  you want for the SQL ID , replace sql_id and plan hash value you want in following blocks.

Following will extract the plan from awr.

declare
 baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
 begin
 open baseline_ref_cursor for
 select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(1, 128,'sql_id='||CHR(39)||'1vktnmmbp6cz3'||CHR(39)||' and plan_hash_value=487911821',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
 DBMS_SQLTUNE.LOAD_SQLSET('sqlid_1vktnmmbp6cz3', baseline_ref_cursor);
 end;
 /
 above when executed will throw out correct snapshot ids as follows

 08:35:15 SQL> declare
 08:35:34   2   baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
 08:35:34   3   begin
 08:35:34   4   open baseline_ref_cursor for
 08:35:34   5   select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(1, 128,'sql_id='||CHR(39)||'1vktnmmbp6cz3'||CHR(39)||' and plan_hash_value=487911821',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
 08:35:34   6   DBMS_SQLTUNE.LOAD_SQLSET('sqlid_1vktnmmbp6cz3', baseline_ref_cursor);
 08:35:34   7   end;
 08:35:34   8   /
 declare
 *
 ERROR at line 1:
 ORA-13768: Snapshot ID must be between 83431 and 93052.
 ORA-06512: at "SYS.DBMS_SQLTUNE", line 4715
 ORA-06512: at line 6

Now replace start and end snap ids as follows

declare
 baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
 begin
 open baseline_ref_cursor for
 select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(83431, 93052,'sql_id='||CHR(39)||'1vktnmmbp6cz3'||CHR(39)||' and plan_hash_value=487911821',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
 DBMS_SQLTUNE.LOAD_SQLSET('sqlid_1vktnmmbp6cz3', baseline_ref_cursor);
 end;
 /

 


 ***************** OR ***********************

Following to get plan from cursor cache.


 declare
  baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
  begin
  open baseline_ref_cursor for
  select value(p) from table(dbms_sqltune.select_cursor_cache('sql_id=''1vktnmmbp6cz3'' and plan_hash_value=487911821',NULL,NULL,NULL,NULL,1,NULL,'ALL')) p;
  DBMS_SQLTUNE.LOAD_SQLSET('sqlid_1vktnmmbp6cz3', baseline_ref_cursor);
  end;
 /

Step3: Queries to check plan.

SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='sqlid_1vktnmmbp6cz3';

 select sql_id, substr(sql_text,1, 15) text
 from dba_sqlset_statements
 where sqlset_name = 'sqlid_1vktnmmbp6cz3'
 order by sql_id;

 SELECT * FROM table (DBMS_XPLAN.DISPLAY_SQLSET('sqlid_1vktnmmbp6cz3','1vktnmmbp6cz3'));

select *  from dba_sql_plan_baselines;

 Step4: Create baseline and fix it.


 set serveroutput on
 declare
 my_integer pls_integer;
 begin
 my_integer := dbms_spm.load_plans_from_sqlset(sqlset_name => 'sqlid_1vktnmmbp6cz3',
                                               sqlset_owner => 'SYSTEM',
                                               fixed => 'YES',
                                               enabled => 'YES');
                                               DBMS_OUTPUT.PUT_line(my_integer);
 end;
 /

Copy files from ASM diskgroup to remote ASM diskgroup.

ASMCMD> cp  +DATA_XDB4/SRCDB/PARAMETERFILE/spfile.3151.869812217  sys@10.112.39.14.+ASM2:+RECO_XDB5/TGTDB/test/spfile_sk.ora  --port 2483
Enter password: *******

10.112.39.14=remote host IP make sure this is the IP where +ASM2 listener listening to.
+ASM2= remote ASM instance

--port 2483 = port number




To debug
export DBI_TRACE=1

then

ASMCMD> cp  +DATA_XDB4/SRCDB/PARAMETERFILE/spfile.3151.869812217  sys@10.112.39.14.+ASM2:+RECO_XDB5/TGTDB/test/spfile_sk.ora  --port 2483
Enter password: *******

Shell script to read list of databases from a file.

while read line;
do
DBNAME=`echo $line |awk {'print $1'}`
TBS_THRESHOLD=`echo $line |awk {'print $2'}`
DBA=`echo $line |awk {'print $3'}`
RMANCAT=`echo $line |awk {'print $4'}`
INSTANCES=`echo $line |awk {'print $5'}`
. /orahome/work/sk/dbmon.sh $DBNAME
. /orahome/work/sk/tbsmon.sh $DBNAME $TBS_THRESHOLD
. /orahome/work/sk/dbimon.sh $DBNAME
done < /orahome/work/sk/Listofdatabases.txt

Above script will execute following for every db listed in /orahome/work/sk/Listofdatabases.txt

. /orahome/work/sk/dbmon.sh $DBNAME
. /orahome/work/sk/tbsmon.sh $DBNAME $TBS_THRESHOLD
. /orahome/work/sk/dbimon.sh $DBNAME

 <@dbax1:/orahome/work/sk>cat Listofdatabases.txt
db1 15 SK rmandbq 2
db2 15 SK rmandbq 2
db3 15 SK rmandbq 2
db4 15 SK rmandbq 2

db1,db2..are database names in tnsnames.ora file.

RMAN restore of few tablespaces in DEV from backup of Prod.

Create a dummy temporary instance DEVDB and restore the required tablespaces skipping all other tablespaces not required(DO NOT skip system,SYSAUX,TEMP,UNDO) during restore it will check dependencies of tablespace.

copy init parameter from existing DEVDB1 and create DEVDB dummy instance and change parameters accordingly to the required locations.

*.DB_FILE_NAME_CONVERT='/oradata/PRODDB/','/orabackup/DEVDB'
*.LOG_FILE_NAME_CONVERT='/oralogs/PRODDB/','/oralogs/DEVDB/'
*.db_create_file_dest='/orabackup/DEVDB/'
*.DB_CREATE_ONLINE_LOG_DEST_1='/orabackup/DEVDB/'
*.DB_CREATE_ONLINE_LOG_DEST_2='/orabackup/DEVDB/'
 *.control_files='/orabackup/DEVDB/control01.ctl','/orabackup/DEVDB/control02.ctl'
*.db_name='DEVDB'

Add tns entries in dev server for  rmancatalogdb,DEVDB,productionDB
Add listener.ora entries for dummy DB and reload.

SID_LIST_LISTENER =
  (SID_LIST =
      (SID_DESC =
      (GLOBAL_DBNAME = DEVDB)
      (ORACLE_HOME = /orahome/oracle/product/11.2.0.3/dbhome_1)
      (SID_NAME = DEVDB)
    )
  )


This restore is from BACKUP_HOST=DDBOOSTSERVER



DEVDB is a dummy temp instance

$sqlplus "/ as sysdba"
SQL> startup nomount
  
$rman log=rmanout.log

connect catalog rman@rmancatalogdb
connect auxiliary sys@DEVDB
connect target sys@productionDB
run
    {
    set UNTIL TIME "to_date('02/06/2015 06:18:00','MM/DD/YYYY HH24:MI:SS')";
    allocate AUXILIARY CHANNEL c1 DEVICE TYPE 'SBT_TAPE' connect sys@DEVDB  PARMS 'BLKSIZE=1048576,SBT_LIBRARY=/orahome/oracle/product/11.2.0.3/dbhome_1/lib/libddobk.so,ENV=(STORAGE_UNIT=Oracle_Production,BACKUP_HOST=DDBOOSTSERVER,ORACLE_HOME=/orahome/oracle/product/11.2.0.3/dbhome_1)';
    allocate AUXILIARY CHANNEL c2 DEVICE TYPE 'SBT_TAPE' connect sys@DEVDB  PARMS 'BLKSIZE=1048576,SBT_LIBRARY=/orahome/oracle/product/11.2.0.3/dbhome_1/lib/libddobk.so,ENV=(STORAGE_UNIT=Oracle_Production,BACKUP_HOST=DDBOOSTSERVER,ORACLE_HOME=/orahome/oracle/product/11.2.0.3/dbhome_1)';
    allocate AUXILIARY CHANNEL c3 DEVICE TYPE 'SBT_TAPE' connect sys@DEVDB  PARMS 'BLKSIZE=1048576,SBT_LIBRARY=/orahome/oracle/product/11.2.0.3/dbhome_1/lib/libddobk.so,ENV=(STORAGE_UNIT=Oracle_Production,BACKUP_HOST=DDBOOSTSERVER,ORACLE_HOME=/orahome/oracle/product/11.2.0.3/dbhome_1)';
    allocate AUXILIARY CHANNEL c4 DEVICE TYPE 'SBT_TAPE' connect sys@DEVDB  PARMS 'BLKSIZE=1048576,SBT_LIBRARY=/orahome/oracle/product/11.2.0.3/dbhome_1/lib/libddobk.so,ENV=(STORAGE_UNIT=Oracle_Production,BACKUP_HOST=DDBOOSTSERVER,ORACLE_HOME=/orahome/oracle/product/11.2.0.3/dbhome_1)';
DUPLICATE TARGET DATABASE TO DEVDB skip tablespace     GSGCAPPBKP_DATA,GSGCAPPBKP_INDX,GSGCPUB_DATA,GSGCPUB_INDX,GSIRAPP_DATA,GSIRAPP_INDX,USERS;
}


If this is from DISK then use following.


connect catalog rman@rmancatalogdb
connect auxiliary sys@DEVDB
connect target sys@productionDB
run
    {
    set UNTIL TIME "to_date('02/06/2015 06:18:00','MM/DD/YYYY HH24:MI:SS')";
    allocate AUXILIARY CHANNEL c1 DEVICE TYPE DISK;
    allocate AUXILIARY CHANNEL c2 DEVICE TYPE DISK;
    allocate AUXILIARY CHANNEL c3 DEVICE TYPE DISK;
    allocate AUXILIARY CHANNEL c4 DEVICE TYPE DISK;
DUPLICATE TARGET DATABASE TO DEVDB skip tablespace     GSGCAPPBKP_DATA,GSGCAPPBKP_INDX,GSGCPUB_DATA,GSGCPUB_INDX,GSIRAPP_DATA,GSIRAPP_INDX,USERS;
}


then export the schema from dummy temp DEVDB and import into DEVDB1

Key command:

DUPLICATE TARGET DATABASE TO DEVDB skip tablespace     GSGCAPPBKP_DATA,GSGCAPPBKP_INDX,GSGCPUB_DATA,GSGCPUB_INDX,GSIRAPP_DATA,GSIRAPP_INDX,USERS;