Transfer oracle sql profiles between two databases

Transfer oracle sql profiles between two databases

Summary 
Here is a simple guide to transfer sql profiles from one database to another. For better understanding assume that you want to transfer one sql profile from the 'source' database to the 'destination' database. 

1. Connect to the source database as sysdba and grant the required privileges to the user which will own the staging table. 

$ sqlplus "/ as sysdba"

2. Create the staging table.

BEGIN

DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name => 'SQL_PROFILES_STGTAB');

END;

/

3. Check the existing sql profiles at the source db and copy the desired to the staging table

SELECT * FROM dba_sql_profiles ORDER BY created DESC;

BEGIN

DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (profile_name => 'SYS_SQLPROF_014c5ae7b1c80001', staging_table_name => 'SQL_PROFILES_STGTAB');

END;

/

SELECT * FROM SQL_PROFILES_STGTAB;

4. Copy the staging table SQL_PROFILES_STGTAB from the source db to the destination db.  ( export and import)
5. Grant again at the destination db the required privilege.

6. Add the sql profiles from the staging table to the destination db.

BEGIN

DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE => TRUE, staging_table_name => 'SQL_PROFILES_STGTAB');

END;

/

SQL - force good plan

run the report find the hash value

SET ECHO OFF
SET MARKUP HTML ON SPOOL ON
SPOOL planhash.html
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = 'gd1by1cgjj853'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3;
SPOOL OFF
SET MARKUP HTML OFF

SET ECHO ON

Step -1- Create SQL Tuning Set

BEGIN  
DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'STS_TEST',description => 'SQL Tuning Set for loading plan into SQL Plan Baseline'); 
END; 
/

Step -2- Load the STS from AWR snap id 

DECLARE  cur sys_refcursor; 
BEGIN  
OPEN cur FOR    
     SELECT VALUE(P)     
     FROM TABLE(dbms_sqltune.select_workload_repository(begin_snap=>12739, end_snap=>12740,basic_filter=>'sql_id =''gd1by1cgjj853''',attribute_list=>'ALL')) p;
      DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_TEST', populate_cursor=>cur);  
 CLOSE cur;
END;
/ 


SQL>


Step -3


SELECT * FROM dba_sql_plan_baselines ;

Step -4  - force hash value to use the SQL 

DECLARE 
my_plans pls_integer; 
BEGIN  
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( 
  sqlset_name => 'STS_TEST',  
   basic_filter=>'plan_hash_value = ''494989362'''); 
END; 
/ 

Step -5


-- List out SQL Tuning Set contents to check we got what we wanted
SELECT
  first_load_time          ,
  executions as execs              ,
  parsing_schema_name      ,
  elapsed_time  / 1000000 as elapsed_time_secs  ,
  cpu_time / 1000000 as cpu_time_secs           ,
  buffer_gets              ,
  disk_reads               ,
  direct_writes            ,
  rows_processed           ,
  fetches                  ,
  optimizer_cost           ,
  sql_plan                ,
 plan_hash_value          ,
  sql_id                   ,
  sql_text
   FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name =>'STS_TEST'));

Drop the STS

BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'STS_TEST');
END;


   
   
To display the contents of an STS:

SQL>

COLUMN SQL_TEXT FORMAT a30   
COLUMN SCH FORMAT a3
COLUMN ELAPSED FORMAT 999999999

SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT, 
       ELAPSED_TIME AS "ELAPSED", BUFFER_GETS
FROM   TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'STS_TEST' ) );


SELECT name, owner FROM dba_sqlset WHERE name='STS_TEST'


You can see the SQL statements loaded in the SQL Tuning Set once it's loaded, by using the following query:

SELECT SQL_TEXT FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('STS_TMSUAT') );



SQL> select SQL_TEXT from DBA_SQLSET_STATEMENTS where SQLSET_NAME='STS_TEST';

no rows selected

SQL>




select * from table(dbms_xplan.display_sqlset('STS_TEST','gd1by1cgjj853'));



SQL> select instance_name from v$instance;




SQL> BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'STS_TEST',description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
/  2    3    4

PL/SQL procedure successfully completed.

SQL> DECLARE  cur sys_refcursor;
  2  BEGIN
  3  OPEN cur FOR
  4       SELECT VALUE(P)
  5       FROM TABLE(dbms_sqltune.select_workload_repository(begin_snap=>12739, end_snap=>12740,basic_filter=>'sql_id =''gd1by1cgjj853''',attribute_list=>'ALL')) p;
  6        DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_TEST', populate_cursor=>cur);
  7   CLOSE cur;
  8  END;
  9  /

