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

转载 2013年12月03日 16:18:59

在Oracle10g之前,优化SQL是个比较费力的技术活,不停的分析执行计划,加hint,分析统计信息等等。在10g中,Oracle推出了自己的SQL优化辅助工具: SQL优化器(SQL Tuning Advisor STA),它是新的DBMS_SQLTUNE包。使用STA一定要保证优化器是CBO模式下。

执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限:

SQL> create user dave identified by dave;

用户已创建。

SQL> grant connect,resource to dave;

授权成功。

SQL> grant advisor to dave;

授权成功。

下面简单介绍一下如何优化一条找到的问题语句。

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;

这里创建一张大表和一张小表,并且都没有索引,下面执行一个查询:

SQL> set timing on

SQL> set autot on

SQL> select count(*) from bigtab a, smalltab b where a.object_name=b.table_name;

  COUNT(*)

----------

   2141537

已用时间:  00: 00: 20.05

执行计划

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

Plan hash value: 3089226980

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

| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |          |     1 |    45 |  3146   (1)| 00:00:38 |

|   1 |  SORT AGGREGATE     |          |     1 |    45 |            |          |

|*  2 |   HASH JOIN         |          |   447K|    19M|  3146   (1)| 00:00:38 |

|   3 |    TABLE ACCESS FULL| SMALLTAB | 27327 |   533K|   264   (1)| 00:00:04 |

|   4 |    TABLE ACCESS FULL| BIGTAB   |   712K|    16M|  2878   (1)| 00:00:35 |

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

Predicate Information (identified by operation id):

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

   2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")

统计信息

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

          0  recursive calls

          0  db block gets

      31149  consistent gets

      21058  physical reads

          0  redo size

        426  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

第一步:创建优化任务

通过调用函数CREATE_TUNING_TASK来创建优化任务,调用存储过程EXECUTE_TUNING_TASK执行该任务:

SQL> set autot off

SQL> set timing off

SQL> DECLARE

  2    my_task_name VARCHAR2(30);

  3    my_sqltext   CLOB;

  4  BEGIN

  5    my_sqltext := 'select count(*) from bigtab a, smalltab b where a.object_name=b.table_name';

  6    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

  7            sql_text    => my_sqltext,

  8            user_name   => 'DAVE',   -- 注意是大写,不然会报错,用户无效

  9            scope       => 'COMPREHENSIVE',

10            time_limit  => 60,

11            task_name   => 'tuning_sql_test',

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

13

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

15  END;

16  /

 

PL/SQL procedure successfully completed.

在函数CREATE_TUNING_TASK,sql_text是需要优化的语句,user_name是该语句通过哪个用户执行,scope是优化范围(limited或comprehensive),time_limit优化过程的时间限制,task_name优化任务名称,description优化任务描述。

第二步: 执行优化任务

通过调用dbms_sqltune.execute_tuning_task过程来执行前面创建好的优化任务。

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

PL/SQL 过程已成功完成。

第三步:检查优化任务的状态

通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态。

SQL> 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函数可以获得优化任务的结果。

SQL> SET LONG 999999

SQL> set serveroutput on size 999999

SQL> SET LINESIZE 100

SQL> 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                 : DEMO

Scope                             : COMPREHENSIVE

Time Limit(seconds)               : 60

Completion Status                 : COMPLETED

Started at                        : 5/28/2010 13:16:43

Completed at                      : 5/28/2010 13:16:44

Number of Index Findings          : 1

 

Schema Name: DEMO

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)

 

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

  indices.

 

  Recommendation (estimated benefit: 100%)

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

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

    or creating the recommended index.

    create index DEMO.IDX$$_06C50001 on SYS.SMALLTAB('TABLE_NAME');

 

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

    or creating the recommended index.

    create index DEMO.IDX$$_06C50002 on SYS.BIGTAB('OBJECT_NAME');

 

  Rationale

  ---------

    Creating the recommended indices significantly improves the execution plan

    of this statement. However, it might be preferable to run "Access Advisor"

    using a representative SQL workload as opposed to a single statement. This

    will allow to get comprehensive index recommendations which takes into

    account index maintenance overhead and additional space consumption.

 

 

EXPLAIN PLANS SECTION

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

1- Original

-----------

Plan hash value: 3089226980

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

| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

 

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

|   0 | SELECT STATEMENT    |          |     1 |    36 |  3550   (2)| 00:00:43 |

|   1 |  SORT AGGREGATE     |          |     1 |    36 |            |          |

|*  2 |   HASH JOIN         |          |   155K|  5462K|  3550   (2)| 00:00:43 |

|   3 |    TABLE ACCESS FULL| SMALLTAB |  1223 | 22014 |    11   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL| BIGTAB   |  1205K|    20M|  3526   (1)| 00:00:43 |

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

Predicate Information (identified by operation id):

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

   2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")

2- Using New Indices

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

Plan hash value: 494801882

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

| Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT       |                |     1 |    36 |  1108   (3)| 00:00:14 |

|   1 |  SORT AGGREGATE        |                |     1 |    36 |            |        |

|*  2 |   HASH JOIN            |                |   155K|  5462K|  1108   (3)| 00:00:14 |

|   3 |    INDEX FAST FULL SCAN| IDX$$_06C50001 |  1223 | 22014 |     3   (0)| 00:00:01 |

|   4 |    INDEX FAST FULL SCAN| IDX$$_06C50002 |  1205K|    20M|  1093   (2)| 00:00:14 |

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

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.

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

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

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

ORACLE 提供了自动SQL优化的工具,当对DBA来说 可以节约下思考的时间,或许提供更多的参考价值...
  • ZengMuAnSha
  • ZengMuAnSha
  • 2016年06月03日 11:40
  • 6081

ORACLE调优概述以及ADDM以及STA辅助生成示例

整理了笔者日常工作中的经验,概述了常见的ORACLE数据库优化的方法,并展示了如何通过ADDM以及STA辅助生成优化意见...
  • zccgood002
  • zccgood002
  • 2017年03月12日 14:52
  • 273

sql语句优化总结

性能不理想的系统中除了一部分是因为应用程序的负载确实超过了服务器的实际处理能力外,更多的是因为系统存在大量的SQL语句需要优化。 为了获得稳定的执行性能,SQL语句越简单越好。对复杂的SQL语句...
  • fyuanfena
  • fyuanfena
  • 2016年07月27日 09:26
  • 1257

sql语句优化的13种方法

原文链接:http://blog.csdn.net/u012942818/article/details/53969972 1,什么是“执行计划”? 执行计划是数据库根据SQL语句和相关表的统计信息...
  • xie_xiansheng
  • xie_xiansheng
  • 2017年05月02日 10:08
  • 563

SQL语句太复杂,怎么优化

 一、视图和存储过程的深度   视图和存储过程能够抽象出一些业务逻辑,简化设计,是很推荐的做法。但是如果在引用视图和存储过程时不加注意,视图套视图,存储过程嵌存储过程,最后嵌套上四五层,那复...
  • whazhl
  • whazhl
  • 2014年09月03日 18:43
  • 1870

SQL优化指导(STA)与SQL访问指导(SAA)

SQL优化指导(STA)   可以使用SQL优化指导分析SQL语句,并获得性能建议。 SQL优化指导的分析来源:          顶级活动:分析当前活动的顶级SQL语句。          SQL优...
  • u013169075
  • u013169075
  • 2017年01月01日 16:56
  • 233

sql语句的细节优化

Sql优化这个话题说来好像很宏大似的,如果你的数据库负载能力超牛B,完全可以不去耗费心思去捋sql;但,话有说回来,一个有条有理的sql,就代表了一个程序员的个人风格,有谁不希望自己的代码被人模仿或称...
  • qq_16216221
  • qq_16216221
  • 2015年05月29日 16:58
  • 547

快速SQL TUNING——1分钟搞定超慢SQL

本文通过真实的示例,展示了一次解决大SQL性能问题的过程,面对如此繁杂而冗长的SQL和执行计划,我们必须掌握一定的经验和方法,才可能在短时间内解决问题,这里主要是提供给大家一个真实的示例,希望大家能从...
  • LHDZ_BJ
  • LHDZ_BJ
  • 2013年09月17日 16:39
  • 1423

oracle sql语句优化原则经验总结

oracle sql语句优化 分类: Database 2008-11-15 14:02 836人阅读 评论(1) 收藏 举报 sqloracle优化数据库bufferd...
  • haiross
  • haiross
  • 2013年12月05日 15:51
  • 1543
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:使用SQL Tuning Advisor (STA) 优化SQL语句
举报原因:
原因补充:

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