《SQL 优化指导和SQL 访问指导的讨论》由会员分享，可在线阅读，更多相关《SQL 优化指导和SQL 访问指导的讨论（7页珍藏版）》请在金锄头文库上搜索。
1、本文格式为Word版，下载可任意编辑SQL 优化指导和SQL 访问指导的讨论 SQL 优化指导和SQL 访问指导的议论 -最近在翻看STA 和 SAA 时，察觉两个问题。 1、第一 互联网上大家对这两个概念的识别对比模糊。 2、SAA的操作特别麻烦，幸好有OEM 以及联机文档中有实例。 下面对这个两个问题举行议论： 以下观点引用自 Arup Nanda - 323-zhs.html SQL Tuning Advisor，它可以供给有关单个查询调整以及在流程中延长整个优化过程的建议 。 SQL Access ADVISOR 调整的是整个负载（对单个语句当然也可用，并且语法及其简朴） Oracle
2、 数据库 10g中SQL Access Advisor 可以分析索引、物化视图等。 Oracle 数据库 11g 中的 SQL Access Advisor 还可以分析表和查询以识别可能的分区策略 这在设计最正确模式时可以供给很大扶助。 SQL Tuning Advisor 提出的建议只对应以下四个目标之一： 1、为统计信息损失或失效的对象收集统计信息 2、考虑优化器的任何数据偏差、繁杂谓词或失效的统计信息 3、 重新构建 SQL 以优化性能 4、 提出新索引建议 联机文档中没有明显找到描述二者差异的论断，下面是联机文档关于两者的介绍 SQL Tuning Advisor You use th
3、e 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 调优一个简
4、朴或繁杂的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
5、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
6、 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 Acce
7、ss 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 work
8、load 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 cre
9、ation 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 i
10、ndex 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 adv
11、ersely 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，会考虑到创造一个新的索引对整个负载的影响，对于它来讲，假设一个索引提高了一个语句的性能但是对整个负载产生负面
12、影响，那么这个索引不会在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 hi
13、gh-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 contai
14、ns 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
15、 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
