Database Tuning‎ > ‎

HOW TO TRANSPORT A SQL TUNING SET

posted Aug 30, 2011, 8:30 AM by Sachchida Ojha
The Sql Tuning Set name is small_sh_sts_4.It is on 10.2.0.2.0 Database server.
It has 4 sqls .

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

SQL_ID TEXT
------------- -------------------------
4qdz7j26mdwzb SELECT /*+ my_q
6y289t15dqj9r SELECT /*+ my_q
ckm14c67njf0q SELECT /*+ my_q
g37muqb81wjau SELECT /*+ my_q


1) Create the Staging Table ON THE SOURCE SYSTEM ( IN this case 10.2.0.2.0 ) .

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

PL/SQL procedure successfully completed.

SQL> select count(*) from test;

COUNT(*)
----------
0

2) Popluate the table TEST using DBMS_SQLTUNE.PACK_STGTAB_SQLSET THE SOURCE  SYSTEM (IN this case 10.2.0.2.0 )

execute DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name => 'small_sh_sts_4',staging_table_name => 'TEST');

PL/SQL procedure successfully completed.


select count(*) from test;

SQL> select count(*) from test;

COUNT(*)
----------
4


3) Export the table table test on THE SOURCE SYSTEM ( IN this case 10.2.0.2.0 )
and move the table to the Destination Server
and Import it . The staging table TEST can also be moved using
the mechanism of choice such as datapump or database link.


While exporting the table you will see something like

About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > TEST

. . exporting table TEST 4 rows exported
. . exporting table TEST_CBINDS 0 rows exported
. . exporting table TEST_CPLANS 34 rows exported

Import on the Destination system ( IN this CASE it was 11.1.0.6.0 )

While Importing the table you will see something like

. importing SH's objects into SH
. importing SH's objects into SH
. . importing table "TEST" 4 rows imported
. . importing table "TEST_CBINDS" 0 rows imported
. . importing table "TEST_CPLANS" 34 rows imported
Import terminated successfully without warnings.


SQL> select count(*) from test;

COUNT(*)
----------
4

Verify the contents of DBA_SQLSET or USER_SQLSET on the Destination system

Select NAME,CREATED,STATEMENT_COUNT FROM DBA_SQLSET;


4) Unpack the table using DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET on the Destination system ( IN this CASE it was 11.1.0.6.0 )

execute DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET( sqlset_name => '%',-
replace => TRUE,-
staging_table_name => 'TEST');

Select NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET;

NAME CREATED STATEMENT_COUNT
------------------------------ --------- ---------------
small_sh_sts_4 24-AUG-07 4

Comments