参考SQL Tunning Adviser进行SQL优化

无论前面介绍的通过“手工”分析手段或通过“工具”分析手段，很多时候我们很有可能面临TOP SQL语句的优化，即SQL优化。很多情况下可以说数据库的优化就是SQL的优化，而SQL的优化呢？那就是的索引的优化！

Oracle 10g开始有个SQL优化功能，即SQL优化顾问（SQL Tuning Adviser）,这个工具可以作为SQL优化过程的参考依据，但并并不能全靠它。因为很多时候SQL的优化还涉及到业务特性，关键业务高峰期等多种因素。另外在使用过程中也发现，SQL Tuning Adviser很多时候对select类语句用处较大…

当我们通过前面介绍动态性能视图的手工查询方式、ASH或后面继续介绍的Statspack、AWR、ADDM工具，会得到一堆TOP SQL语句，这些语句SQL_ID非常关键。因为我们可以根据这些SQL_ID很容易的得到SQL Tuning Adviser的建议。

另外，官方文档也介绍了如下查询（当前Cache中）方法，buffer_gets值得大小根据不同系统会有不同的值：

SELECT sql_id, sql_text

FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> ''SYS'' AND buffer_gets > 500000'))

ORDER BY sql_id;

得到了TOP SQL的SQL_ID后，按如下步骤就可以得到SQL Tuning Adviser的建议，具体步骤如下：

[@more@]

1、根据SQL_ID通过DBMS_SQLTUNE.CREATE_TUNING_TASK包创建Task

DECLARE

my_task_name1 VARCHAR2(30);

BEGIN

my_task_name1 := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_id => '6cpjmb62rhhp3',

task_name => 'my_sql_tuning_task1',

description => 'Task to tune a top sql');

END;

2、通过如下查询查询owner和task_id

SELECT * FROM DBA_ADVISOR_LOG WHERE task_name='my_sql_tuning_task1' --owner = 'sa'; --129863

3、根据Task名执行优化顾问

BEGIN

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task1' );

END;

4、可以通过如下查询得知优化执行进度

SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE TASK_ID = 129863 --and USERNAME = 'sa' ;

5、优化顾问执行完毕后，通过如下查询即可得到最终想要的优化建议

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task1')FROM DUAL;

6、建议结果很多时候是创建索引（或组合索引），有的时候也要建议采用SQL_PROFILE，Profile的具体应用方式建议结果中有，类此如下：

----执行如下建议：

BEGIN

dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task1', replace => TRUE);

END;

7、参考完毕TASK建议后，最后步骤就是删除已创建的TASK，因为TASK名是不能重复的。

BEGIN

DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'my_sql_tuning_task1');

END;

通过如上步骤我们会得到SQL Tunning Adviser的建议，通过如下SQL可以查看绑定变量值：

select dbms_sqltune.extract_binds(bind_data) from sys.wrh$_sqlstat a where a.sql_id='6cpjmb62rhhp3'

或者

select dbms_sqltune.extract_bind(bind_data, 1).value_string || '-' || dbms_sqltune.extract_bind(bind_data, 2)

.value_string || '-' || dbms_sqltune.extract_bind(bind_data, 3)

.value_string || '-' || dbms_sqltune.extract_bind(bind_data, 4)

.value_string || '-' || dbms_sqltune.extract_bind(bind_data, 5)

.value_string || '-' || dbms_sqltune.extract_bind(bind_data, 6)

.value_string || '-' || dbms_sqltune.extract_bind(bind_data, 7)

.value_string

from sys.wrh$_sqlstat

where sql_id = '6cpjmb62rhhp3'

有时候我们需要收集统计信息，未及时更新的统计信息也会影响SQL语句执行计划：

begin

dbms_stats.gather_table_stats(ownname => 'scott',

tabname => 'test',

estimate_percent => 20,

method_opt => 'for all indexed columns',

cascade => true);

end;

或者

analyze table scott.test compute statistics;

无论是否采用SQL Tunning Adviser,当我们知道SQL_ID后可以通过AWR的awrsqrpt脚本查看真正的执行计划

SQL> @?/rdbms/admin/awrsqrpt

或者如下语句也是可以

select * from table(dbms_xplan.display_awr('fjhvhc6fytp1c'));

如下方法也能得到SQL过去执行的执行计划：

select id,operation, options,object_owner,object_name,object_type,cost,cardinality,bytes,cpu_cost,io_cost

from DBA_HIST_SQL_PLAN where sql_id='djpvmvjddy8av'

order by id;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18841027/viewspace-1058816/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18841027/viewspace-1058816/