Oracle DB SQL 性能分析器
• 确定使用SQL 性能分析器的优点
• 描述SQL 性能分析器工作流阶段
• 使用SQL 性能分析器确定数据库更改所带来的性能改进
- SQL 性能分析器：概览
• 11g 的新增功能
• 目标用户：DBA、QA、应用程序开发人员
• 帮助预测系统更改对SQL 工作量响应时间的影响
• 建立不同版本的SQL 工作量性能（即SQL 执行计划和
执行统计信息）
• 以串行方式执行SQL（不考虑并发性）
• 分析性能差异
• 提供对单个SQL 的细粒度性能分析
• 与SQL 优化指导集成在一起以优化回归
SQL 性能分析器：概览
Oracle Database 11 g 引入了SQL 性能分析器；使用该工具可以准确地评估更改对组成工作
量的SQL 语句的影响。SQL 性能分析器可帮助预测潜在的更改对SQL 查询工作量的性能
影响。这种功能可向DBA 提供有关SQL 语句性能的详细信息，例如，执行前后的统计信
息，提高或降低性能的语句。这样一来，您就可以执行诸如以下操作的操作：在测试环境
中进行更改，以确定数据库升级是否会改进工作量性能。
- SQL 性能分析器：使用情形
在以下情形中使用SQL 性能分析器很有益：
• 数据库升级
• 实施优化建议
• 更改方案
• 收集统计信息
• 更改数据库参数
• 更改操作系统和硬件
SQL 性能分析器：使用情形
SQL 性能分析器可用于预测和防止会影响SQL 执行计划结构的任何数据库环境更改所带
来的潜在性能问题。这些更改可以包括（但不限于）以下任何一种更改：
• 数据库升级
• 实施优化建议
• 更改方案
• 收集统计信息
• 更改数据库参数
• 更改操作系统和硬件
DBA 甚至可以使用SQL 性能分析器为最复杂的环境预测先期更改导致的SQL 性能更
改。例如，随着应用程序在开发周期中的变化，数据库应用程序开发人员可以测试对方
案、
数据库对象和重写应用程序的更改，以减轻任何潜在的性能影响。
使用SQL 性能分析器还可以比较SQL 性能统计信息。
- 使用模型：捕获 SQL 工作量
• SQL 优化集 (STS) 用于存储SQL 工
作量。包括：
– SQL 文本
– 绑定变量
– 执行计划
– 执行统计信息
• 增量捕获用于填充某个时间段内来自
游标高速缓存的 STS 。
• STS 的过滤和排名功能可过滤掉不需
要的SQL。
使用模型：捕获SQL 工作量
使用SQL 性能分析器的第一步是捕获呈现工作量的SQL 语句。这是使用SQL 优化集技
术完成的。
- 使用模型：传输到测试系统
• 将SQL 优化集复制到登台表（“打包”）。
• 将登台表传送到测试系统（数据泵、DB 链接等）。
• 从登台表中复制 SQL 优化集（“解包”）。
使用模型：传输到测试系统
第二步是将这些SQL 语句传送到要测试的类似系统。在此步骤中，可将STS 从生产系统
中导出，然后将其导入到测试系统。
- 使用模型：在更改性能前建立
•
在更改之前，SQL 性能版本是
SQL 工作量性能基线。
• SQL 性能= 执行计划+ 执行统计信息
• 测试/执行STS 中的SQL：
– 生成执行计划和统计信息。
– 以串行方式执行SQL（无并行操作）。
– 每个SQL 只执行一次。
– 跳过DDL/DML 结果。
• 解释STS 中的计划SQL 以便仅生成
使用模型：在更改性能前建立
第三步是捕获由执行计划和执行统计信息组成的测试系统性能的基线。
- 使用模型：在更改性能后建立
• 手动实施计划的更改：
– 数据库升级
– 实施优化建议
– 更改方案
– 收集统计信息
– 更改数据库参数
– 更改操作系统和硬件
• 在更改后重新执行 SQL：
– 测试/执行STS 中的SQL 以生成
SQL 执行计划和执行统计信息。
– 解释STS 中的计划SQL 以便生成SQL 计划。
使用模型：在更改性能后建立
第四步是更改测试系统，然后重新运行SQL 语句来评估更改对SQL 性能的影响。
- 使用模型：比较和分析性能
• 依据用户指定的度量比较 SQL 性能：
– elapsed_time, buffer_gets ,
disk_reads, ...
• 计算更改对单个 SQL 和SQL 工作量
的影响：
– 对工作量的整体影响
– 对工作量的纯SQL 影响
• 使用SQL 执行频率定义重要性权重。
• 检测改进、回归和未发生更改的性能。
• 检测执行计划中的更改。
• 建议运行SQL 优化指导来优化回归
SQL。
• 分析结果可用于植入 SQL 计划管理基
线。
使用模型：比较和分析性能
Enterprise Manager 提供了一些工具，可用于对性能数据进行完全比较，其中包括所用时
间、CPU 时间和缓冲区获取次数之类的执行统计信息。如果SQL 性能在有些情况下出现
回归，则DBA 必须立即或按预定时间运行SQL 优化指导来优化SQL 语句。对于任何优
化策略，建议一次仅实施一项更改，并在进一步更改之前重新进行测试。
- SQL 性能分析器：概要
1. 捕获SQL 生产工作量。
2. 将SQL 工作量传送至测试系统。
3. 建立“更改前”性能数据。
4. 进行更改。
5. 建立“更改后”性能数据。
6. 比较步骤3 和步骤5 的结果。
7. 优化回归的SQL。
SQL 性能分析器：概要
1. 收集SQL：在这个阶段中，将收集用于表示生产系统中的 SQL 工作量的SQL 语句
集。可以使用SQL 优化集或自动工作量资料档案库(AWR) 来捕获要传送的信息。因
为AWR 本质上是捕获高负载的SQL ，所以应考虑修改默认的AWR 快照设置和捕获
的顶级SQL ，以确保AWR 捕获最大数量的SQL 语句。这可以确保捕获更加完整的
SQL 工作量。
2. 传送：在这个阶段中，应将得到的工作量结果传送到测试系统。从生产系统导出
STS ，然后将STS 导入到测试系统。
3. 计算“之前版本”性能：在进行任何更改之前，执行 SQL 语句，收集评估将来的更
改对工作量性能的可能影响所需的基线信息。在此阶段收集的信息给出了系统工作量
当前状态的一个快照。性能数据包括：
- 执行计划（如由解释计划生成的计划）
- 执行统计信息（如由占用时间、缓冲获取次数、磁盘读取次数和已处理的行数组
成的信息）
4. 进行更改：获得了之前版本数据后，可以实施计划的更改，然后开始查看对性能的影
响。
5. 计算“之后版本”性能：在数据库环境中进行了更改之后才执行此步骤。SQL 工作量
的每个语句都在虚拟执行（仅收集统计信息）模式下运行，收集与步骤3 所捕获的信
息相同的信息。
6. 比较和分析SQL 性能：在获得了两个版本的 SQL 工作量性能数据后，可以通过比较
之后版本与之前版本的数据来进行性能分析。比较的根据是执行统计信息，如所用时
间、CPU 时间和缓冲区获取次数等。
7. 优化回归的SQL：在此阶段中，已经准确地确认了哪些 SQL 语句在进行数据库更改
时可能导致性能问题。在此阶段中可以使用任何一种数据库工具来优化系统。例如，
可以对确认的语句使用SQL 优化指导或访问指导，然后实施相应的建议。也可以使
用在步骤3 中捕获的计划植入SQL 计划管理(SPM) 以确保计划保持不变。在实施了
任何优化操作后，应重复该过程来创建新的之后版本，然后分析性能差异以确保新的
性能是可接受的。
- 捕获SQL 工作量
1. 在原始系统上创建SQL 优化集(STS) 。
2. 创建登台表，并在登台表中上载STS 。
3. 将登台表导出到测试系统。
4. 将登台表解包到测试系统上的STS 。
捕获SQL 工作量
捕获SQL 工作量是使用SQL 优化集(STS) 完成的。这个概念并不是Oracle Database 11 g
中新引进的，它与以前版本的数据库遵循完全相同的工作流。图中对此概念进行了简
短的说明。可以使用Enterprise Manager 向导或DBMS_SQLTUNE PL/SQL 程序包。
使用Oracle Database 11 g 时，可以通过Database Control 中的“Performance（性能）”
选项卡访问“SQL Tuning Sets （SQL 优化集）”页。
捕获的工作量应反映一个有代表性的时段（在捕获的SQL 语句中），您希望在一些发生
变化的条件下对其进行测试。此过程中将捕获以下信息：
•SQL 文本
• 执行上下文（包括绑定值、语法分析方案和编译环境），其中包含一组执行语句时所
用的初始化参数。
• 执行频率，即在工作量的时间间隔期间SQL 语句执行的次数。
正常情况下，将捕获生产系统中的SQL ，从而捕获其中运行的工作量。稍后，计算SQL
性能的进程会在测试系统中计算性能数据。SQL 性能分析器将在数据库更改前后跟踪同
一STS 的SQL 性能。
- 创建SQL 性能分析器任务
创建SQL 性能分析器任务
EM 可帮助管理SQL 性能分析器进程中的每个组件，并报告分析结果。工作流和用户界
面适用于EM Database Control 和EM Grid Control 。
可以通过Database Control 的“Software and Support （软件和支持）”选项卡访问SQL 性
能分析器。或者选择“Database Instance（数据库实例）> Advisor Central（指导中心）>
Advisors（指导）> SQL Performa nce Analyzer（SQL 性能分析器）”。
SQL 性能分析器提供了三种工作流，可用于测试不同情况：
• 优化程序升级模拟：测试指定优化程序版本的更改对 SQL 优化集性能产生的影响。
将创建SQL 性能分析器任务，并使用设置为初始值的
optimizer_features_enable参数执行初次试运行。使用设置为目标版本的
optimizer_features_enable参数执行第二次试运行。然后，运行这两次试运
行的重放试验比较报告。
• 参数更改：测试并比较初始化参数的更改对SQL 优化集性能的影响。将创建SQL 性
能分析器任务，并使用设置为基础值的参数执行初次试运行。使用设置为更改值的参
数执行第二次试运行。然后，运行这两次试运行的重放试验比较报告。
• 指导式工作流：将创建一个 SQL 性能分析器任务，并通过使用手动创建的重放试验
执行自定义试验。
- 优化程序升级模拟
优化程序升级模拟
使用此页可创建一个任务，以度量数据库从一个版本升级到另一个版本时对SQL 优化集
的性能影响。在示例中，执行了一次从10.2.0.1 到11.1.6 的模拟升级（可以降
级到8.0.0）。
要创建分析任务，必须指定以下详细信息：
• 输入任务名称和可选的说明。
• 指定要用于此次分析的STS 。STS 必须已创建。
• 从列表中选择“Per-SQL Time Limit（每个SQL 的时间限制）”，指定执行每个SQL
语句的时间限制：
- UNLIMITED ：对于每个 SQL 语句的执行无时间限制。
- EXPLAIN ONLY：生成但不执行测试计划。
- CUSTOMIZE：可以定制执行时间限制。
• 选择“Optimizer Versions （优化程序版本）”指明数据库的原始版本，以及数据库要
升级到的新版本。将创建两个重放试验。第一个重放试验使用原始优化程序版本捕获
STS 性能，第二个重放试验使用目标版本。
• 选择用于评估数据库升级带来的性能影响的“Comparison Metric （比较度量）”。
• 指定任务的“Schedule （调度）”。
• 单击“Submit （提交）”继续分析。
- SQL 性能分析器：任务
SQL 性能分析器：任务
创建了SQL 性能分析器任务后，可能需要长时间执行该任务，具体取决于SQL 优化集中
包含的语句数量。在执行任务时，可以单击“SQL Performance Analyzer（SQL 性能分析
器）”页上的“Refresh（刷新）”按钮，直到在“SQL Performance Analyzer Tasks（SQL
性能分析器任务）”表中任务的“Last Run Status（上次运行状态）”列中看到一个绿色
的对勾。
执行之后，可以单击“SQL Performance Analyzer Tasks （SQL 性能分析器任务）”表中与
任务名称对应的链接。这时会转到相应的“SQL Performance Analyzer Task（SQL 性能分
析器任务）”页。
- SQL 性能分析器任务页
SQL 性能分析器任务页
通过“SQL Performance Analyzer Task（SQL 性能分析器任务）”，可以在已更改的环境
条件下执行特定的SQL 优化集。在执行了该任务后，可以评估这些更改对SQL 优化集性
能的影响。在评估更改的环境条件对指定SQL 优化集性能的影响时，“Comparison
Report （比较报表）”很有用。
在此页上，还可以执行以下操作：
• 创建重放试验来测试特定环境下SQL 优化集的性能。单击“Create Replay Trial
（创建重放试验）”。有关创建重放试验的详细信息，请参阅“Guided Workflow
（指导式工作流）”页。
• 运行重放试验比较来比较迄今为止所创建的重放试验间的差异。系统将为每个重放试
运行创建一个比较报表。
单击“Run a Replay Trial Comparison （运行重放试验比较）”。有关运行重放试验比
较的详细信息，请参阅“Guided Workflow（指导式工作流）”页。
• 单击“Comparison Report （比较报表）”列中的眼镜图标，查看任务的重放试验比较
报表。
- 比较报表
使用“SQL Performance Analyzer Task Result（SQL 性能分析器任务结果）”页查看重放
试验比较报表。该页将显示以下常规详细资料：
• 任务详细资料，如任务的名称、所有者和说明
•SQL 优化集的名称和所有者
•SQL 语句的总数以及任何出现错误的SQL 语句。单击“SQL Statements With Errors
（出现错误的SQL 语句）”链接可访问“Errors（错误）”表。
• 进行比较的重放试验以及使用的比较度量。
除了以上详细资料外，还可以查看以下内容：
• Projected Workload [Comparison Metric]（计划的工作量[比较度量]）：此图表根据
比较度量显示每个重放试验的计划工作量，以及改进、回归和整体影响。单击各个影
响的链接可以细化到每个类别的SQL 语句的完整列表。
• SQL Statement Count（SQL 语句计数）：此图表根据比较度量显示改进、回归或未
改变性能的SQL 语句的数量。柱形的颜色表明在两次试运行之间计划是否有更改。
单击链接或数据存储桶可访问SQL 语句计数详细资料页（从中可以看到一个SQL 语
句列表），然后单击“SQL ID”便可访问SQL 详细资料。
• “Top 10 SQL Statements Based on Impact on Workload（根据对工作量的影响列出
的前10 个SQL 语句）”表：在此表中单击特定的“SQL ID”可细化至对应的“SQL
Details （SQL 详细资料）”页。
在“SQL Details （SQL 详细资料）”页上，可以查看SQL 语句以及针对每条统计信息的
每个重放试运行之间的逐行比较。还可以找到每个试验的解释计划。
- 优化回归语句
在“SQL Performance Analyzer Task Result （SQL 性能分析器任务结果）”页中，可以通
过调用SQL 优化指导直接优化所有回归语句。为此，请单击“Run SQL Tuning Advisor
（运行SQL 优化指导）”按钮访问“Schedule SQL Tuning Task（调度SQL 优化任务）”
页，从中可以指定优化任务名称和一个调度。
完成后，单击“Next （下一步）”。此时将创建一个新的优化任务，可分析SQL 性能分
析器找到的所有回归语句。
- 优化回归语句
创建了SQL 优化任务后，将返回到“SQL Performance Analyzer Task Result （SQL 性能分
析器任务结果）”页，在此页中的“Recommendations（建议案）”部分可以清楚地看
到，现在有了一个与性能分析关联的优化任务。
单击“SQL Tune Report（SQL 优化报表）”链接可访问相应的“SQL Tuning Results
（SQL 优化结果）”页，从中可以看到“Recommendations（建议案）”表，其中列出了
回归语句的所有建议案。
也可以从“SQL Performance Analyzer Task（SQL 性能分析器任务）”页直接访问“SQL
Tuning Results（SQL 优化结果）”页，方法是：针对您的试验比较单击“Replay Trial
Comparisons（重放试验比较）”部分“SQL Tune Report（SQL 优化报表）”列中的眼镜
图标。
- 防止回归
可以不使用SQL 优化指导来优化回归语句，而是使用SQL 计划基线来防止回归。为此，
请在“SQL Performance Analyzer Task Result（SQL 性能分析器任务结果）”页中单击
“Create SQL Plan Baselines （创建SQL 计划基线）”按钮。
- 参数更改分析
使用“Parameter Change（参数更改）”页可创建一个任务，可使用该任务来测试初始化
参数值发生更改时对SQL 优化集性能的影响。此选项很有用，因为很难预测参数值的更
改会带来正面的影响还是负面的影响。
要创建任务，请执行以下操作：
• 输入任务名称和说明。
• 单击“Select（选择）”图标，然后从列表中选择一个SQL 优化集。
• 从列表中选择“Per-SQL Time Limit（每个SQL 的时间限制）”，指定执行每个SQL
语句的时间限制，
• 单击“Select（选择）”图标，从列表中选择一个初始化参数。
• 选择初始化参数的当前值（基础值）和新值（更改值）。
• 选择用于评估更改带来的性能影响的比较度量。
• 指定任务的调度。
创建了任务后，使用设置为基础值的初始化参数执行初次试运行。使用设置为更改值的初
始化参数执行第二次试运行。最后，使用指定的比较度量生成两次试验的重放试验比较报
表。
- 指导式工作流分析
可以使用“Guided Workflow（指导式工作流）”页定义执行两次试验的SQL 性能分析器
测试的步骤顺序。这些步骤如下：
• 根据SQL 优化集创建一个SQL 性能分析器任务。
• 在初始环境中重放SQL 优化集。在执行重放试验之前，必须手动更改影响STS 的试
验环境。这些试验可以包括更改初始化参数、收集优化程序统计信息以及创建索引。
• 使用更改的环境创建重放试验：现在，通过指定所有必需的信息，可以使用更改的环
境创建第二次重放试验。两次试验之间的性能差异归因于环境的差异。
• 使用前述各步骤中的试验创建重放试验比较：这允许您评估执行每个重放试验时对
STS 的性能影响。
• 查看试验比较报表：现在，可以生成重放试验比较报表了。
注：在提交重放试验之前，必须在相应的任务页上选中“Trial environment established
（试验环境已建立）”选项。但是，必须手动进行必要的更改。
- SQL 性能分析器：PL/SQL 示例
SQL 性能分析器：PL/SQL 示例
图中的常规示例显示了如何使用DBMS_SQLPA程序包调用SQL 性能分析器，以了解
某些更改所带来的SQL 性能影响。可以方便地改写此示例来运行自己的分析。
1. 创建优化任务以运行SQL 性能分析器。
2. 运行一次任务以建立更改前性能数据，并生成更改前报表（报表的特殊设置为：set
long 100000 、longchunksize 100000和linesize 90 ）。通过此项调用，
可以指定各种参数，其中包括：
- 通过以下任一方式设置execution_type 参数：设置为EXPLAIN PLAN，为
SQL 工作量中的所有SQL 语句生成解释计划。设置为TEST EXECUTE，执行
SQL 工作量中的所有SQL 语句。该过程仅执行DML 语句中的查询部分，以避免
对数据库或用户数据产生副作用。如果指定了TEST EXECUTE，则该过程将生
成执行计划和执行统计信息。
- 通过使用execution_params 参数指定需要指定为
dbms_advisor.arglist(name,value,…) 的执行参数。time_limit参数
指定了在超时前处理SQL 优化集中所有SQL 语句的全局时间限制。
local_time_limit 参数指定了在超时前处理SQL 优化集中每个SQL 语句的
时间限制。
3. 进行更改。
4. 更改后重新执行任务，并获取更改后报表。
5. 比较两次执行，并获取分析报表。可以使用不同的执行参数执行以下命令：
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => :tname, -execution_type => 'compare performance', execution_params =>
dbms_advisor.arglist( 'execution_name1', 'before',
'execution_name2', 'after', 'comparison_metric', 'buffer_gets'));
- SQL 性能分析器：数据字典视图
• Oracle Database 11g 中修改的视图：
– DBA{USER}_ADVISOR_TASKS：显示有关分析任务的详细资料
– DBA{USER}_ADVISOR_FINDINGS：显示分析结果
• Oracle Database 11g 中的新视图：
– DBA{USER}_ADVISOR_EXECUTIONS：列出任务执行的元数据
信息
– DBA{USER}_ADVISOR_SQLPLANS：显示 SQL 执行计划列表
– DBA{USER}_ADVISOR_SQLSTATS：显示 SQL 编译和执行统
计信息的列表
SQL 性能分析器：数据字典视图
• DBA{USER}_ADVISOR_SQLPLANS ：显示所有（或当前用户拥有的）SQL 执行计划
列表。
• DBA{USER}_ADVISOR_SQLSTATS ：显示SQL 编译或执行统计信息列表，或当前用
户拥有的SQL 编译或执行统计信息列表。
• DBA{USER}_ADVISOR_TASKS ：显示有关特定指导任务的详细信息，这些任务是为
分析系统环境更改所带来的影响而创建的。
• DBA{USER}_ADVISOR_EXECUTIONS ：列出任务执行的元数据信息。SQL 性能分析
器至少创建三次执行，分析更改对SQL 工作量的影响：一次执行用于收集工作量更
改前版本的性能数据，第二次执行用于收集工作量更改后版本的数据，最后一次执行
用于进行实际分析。
• DBA{USER}_ADVISOR_FINDINGS ：显示分析结果。指导将生成四种类型的查找结
果：性能回归、症状、错误和参考性消息。
- 小结
• 确定使用SQL 性能分析器的优点
• 描述SQL 性能分析器工作流阶段
• 使用SQL 性能分析器确定数据库更改所带来的性能改进
