Oracle DB通过SQL优化管理性能

Oracle DB通过SQL优化管理性能
 

• 将SQL 优化指导用于：
– 确定使用资源最多的 SQL 语句
– 优化使用资源最多的 SQL 语句
• 使用SQL 访问指导优化工作量

SQL 优化进程
• 确定没有很好地优化的SQL 语句。
• 优化各条语句。
• 优化整个应用程序。

一般情况下，效果最明显的优化工作是SQL 优化。没有很好地优化的SQL 会不必要地使用过多资源。这种低效率会降低可伸缩性、使用更多的OS 和数据库资源并增加响应时间。要对没有很好地优化的SQL 语句进行优化，必须先确定这些语句，然后再进行优化。可以分别对各条SQL 语句进行优化，但优化一条语句的解决方法经常会影响多条其它语句的性能。
顾名思义，使用资源最多的SQL 语句是需要优化的语句。这些语句的用时最长，使用的CPU 资源最多，或者物理或逻辑读取次数最多。
优化各条语句的方法是：检查优化程序统计信息，检查最高效的访问路径的解释计划，测试替代SQL 构造，以及测试可能的新索引、实体化视图和分区。
使用优化后的SQL 语句测试整个应用程序的性能。整体性能是否有所提高？
方法很可靠，但有些拖沓。优化单条语句并不难。测试各条语句优化对应用程序的整体影响可能非常困难。
在Oracle Database 11 g 中，可以使用一组SQL 指导来确定和优化语句（单独优化或集体优化）。

Oracle Database 11 g 提供了一组SQL 指导：SQL 访问指导、SQL 优化指导、SQL 性能分析器和SQL 修复指导。AWR 可确定最近的高负荷SQL 语句并记录有关的统计信息。
SQL 优化指导可分析一个或多个SQL 语句（一次分析一个）。它会检查统计信息、SQL 概要文件、索引、实体化视图以及调整后的SQL 。SQL 优化指导可随时通过手动方式运行；但主要是在每个维护窗口期间针对最近的高负荷SQL 语句运行。单击“Automatic SQL Tuning Results（自动SQL 优化结果）”可查看和实施建议。可以对此自动作业进行配置，自动为高负荷语句实施建议的SQL 概要文件。
SQL 访问指导会考察应用到一组SQL 语句的更改，确定性能是否有净提高。这组语句可能是一组假想的SQL 语句，以往的语句或手动创建的语句。
SQL 性能分析器可用于预测和防止任何影响SQL 执行计划结构的数据库环境更改所带来的潜在性能问题。
SQL 修复指导是在某个SQL 语句因严重错误而失败时从支持工作台运行的。严重错误还会产生意外事件。修复指导会尝试查找并推荐一个SQL 补丁程序。如果未找到任何补丁程序，可以接着在支持工作台中打包意外事件，并将意外事件程序包作为服务请求(SR) 提交给Oracle  技术支持部门。

默认情况下，自动SQL 优化任务在每天晚上运行。自动SQL 优化结果链接会显示结果概要页。如果单击“View Report（查看报表）”，则可查看接受检查的每条SQL 语句。
单击“Configure（配置）”按钮会显示一个页面；在此页面中，可以更改自动优化任务的默认设置，并启用SQL 概要文件自动实施功能。

如果单击“Automatic Tuning Results Summary （自动优化结果概要）”页上的“View Report （查看报表）”按钮，将显示“Automatic SQL Tuning Result Details （自动SQL 优化结果详细资料）”页。可以实施所有建议，或细化以查看或实施单个建议。

在“Recommendations（建议）”页上，可以单击右侧的眼镜图标查看实施SQL 概要文件会对解释计划产生的影响。

全面SQL优化
检测过时或缺少的统计信息

优化SQL 计划（SQL 概要文件）

添加缺少的索引
调整SQL 结构

SQL 优化指导是优化过程中使用的主要驱动程序。它可以执行多种类型的分析：
• 统计信息分析：检查每个查询对象是否缺少统计信息或统计信息是否过时，然后提出建议以收集相关的统计信息。
• SQL 概要分析：优化程序会验证它自身的估计值并收集辅助信息以消除估计错误。它使用辅助信息构建SQL 概要文件，并提出创建SQL 概要文件的建议。创建SQL 概要
文件后，查询优化程序便可以通过此文件生成合理优化的计划。
• 访问路径分析：考察新索引是否会显著改善对查询中的每个表的访问性能。如果合适，则会建议创建此类对象。
• SQL 结构分析：确定使用了错误计划的 SQL 语句，并提出对这些语句进行结构调整的相关建议。建议的更改可能涉及语法方面的更改，也可能涉及语义方面的更改。
SQL 优化指导会分别考察指导任务中包括的每个SQL 语句。创建一个新索引可能会对查询有帮助，但也可能增加DML 的响应时间。因此，应使用SQL 访问指导检查建议的索引或其它对象对工作量（一组SQL 语句）的影响，以确定性能是否有净提高。

