OracleAppsDBAKK1: How to Move SQL Profiles from One Database to Another (Including to Higher Versions) (Doc ID 457531.1)

In this Document

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

GOAL

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

The purpose of this document is to assist in moving an SQL Profile implemented on one database to another database.

NOTE: A SQL profile can be transported to any Oracle database created in the same release or later. The reverse may not be true as profiles are not guaranteed to be backwardly compatible.

See:

Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
E16638-07
Chapter 17 Automatic SQL Tuning
Section 17.5.5 Transporting a SQL Profile
http://docs.oracle.com/cd/E36909_01/server.1111/e16638/sql_tune.htm#CHDGHGCJ

SOLUTION

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:

Document 271196.1 Automatic SQL Tuning - SQL Profiles

Steps to Create and Transfer Profile from One Database to Another

The following example illustrates the process of moving a SQL Profile from 10.2 onwards.

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, 
        user_name => 'SCOTT', 
        scope => 'COMPREHENSIVE', 
        time_limit => 60, 
        task_name => 'my_sql_tuning_task', 
        description => 'Demo Task to tune a query'); 
  
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task'); 

my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name =>'my_sql_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=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

NOTE: Even though no_index hint included, the plan uses an index as determined by the SQL profile. The Note section provides plan information that indicates that "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/<PASSWORD> 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/<PASSWORD> 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: 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 => 'SQLTXPLAIN');

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

OracleAppsDBAKK1

Friday, June 11, 2021

How to Move SQL Profiles from One Database to Another (Including to Higher Versions) (Doc ID 457531.1)