Home > DBMS Packages, SQL-PL/SQL > Moving SQL Tuning Set (STS) Accross Databases

Moving SQL Tuning Set (STS) Accross Databases

You can create SQL Tuning Set for analyzing and tuning SQL statements. You may want to move STS to test database. You can do it with Oracle Enterprise Manager database console. And also you can do it with using DBMS_SQLTUNE package. In the background OEM will move STS with using this package.

STS moving steps are as follows.

 1- Create staging table for STS

 BEGIN

DBMS_SQLTUNE.create_stgtab_sqlset (table_name => ‘STG_TABLE’,

schema_name => ‘TALIP’,

tablespace_name => ‘TALIP_TS’

);

END;

/

 2- Pack STS to staging table

 BEGIN

DBMS_SQLTUNE.pack_stgtab_sqlset (sqlset_name => ‘STS_TALIP’,

sqlset_owner => ‘TALIP’,

staging_table_name => ‘STG_TABLE’,

staging_schema_owner => ‘TALIP’

);

END;

/

 3- Export the staging table and copy export file to server which you want to move STS.

exp userid=talip@dbtalip file=stg_table.dmp log=stg_table.log tables=talip.stg_table compress=no recordlength=65535 direct=y feedback=1000000 CONSISTENT=N

This export will take 3 tables. These tables are “STG_TABLE” , “STG_TABLE_CBINDS” and “STG_TABLE_CPLANS”. The first table contains our sql statements, the second table contains our bind variables and the last table contains execution plans.

 4- Import the staging table to test database.

imp userid=talip@testdb file=stg_table.dmp log=stg_table_imp.log fromuser=talip touser=talip recordlength=65535 feedback=1000000

 5- Unpack staging table to STS.

 BEGIN

DBMS_SQLTUNE.unpack_stgtab_sqlset (sqlset_name => ‘STS_TALIP’,

sqlset_owner => ‘TALIP’,

replace => TRUE,

staging_table_name => ‘STG_TABLE’,

staging_schema_owner => ‘TALIP’

);

END;

/

Your SQL Tuning Set (STS) is ready for analyzing and tuning.

 

Talip Hakan Öztürk

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: