Example - Create SQL Tuning Set

Example - Create SQL Tuning Set

Step 1) Login source database  and  Create SQL Tuning Set

BEGIN

  DBMS_SQLTUNE.CREATE_SQLSET ( sqlset_name => 'SQLSET_01',description => 'SQLSET created for migration');

END;

/

Step 2) --Note the minimum and maximum snap id' available.

select min(snap_id), max(SNAP_ID) from dba_hist_snapshot;

here min(snap_id) =  13472

and max(SNAP_ID) = 14220

Step 3) Replace the 2 values in step2 below.

-- Load STS from AWR

DECLARE

stscur dbms_sqltune.sqlset_cursor;

  sql_rec sqlset_row;

BEGIN

   OPEN stscur FOR

       SELECT VALUE(P) FROM TABLE(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY

        ( 13472,14220,

        'parsing_schema_name <> ''SYS''',

                   null, null, null, null, 1, null, 'ALL')) P ;

    fetch stscur into sql_rec ;

    sys.dbms_sqltune.load_sqlset (sqlset_name => 'SQLSET_01',  populate_cursor => stscur);

EXCEPTION WHEN OTHERS THEN

  raise_application_error(-20001,'An error was encountered - '||SQLCODE|| ' -ERROR- '||SQLERRM||' - '||sql_rec.sql_id);

END;

/

Step 4) Check object count details

select count(*) from dba_sqlset_statements where sqlset_name = 'SQLSET_01' order by sql_id;

Step 5) Create the Staging Table ON THE SOURCE SYSTEM ( IN this case 10.2 )

execute DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(table_name =>'STS_TABLE');

SQL> select count(*) from STS_TABLE;

Step 6) Populate the table TEST using DBMS_SQLTUNE.PACK_STGTAB_SQLSET THE SOURCE SYSTEM (IN this case 10.2 )

execute DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name => 'SQLSET_01',staging_table_name => 'STS_TABLE');

SQL> select count(*) from STS_TABLE;

COUNT(*)

----------

         4

Step 7) Take export backup and copy dump to target system

exp file=STS.dmp tables=<username>.MIGRATION1 log=exp_STS.log

Step 8) Login to target system and import using "imp". Change VID below 

imp file=STS.dmp tables=STS_TABLE log=imp_STS.log FROMUSER=<username> TOUSER=<username>

Step 9) Unpack the table using DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET on the Destination system ( IN this CASE it was 11.2)

• Verify the contents of DBA_SQLSET or USER_SQLSET on the Destination system

Select NAME,CREATED,STATEMENT_COUNT FROM DBA_SQLSET;

• Based on the results,  a tuning set might need to be created  before unpacking the table:

execute DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'SQLSET_01');

execute DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET( sqlset_name => 'MIGRATION_SQLSET_01',replace => TRUE,staging_table_name => 'STS_TABLE');

Select NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET; 

select count(*) from STS_TABLE;

#######################       Example    ##################################

• 
  

SQL> BEGIN

  DBMS_SQLTUNE.CREATE_SQLSET ( sqlset_name => 'MIGRATION_SQLSET_01',description => 'SQLSET created for migration');

END;

/  2    3    4

PL/SQL procedure successfully completed.

• 
  

SQL> select min(snap_id), max(SNAP_ID) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)

------------ ------------

       77981        79065

• 
  

SQL> DECLARE

stscur dbms_sqltune.sqlset_cursor;

  2    3    sql_rec sqlset_row;

  4  BEGIN

  5     OPEN stscur FOR

  6         SELECT VALUE(P) FROM TABLE(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY

  7          ( 77981,79065,

        'parsing_schema_name <> ''SYS''',

  8    9                     null, null, null, null, 1, null, 'ALL')) P ;

 10      fetch stscur into sql_rec ;

 11      sys.dbms_sqltune.load_sqlset (sqlset_name => 'MIGRATION_SQLSET_01',  populate_cursor => stscur);

 12  EXCEPTION WHEN OTHERS THEN

 13    raise_application_error(-20001,'An error was encountered - '||SQLCODE|| ' -ERROR- '||SQLERRM||' - '||sql_rec.sql_id);

 14  END;

/ 15

PL/SQL procedure successfully completed.

• 
  

SQL> select count(*) from dba_sqlset_statements where sqlset_name = 'MIGRATION_SQLSET_01' order by sql_id;

  COUNT(*)

----------

      1065

• 
  

SQL> execute DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(table_name =>'MIGRATION1');

PL/SQL procedure successfully completed.

• 
  

SQL> select count(*) from MIGRATION1;

  COUNT(*)

----------

         0

• 
  

SQL> execute DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name => 'MIGRATION_SQLSET_01',staging_table_name => 'MIGRATION1');

PL/SQL procedure successfully completed.

• 
  

SQL> select count(*) from MIGRATION1;

  COUNT(*)

----------

     47233

SQL>

• 
  

exp file=STS.dmp tables=<username>.MIGRATION1 log=exp_STS.log

Target database 

imp file=MIGRATION.dmp tables=MIGRATION1 log=imp_MIGRATION.log FROMUSER=<username> TOUSER=<username>

SQL> select count(*) from MIGRATION1;

  COUNT(*)

----------

     47233

SQL> Select NAME,CREATED,STATEMENT_COUNT FROM DBA_SQLSET;

NAME

--------------------------------------------------------------------------------

CREATED   STATEMENT_COUNT

--------- ---------------

SYS_AUTO_STS

17-NOV-21               0

MIGRATION_SQLSET_01

15-OCT-22               0

SQL> execute DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'SQLSET_01');

PL/SQL procedure successfully completed.

SQL> execute DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET( sqlset_name => 'MIGRATION_SQLSET_01',replace => TRUE,staging_table_name => 'MIGRATION1');

PL/SQL procedure successfully completed.

SQL> Select NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET;

NAME

--------------------------------------------------------------------------------

OWNER

--------------------------------------------------------------------------------

CREATED   STATEMENT_COUNT

--------- ---------------

SYS_AUTO_STS

SYS

17-NOV-20               0

MIGRATION_SQLSET_01

<username>

15-OCT-22             974

NAME

--------------------------------------------------------------------------------

OWNER

--------------------------------------------------------------------------------

CREATED   STATEMENT_COUNT

--------- ---------------

MIGRATION_SQLSET_01

<username>

15-OCT-22               0

SQL> select count(*) from MIGRATION1;

  COUNT(*)

----------

     47233