Oracle 数据库 11g新特性：SQL Performance Analyzer

学习如何准确预测针对数据库发出的实际 SQL 语句的变化。

数据库重放是一款很棒的工具，可用于捕获数据库中的实际负载并随意重放。它是 Oracle 真正应用测试选件的一部分，它的重点是“实际”一词，即重放的负载就是数据库中实际发生的负载。

在数据库重放中，捕获的全部负载都针对数据库进行重放。但是，如果您不想那样做那该怎么办？例如，您可能很想了解 SQL 执行计划以及 SQL 性能是如何受更改影响的，因为它们会严重影响应用程序的性能和可用性。此外，数据库重放只重放捕获的负载。在生产版本中执行之前，您可能很想知道参数更改对某些 SQL 的影响。

这一领域正是真正应用测试系列的另一个重要组件 SQL Performance Analyzer (SPA) 的用武之地。通过 SPA，您可以根据各种更改类型（如初始化参数更改、优化程序统计刷新和数据库升级）播放特定的 SQL 或整个的 SQL 负载，然后生成比较报告，帮助您评估它们的影响。在本文中，您将学习如何使用 SQL Performance Analyzer 工具解决这个重要的问题。

示例问题

我们来进行一个测试。首先，我们定义要解决的问题。

这是一个典型的问题：Oracle 不使用索引，而您想了解其中的原因。为了回答这个问题，我拜读了 Oracle 精英 Tim Gorman 的经典论文“Searching for Intelligent Life in Oracle's CBO”。（在 Web 上随处都能找到这篇论文多种形式的各个版本。）

Tim 的一个建议是，将 optimizer_index_cost_adj 参数的值由默认的 100 更改为一个较小的值。这篇论文还给出了计算这个值的公式。根据公式，在我的案例中，我计算得出这个值为 10。但是，这带来一个比较棘手的问题：这样的更改会对每条 SQL 语句都有裨益吗？

在 Oracle 数据库 11g 之前的版本中，我需要捕获所有 SQL 语句，通过跟踪运行这些语句，然后得到执行计划 — 这是一项极其耗时又极易出错的任务。有了新版本之后，我不需要再那样做了，我改用非常简单而有效的 SQL Performance Analyzer。

首先，为了举例说明，我们在数据库中运行以下查询：

select /* CONTROL_QUERY11 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 33;
select /* CONTROL_QUERY12 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 350;
select /* CONTROL_QUERY13 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 351;
select /* CONTROL_QUERY14 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 534;
select /* CONTROL_QUERY15 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 999;

select /* CONTROL_QUERY21 */ sum(QUANTITY_SOLD) from sales where channel_id = 2;
select /* CONTROL_QUERY22 */ sum(QUANTITY_SOLD) from sales where channel_id = 3;
select /* CONTROL_QUERY23 */ sum(QUANTITY_SOLD) from sales where channel_id = 4;
select /* CONTROL_QUERY24 */ sum(QUANTITY_SOLD) from sales where channel_id = 5;
select /* CONTROL_QUERY25 */ sum(QUANTITY_SOLD) from sales where channel_id = 9;

假设这些查询由您的应用程序发出。通过调整 SQL 中的注释，您可以在以后对它们进行搜索。语句执行完成后，您可以对其使用 SPA。

用法

通常，最好通过 Oracle 企业管理器使用 SPA 功能。（当然，您也可以通过系统提供的程序包 dbms_sqlpa 使用命令行选项，但是不能超过企业管理器添加的可管理性值。）

执行以下步骤。

1. 打开 Enterprise Manager Database Control 并单击 Performance 选项卡。然后滚动到页面底部，您将看到如下超链接。
   

2. 单击 Search Sessions，出现如下屏幕：
   

3. 从游标缓存中搜索已经执行的 SQL 中的模式。注意，这些 SQL 都有一个注释 CONTROL_QUERYn，其中 n 为 11、12 等等。输入该字符串作为搜索函数。将给出先前执行过的所有 SQL。在上面显示的屏幕中，您将看到标题为 Save to a new SQL Tuning Set 的单选按钮。选择该按钮，然后输入一个名为 CONTROL1 的 SQL 调整工具集。（注：为了举例说明，您要选择一些语句。）无需在 SQL 中加入任何注释，您只要创建一个“SQL 调整工具集”并填入所有相关语句即可。
   
   
4. 单击 SQL Tuning Sets，将出现 SQL Tuning Sets 页面。现在选择名为 CONTROL1 的 STS。在此页面中，您可以检查该 STS 并为其添加和删除 SQL。下面是该 STS 页面的屏幕截图：
   

5. 现在，从 Performance 页面中单击 SQL Performance Analyzer。出现如下所示的 SPA 主页面。
   

6. 如您所见，还没有定义任何 SPA 任务。现在定义一个 SPA 任务。在本例中，您将对参数 optimizer_index_cost_adj 的更改所带来的影响进行评估，因此请单击 Parameter Change。出现如下所示的 SPA Task Definition 页面。
   

7. 在此页面中，您需要输入 SPA 任务的必要信息。为任务命名：例如 STS1。
   
   
8. 接下来，您需要输入 SQL 调整工具集的名称。单击旁边的手电筒图标，然后选择名为 CONTROL1 的 SIS。
   
   
9. 在 Parameter Change 部分中，输入要更改的参数。单击手电筒图标，然后选择参数 optimizer_index_cost_adj。当前值已经填入，在 Changed Value 框中输入目标值。
   
   
10. 接下来，决定这些更改的比较方式：按所用时间、按 CPU 时间等等。为了举例说明，选择 Buffer Gets。
    
    
11. 最后，安排该 SPA 任务的执行时间。选择单选按钮 Immediate，表明您要立即运行这个任务。
    
12. 所有详细信息都填好后，单击 Submit。这样就创建了一个您能够独立监控的作业，但您也可以停留在这个页面中并监控该 SPA 任务的状态，如下所示。
    

13. 单击 Refresh 查看该任务当前的状态。注意 Last Run Status 栏下的图标。下面是对这些图标的说明：
    

14. 图中的图标表示该任务现在正在运行。不断单击 Refresh。当图标变为 Completed，显示为一个核对符号时，该 SPA 任务就完成了。
    
    
15. 单击该 SPA 任务的名称 (STS1)，查看比较标准。出现如下所示的屏幕。
    

16. 注意 Comparison Report 栏下的眼镜图标。在这里，您可以比较参数更改前后执行 SQL 的结果。单击该图标。
    
    
17. 这是最令人兴奋的部分。出现如下所示的屏幕：
    

这就是您一直等待的结果。在左上角，您会看到更改前后两次运行 SQL 集的结果比较。总体而言，性能提升了 60%！这个结果就显示在这，非常明确。在屏幕的右手边，您会看到有多少 SQL 语句改变了执行计划。似乎大部分都改变了计划，只有少数没有。

屏幕底端显示了该任务分析的 SQL 语句的 SQL ID。SQL ID 前面的小箭头显示了这些 SQL 语句是改进了还是退化了，SQL ID 后面的数字显示了改进或退化的百分比。这些数据告诉您更改对每条 SQL 语句的确切影响。如果您愿意，可以通过单击 SQL ID 查看相应的 SQL。注意第一条 SQL，它受到的影响最大，如果单击该 SQL，您会看到与下面类似的屏幕：

这个屏幕显示了有关执行该 SQL 的大量统计信息。 屏幕底部显示了执行计划的比较：

现在您可以看到，使用索引是如何强制减少缓冲区的。但是，情况总是那么乐观吗？看看另一条 SQL：

与上一例的 31.95% 相比，此例改进甚微，只有 0.48%。原因是什么？为了找到答案，单击 SQL ID，出现如下屏幕：

在这里，您可以看到究竟是什么改变了。所用时间实际上从 0.504 秒延长为 1.022 秒，而且都是因为 CPU 时间。为什么？如果您检查一下数据分布模式，您就会看到 promo_id 是这样分布的：

SQL> select promo_id, count(1) cnt from sales group by promo_id;
 
   PROMO_ID        CNT
---------- ----------
       534          1
       999     887837
       350      18022
        33       2074
       351      10910
           ----------
sum            918844

promo_id 999 在表中出现了 887,837 次，将近 97%。当将计划改为包含索引扫描时，这个查询就比较困难了。如果对全表进行扫描，情况应该会好一些。因此，即使整体影响是积极的，也会有个别组件拖后腿。当您决定是否要更改参数时，您应该考虑到这些 SQL 语句的重要性，这些语句既可能改进也可能退化。

正如您所见，您希望评估对数据库参数进行重要更改而带来的影响。使用 SPA，您不必估计潜在的性能影响，连“猜测估计”也不必。您可以使用应用程序针对数据库执行的 SQL 语句客观地衡量。

现在看另一个案例：更改参数后，性能退化了，而不是改进了。下面是一个屏幕截图：

这里，SQL 语句的运行情况都比更改之前要差。您可以利用（中讨论的）SQL 计划管理解决这个问题。SPM 允许您选择优良的执行计划作为您的基准，从而保证执行计划的稳定性。随后，优化程序会将这个基准用于相应 SQL 的所有执行过程。这个基准计划会一直使用，直到被禁用或者您创建了新的基准计划。另一个解决 SQL 退化问题的方法是使用 SQL Tuning Advisor，它能提出 SQL 调整建议或建议进行外部修改，如通过创建索引提高性能。

应用案例

SPA 在很多情形中都是极有用的，包括数据库版本升级、部署数据库补丁集、数据库参数更改和优化程序参数更改等等。

例如，当您决定是否要提高优化程序参数时，比如从 10.2 更改为 11.1，您肯定想了解这个更改会对您的 SQL 语句产生怎样的影响。执行该任务最好的工具就是 SPA。唯一不同的是，在上面的步骤 5 中，不是选择 Database Parameter Changes，而是选择 Optimizer Changes，将出现如下所示屏幕。

在此屏幕中，选择合适的源优化程序版本和目标优化程序版本，然后完成剩余的步骤。

结论

使用这个新工具的最佳时间是什么时候？简单的回答就是：在您进行任何更改的时候。与数据库重放不同，在数据库重放中您看不到实际的 SQL，而使用 SPA，您能够得到特定 SQL 或整个应用程序 SQL 负载的结果。您可以评估正反两方面的影响，并达到最佳的可能更改状态而并不危害您的应用程序的性能。没有哪种选择是永远绝对正确或错误的，是对与错的程度使人们难于进行决策。SPA 将对错程度推向某一个极端，从而使您更容易作出决策。