在Oracle10g之前，优化SQL是个比较费力的技术活，不停的分析执行计划，加hint，分析统计信息等等。在10g中，Oracle推出了自己的SQL优化辅助工具: SQL优化器(SQL Tuning Advisor ：STA)，它是新的DBMS_SQLTUNE包。使用STA一定要保证优化器是CBO模式下。执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限。
下面简单介绍一下如何优化一条找到的问题语句。
create table bigtab as select rownum as "id",a.* from sys.all_objects a;
create table smalltab as select rownum as "id", a.* FROM sys.all_tables a;
然后多运行几次下面的脚本，增加表里的数据：
insert into bigtab select rownum as "id",a.* from sys.all_objects a;
insert into smalltab select rownum as "id", a.* FROM sys.all_tables a;
exec dbms_stats.gather_table_stats(user,'bigtab',estimate_percent=>100,cascade=>true);
exec dbms_stats.gather_table_stats(user,'smalltab',estimate_percent=>100,cascade=>true);
这里创建一张大表和一张小表，并且都没有索引，下面执行一个查询：
sys@ORCL> set autot on exp;
sys@ORCL> select count(*) from bigtab a, smalltab b where a.object_name=b.table_name;
COUNT(*)
----------
63809536
已用时间: 00: 00: 14.60
执行计划
----------------------------------------------------------
Plan hash value: 3089226980
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | | 24324 (3)| 00:04:52 |
| 1 | SORT AGGREGATE | | 1 | 45 | | | |
|* 2 | HASH JOIN | | 95M| 4094M| 55M| 24324 (3)| 00:04:52 |
| 3 | TABLE ACCESS FULL| SMALLTAB | 1816K| 34M| | 11061 (1)| 00:02:13 |
| 4 | TABLE ACCESS FULL| BIGTAB | 2301K| 54M| | 5952 (1)| 00:01:12 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
通过调用函数CREATE_TUNING_TASK来创建优化任务，调用存储过程EXECUTE_TUNING_TASK执行该任务：
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select count(*) from bigtab a, smalltab b where a.object_name=b.table_name';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext,
user_name => 'U1', -- 注意是大写，不然会报错，用户无效
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'tuning_sql_test',
description => 'Task to tune a query on a specified table');
END;
/
第二步： 执行优化任务
通过调用dbms_sqltune.execute_tuning_task过程来执行前面创建好的优化任务。
sys@ORCL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'tuning_sql_test');
PL/SQL 过程已成功完成。
第三步：检查优化任务的状态
通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态。
sys@ORCL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_sql_test';
TASK_NAME STATUS
------------------------------ -----------
tuning_sql_test COMPLETED
第四步：查看优化结果
通过dbms_sqltune.report_tning_task函数可以获得优化任务的结果。
u1@ORCL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tuning_sql_test
Tuning Task Owner : U1
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 11/21/2013 18:59:51
Completed at : 11/21/2013 19:00:09
-------------------------------------------------------------------------------
Schema Name: U1
SQL ID : 6p64dnnsqf9pm
SQL Text : select count(*) from bigtab a, smalltab b where
a.object_name=b.table_name
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Index Finding (see explain plans section below)
--------------------------------------------------
通过创建一个或多个索引可以改进此语句的执行计划。
Recommendation (estimated benefit: 55.99%)
------------------------------------------
- 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
create index U1.IDX$$_034D0001 on U1.SMALLTAB("TABLE_NAME");
- 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
create index U1.IDX$$_034D0002 on U1.BIGTAB("OBJECT_NAME");
Rationale
---------
创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运
行 "访问指导"
可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护
的开销和附加的空间消耗。
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3089226980
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | | 24821 (3)| 00:04:58 |
| 1 | SORT AGGREGATE | | 1 | 45 | | | |
|* 2 | HASH JOIN | | 98M| 4229M| 55M| 24821 (3)| 00:04:58
|
| 3 | TABLE ACCESS FULL| SMALLTAB | 1819K| 34M| | 11157 (1)| 00:02:14 |
| 4 | TABLE ACCESS FULL| BIGTAB | 2373K| 56M| | 6209 (1)| 00:01:15 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
2- Using New Indices
--------------------
Plan hash value: 4218120191
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%C
PU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | | 10923 (6)|
00:02:12 |
| 1 | SORT AGGREGATE | | 1 | 45 | | | |
|* 2 | HASH JOIN | | 98M| 4229M| 55M| 10923 (6)| 00:
02:12 |
| 3 | INDEX FAST FULL SCAN| IDX$$_034D0001 | 1819K| 34M| | 1384 (2)| 00:00:17 |
| 4 | INDEX FAST FULL SCAN| IDX$$_034D0002 | 2373K| 56M| | 2084 (1)| 00:00:2
6 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
-------------------------------------------------------------------------------第一部分是关于这次优化任务的基本信息：如任务名称、执行时间、范围、涉及到的语句等等。
第二部分是关于这次优化任务的所找到的问题以及给出的优化建议。前面先给出了问题描述：可以通过建立更多的索引来提高性能；然后是建议的具体内容：在表smalltab的字段table_name上创建索引，在表bigtab的字段object_name上创建索引；最后是相关注意事项：此次优化虽然给出了创建索引的建议，但是最好通过SQL访问建议器(SQL Access Advisor SAA)结合整个数据库的工作量来深入分析，那样就能给出考虑了索引维护和空间消耗等因素的更加合理的建议。
最后，报告还给出了原有的查询计划，以及采用优化建议以后的查询计划的对比。可以看出COST值大大下降。
五、删除优化任务
通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务
SQL>exec dbms_sqltune.drop_tuning_task('tuning_sql_test');
PL/SQL procedure successfully completed.
注： 整理自网络
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
