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
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 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
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 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
Comments
Post a Comment