SQL Performance Analyzer 和实时 SQL 监视

了解如何准确预测对数据库执行的实际 SQL 语句的更改，并实时监视 SQL 性能（仅第 2 版具备此特性）。

    数据重放是一种非常出色的工具，可用于捕获数据库中的实际负载并随意重放。它是 Oracle Real Application Testing 选件的一部分，重点在于“实际”一词，即重放的负载就是数据库中实际发生的负载。

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

这一领域正是 Real Application Testing 系列的另一个重要组件 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 Database 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 Enterprise Manager 使用 SPA 功能。（当然，您也可以通过系统提供的 dbms_sqlpa 程序包使用命令行选项，但不能超过 Enterprise Manager 添加的可管理性值。）

执行以下步骤。

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 将对错程度推向某一个极端，从而使您更容易作出决策。

第 2 版新增特性：

在您数据库里造成严重破坏的可怕查询又出现了。您找到了它，消灭了它。一切再次恢复正常。但考虑另外一个难题：如果查询从技术上来讲是善意的，但可能会像麦田里的蝗虫一样蚕食您的 I/O 带宽和 CPU，因此成为恶性查询，又该如何？如果您理解了所有这些内部 X$ 和 V$ 视图，对 10046 事件了如指掌，那么就可以制作出一些 SQL 脚本，迅速告诉您一切情况。对于我们这些凡人来说，我们无疑希望有更轻松的选择。

幸运的是，Oracle Enterprise Manager Database Control 11g 第 2 版具有实时 SQL 监视功能，可在 GUI 屏幕上为您显示目前正在运行的 SQL 语句，以及 CPU、IO 和等待事件等资源的所有相关详细信息。此外，它还会为您显示执行计划的具体步骤以及各步骤的资源统计信息。为避免屏幕上过于混乱，该特性仅监视占用 CPU 或 I/O 时间不低于 5 秒的 SQL。

我们来看一下它的工作原理。为了演示此特性，我们需要运行一些较为复杂的 SQL。我将使用 Oracle 示例附带的 SH 模式。然后，在 SQL*Plus 中运行此语句：

SELECT
        /*+ parallel */
        prod_name          ,
        cust_state_province,
        channel_desc       ,
        SUM(amount_sold)
FROM    sales s    ,
        customers c,
        products p ,
        channels c
WHERE   s.cust_id        = c.cust_id
        AND s.prod_id    = p.prod_id
        AND s.channel_id = c.channel_id
        AND c.cust_year_of_birth BETWEEN 2010 - 30 AND 2010 - 20
GROUP BY prod_name         ,
        cust_state_province,
        channel_desc;
        

接下来，我们看看 SQL 监视。在 Database Control 中，单击 Performance 选项卡，向下滚动到选项卡底部，找到名为 Additional Monitoring Links 的超链接组。在该组中，单击 SQL Monitoring。这将显示一个如下所示的屏幕。

注意 Status 列，如果您之前执行的 SQL 语句仍在执行，该列将显示一个日晷型的图标，表示 SQL 语句正在执行。如果单击 SQL ID，您将看到有关 SQL 的详细信息。将弹出一个名为 Monitored SQL Execution Details 的屏幕，如下所示。这是屏幕的顶端部分，显示了 SQL 的执行概况。

每一条都向您实时显示了相应的统计信息。您可以将鼠标指针悬停在各条上，查看其代表的数字。
 

右键单击屏幕将为您显示上下文相关的屏幕，如下所示。

 

在这个弹出菜单中，您可以选择其他相关信息（如 SQL 文本、会话等)，还可显示 Enterprise Manager 的其他常规屏幕。

以下是这个屏幕的底端部分，同样实时显示了执行计划。

请仔细观察屏幕。其中不仅显示了执行计划，还显示了多种统计信息，例如各阶段的 CPU 和 IO 使用情况，并不仅限于整个语句。这有助于识别当前执行缓慢的语句所在的具体区域。

除了显示执行计划之外，还显示了并行查询的统计信息。请注意 Plan Statistics 选项卡旁边的 Parallel 选项卡。它应显示如下屏幕：

其中显示了并行查询服务器、它们目前正处于何种类型的等待状态、它们所发出的 IO 请求、它们对每个并行查询从属进程要求的一致 get 数量。右键单击 IO 请求条，此时将看到与之前相同的弹出菜单，但略有不同 — 包含一个名为 Toggle IO Bytes 的新项。选择此项将按字节显示 IP，而非请求数量。

IO Bytes 屏幕如下所示。

如果并行性下降，您将在详细信息列中看到实际的并行度，但还会有一个红色的小图标，表明实际并行度和所请求的并行度存在差异，如下所示。如果您将鼠标指针悬停在 Parallel 值上，将看到所请求的并行度。

如果希望向同事显示这份报表以便获得建议或进一步的分析，应该怎么办？您可以生成一份 html 报表。单击主页中的 Report，可将其显示为常规 HTML 文件。下面是该报表的节选：

报表中包含 SQL 语句的所有相关信息，包括您之前看到的并行查询从属进程。当然，这只是一个即时快照，而非您在 EM 屏幕中实时看到的移动图片。如果您希望查明执行中的哪个步骤占用了较长时间，那么 SQL 语句的实时监视尤为有用，这是一种性能调优的好方法，必将在您的工具箱中占据重要位置。