SQL Tuning 基础概述08

SQL调优顾问 SQL Tuning Advisor的使用案例：

• 1.构建测试表T
• 2.定义调整任务
• 3.修改调整任务参数
• 4.执行调整任务
• 5.监控调整任务
• 6.查看调整任务建议
• 7.删除调整任务

1.构建测试表T

SQL> conn zjy/zjyConnected.SQL> create table t as select * from dba_objects;Table created.SQL> select count(*) from t;COUNT(*)----------653500

2.定义调整任务

对“select owner, object_id, object_name from t where object_id=200000”这个sql定义调整任务：

declare tune_task_name varchar2(30);bad_sql_stmt clob;begin bad_sql_stmt := ''; tune_task_name := dbms_sqltune.create_tuning_task (sql_text => bad_sql_stmt, user_name => 'ZJY', scope => 'COMPREHENSIVE', time_limit => 3600, task_name => 'zjy_sql_tuning_task', description => 'sql_tuning_advisor for this sql' ); end;/

 

SQL> declare 2 tune_task_name varchar2(30);3 bad_sql_stmt clob;4 begin5 bad_sql_stmt := 'select owner, object_id, object_name from t where object_id=200000';6 tune_task_name := dbms_sqltune.create_tuning_task7 (sql_text => bad_sql_stmt,8 user_name => 'ZJY',9 scope => 'COMPREHENSIVE',10 time_limit => 3600,11 task_name => 'zjy_sql_tuning_task',12 description => 'sql_tuning_advisor for this sql'13 ); 14 end;15 /PL/SQL procedure successfully completed.

3.修改调整任务参数

可以修改调整任务的一些参数，比如：

SQL> begin2 dbms_sqltune.set_tuning_task_parameter3 (task_name => 'zjy_sql_tuning_task',4 parameter => 'TIME_LIMIT', value => 18005 );6 end;7 /PL/SQL procedure successfully completed.

4.执行调整任务

开始执行调整任务：

begin dbms_sqltune.execute_tuning_task (task_name => 'zjy_sql_tuning_task');end;/

 

SQL> begin2 dbms_sqltune.execute_tuning_task3 (task_name => 'zjy_sql_tuning_task');4 end;5 /PL/SQL procedure successfully completed.

5.监控调整任务

监控调整任务的执行状态：

select task_name, status, sofar, totalwork from dba_advisor_tasks join v$advisor_progress using(task_id) where task_name = 'zjy_sql_tuning_task';

 

SQL> select task_name, status, sofar, totalwork2 from dba_advisor_tasks3 join v$advisor_progress using(task_id)4 where task_name = 'zjy_sql_tuning_task';TASK_NAME STATUS SOFAR TOTALWORK------------------------------ ----------- ---------- ----------zjy_sql_tuning_task COMPLETED 1 1

6.查看调整任务建议

查看调整任务最终给出的建议：

SQL> select dbms_sqltune.report_tuning_task('zjy_sql_tuning_task') from dual;DBMS_SQLTUNE.REPORT_TUNING_TASK('ZJY_SQL_TUNING_TASK')-------------------------------------------------------------------------------GENERAL INFORMATION SECTION-------------------------------------------------------------------------------Tuning Task Name : zjy_sql_tuning_taskTuning Task Owner : ZJYWorkload Type : Single SQL StatementScope : COMPREHENSIVETime Limit(seconds): 1800Completion Status : COMPLETEDStarted at : 01/23/2015 14:58:40Completed at : 01/23/2015 14:58:58-------------------------------------------------------------------------------Schema Name: ZJYSQL ID : dmw0nx6g0b6h7SQL Text : select owner, object_id, object_name from t whereobject_id=200000-------------------------------------------------------------------------------FINDINGS SECTION (2 findings)-------------------------------------------------------------------------------1- Statistics Finding---------------------尚未分析表 "ZJY"."T"。Recommendation--------------- 考虑收集此表的优化程序统计信息。execute dbms_stats.gather_table_stats(ownname => 'ZJY', tabname => 'T',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =>'FOR ALL COLUMNS SIZE AUTO');Rationale---------为了选择好的执行计划, 优化程序需要此表的最新统计信息。2- Index Finding (see explain plans section below)--------------------------------------------------通过创建一个或多个索引可以改进此语句的执行计划。Recommendation (estimated benefit: 99.86%)------------------------------------------- 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。create index ZJY.IDX$$_3EC90001 on ZJY.T("OBJECT_ID","OWNER","OBJECT_NAME");Rationale---------创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "访问指导"可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。-------------------------------------------------------------------------------EXPLAIN PLANS SECTION-------------------------------------------------------------------------------1- Original-----------Plan hash value: 1601196873--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 124 | 11904 | 3059 (1)| 00:00:37 ||* 1 | TABLE ACCESS FULL| T | 124 | 11904 | 3059 (1)| 00:00:37 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("OBJECT_ID"=200000)2- Using New Indices--------------------Plan hash value: 3141638344-----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 96 | 4 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| IDX$$_3EC90001 | 1 | 96 | 4 (0)| 00:00:01 |-----------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - access("OBJECT_ID"=200000)-------------------------------------------------------------------------------

7.删除调整任务

删除调整任务：

SQL> exec dbms_sqltune.drop_tuning_task('zjy_sql_tuning_task') ;PL/SQL procedure successfully completed.