Sql Tuning Advisor是10g以后出现的一个十分有用的调优工具,大多数情况下我们可以通过dbconsole或者Grid Control的web界面调用SQL Advisor;但如果系统中没有配置dbconsole或者Grid Control的话,我们则需要通过手动调用DBMS_SQLTUNE PL/SQL程序包来使用该特性。这里我列出一个针对单个SQL语句Autotune的脚本,具体脚本:
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;
具体使用示例:
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 | ------------------------------------------------------------------------------
Leave a Reply