SQL Tuning Advisor(SQL调优顾问,STA)

原创 2013年12月05日 16:47:51

SQL调优顾问需要一个或多个SQL语句作为输入,并调用自动优化器执行SQL调优。SQL调优顾问输出是以一种意见或者建议的形式,以及对每一项建议和期望效益的理由。该建议涉及对象的统计收集新索引的创建SQL语句的重组,或SQL概要的创建。你可以选择该建议来完成SQL语句的调优。

Oracle数据库可以自动优化有问题的SQL语句,并使用SQL调优顾问实现调优建议。也可以手动运行SQL调优顾问选择单个SQL语句或者一个SQL语句集来完成SQL语句调优。

一般使用的比较多的是手动SQL调优顾问,因此也重点介绍手动SQL调优顾问。

 

1.自动SQL调优顾问

1.1启用自动SQL调优

begin
  dbms_auto_task_admin.enable(client_name => 'sql tuning advisor',
                              operation   => null,
                              window_name => null);
end;

1.2失效自动SQL调优

begin
  dbms_auto_task_admin.disable(client_name => 'sql tuning advisor',
                               operation   => null,
                               window_name => null);
end;

 

1.3查看自动SQL调优报表

create  table a (a1 clob);

declare
  my_rept clob;
begin
  my_rept := dbms_sqltune.report_auto_tuning_task(begin_exec   => null,
                                                  end_exec     => null,
                                                  type         => 'TEXT',
                                                  level        => 'TYPICAL',
                                                  section      => 'ALL',
                                                  object_id    => null,
                                                  result_limit => null);

  insert into a values (my_rept);
end;

select * from a;

 

截取报告片段:

FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  表 "SYS"."OBJAUTH$" 及其索引的优化程序统计信息已失效。

  Recommendation
  --------------
  - 考虑收集此表的优化程序统计信息。
    execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
            'OBJAUTH$', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------
    为了选择好的执行计划, 优化程序需要此表的最新统计信息。

 

2.手动SQL调优顾问

SQL调优顾问可以按照需求手动调用一个或者多个SQL语句。要调用多条SQL语句,需要创建一个SQL调优集(STS)。SQL调优集是一个存储SQL语句以及执行上下文的数据库对象。

使用DBMS_SQLTUNE包运行SQL调优顾问,需要一下几个步骤:

  1. 创建SQL调优集(如果调优多个SQL语句)
  2. 创建SQL调优任务
  3. 执行优化SQL调优任务
  4. 显示SQL调优任务结果
  5. 实现适当的建议

一个SQL调优任务可以为单个SQL语句创建。对于多个SQL语句,需要先创建SQL调优集(STS)。具体步骤如下:

 

为了找出有问题的SQL语句,需要创建创建一张没有任何所有的表。如下:

create table my_objects as select * from dba_objects;

查询EMP对象的SQL语句

select * from my_objects o where o.object_name='EMP';

my_objects表上没有任何创建索引,因此该查询语句肯定是有问题的,接下来使用SQL调优顾问来优化该SQL语句:

 

2.1.创建SQL调优任务

declare
  v_my_task_name varchar2(50);
  v_my_sql_text  clob;
begin
  v_my_sql_text := 'select * from my_objects o where o.object_name=:bin';

  v_my_task_name := dbms_sqltune.create_tuning_task(sql_text    => v_my_sql_text,
                                                    bind_list   => sql_binds(anydata.convertvarchar2('EMP')),
                                                    user_name   => 'GDSHEC',
                                                    scope       => 'COMPREHENSIVE',
                                                    time_limit  => 60,
                                                    task_name   => 'my_sql_tuning_task',
                                                    description => 'Task to tune a query on a specified  ');

end;

 

2.2.配置SQL调优任务

begin
  dbms_sqltune.set_tuning_task_parameter(task_name => 'my_sql_tuning_task',
                                         parameter => 'TIME_LIMIT',
                                         value     => 300);
end;

 

2.3.执行SQL调优任务

begin
  dbms_sqltune.execute_tuning_task(task_name => 'my_sql_tuning_task');
end;

 

