Move Sql Profile from one database to other database in Oracle

Move SQL Profile from one database to other database

Helped in moving SQL profile if got recommendation in SQL advisory also.
If i want to move SQL profile from test environment to production environment.

1. Run the SQL advisory for the statement

-- Tuning task created for specific a statement from the cursor cache.
l_sql_tune_task_id VARCHAR2(100);
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '1rkyanbmtzjav',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'SQL_TUNING_TASK1',
description => 'Tuning task for statement 19v5guvsgcd1v.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'SQL_TUNING_TASK1');

— Report

SET LONG 10000;
SELECT DBMS_SQLTUNE.report_tuning_task('SQL_TUNING_TASK1') AS recommendations FROM dual;

As result in report you got the following recommendation:

execute dbms_sqltune.accept_sql_profile(task_name => 'SQL_TUNING_TASK1',task_owner => 'SYS', replace => TRUE);

Then you execute this statement into the test environment:

2. Check the SQL query again with set autotrace on

set autotrace on
execute the sql statement on test environment

In Note section you got the name of SQL profile which your sql statement used.
– SQL profile “SYS_SQLPROF_014c318886710000” used for this statement

3. Check the sql profiles is exist in database:

SELECT name FROM dba_sql_profiles;

4. For copy to production you need to create a table in test environment with following package:

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE',schema_name=>'SCOTT');

5. Import the sql profile into the stage table with following package:

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_014c318886710000',staging_schema_owner=>'SCOTT');

6. Export the stage table created in schema

expdp tables=scott.stage dumpfile=profiletable.dmp logfile=profiletable.log directory=dbbackup

7 Copy and paste the dump file into the production environment:

impdp directory=dbbackup dumpfile=profiletable.dmp logfile=impprofiletable.log

8. Check the already profile exist in the production database:

SELECT name FROM dba_sql_profiles;

Note: if name already exists you create with new name in test environment and repeat all steps

9. Import the profile for sql statement into the database

EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name =>'STAGE');

10. Check the sql with autotrace on in Production environment

set autotrace on
execute sql statement


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.