Automatic SQL Tuning in Oracle Database(三)
S_SQLTUNE.resume_tuning_task (task_name => 'tuning_sql_test');
-- Cancel a tuning task.
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'tuning_sql_test');
-- Reset a tuning task allowing it to be re-executed.
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'tuning_sql_test');
3.查看任务是否结束
SQL> SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT';
TASK_NAME STATUS
------------------------------ -----------
tuning_sql_test COMPLETED
4.查看优化结果
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('tuning_sql_test') AS recommendations FROM dual;
SET PAGESIZE 24
其中内容如下：oracle给了很详细的建议。
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST3')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tuning_sql_test3
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Execution Count : 2
Current Execution : EXEC_1088
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 10/28/2013 16:30:49
Completed at : 10/28/2013 16:31:33
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : gmc689fch5gr4
SQL Text : select count(*) from t,t1 where t.object_id=t1.object_id
-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
尚未分析表 "SCOTT"."T1"。
Recommendation
--------------
- 考虑收集此表的优化程序统计信息。
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'T1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt
=> 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
为了选择好的执行计划, 优化程序需要此表的最新统计信息。
2- Statistics Finding
---------------------
尚未分析表 "SCOTT"."T"。
Recommendation
--------------
- 考虑收集此表的优化程序统计信息。
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'T',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =>
'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
为了选择好的执行计划, 优化程序需要此表的最新统计信息。
3- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
为此语句找到了性能更好的执行计划。
Recommendation (estimated benefit: 86.96%)
------------------------------------------
- 考虑接受建议的 SQL 概要文件, 以便对此语句使用并行执行。
execute dbms_sqltune.accept_sql_profile(task_name => 'tuning_sql_test3',
task_owner => 'SCOTT', replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);
与 DOP 8 并行执行此查询会使原始计划上的响应时间缩短 86.97%。但是, 启用并行执行时要付出一些代价。它将增加语句的资源消耗 (预计为4.25%), 这会导致系统吞吐量降低。此外, 由于在非常短的持续时间内消耗了这些资源,因此如果没有足够可用的硬件容量,并发语句的响应时间将受到负面影响。
The following data shows some sampled sta