2.4.检查SQL调优任务状态

select * from dba_advisor_tasks t where t.task_name = 'my_sql_tuning_task';

2.5.显示SQL调优任务结果

select dbms_sqltune.report_tuning_task(task_name => 'my_sql_tuning_task')
  from dual;

 

优化结果报告:

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task
Tuning Task Owner  : GDSHEC
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 300
Completion Status  : COMPLETED
Started at         : 12/05/2013 16:10:32
Completed at       : 12/05/2013 16:10:34

-------------------------------------------------------------------------------
Schema Name: GDSHEC
SQL ID     : 14suc19101mgf
SQL Text   : select * from my_objects o where o.object_name=:bin

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Index Finding (see explain plans section below)
--------------------------------------------------
  通过创建一个或多个索引可以改进此语句的执行计划。

  Recommendation (estimated benefit: 98.73%)
  ------------------------------------------
  - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
    create index GDSHEC.IDX$$_02290001 on GDSHEC.MY_OBJECTS("OBJECT_NAME");

  Rationale
  ---------
    创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "访问指导"
    可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。

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

1- Original
-----------
Plan hash value: 880823944

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     2 |   194 |   317   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| MY_OBJECTS |     2 |   194 |   317   (1)| 00:00:04 |
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("O"."OBJECT_NAME"=:BIN)

2- Using New Indices
--------------------
Plan hash value: 394593291

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     2 |   194 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_OBJECTS     |     2 |   194 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX$$_02290001 |     2 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("O"."OBJECT_NAME"=:BIN)

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

 

总结:SQL调优顾问发现一个问题,建议在OBJECT_NAME表的MY_OBJECTS字段上创建索引。并对照创建索引前后的执行计划进行对比。

 

2.6删除SQL调优任务

begin
  dbms_sqltune.drop_tuning_task(task_name => 'my_sql_tuning_task');
end;

 

 

 

 

SQL Tuning Advisor 使用11G的自动调优建议

ORACLE 提供了自动SQL优化的工具,当对DBA来说 可以节约下思考的时间,或许提供更多的参考价值...

初次使用SQL调优建议工具--SQL Tuning Advisor

在10g中,Oracle推出了自己的SQL优化辅助工具: SQL优化器(SQL Tuning Advisor :STA),它是新的DBMS_SQLTUNE包。使用STA一定要保证优化器是CBO模式下。...

利用oracle sql tuning advisor 进行sql调优

sql tunning advisor 使用的主要步骤:   1 建立tunning task   2 执行task  3 显示tunning 结果  4 根据建议来运行相应的调优方法下面来按照这个顺...

用 SQL Tuning Advisor (STA) 优化SQL语句

转自http://blog.csdn.net/tianlesoftware/article/details/5630888 在Oracle10g之前,优化SQL是个比较费力的技术活,不停的分析执行计...

如何用 SQL Tuning Advisor (STA) 优化SQL语句

在Oracle10g之前,优化SQL是个比较费力的技术活,不停的分析执行计划,加hint,分析统计信息等等。在10g中,Oracle推出了自己的SQL优化辅助工具: SQL优化器(SQL Tuning...

SQL Tuning Advisor使用实例

在oracle10g之前,想要优化一个sql语句是比较麻烦,但是在oracle10g这个版本推出的SQL Tuning Advisor这个工具,能大大减少sql调优的工作量,不过要想使用SQL Tun...

SQL Tuning Advisor

试着回答: A) Access path analysis is not performed for the SQL statements.  B) SQL structure analysi...

Automatic SQL Tuning Advisor

在10gR2中,提供了$ORACLE_HOME/rdbms/admin/sqltrpt.sql脚本,用于抽取占用资源较多的sql,并可以为指定的sql生成执行计划,资源占用较多的sql分为两部分 1...
  • rgb_rgb
  • rgb_rgb
  • 2012年08月26日 19:09
  • 1554

oracle 10gADDM 和 SQL Tuning Advisor

  • 2008年09月02日 18:10
  • 347KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL Tuning Advisor(SQL调优顾问,STA)
举报原因:
原因补充:

(最多只允许输入30个字)