自动sql调优（automatic tuning）中的 SQL_profile完整例子

-- general create a tuning task
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT /*+ ORDERED */ * ' ||
'FROM employees e, locations l, departments d ' ||
'WHERE e.department_id = d.department_id AND ' ||
'l.location_id = d.location_id AND ' ||
'e.employee_id < :bnd'; 写新文章
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'SCOTT',
scope => 'COMPREHENSIVE',
time_limit => 60,
ask_name => 'my_sql_tuning_task',
description => 'Task to tune a query on a specified employee');
END;
/
-- configure the tuning task parameters
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER( task_name => 'my_sql_tuning_task', parameter => 'TIME_LIMIT', value => 300);
END;
/

--------       full   section  test           --------------------
-------- environment：oracle 11g  --------------------
1. create table ;
create table ptest (n number ); 


2. insert  data
declare 
    begin 
      for i in 1 .. 10000 loop 
       insert into ptest values(i); 
       commit; 
      end loop; 
 end; 
 
 3. create index  
 create index ptest_idx on ptest(n); 
 
 4. gether statistics 
 exec dbms_stats.gather_table_stats('','PTEST'); 
 5. select 
 ----- force no index-----
SQL> select /*+ no_index(ptest ptest_idx) */ * from ptest where n=1 
  2  /

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

Execution Plan
----------------------------------------------------------
Plan hash value: 735595066
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PTEST |     1 |     4 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N"=1)
Statistics
----------------------------------------------------------
          6  recursive calls
          4  db block gets
         25  consistent gets
          0  physical reads
        548  redo size
        519  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

6. create tuning task

declare 
  my_task_name VARCHAR2(30); 
  my_sqltext CLOB; 
  begin 
     my_sqltext := 'select /*+ no_index(ptest ptest_idx) */ * from ptest where n=1'; 
     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_2', 
     description => 'Task to tune a query on a specified table'); 
end; 

7. execute tuning task
begin 
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2'); 
end;

8. check the tuning task

SQL> set long 10000
SQL> set longchunksize 1000 
SQL> set linesize 200
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_2') from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task_2
Tuning Task Owner  : SCOTT
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 12/01/2011 10:07:36
Completed at       : 12/01/2011 10:07:37


-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : 7u2x50zw8g9qq
SQL Text   : select /*+ no_index(ptest ptest_idx) */ * from ptest where n=1


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

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

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

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time(us):                 194                21      89.17 %
  CPU Time(us):                     200                 0        100 %
  User I/O Time(us):                  0                 0
  Buffer Gets:                       20                 1         95 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                     1                 1
  Fetches:                            1                 1
  Executions:                         1                 1


  Notes
  -----
  1. The original plan was first executed to warm the buffer cache.
  2. Statistics for original plan were averaged over next 9 executions.
  3. The SQL profile plan was first executed to warm the buffer cache.
  4. Statistics for the SQL profile plan were averaged over next 9 executions.


-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 735595066

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PTEST |     1 |     4 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=1)


2- Using SQL Profile
--------------------
Plan hash value: 2258500329


------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| PTEST_IDX |     1 |     4 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("N"=1)

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

9. accept the new sql profile
a:
execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', task_owner => 'SCOTT', replace => TRUE);

b: 
DECLARE 
my_sqlprofile_name VARCHAR2(30); 
begin 
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( 
task_name => 'my_sql_tuning_task_2', 
name => 'my_sql_profile'); 
end; 

10. test the result 
SQL> select /*+ no_index(ptest ptest_idx) */ * from ptest where n=1
  2  /

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

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

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("N"=1)

Note
-----
   - SQL profile "SYS_SQLPROF_0133f767367b0000" used for this statement
   - SQL plan baseline "SQL_PLAN_g83gv68t4rn3z9acce914" used for this statement

Statistics
----------------------------------------------------------
         68  recursive calls
         25  db block gets
         21  consistent gets
          1  physical reads
      11528  redo size
        519  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed