使用SQL Tuning Advisor(STA)为sql提供优化建议

在Oracle10g之前，优化SQL是个比较费力的技术活，不停的分析执行计划，加hint，分析统计信息等等。

在10g中，Oracle推出了自己的SQL优化辅助工具: SQL优化器(SQL Tuning Advisor ：STA)，它可以给出优化建议。

使用STA一定要保证优化器是CBO模式下。

一 创建优化任务

可以通过传入文本来获取相应的优化建议，也可以通过sql_id方式获取。

1.1 传入文本方式

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   => 'SCOTT',   -- 表示该语句由哪个用户执行，注意用户名须大写

scope       => 'COMPREHENSIVE',

time_limit  => 60,

task_name   => 'tuning_sql_test',

description => 'Task to tune a query on a specified table');

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

END;

/

1.2 传入sql_id方式

#根据sql文本获取sql_id：

select sql_id,sql_text from v$sqlarea where sql_text like '%bigtab%'

#传入sql_id

DECLARE

my_task_name VARCHAR2(30);

sql_id VARCHAR2(30);

BEGIN

sql_id := '&sqlid';

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK

(sql_id=> sql_id,

scope => 'comprehensive',

time_limit=>60,

task_name=>'tuning_sql_test',

description => 'Tuning Task');

DBMS_SQLTUNE.EXECUTE_TUNING_TASK('tuning_sql_test');

END;

/

二 执行优化任务

SQL>exec dbms_sqltune.execute_tuning_task('tuning_sql_test');

--检查优化任务的状态

SYS@PROD1>SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_sql_test';

TASK_NAME                STATUS

------------------------------ -----------

tuning_sql_test            COMPLETED

三 查看优化建议

SET LONG 999999
set serveroutput on size 999999
SET LINESIZE 1000

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from DUAL;

优化建议如下：

……

The execution plan of this statement can be improved by creating one or more

  indices.



  Recommendation (estimated benefit: 99.57%)

  ------------------------------------------

  - Consider running the Access Advisor to improve the physical schema design

    or creating the recommended index.



DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

--------------------------------------------------------------------------------

    create index SCOTT.IDX$$_0E4C0001 on SCOTT.BIGTAB("OBJECT_NAME");



  - Consider running the Access Advisor to improve the physical schema design

    or creating the recommended index.

create index SCOTT.IDX$$_0E4C0002 on SCOTT.SMALLTAB("TABLE_NAME");

……

--注意搜索关键字Recommendation和benefit。

四 删除优化任务

exec dbms_sqltune.drop_tuning_task('tuning_sql_test');

--本篇文章主要参考自如何用 SQL Tuning Advisor (STA) 优化SQL语句_Dave的博客-CSDN博客