How to Create and Move SQL Profile from One Database to Another

Steps:

1. Create SQL Profile in APPS schema

The SQL Profile is created based on the tuning task created and the recommendations given by the tuning task:

DECLARE

my_task_name VARCHAR2(30);

my_sqltext CLOB;

my_sqlprofile_name VARCHAR2(30);

BEGIN

my_sqltext := 'select /*+ no_index(emp pk_emp) */ * from emp where empno=20';

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext,

user_name => 'APPS',

scope => 'COMPREHENSIVE',

time_limit => 60,

task_name => 'fun_ebs_tuning_task',

description => 'Demo Task to tune a query');

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'fun_ebs_tuning_task');

my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name =>'fun_ebs_tuning_task',

name => 'my_sql_profile');

END;

PL/SQL procedure successfully completed.

set lines 130

set autotrace on

select /*+ no_index(emp pk_emp) */ * from emp where empno=20;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

20 KING PRESIDENT 17-NOV-81 5000 10

Execution Plan

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

Plan hash value: 4066871323

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

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

| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("EMPNO"=20)

Note

-----

- SQL profile "fun_ebs_tuning_task" used for this statement

2. Creating a staging table to store the SQL Profiles

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

PL/SQL procedure successfully completed.

• table_name => name of the table to store the SQL Profiles.

• schema_name => name of the schema where the table is to be created.

3. Pack the SQL Profiles into the Staging Table

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'my_sql_profile');

PL/SQL procedure successfully completed.

• staging_table_name => name of the table to store the SQL Profiles.

• profile_name => name of the SQL Profile to be packed.

Note: The table_name and schema_name are case-sensitive.

SQL> desc STAGE

Name Null? Type

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

PROFILE_NAME VARCHAR2(30)

CATEGORY VARCHAR2(30)

SIGNATURE NUMBER

SQL_TEXT CLOB

DESCRIPTION VARCHAR2(500)

TYPE VARCHAR2(9)

STATUS VARCHAR2(8)

BOOLEAN_FLAGS NUMBER

ATTRIBUTES SQLPROF_ATTR

VERSION NUMBER

SPARE1 CLOB

SPARE2 BLOB

4. Export the Staging Table to the Target Database

Using Datapump or Export/Import transfer the table to the target database where you would like to create the same profile.

4a. Export from Source Database

my_linux_1:~> exp APPS/<PASSWORD> tables=STAGE

4b. Import into Target Database

my_linux_1:~> imp APPS/<PASSWORD> tables=STAGE

5. Unpack the SQL Profiles

5a. Test before unpacking

SQL> set lines 130

SQL> set autotrace on

SQL> select /*+ no_index(emp pk_emp) */ * from emp where empno=20;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

20 KING PRESIDENT 17-NOV-81 5000 10

Execution Plan

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

Plan hash value: 2872589290

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

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

| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("EMPNO"=20)

Note: The NO_INDEX hint has been honoured and a FULL table scan has been done on EMP

5b. Unpack Staging Table

If importing to the same schema, schema owner does not need to be specified:

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

However, if importing to different schema, the staging schema owner needs to be changed:|

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

PL/SQL procedure successfully completed.

6. Check the SQL Profile is enabled in Target Database

set lines 130

set autotrace on

select /*+ no_index(emp pk_emp) */ * from emp where empno=20;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

20 KING PRESIDENT 17-NOV-81 5000 10

Execution Plan

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

Plan hash value: 4066871323

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

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

| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("EMPNO"=20)

Note

-----

- SQL profile "my_sql_profile" used for this statement

If you like please follow and comment

How to Create and Move SQL Profile from One Database to Another