Move SQL Profiles Fm One db To another

Move SQL Profiles Fm One db To another

Steps are -

1. Create profile using DBMS_SQLTUNE package...

2. Stage profile into staging table - CREATE_STGTAB_SQLPROF

3. Packit with -Pack_STGTAB_SQLPROF

4. Export staging table using exp/expdp

5. Import it to the target db using imp/impdp

6. Un-pack it - UNPACK_STGTAB_SQLPROF

7. Check the new profile is getting used or not...

What is a SQL Profile?

SQL Profile is a collection of information stored in the data dictionary that enables the query optimizer to create an optimal execution plan for a SQL statement.The SQL profile contains corrections for poor optimizer estimates discovered during Automatic SQL Tuning. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans..

Managing SQL Profiles

For information on SQL Profiles see:

Steps to Create and Transfer Profile from One Database to Another

1. Create SQL Profile in SCOTT 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=7839';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext,

       task_name => 'my_sql_tuning_task',
user_name => 'SCOTT',
       scope => 'COMPREHENSIVE',
       time_limit => 60,

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task');
description => 'Demo Task to tune a query');

PL/SQL procedure successfully completed. </span>
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name =>'my_sql_tuning_task',
        name => 'my_sql_profile');
END;
 
/
 

 

set lines 130

set autotrace on

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

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
--------- ---------- --------- ---------- --------- ---------- -------

Execution Plan
7839 KING       PRESIDENT            17-NOV-81       5000                    10
----------------------------------------------------------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
Plan hash value: 4066871323
-----------------------------------------------------------------------
-----------------------------------------------------------------------

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|   0 | SELECT STATEMENT            |        |     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"=7839)
Note
-----
  - SQL profile "my_sql_profile" used for this statement</span>

 

Note: Even though no_index hint included, plan uses index as determined by profile

Note in Plan information indicates "my_sql_profile" is used.

2. Creating a staging table to store the SQL Profiles

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

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 scott/tiger tables=STAGE

Export: Release 10.2.0.4.0 - Production on Sun Feb 12 17:43:21 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table STAGE 1 rows exported

Export terminated successfully without warnings.

4b. Import into Target Database

my_linux_1:~> imp scott/tiger tables=STAGE

Import: Release 11.2.0.3.0 - Production on Mon Feb 13 14:49:12 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses WE8MSWIN1252 character set (possible charset conversion)

. importing SCOTT's objects into SCOTT

. importing SCOTT's objects into SCOTT

. . importing table "STAGE" 1 rows imported

Import terminated successfully with warnings.

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=7839;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7839 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"=7839)

Note: NO_INDEX hint honoured and FULL SCAN done on EMP

5b. Unpack Staging Table

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

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=7839;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7839 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"=7839)

Note

- SQL profile "my_sql_profile" used for this statement

code