PL/SQL procedure successfully completed.


SQL> select name from v$database;



SQL> DECLARE
  2  my_plans pls_integer;
  3  BEGIN
  4    my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
  5    sqlset_name => 'STS_TEST',
  6     basic_filter=>'plan_hash_value = ''494989362''');
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL>


SQL> select SQL_TEXT from DBA_SQLSET_STATEMENTS where SQLSET_NAME='STS_TEST';

FYI

Correcting Optimizer Cost Estimates to Encourage Good Execution Plans Using the COE XFR SQL Profile Script (Doc ID 1955195.1)

SQL> START coe_xfr_sql_profile.sql <sql_id> or <plan hash value for good plan>


Unzip sqlt.zip and navigate to the sqlt directory
Navigate to the subdirectory utl
Find the SQL_ID and the Plan Hash Value (PHV) for the plan you want to base the profile on. You can get the SQL_ID from SQLT in the "SQL Identification" section and the PHV from the "Execution Plans" section:

DB Blocking and log_switch_history

--Check the existing blocking sessions in database:

set lines 130 pages 1000
col BLOCKING_STATUS for a100
select s1.INST_ID,s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
/

--Check internal blocking sessions which are holding in background:

set lines 200
set pages 1000
col event for a40
col obj for a30
select inst_id,sid,blocking_session BSID,blocking_instance BINST,sql_id,event,last_Call_et,wait_time_micro,
(select object_name from dba_objects where object_id = row_wait_obj#) obj,
decode(event,'enq: TX - row lock contention',dbms_rowid.ROWID_CREATE(1,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#),null) rid
from gv$session where status = 'ACTIVE' and type = 'USER' order by last_Call_et
/

set lines 200
set pages 1000
col machine for a20
col osuser for a15
col username for a15
col program for a20
select inst_id,sid,serial#,sql_id,prev_sql_id,sql_hash_value,status,osuser,username,program,machine,last_call_et,to_char(logon_time,'DD/MON/YYYY hh24:mi') logon_time
from gv$session
where sid in (&sid)
order by inst_id;


SET VERIFY OFF
SET LINESIZE 180
SET PAGESIZE 66

ACCEPT startDate PROMPT 'Enter start date (DD-MON-YYYY): '
ACCEPT endDate   PROMPT 'Enter end date   (DD-MON-YYYY): '

COLUMN H00   FORMAT 999     HEADING '00'
COLUMN H01   FORMAT 999     HEADING '01'
COLUMN H02   FORMAT 999     HEADING '02'
COLUMN H03   FORMAT 999     HEADING '03'
COLUMN H04   FORMAT 999     HEADING '04'
COLUMN H05   FORMAT 999     HEADING '05'
COLUMN H06   FORMAT 999     HEADING '06'
COLUMN H07   FORMAT 999     HEADING '07'
COLUMN H08   FORMAT 999     HEADING '08'
COLUMN H09   FORMAT 999     HEADING '09'
COLUMN H10   FORMAT 999     HEADING '10'
COLUMN H11   FORMAT 999     HEADING '11'
COLUMN H12   FORMAT 999     HEADING '12'
COLUMN H13   FORMAT 999     HEADING '13'
COLUMN H14   FORMAT 999     HEADING '14'
COLUMN H15   FORMAT 999     HEADING '15'
COLUMN H16   FORMAT 999     HEADING '16'
COLUMN H17   FORMAT 999     HEADING '17'
COLUMN H18   FORMAT 999     HEADING '18'
COLUMN H19   FORMAT 999     HEADING '19'
COLUMN H20   FORMAT 999     HEADING '20'
COLUMN H21   FORMAT 999     HEADING '21'
COLUMN H22   FORMAT 999     HEADING '22'
COLUMN H23   FORMAT 999     HEADING '23'
COLUMN TOTAL FORMAT 999,999 HEADING 'Total'

SPOOL log_switch_history.lst

SELECT
    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)                          DAY
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)                                                                      TOTAL
FROM
  v$log_history  a
WHERE
    (TO_DATE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')
     >=
     TO_DATE('&startDate', 'DD-MON-YYYY')
     )
     AND
    (TO_DATE(substr(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')
     <=
     TO_DATE('&endDate', 'DD-MON-YYYY')
     )
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
/

SPOOL OFF