当sql 执行计划出错时，可以利用sql tuning profile功能使得sql走正确的执行计划

也可以从其他db上导出正确的执行计划然后到其他db中

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.3 - Release: 10.2 to 11.2
Information in this document applies to any platform.
Checked for relevance on 09-SEP-2010

Goal

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

Scope
DBAs and Support Analysts

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

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, 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. (table_name大写)
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.

Or

Imp scott/trigger full=y file=expdat.dmp

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

References

NOTE:271196.1 - Automatic SQL Tuning - SQL Profiles.

Sql_tuning.sql

declare

my_task_name VARCHAR2(30);

my_sqltext CLOB;

my_sqlprofile_name VARCHAR2(30);

BEGIN

my_sqltext := 'select /*+ no_index(t2 t2_idx) */ * from t2 where object_id=7839';

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

user_name => 'LERRY',

scope => 'COMPREHENSIVE',

time_limit => 60,

task_name => 'sql_tuning_task',

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

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

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

name => 'my_sql_profile');

end;

or:

tuning_sqlid.sql: