Sql Tuning Advisor是10g以后出现的一个十分有用的调优工具，大多数情况下我们可以通过dbconsole或者Grid Control的web界面调用SQL Advisor；但如果系统中没有配置dbconsole或者Grid Control的话，我们则需要通过手动调用DBMS_SQLTUNE PL/SQL程序包来使用该特性。这里我列出一个针对单个SQL语句Autotune的脚本，具体脚本:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
begin
   DBMS_SQLTUNE.drop_tuning_task( '&task_name' );
end ;
/
 
 
 
DECLARE
   my_task_name VARCHAR2(30);
   my_sqltext CLOB;
   my_sqlid varchar2(30);
BEGIN
   my_sqlid :=  '&sqlid' ;
   my_task_name := dbms_sqltune.create_tuning_task(sql_id => my_sqlid,
                                                   scope =>  'COMPREHENSIVE' ,
                                                   time_limit => 300,
                                                   task_name =>  '&task_name' ,
                                                   description =>  'comment'
                                                   );
END ;
/
 
 
BEGIN
   dbms_sqltune.execute_tuning_task(task_name =>  '&task_name' );
END ;
/
 
SELECT  status  FROM  USER_ADVISOR_TASKS  WHERE  task_name =  '&task_name' ;
 
SET  LONG 10000
SET  LONGCHUNKSIZE 10000
SET  LINESIZE 100
set  pages 60
 
SELECT  DBMS_SQLTUNE.REPORT_TUNING_TASK( '&task_name' )  FROM  DUAL;
具体使用示例:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
SQL>  begin
   DBMS_SQLTUNE.drop_tuning_task( '&task_name' );
end ;
/
Enter value  for  task_name: newtask
old   3:   DBMS_SQLTUNE.drop_tuning_task( '&task_name' );
new   3:   DBMS_SQLTUNE.drop_tuning_task( 'newtask' );
begin
*
ERROR  at  line 1:
ORA-13605: The specified task  or  object newtask does  not  exist  for  the  current
user .
ORA-06512:  at  "SYS.PRVT_ADVISOR" , line 2307
ORA-06512:  at  "SYS.DBMS_ADVISOR" , line 172
ORA-06512:  at  "SYS.DBMS_SQLTUNE" , line 751
ORA-06512:  at  line 3
 
SQL>  DECLARE
   my_task_name VARCHAR2(30);
   my_sqltext CLOB;
   my_sqlid varchar2(30);
BEGIN
   my_sqlid :=  '&sqlid' ;
   my_task_name := dbms_sqltune.create_tuning_task(sql_id => my_sqlid,
                                                   scope =>  'COMPREHENSIVE' ,
                                                   time_limit => 300,
                                                   task_name =>  '&task_name' ,
                                                   description =>  'comment'
                                                   );
END ;
/
Enter value  for  sqlid: 17usubxchdf2w
old  11:   my_sqlid :=  '&sqlid' ;
new  11:   my_sqlid :=  '17usubxchdf2w' ;
Enter value  for  task_name: new_task
old  19:                           task_name =>  '&task_name' ,
new  19:                           task_name =>  'new_task' ,
 
SQL>  BEGIN
   dbms_sqltune.execute_tuning_task(task_name =>  '&task_name' );
END ;
/
Enter value  for  task_name: new_task
old   3:   dbms_sqltune.execute_tuning_task(task_name =>  '&task_name' );
new   3:   dbms_sqltune.execute_tuning_task(task_name =>  'new_task' );
 
PL/SQL  procedure  successfully completed.
 
SQL>  SELECT  status  FROM  USER_ADVISOR_TASKS  WHERE  task_name =  '&task_name' ;
Enter value  for  task_name: new_task
old   1:  SELECT  status  FROM  USER_ADVISOR_TASKS  WHERE  task_name =  '&task_name'
new   1:  SELECT  status  FROM  USER_ADVISOR_TASKS  WHERE  task_name =  'new_task'
 
STATUS
-----------
COMPLETED
 
SQL>  SELECT  DBMS_SQLTUNE.REPORT_TUNING_TASK( '&task_name' )  FROM  DUAL;
Enter value  for  task_name: new_task
old   1:  SELECT  DBMS_SQLTUNE.REPORT_TUNING_TASK( '&task_name' )  FROM  DUAL
new   1:  SELECT  DBMS_SQLTUNE.REPORT_TUNING_TASK( 'new_task' )  FROM  DUAL
 
DBMS_SQLTUNE.REPORT_TUNING_TASK( 'NEW_TASK' )
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION  SECTION
-------------------------------------------------------------------------------
Tuning Task  Name    : new_task
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope          : COMPREHENSIVE
Time  Limit(seconds): 300
Completion Status  : COMPLETED
Started  at      : 03/25/2011 00:14:41
Completed  at        : 03/25/2011 00:14:45
 
-------------------------------------------------------------------------------
Schema  Name : SYS
SQL ID     : 17usubxchdf2w
SQL Text   :  select  count (t1)  from  hashtab
 
-------------------------------------------------------------------------------
FINDINGS  SECTION  (1 finding)
-------------------------------------------------------------------------------
 
1-  Statistics  Finding
---------------------
   Table  "SYS" . "HASHTAB"  was  not  analyzed.
 
   Recommendation
   --------------
   - Consider collecting optimizer  statistics  for  this  table .
     execute  dbms_stats.gather_table_stats(ownname =>  'SYS' , tabname =>
         'HASHTAB' , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
         method_opt =>  'FOR ALL COLUMNS SIZE AUTO' );
 
   Rationale
   ---------
     The optimizer requires up- to - date  statistics  for  the  table  in  order  to
     select  a good execution plan.
 
-------------------------------------------------------------------------------
EXPLAIN PLANS  SECTION
-------------------------------------------------------------------------------
 
1- Original
-----------
Plan hash value: 485915006
 
------------------------------------------------------------------------------
| Id  | Operation      |  Name     |  Rows   | Bytes | Cost (%CPU)|  Time      |
------------------------------------------------------------------------------
|   0 |  SELECT  STATEMENT   |         |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |         |     1 |    13 |        |      |
|   2 |    TABLE  ACCESS  FULL | HASHTAB |   102 |  1326 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------