转 sql profile 绑定 litera and move profile to another db l for spa

SQL TYPE 1:
for bind value sql ,

first create a good plan with literal and with good  profile.

then use sqlT to replace the profile‘’s good plan  to bind value sql_id. 

then double checking .

var 1 varchar2(32);
var 2 VARCHAR2(32);
var 3 VARCHAR2(32);
var 4 VARCHAR2(32);
var 5 VARCHAR2(32);
exec :1:='313585000990';
exec :2:='313585000990';
exec :3:='121.001.01';
exec :4:='L01';

select a,b,c,d,e from a where (a = :1 and b = :2 and c = :3 and d = :4) and ((packsndflg is NULL) or (packsndflg = ''))

SQL TYPE 2:
for literal value sql ,first create a good plan literal  with profile with force_match => TRUE

 另外如果自动调优，仍然无法找到一条好的执行计划 ，需要考虑到将10G 执行计划 迁移到11G .具体步骤见下文。

his document provides information on the following topics:

1. To create SQL Profiles for queries using literals where different literal values are passed for every execution.
2. Use this SQL Profile for every execution of this SQL whatever the literal is changed to.

Default behavior: By default, if a SQL Profile is created for a sql containing literal values, then the profile would be generated solely for the query with whatever literals were used while running the tuning advisor. If the same SQL was executed with only a change in literals, then that profile would not be used for this query since the changing literal would make the SQL deemed to be a new query. Since profiles are identified by the SQL that they are recorded against, the profile would not be used for this "new" SQL.

SOLUTION

Default Behavior

Firstly, lets look at the default behavior of a SQL Profile.

Example:

Create a table, populate it with some values, add an index and gather statistics:

SQL> create table test (n number );

Table created.

declare
begin
for i in 1 .. 10000
loop
insert into test values(i);
commit;
end loop;
end;

PL/SQL procedure successfully completed.

create index test_idx on test(n);

Index created.

analyze table test estimate statistics (OR use dbms_stats)

Table analyzed.

Run a test query against it with a literal predicate (n=1):

select /*+ no_index(test test_idx) */ * from test where n=1

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=13)
1 0 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=6 Card=1 Bytes=13)

Now, use SQL Tuning Advisor  to create a profile for the query:

SQL> DECLARE
2 my_task_name VARCHAR2(30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=1';
6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
7 sql_text=> my_sqltext,
8 user_name => 'SCOTT',
9 scope => 'COMPREHENSIVE',
10 time_limit => 60,
11 task_name => 'my_sql_tuning_task_2',
12 description => 'Task to tune a query on a specified table');
13 END;
14 /

PL/SQL procedure successfully completed.

SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2');

PL/SQL procedure successfully completed.

SQL> set long 2000
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_2') from DUAL;

FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
--------------------------------------------------------------------------------
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 84.03%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', replace => TRUE);

If we accept the profile:

SQL> execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', replace => TRUE);

PL/SQL procedure successfully completed.

Then we now have a profile for that SQL statement.

If we execute the sql with various literals, the SQL Profile will only be used for the query with the specific literals used when it was created:

SQL> select /*+ no_index(test test_idx) */ * from test where n=1;

N
----------
         1

Execution Plan
----------------------------------------------------------
Plan hash value: 2882402178
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 3 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=1)

Note
-----
- SQL profile "SYS_SQLPROF_014af9c017890000" used for this statement

In this example the profile is used, but if we change the literal to a different value, then the profile would not be used and a different plan may be chosen (as in this case).

SQL> select /*+ no_index(test test_idx) */ * from test where n=2;

N
----------
         2

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU) | Time     |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |      |    1 |     3 |       6 (0) | 00:00:01 |
|* 1 |  TABLE ACCESS FULL | TEST |    1 |     3 |       6 (0) | 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=2) 

As you can see, there is now no message about a profile being used for this statement.

Using the Force_Match parameter of DBMS_SQLTUNE.ACCEPT_SQL_PROFILE

By default DBMS_SQLTUNE.ACCEPT_SQL_PROFILE executes with the force_match parameter set to false. When set to true, this is analogous to the matching algorithm used by the FORCE option of the cursor_sharing parameter in that it forces literals in the statement to be converted to binds and then the statement can be shared when different literals are supplied. It also causes SQL profiles to target all SQL statements which have the same text after normalizing all literal values into bind variables. (Note that if a combination of literal values and bind values is used in a SQL statement, no bind transformation occurs.) 

If we recreate the SQL profile with this option, it will be used whatever literals are supplied , since it has internally replaced the literals with binds.

What follows is an example illustrating this behavior:

SQL> execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', replace => TRUE, force_match=>true);

PL/SQL procedure successfully completed.

Now even if the literals are changed, the SQL profile gets used.

SQL> select /*+ no_index(test test_idx) */ * from test where n=10;

N
----------
        10

Execution Plan
----------------------------------------------------------
Plan hash value: 2882402178
-----------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU) | Time     |
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |          |    1 |     3 |       1 (0) | 00:00:01 |
|* 1 |  INDEX RANGE SCAN | TEST_IDX |    1 |     3 |       1 (0) | 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=10)

Note
-----
- SQL profile "SYS_SQLPROF_014af9c167e84001" used for this statement

Now that force_match is set to true the SQL Profile is used.

1. You can verify that the new execution plan is being used as followed:
   
   
   select SQL_ID, SQL_PROFILE,PLAN_HASH_VALUE from V$SQL where SQL_ID='2qknbzqt0aoxb';
    
    
2. The profile can be disabled or dropped as follows using the the SQL_PROFILE name returned from the query above:
   
   To disable the profile:
   
   
   EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(Name => '<SQL PROFILE>', Attribute_Name => 'STATUS', Value => 'DISABLED');
   
   To drop the profile:
   
   
   EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(Name => '<SQL PROFILE>');

##############2 move profile

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..

<br class="Apple-interchange-newline"><div></div>

 

409/5000

 

SQL Profile是存储在数据字典中的信息集合，它使查询优化器能够为SQL语句创建最佳执行计划.SQL配置文件包含对自动SQL调整期间发现的较差优化程序估计的更正。 此信息可以改进优化器基数和选择性估计，从而导致优化器选择更好的计划。

 

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/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: 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

 

 

#######drop 

 

   SET SERVEROUTPUT ON
                DECLARE
                  l_sql_tune_task_id  VARCHAR2(20);
                BEGIN
                  l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
                                          task_name => 'test_tuning_task',
                                          name      => 'test_profile');
                  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
                END;
                /
                
                BEGIN
                  DBMS_SQLTUNE.alter_sql_profile (
                    name            => 'test_profile',
                    attribute_name  => 'STATUS',
                    value           => 'DISABLED');
                END;
                /
                
                BEGIN
                  DBMS_SQLTUNE.drop_sql_profile (
                    name   => 'test_profile',
                    ignore => TRUE);
                END;
                /

 ###########sample 0

Steps to Create and Transfer Profile from One Database to Another

set linesize 450 pagesize 0
select 'exec DBMS_SQLTUNE.DROP_SQL_PROFILE (name=>'''||name||''',ignore =>TRUE);' from dba_sql_profiles

1. Creating a staging table to store the SQL Profiles

--exec DBMS_SQLTUNE.DROP_STGTAB_SQLPROF(table_name=>'STAGE');

conn dbmgr/dbmgr
--exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE_AFA',schema_name=>'AFA');

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE_AFA',schema_name=>'DBMGR');

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');

--select 'exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>''STAGE'',profile_name=>'''||name||''');' from dba_sql_profiles
select 'exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>''STAGE_AFA'',profile_name=>'''||name||''');' from dba_sql_profiles

--conn afa/afa
conn dbmgr/dbmgr
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655f9bc2580006');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655f9bc2580006');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0165608434ca0012');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01656042dc15000a');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0165606254d8000f');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655f9181320004');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01656081bfb10010');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_016560870ead0013');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0165605930f6000b');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655f9701d10005');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655fa4a7670007');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01656083393e0011');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0165605bf9c7000c');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0165608ae11f0014');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655faa9ab30009');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01656060f311000e');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655fa7fc680008');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0165605fc25d000d');

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.

conn afa/afa
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.

--exp "'/ as sysdba'" tables=AFA.STAGE

exp dbmgr/dbmgr tables=STAGE_AFA

racle@localhost admin]$ exp "'/ as sysdba'" tables=AFA.STAGE

Export: Release 11.2.0.4.0 - Production on Wed Aug 22 17:14:16 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to AFA
. . exporting table STAGE 19 rows exported
Export terminated successfully without warnings.

D: mpspa esult_1_1 unningexpdat.dmp

4b. Import into Target Database

--imp "'/ as sysdba'" tables=STAGE fromuser=afa touser=afa

imp dbmgr/dbmgr tables=STAGE_AFA fromuser=DBMGR touser=DBMGR

Import: Release 11.2.0.4.0 - Production on Thu Aug 23 01:31:06 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing AFA's objects into AFA
. . importing table "STAGE" 19 rows imported
Import terminated successfully without 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

error:

EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'STAGE');
ERROR at line 1:
ORA-38171: Insufficient privileges for SQL management object operation
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SMB", line 83
ORA-06512: at "SYS.DBMS_SQLTUNE", line 7657
ORA-06512: at "SYS.DBMS_SQLTUNE", line 6349
ORA-06512: at line 1

fix:
grant execute on DBMS_SQLTUNE to afa;
grant administer SQL MANAGEMENT OBJCET to afa;

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');

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

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

############sample 1

Steps to Create and Transfer Profile from One Database to Another

select 'exec DBMS_SQLTUNE.DROP_SQL_PROFILE (name=>'''||name||''',ignore =>TRUE);' from dba_sql_profiles

1. Creating a staging table to store the SQL Profiles

--exec DBMS_SQLTUNE.DROP_STGTAB_SQLPROF(table_name=>'STAGE');

conn dbmgr/dbmgr

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE_AFA',schema_name=>'DBMGR');

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

--select 'exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>''STAGE'',profile_name=>'''||name||''');' from dba_sql_profiles
select 'exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>''STAGE_AFA'',profile_name=>'''||name||''');' from dba_sql_profiles

--conn afa/afa
conn dbmgr/dbmgr
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655f9bc2580006');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655f9bc2580006');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0165608434ca0012');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01656042dc15000a');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0165606254d8000f');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655f9181320004');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01656081bfb10010');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_016560870ead0013');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0165605930f6000b');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655f9701d10005');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655fa4a7670007');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01656083393e0011');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0165605bf9c7000c');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0165608ae11f0014');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655faa9ab30009');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01656060f311000e');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655fa7fc680008');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0165605fc25d000d');

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.

conn dbmgr/dbmgr
SQL> desc STAG_AFA
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

exp dbmgr/dbmgr tables=STAGE_AFA

racle@localhost admin]$ exp "'/ as sysdba'" tables=AFA.STAGE

Export: Release 11.2.0.4.0 - Production on Wed Aug 22 17:14:16 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to AFA
. . exporting table STAGE 19 rows exported
Export terminated successfully without warnings.

D: mpspa esult_1_1 unningexpdat.dmp

4b. Import into Target Database

imp dbmgr/dbmgr file=expdat.dmp tables=STAGE_AFA fromuser=DBMGR touser=DBMGR

Import: Release 11.2.0.4.0 - Production on Thu Aug 23 01:31:06 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing AFA's objects into AFA
. . importing table "STAGE" 19 rows imported
Import terminated successfully without 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

error:

EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'STAGE');
ERROR at line 1:
ORA-38171: Insufficient privileges for SQL management object operation
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SMB", line 83
ORA-06512: at "SYS.DBMS_SQLTUNE", line 7657
ORA-06512: at "SYS.DBMS_SQLTUNE", line 6349
ORA-06512: at line 1

fix:
grant execute on DBMS_SQLTUNE to afa;
grant administer SQL MANAGEMENT OBJCET to afa;

5b. Unpack Staging Table
If importing to the same schema, schema owner does not need to be specified:

SELECT * FROM DBA_SQL_PROFILES

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

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

回退方法：
set linesize 450 pagesize 0
select 'exec DBMS_SQLTUNE.DROP_SQL_PROFILE (name=>'''||name||''',ignore =>TRUE);' from dba_sql_profiles;

############sample 1-2   另外如果自动调优，仍然无法找到一条好的执行计划 ，需要考虑到将10G 执行计划 迁移到11G .具体步骤见下文。

 注意：

 for test sql profile is ok ,some time we need re-login user to check use hard parse to check profile is ok or not.

step 1: runing sql ,get 10g sql_id and sql good plan 

step 2: in 10g
SQL> START coe_xfr_sql_profile.sql <sql_id> or <plan hash value for good plan>

START coe_xfr_sql_profile.sql 61rt19kd6qj1a 86617906
coe_xfr_sql_profile_61rt19kd6qj1a_86617906.sql

step 3 .in 10g
edit coe_xfr_sql_profile_ft410dysd8sn2_3795378112.sql
force_match =ture

 --scp to 11g

 scp coe_xfr_sql_profile_ft410dysd8sn2_3795378112.sql  oracle@10.241.94.108:/tmp/dba/sqlt 

step 4: in 11g

@coe_xfr_sql_profile_ft410dysd8sn2_3795378112.sql 

 new profile  is used .

step 5: to double check.

 有的时候，需要重新登录，使用硬解析 检查profile 是否生效，check use hard parse to check profile is ok or not.

###########sample 0: auto tuning whith sql profile  (sql profile can do select /update )

To what statements can a SQL Profile be applied?

SELECT statements 
UPDATE statements 
INSERT statements (only with a SELECT clause) 
DELETE statements 
CREATE TABLE statements (only with the AS SELECT clause) 
MERGE statements (the update or insert operations)

for example:

#########

test 14 67927knpgc11c

select packdate
from t_beps
where  packdate=1

/

##########auto scripts 1:  , the sql 是常量组成的，没有绑定变量:  

##begin auto tuning 
rm t.log
sqlplus aa/aa123456 <<eof
spool t.log

select packdate
from t_beps
where  packdate=1

/

select * from table(dbms_xplan.display_cursor());

spool off
eof

sql_id=`grep SQL_ID t.log|awk '{print $2}'|awk -F, '{print $1}'`

sqlplus / as sysdba <<eof1
set pagesize 0 linesize 300
select * from dual;
exec DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'my_sql_tuning_task_test1');
select * from dual;
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
select dbms_lob.substr(sql_fulltext,4000) sql_text into my_sqltext from v$sqlarea where sql_id='$sql_id';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text=> my_sqltext,
user_name => 'AFA',
scope => 'COMPREHENSIVE',
time_limit => 1600,
task_name => 'my_sql_tuning_task_test1',
description => 'Task to tune a query on a specified table');
END;
/
exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_test1');
/
set long 200000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_test1') from DUAL;
execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task_test1', task_owner => 'SYS', replace => TRUE,force_match=>true);
/
eof1

###end auto auto scripts 1 wthi sql type 1 , the sql 是常量组成的，没有绑定变量: 

issue :

1.we use  autpo script1 meet the error the tunning report indicate:
"
- Type of SQL statement not supported."

issue :

1.we use script1 meet the error the tunning report indicate:
"
- Type of SQL statement not supported."

#######auto script 2 begin  , the sql 是常量组成的，没有绑定变量:  

rm t.log
sqlplus aa/aa123456 <<eof
spool t.log

select packdate
from t_beps
where  packdate=1

/

select * from table(dbms_xplan.display_cursor());

spool off 
eof

sql_id=`grep SQL_ID t.log|awk '{print $2}'|awk -F, '{print $1}'`

sqlplus / as sysdba <<eof1
set pagesize 0 linesize 300
select * from dual;
exec DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'my_sql_tuning_task_test1');
select * from dual;
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '$sql_id',
scope => 'COMPREHENSIVE',
time_limit => 1600,
task_name => 'my_sql_tuning_task_test1',
description => 'Task to tune a query on a specified table');
END;
/
exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_test1');
/
set long 20000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_test1') from DUAL;
execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task_test1', task_owner => 'SYS', replace => TRUE,force_match=>true);
/
eof1

#########auot script 2  wthi sql type 1 , the sql 是常量组成的，没有绑定变量:  

###################

如果SQL 是由绑定变量组成的 。脚本如下： 

step 0

######ref 2 从10g库 hist 信息里 查找绑定变量值  cat bind sql value

To see a colleague wrote a SQL bound variables are interesting, again to reprint:

-
select t.sql_id,t.name,t.position,t.datatype_string,t.value_string,t.last_captured from v$sql_bind_capture t where sql_id='f78cpkf8cc003';

 -这个sql从awr中读取绑定变量值信息
select instance_number, sql_id,name, datatype_string, last_captured,value_string from dba_hist_sqlbind where sql_id='fahv8x6ngrb50'order by LAST_CAPTURED,POSITION;

step1 : 使用常量 构造出一个良好的profile , 进而获取到 SQL PLAN HASH VALUE.

rm t.log
sqlplus aa/aa123456 <<eof
spool t.log

select packdate
from t_beps
where  packdate=1

/

select * from table(dbms_xplan.display_cursor());

spool off 
eof

sql_id=`grep SQL_ID t.log|awk '{print $2}'|awk -F, '{print $1}'`

sqlplus / as sysdba <<eof1
set pagesize 0 linesize 300
select * from dual;
exec DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'my_sql_tuning_task_test1');
select * from dual;
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '$sql_id',
scope => 'COMPREHENSIVE',
time_limit => 1600,
task_name => 'my_sql_tuning_task_test1',
description => 'Task to tune a query on a specified table');
END;
/
exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_test1');
/
set long 20000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_test1') from DUAL;
execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task_test1', task_owner => 'SYS', replace => TRUE,force_match=>true);
/
eof1

step 2: 使用SQLT 中的CORE*.SQL  ,生成SQL_ID ,并将step 1生成的 good plan 嵌入到SQL_ID(此SQL_ID 带绑定变量 ) 

SQL> START coe_xfr_sql_profile.sql <sql_id> or <plan hash value for good plan>

@START coe_xfr_sql_profile.sql 61rt19kd6qj1a 86617906
@coe_xfr_sql_profile_61rt19kd6qj1a_86617906.sql

### 如何验证带绑定变量的SQL 的profile 是否生效。生成10046 TRACE 文件，通过10046 trace 文件查看，good plan 是否生效。  

4.67927knpgc11c

var 1 varchar2(32);
var 2 VARCHAR2(32);
var 3 VARCHAR2(32);
var 4 VARCHAR2(32);
var 5 VARCHAR2(32);
exec :1:='313585000990';
exec :2:='313585000990';
exec :3:='121.001.01';
exec :4:='L01';

select a,b,c,d,e from a where (a = :1 and b = :2 and c = :3 and d = :4) and ((packsndflg is NULL) or (packsndflg = ''))

unix tools:

SELECT s.username, s.user#, s.sid, s.serial#, s.prev_hash_value, p.spid os_pid
FROM V$SESSION S, v$process p
WHERE sid = nvl('&sid',sid)
and p.addr = s.paddr
and s.username is not null

oradebug setospid 16866
oradebug unlimit
oradebug Event 10046 trace name context forever, level 12

<
running the sql in pl/sql developer command windows , if runing in the unix tool with sqlplus . it will report :1 not supported.
>
oradebug Event 10046 trace name context off;

oradebug tracefile_name

exit

####

参考文档：

https://blog.csdn.net/dbaheng/article/details/38519019

一、手工生成Sql tuning advisor 
1、SQL text format:
DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext   CLOB;
BEGIN
  my_sqltext := 'SELECT * FROM DBA_SEGMENTS WHERE OWNER=''CLIC'' AND SEGMENT_TYPE=''TABLE''';
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text    => my_sqltext,
                                                  scope       => 'COMPREHENSIVE',
                                                  time_limit  => 60,
                                                  task_name   => 'test_sql_tuning_task1',
                                                  description => 'Task to tune a query');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'test_sql_tuning_task1');
