Oracle DB 通过SQL 优化管理性能_要运行sql优化指导,用户 infodba需要系统权限advisor-CSDN博客

本文链接：https://blog.csdn.net/rlhua/article/details/13021985

• 将SQL 优化指导用于：

– 确定使用资源最多的 SQL 语句

– 优化使用资源最多的 SQL 语句

• 使用SQL 访问指导优化工作量

SQL 优化

SQL 优化进程

• 确定没有很好地优化的SQL 语句。

• 优化各条语句。

• 优化整个应用程序。

一般情况下，效果最明显的优化工作是SQL 优化。没有很好地优化的SQL 会不必要地使用过多资源。这种低效率会降低可伸缩性、使用更多的OS 和数据库资源并增加响应时间。要对没有很好地优化的SQL 语句进行优化，必须先确定这些语句，然后再进行优化。可以分别对各条SQL 语句进行优化，但优化一条语句的解决方法经常会影响多条其它语句的性能。

顾名思义，使用资源最多的SQL 语句是需要优化的语句。这些语句的用时最长，使用的 CPU 资源最多，或者物理或逻辑读取次数最多。

优化各条语句的方法是：检查优化程序统计信息，检查最高效的访问路径的解释计划，测试替代SQL 构造，以及测试可能的新索引、实体化视图和分区。

使用优化后的SQL 语句测试整个应用程序的性能。整体性能是否有所提高？

方法很可靠，但有些拖沓。优化单条语句并不难。测试各条语句优化对应用程序的整体影响可能非常困难。

在Oracle Database 11 g 中，可以使用一组SQL 指导来确定和优化语句（单独优化或集体优化）。

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 优化任务在每天晚上运行。自动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 优化指导会分别考察指导任务中包括的每个SQL 语句。创建一个新索引可能会对查询有帮助，但也可能增加DML 的响应时间。因此，应使用SQL 访问指导检查建议的索引或其它对象对工作量（一组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 访问指导。

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 优化指导建议

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

使用SQL 优化指导：示例

可以通过执行以下步骤来调用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 （结果）”区域中此标题的下面。请一直等待，直至任务状态变为已完成。通过单击浏览

器中的“Refresh（刷新）”检查状态。选择任务并单击“View Result （查看结果）”。此时会显示“SQL Tuning Result （SQL 优化结果）”页。

6. 选择SQL 语句并单击“View Recommendations （查看建议）”。

重复的SQL

重复的SQL 语句是指只是所用的文字值或格式有所不同的语句。每个不同的语句在库高速缓存中都会有一个单独的游标。如果将文字替换为绑定变量，并统一格式，则重复的语句可以使用相同的游标。

通过在“Additional Monitoring Links（其他监视链接）”区域中单击“Performance（性能）”选项卡页上的“Duplicate SQL（重复的SQL ）”，可以确定重复的SQL 语句。已确定为重复的SQL （不考虑格式或文字差异）会列在一起。这将有助于确定应用程序中的哪个SQL 可以合并，从而降低对库高速缓存的需求，提高语句的执行速度。

SQL 访问指导：概览

评估 SQL 的全部工作量, 并建议索引, 分区和实体化视图, 它们将改善 SQL 工作量的总体性能。

SQL 访问指导能够为给定工作量建议一组适当的实体化视图、实体化视图日志、分区和索引。了解和使用这些结构在优化SQL 时至关重要，因为这些结构在数据检索时能够极大地改善性能。

SQL 访问指导建议使用位图索引、基于函数的索引和B 树索引。位图索引可减少许多类型的即席查询的响应时间，并且相对于其它索引技术而言降低了存储要求。B 树索引在数据仓库中最为常用，用于为唯一关键字或接近唯一的关键字编制索引。

SQL 访问指导的另一个组件还提供了优化实体化视图的建议，使这些视图可以快速刷新并利用常规查询重写。

典型的SQL 访问指导会话

启动SQL 访问指导会话时，可以选择“Use Default Options（使用默认选项）”，以一组建议的预定义指导选项开始。此外，还可以选择“Inherit Options from a Task or Template （从任务或模板继承选项）”来启动任务，并使该任务继承一组由模板或任务定义的选项值。这些模板包括多个针对通用环境、OLTP 和数据仓库数据库设计的通用模板。你可以保存先前任务中的定制模板并在需要时重新使用。

单击“Continue （继续）”启动SQL 访问指导向导。

工作量来源

使用SQL 访问指导向导的“Workload Source（工作量源）”页提供已定义的工作量，以便访问指导可以生成建议。支持的工作量源包括：

• Current and Recent SQL Activity（当前和最近的SQL 活动）：使用高速缓存中的当前SQL 作为工作量

• Use an existing SQL Tuning Set（使用现有的SQL 优化集）：可以将先前创建的 SQL 优化集指定为工作量源

• Create a Hypothetical Workload from the Following Schemas and Tables （从下列方案和表创建假想工作量）：提供方案以便访问指导可以搜索维表并生成工作量

通过应用“Filter Options （过滤器选项）”部分中的可用过滤器，可以进一步缩小工作量的范围。使用这些选项，可以缩小出现在工作量中的SQL 语句的范围。指导将过滤器应用于工作量后，可以对重点部分进行优化。

可用的过滤器选项包括：

• 消耗资源最多的SQL 语句

• 用户、模块标识符或操作

• 表

建议选项

使用“Recommendations Options （建议选项）”页可以选择是否限制指导基于单个访问方法提出建议。从“Access Structures to Recommend（要推荐的访问结构）”部分选择 “Indexes（索引）”、“Materialized Views （实体化视图）”、“Partitioning （分区）” 或者上述各项的任意组合。可以选择“Evaluation Only（仅评估）”，从而只评估现有的访问结构。在此模式下，指导不生成新的建议，而是就现有结构的使用提出意见。此项在跟踪当前索引、实体化视图的有效性和MV 日志使用率随时间变化方面非常有用。

可以使用“Advisor Mode （指导模式）”部分，以两种模式之一运行指导。这些模式会影响建议的质量和处理所需的时间。在“Comprehensive（综合）”模式中，指导将搜索候选的大型池，以便得到最高质量的建议。在“Limited（有限制）”模式中，指导执行速度很快，但是会限制候选建议。

可以选择“Advanced Options（高级选项）”来显示或隐藏选项，这些选项可用于设置空间限制、优化选项和默认存储位置。使用“Workload Categorization（工作量类别）” 部分可以设置“Workload Volatility（工作量不稳定性）”和“Workload Scope（工作量范围）”选项。生成建议时，可以选择关注只读操作，或者考察被引用对象的不稳定性。你还可以选择“Partial Workload （部分工作量）”，其中不包括删除未使用的访问结构的建议；或者选择“Complete Workload（全部工作量）”，其中包括删除未使用的访问结构的建议。

使用“Space Restrictions（空间限制）”部分可指定硬性空间限制，强制指导仅使用不超过指定限制的总空间要求生成建议。

使用“Tuning Options（优化选项）”部分可指定相关选项对指导生成的建议进行定制。

使用“Prioritize Tuning of SQL Statements by（确定优化SQL 语句优先级的依据）”下拉列表，可以按“Optimizer Cost（优化程序开销）”、“Buffer Gets （缓冲区获取数）”、 “CPU Time（CPU 时间）”、“Disk Reads（磁盘读取数）”、“Elapsed Time（用时）” 和“Execution Count （执行计数）”划分优先级。

使用“Default Storage Locations（默认存储位置）”部分可以覆盖为方案和表空间位置定义的默认值。默认情况下，索引放置在所引用表的方案和表空间中。实体化视图放置在相应用户的方案和表空间中，该用户执行的某一个查询为实体化视图建议提供了信息。

定义了这些参数以后，可以调度和复查优化任务。

复查建议

使用“Advisor Central（指导中心）”页可以列出所有已完成的SQL 访问指导任务。选择要查看建议的任务，然后单击“View Result （查看结果）”按钮。使用“Results for Task （任务结果）”的“Summary （概要）”页，可以访问指导查找结果的概览。该页中给出了图表和统计信息，提供了建议可能对整体工作量性能和查询执行时间产生的改进。使用该页可以显示语句计数和建议操作计数。

要查看指导任务结果的其它方面，可单击该页上其它三个选项卡之一：“Recommendations （建议）”、“SQL Statements（SQL 语句）”或“Details （详细资料）”。

“Recommendations（建议）”页中提供了一个图表和一个表，其中按建议对整个工作量总开销的改进百分比显示顶级建议。顶级建议对总体性能的改进幅度最大。

单击“Show SQL（显示SQL ）”按钮，可以查看为所选建议生成的SQL 脚本。在表中单击相应的建议标识符，可以查看实施建议所需执行的操作列表。在“Actions（操作）”

页上，可以查看实施操作需要执行的所有相应SQL 语句。对于不希望实施的建议，请使对应的复选框处于未选中状态。然后，单击“Schedule Implementation（安排实施）”按钮来实施保留的操作。此步骤以调度程序作业的形式执行。

SQL 性能分析程序：概览

• 目标用户：DBA、QA、应用程序开发人员

• 帮助预测系统更改对SQL 工作量响应时间的影响

• 构建不同版本的SQL 工作量性能数据（即SQL 执行计划和执行统计信息）

• 以串行方式执行SQL（不考虑并发）

• 分析性能差异

• 针对单个SQL 提供细粒度性能分析

• 与SQL 优化指导相集成以优化回归

Oracle Database 11 g 包括SQL 性能分析程序，该工具可以准确地评估更改对构成工作量的 SQL 语句的影响。SQL 性能分析程序有助于预测潜在的更改对SQL 查询工作量的性能影响。此功能可为DBA 提供有关SQL 语句性能的详细信息，如执行前后的统计信息，以及性能有所提高或降低的语句。这样您便可以（例如）在测试环境中进行更改，以确定数据库的升级是否会改进工作量性能。