手动使用10g Tuning Adviser

A、没有绑定变量的情况：
                
                   1、 SQL> DECLARE
                             l_sql               VARCHAR2(500);
                               l_sql_tune_task_id  VARCHAR2(100);
                            BEGIN
                               l_sql := 'select /*We will trace this sql*/ name,addr,insertdata from
                          (select name,addr,insertdata,rank() over(partition by name,addr order by
                          insertdatadesc) rn from t ) where rn = 1';
                              l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                                                       sql_text    => l_sql,
                                                       user_name   => 'TEST',
                                                      scope       => DBMS_SQLTUNE.scope_comprehensive,
                                                    time_limit  => 60,
                                                      task_name   => 'test_tuning_task',
                                                      description => 'Tuning task for an a simple query.');
                             DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
                            END;
                            /
                       
                        PL/SQL 过程已成功完成。
                        
                     2、 SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'test_tuning_task');
                       
                        PL/SQL 过程已成功完成。
                        
                     3、SQL> SELECT DBMS_SQLTUNE.report_tuning_task('test_tuning_task') AS recommendations FROM dual;
       

 B、有绑定变量的情况：                        
                        SQL> variable var_1 number;
                        SQL> variable var_2 number;
                        SQL> variable var_3 number;
                        SQL> exec :var_1 := 5;
                        SQL> exec :var_2 := 4;                        
                        SQL> exec :var_3 := 3;                        
                        SQL> select id,name,addr from t where id<>:var_1 and id<:var_2 and id<:var_3;
                       
                                ID NAME                 ADDR
                        ---------- -------------------- --------------------
                                 1 张                   北京
                                 2 张                   北京
                       
                        SQL> SELECT task_name, status FROM dba_advisor_log WHERE owner = 'TEST';
                       
                        TASK_NAME                      STATUS
                        ------------------------------ -----------
                        ADDM:1184202927_1_201          COMPLETED
                        ADDM:1184202927_1_202          COMPLETED
                        test_tuning_task                           COMPLETED
                       
                        SQL> BEGIN
                               DBMS_SQLTUNE.drop_tuning_task (task_name => 'test_tuning_task');
                          END;
                           /                        
                        SQL>
                        SQL>
                        SQL> DECLARE
                             l_sql               VARCHAR2(500);
                            l_sql_tune_task_id  VARCHAR2(100);
                           BEGIN
                             l_sql := 'select id,name,addr from t where id<>:var_1 and id<:var_2 and i
                        d<:var_3';
                                                      l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                                                   sql_text    => l_sql,
                                                       bind_list   => sql_binds(anydata.ConvertNumber(5),
                  anydata.ConvertNumber(4),anydata.ConvertNumber(3)),
                                                      user_name   => 'TEST',
                                                      scope       => DBMS_SQLTUNE.scope_comprehensive,
                                                      time_limit  => 60,
                                                      task_name   => 'test_tuning_task',
                                                      description => 'Tuning task for an a simple query
                        .');
                              DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
                             END;
                            /
                        
                        SQL> SELECT * FROM DBA_SQLTUNE_BINDS;

                           TASK_ID  OBJECT_ID   POSITION VALUE()
                        ---------- ---------- ---------- --------------------
                               393          1          1 ANYDATA()
                               393          1          2 ANYDATA()
                               393          1          3 ANYDATA()
      
                        SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'test_tuning_task');
                       
                        PL/SQL 过程已成功完成。
                       
                        SQL> SELECT DBMS_SQLTUNE.report_tuning_task('test_tuning_task') AS recommendatio
                        ns FROM dual;

转自：http://www.itpub.net/thread-1019685-1-1.html