SQL 优化指导和SQL 访问指导的讨论

SQL 优化指导和SQL 访问指导的讨论

----最近在翻看STA 和 SAA 时，发现两个问题。 1、第一 互联网上大家对这两个概念的区别比较模糊。

2、SAA的操作十分麻烦，幸好有OEM 以及联机文档中有实例。

下面对这个两个问题进行讨论：

以下观点引用自 Arup

Nanda --http://www.oracle.com/technetwork/cn/articles/11g-sqlaccessadvisor-093

323-zhs.html

SQL Tuning Advisor，它可以提供有关单个查询调整以及在流程中延长整个优化过程的建议 。

SQL Access ADVISOR 调整的是整个负载（对单个语句当然也可用，并且语法及其简单）

Oracle 数据库 10g中SQL Access Advisor 可以分析索引、物化视图等。

Oracle 数据库 11g 中的 SQL Access Advisor 还可以分析表和查询以识别可能的分区策略 — 这在设计最佳模式时可以提供很大帮助。

SQL Tuning Advisor 提出的建议只对应以下四个目标之一： 1、为统计信息丢失或失效的对象收集统计信息

2、考虑优化器的任何数据偏差、复杂谓词或失效的统计信息 3、 重新构建 SQL 以优化性能 4、 提出新索引建议

联机文档中没有明显找到描述二者差异的论断，下面是联机文档关于两者的介绍

SQL Tuning Advisor

You use the SQL Tuning Advisor to tune a single or multiple SQL statements.

Typically, you run this advisor in response to an ADDM performance finding that recommends its use. You can also run it periodically on the most resource-intensive SQL statements, and on a SQL workload.

--本人翻译水平有限，大家不要见笑

你可以使用STA 调优一个简单或复杂的SQL 语句。典型的情况是使用STA应用于在ADDM中报出来的一些问题，你也可以周期性地对那些资源消耗最严重的SQL 语句或负载去用STA。

When tuning multiple SQL statements, the SQL Tuning Advisor does not recognize interdependencies between the SQL statements. It solves SQL

performance problems by identifying problems with individual SQL statements, such as a poorly performing optimizer plan or the mistaken use of certain SQL structures.

当调优复杂语句时，STA不能够识别语句之间的依赖关系，它解决SQL性能问题是通过检查单个语句的问题来实现的，比如一个差的执行计划或者固定SQL结构的误用。

SQL Access Advisor

The SQL Access Advisor is primarily responsible for making schema

modification recommendations. It can recommend that you create access structures such as indexes and materialized views to optimize SQL queries. It can also recommend that you partition tables, indexes, or materialized views to improve query performance.

SAS主要的作用在于对schema的对象对一些改变，它能够建议你去创建访问结构，例如创建索引和物化视图来优化SQL查询，推荐的内容还包括分区表，索引，物化视图来提高查询性能。

The SQL Access Advisor takes a SQL workload as input. You can select your workload from different sources, including current and recent SQL activity, a SQL repository, or a user-defined workload such as from a development environment. The advisor then makes recommendations to improve the performance of the workload as a whole.

SAS可以使用一个SQL 负载作为输入，SQL 负载来自于不同的地方，包括有现在或者最近活动的SQL或者一个SQL库，或者一个来自于用户开发环境中用户预定义的负载。SAS主要从整体上调高性能入手来给出建议。

Statement Tuning and Workload Tuning

Note that both the SQL Tuning Advisor and the SQL Access Advisor provide index creation recommendations. The SQL Tuning Advisor recommends

creation of indexes only when it anticipates exceptional performance gains for the SQL statement being tuned. However, creation of new indexes may

adversely impact the performance of DML insert, update, and delete operations. The SQL Tuning advisor does not take this into account while generating new index recommendations.

需要注意的是STA和SAA都会提供创建索引的建议，STA 推荐创建索引仅仅当它认为这样做SQL语句性能获得很大的提高，然而，创建索引本身会影响中DML操作（插入，更新，删除）的性能，STA在推荐创建索引时不会考虑到这些因素。 The SQL Access Advisor, however, considers the impact of new indexes on the complete workload. As such, if an index improves performance of one SQL statement but adversely impacts the rest of the workload, then the new index is not recommended by the SQL Access Advisor. For this reason, the SQL Tuning Advisor always recommends validating its new index recommendation by running the SQL Access Advisor.

然而，SAA，会考虑到创建一个新的索引对整个负载的影响，对于它来讲，如果一个索引提高了一个语句的性能但是对整个负载产生负面影响，那么这个索引不会在SAA中推荐，基于这个原因，STA常常推荐在SAA中验证它推荐的新索引。

关于自动SQL 调优指导

About the Automatic SQL Tuning Advisor

Beginning with Oracle Database 11g, the SQL Tuning Advisor runs automatically during system maintenance windows (time periods) as a

maintenance task. During each automatic run, the advisor selects high-load SQL queries in the system and generates recommendations on how to tune these queries.

从11g开始，STA作为一个自动任务在系统维护窗口中自动运行，在每一次自动运行期间，该指导选择在系统中负载最高的SQL 查询进行处理并且产生建议以优化查询。

The Automatic SQL Tuning Advisor can be configured to automatically

implement SQL profile recommendations. A SQL profile contains additional SQL statistics that are specific to the SQL statement and enable the query optimizer to generate a significantly better execution plan at run time. If you enable automatic implementation, then the advisor creates SQL profiles for only those SQL statements where the performance increase would be at least threefold. Other types of recommendations, such as the creation of new indexes, refreshing optimizer statistics, or restructuring SQL, can be

implemented only manually. DML statements are not considered for tuning by the Automatic SQL Tuning Advisor.

You can view a summary of the results of automatic SQL tuning over a specified period (such as the previous 7 days), and a detailed report about

recommendations made for all SQL statements that the SQL Tuning Advisor has processed. You can then implement selected recommendations. You can also view the recommendations that were automatically implemented.

You can control when the Automatic SQL Tuning Advisor runs, and you can disable it if desired.

关于STA和SAA的操作使用语法比较复杂，下面用EM来给大家举例。 EM中发现了问题

找出有问题的语句，进行STA

提交任务，立即执行

执行中

--执行结束得到调优的指导意见

--不实施以上意见，对此优化集进行SAA 对发现的语句创建优化集

对此优化集合进行SAA

得到建议方案

---到此结束吧，夜已深。

本文来源：https://www.bwwdw.com/article/5fyo.html