• 使用SQL 优化指导可分析SQL 语句，并获得性能建议。
• SQL 优化指导分析的来源：
– 顶级活动：分析当前处于活动状态的顶级 SQL 语句
– SQL 优化集：分析用户提供的一组 SQL 语句
– 以往的SQL (AWR)：分析 AWR 快照收集的 SQL 语句中的语句

SQL 优化指导会在每晚以自动SQL 优化任务的形式自动运行。有时，可能需要对某个SQL 语句立即进行优化操作。你可以随时使用SQL 优化指导分析SQL 语句，并获得性能建议。通常，运行此指导类似于使用ADDM 查找性能问题的操作。
此外，可以运行SQL 优化指导来分析哪些SQL 语句占用的CPU 时间、I/O  和内存最多。
即使在一项任务中提交了多条语句进行分析，对每条语句的分析仍是分别进行的。要获得考虑了一组SQL 语句的整体性能的优化建议，使用SQL 访问指导。

在“Schedule SQL Tuning Advisor（调度SQL 优化指导）”页上，可以选择要包括的SQL 语句，以及更改优化任务的自动默认设置。可以设置SQL 语句的来源；如果有
ADVISOR系统权限，可以提交任务。随后，Oracle Enterprise Manager 会为SQL 优化指导创建一项优化任务。
通过SQL 语句选项，可以从以下来源中选择一条或多条SQL 语句：最近的顶级活动、AWR 中存储的以往的SQL 语句或者已创建的SQL 优化集。
选择优化任务的适当范围很重要。如果选择“Limited（有限制）”选项，SQL 优化指导会根据统计信息检查结果、访问路径分析结果和SQL 结构分析结果来生成建议。“Limited（有限制）”选项不会产生SQL 概要文件建议。如果选择“Comprehensive（综合）”选项，SQL 优化指导不仅会生成“Limited（有限制）”选项生成的所有建议，在SQL 概要分析模式下还会调用优化程序来构建SQL 概要文件。使用“Comprehensive（综合）”选项时，还可以指定优化任务的时间限制，该时间限制的默认值是30 分钟。选择“Run SQL Tuning Advisor（运行 SQL 优化指导）”之后，请使用“SQL Tuning Options（SQL 优化选项）”页配置优化任务。

任务的SQL 优化结果会在任务完成后即刻显示，也可以在以后通过“Advisor Central（指导中心）”页访问这些优化结果。此时还会显示建议的概要。可以复查和实施单个建议。选择语句并单击“View（查看）”。

可以通过执行以下步骤来调用SQL 优化指导：
1. 在“Database（数据库）”主页中，单击“Related Links（相关链接）”区域中的“Advisor Central（指导中心）”。
2. 单击“SQL Advisors （SQL 指导）”。此时将出现“SQL Tuning Advisor Links（SQL 优化指导链接）”页。
可以对以下任一来源运行该指导：
- Active SQL （活动SQL）：分析当前活动的顶级 SQL 语句
- SQL Tuning Sets（SQL 优化集）：分析用户提供的一组 SQL 语句
- Historical SQL (AWR) （以往的SQL (AWR)）：分析AWR 快照捕获的SQL 语句
3. 选择“Active SQL （活动SQL ）”。选择要分析的五分钟间隔，方法是将灰色框拖到目标时间段上。请选择要在选定期间内分析的一条或多条语句。

4. 单击“Run SQL Tuning Advisor（运行 SQL 优化指导）”。此时会出现“SQL Tuning Options（SQL 优化选项）”页，显示此时间间隔内的SQL 语句。提供任务的名称和
描述，选择“Comprehensive（综合）”作为范围，选择“Immediately（立即）”作为启动时间。单击“OK（确定）”。

5. 重新导航到“Advisor Central（指导中心）”页。指导任务的状态列于“Results （结果）”区域中此标题的下面。请一直等待，直至任务状态变为已完成