END;
/


2、SQL id format：
DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext   CLOB;
BEGIN
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'b3uaak09jfaxc',
                                                  scope       => 'COMPREHENSIVE',
                                                  time_limit  => 60,
                                                  task_name   => 'test_sql_tuning_task1',
                                                  description => 'Task to tune a query');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'test_sql_tuning_task1');
END;
/


二、查看生成的STAreport：


set long 999999
set LONGCHUNKSIZE 999999
set serveroutput on size 999999
set linesize 200
select dbms_sqltune.report_tuning_task('test_sql_tuning_task1') from dual;


exec dbms_sqltune.drop_tuning_task('test_sql_tuning_task1');


删除优化任务
SQL> execdbms_sqltune.drop_tuning_task(task_name => 'li_sql_1');


三、accept sql profile
接受建议的 SQL 概要文件,即创建SQL_Profle
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'test_sql_tuning_task1',task_owner =>'SYS', replace => TRUE);
 
查看创建起来的SQL_Profile信息
SQL>select a.name,a.task_id,a.createdfrom dba_sql_profiles a,dba_advisor_log bwhere a.task_id=b.task_idand b.task_name='test_sql_tuning_task1';


删除SQL_Profile
SQL>exec dbms_sqltune.drop_sql_profile(name =>'SYS_SQLPROF_01411bdf99410002');