oracle 11R2 sql tuning advior

一直都想用下10G中自带的sql tuning advior，今天终于大致了解下如何用，记录下

1.SYS用户建测试表,测试数据
SQL> create table student (id number,name varchar2(50));

表已创建。
SQL> insert into student values (1,'xxx');

已创建 1 行。

SQL> insert into student values (2,'yyy');

已创建 1 行。

SQL> create table grade (id number,score number);

表已创建。

SQL> insert into grade values (1,100);

已创建 1 行。

SQL> insert into grade values (2,90);

已创建 1 行。

SQL> commit;

提交完成。

2.通过包dbms_sqltune调用SQL TUNING ADVISOR

SQL> declare
  2
  3  l_task_id     varchar2(20);
  4  l_sql         varchar2(2000);
  5  begin
  6  l_sql := 'select s.name,g.score from student s,grade g where s.id=g.id';
  7  --dbms_sqltune.drop_tuning_task ('TEST');
  8  l_task_id := dbms_sqltune.create_tuning_task (
  9  sql_text  => l_sql,
 10  user_name  => 'SYS',
 11  scope      => 'COMPREHENSIVE',
 12  time_limit => 120,
 13  task_name  => 'TEST'
 14     );
 15  dbms_sqltune.execute_tuning_task ('TEST');
 16  end;
 17  /

PL/SQL 过程已成功完成。

3.查看SQL TUNING ADVISOR给的优化建议

select dbms_sqltune.report_tuning_task ('TEST') from dual;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : daibo
Tuning Task Owner                 : SYS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 120
Completion Status                 : COMPLETED
Started at                        : 04/11/2010 00:19:15
Completed at                      : 04/11/2010 00:19:15
Number of Statistic Findings      : 2

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : 4y830pqda3mzs
SQL Text   : select s.name,g.score from student s,grade g where s.id=g.id

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
尚未分析表 "SYS"."GRAD

  Recommendation
 --------------
  - 考虑收集此表的优
    execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
            'GRADE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------
为了选择好的执行计划, 优化程序需

2- Statistics Finding
---------------------
尚未分析表 "SYS"."STUDEN

  Recommendation
  --------------
  - 考虑收集此表的优
    execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
            'STUDENT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------
为了选择好的执行计划, 优化程序需

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 1192007014

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     2 |   132 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |         |     2 |   132 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| STUDENT |     2 |    80 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| GRADE   |     2 |    52 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S"."ID"="G"."ID")

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

如果想查看系统对单个语句的优化建议，该如何处理呢，也有相关包供使用,那就是dbms_advisor.quick_tune

例如:

exec dbms_advisor.quick_tune(

advisor_name =>  dbms_advisor.SQLACCESS_ADVISOR,

task_name => 'test6',

attr1 => 'select avg(a) from dbtest1 where a=''5''');