SQL Performance Analyzer 和实时 SQL 监视
<DIV><P><STRONG>了解如何准确预测对数据库执行的实际 SQL 语句的更改，并实时监视 SQL 性能（仅第 2 版具备此特性）。</STRONG></P>
<P><BR> 数据重放是一种非常出色的工具，可用于捕获数据库中的实际负载并随意重放。它是 Oracle Real Application Testing 选件的一部分，重点在于“实际”一词，即重放的负载就是数据库中实际发生的负载。</P>
<P>在数据库重放中，捕获的全部负载都针对数据库进行重放。但如果您不想那样做那该怎么办？例如，您可能很想了解 SQL 执行计划以及 SQL 性能是如何受更改影响的，因为它们会严重影响应用程序的性能和可用性。此外，数据库重放只重放捕获的负载。在生产环境中执行之前，您可能很想知道参数更改对某些 SQL 的影响。</P>
<P>这一领域正是 Real Application Testing 系列的另一个重要组件 SQL Performance Analyzer (SPA) 的用武之地。通过 SPA，您可以根据各种更改类型（如初始化参数更改、优化器统计刷新和数据库升级）播放特定的 SQL 或整个 SQL 负载，然后生成比较报告，帮助您评估它们的影响。在本文中，您将学习如何使用 SQL Performance Analyzer 工具解决这个重要的问题。</P>
<H2>示例问题</H2>
<P>我们来进行一个测试。首先，我们定义要解决的问题。</P>
<P>这是一个典型的问题：Oracle 不使用索引，而您想了解其中的原因。为了回答这个问题，我拜读了 Oracle 精英 Tim Gorman 的经典论文“Searching for Intelligent Life in Oracle's CBO”。（在 Web 上随处都能找到这篇论文多种形式的各个版本。）</P>
<P>Tim 的一个建议是，将 optimizer_index_cost_adj 参数的值由默认的 100 更改为一个较小的值。这篇论文还给出了计算这个值的公式。根据公式，在我的案例中，我计算得出这个值为 10。但是，这带来一个比较棘手的问题：这样的更改会对每条 SQL 语句都有裨益吗？</P>
<P>在 Oracle Database 11<EM>g</EM> 之前的版本中，我必须捕获所有 SQL 语句，通过跟踪运行这些语句，然后得到执行计划 — 这是一项极其耗时又极易出错的任务。有了新版本之后，我不需要再那样做了，我改用非常简单而有效的 SQL Performance Analyzer。</P>
<P>首先，为了举例说明，我们在数据库中运行以下查询：</P><PRE>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;
</PRE><BR>假设这些查询由您的应用程序发出。通过调整 SQL 中的注释，您可以在以后对它们进行搜索。语句执行完成后，您可以对其使用 SPA。
<H3><BR>用法</H3>
<P>通常，最好通过 Oracle Enterprise Manager 使用 SPA 功能。（当然，您也可以通过系统提供的 dbms_sqlpa 程序包使用命令行选项，但不能超过 Enterprise Manager 添加的可管理性值。）</P>
<P>执行以下步骤。</P>
<OL>
<LI>打开 Enterprise Manager Database Control，单击 Performance 选项卡。然后滚动到页面底部，您将看到如下超链接。
<P> </P>
<DIV align=center><IMG alt="图 1" src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/117579.jpg"></DIV>
<P> </P></LI>
<LI>单击 <STRONG>Search Sessions</STRONG>，将显示如下屏幕：
<P> </P>
<DIV align=center><IMG alt="图 2" src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/117623.jpg"></DIV>
<P> </P></LI>
<LI>从游标缓存中搜索已经执行的 SQL 中的模式。注意，这些 SQL 都有一个注释 CONTROL_QUERYn，其中 n 为 11、12 等等。输入该字符串作为搜索函数。将显示先前执行过的所有 SQL。在上面显示的屏幕中，您将看到标题为 <STRONG>Save to a new SQL Tuning Set</STRONG> 的单选按钮。选择该按钮，然后输入一个名为 CONTROL1 的 SQL 调优集。（注意：为了举例说明，您要选择一些语句。）无需在 SQL 中加入任何注释，您只要创建一个“SQL 调优集”并填入所有相关语句即可。</LI>
<LI>单击 <STRONG>SQL Tuning Sets</STRONG>，将显示 SQL Tuning Sets 页面。现在选择名为 <STRONG>CONTROL1</STRONG> 的 STS。在该页面中，您可以检查该 STS 并为其添加和删除 SQL。下面是该 STS 页面的屏幕截图：
<P> </P>
<DIV align=center><IMG alt="图 3" src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/113703.jpg"></DIV>
<P> </P></LI>
<LI>现在，在 <STRONG>Performance</STRONG> 页面中单击 <STRONG>SQL Performance Analyzer</STRONG>。将显示如下所示的 SPA 主页面。
<P> </P>
<DIV align=center><IMG alt="图 4" src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/123446.jpg"></DIV>
<P> </P></LI>
<LI>如您所见，尚未定义任何 SPA 任务。现在定义一个 SPA 任务。在本例中，您将对参数 optimizer_index_cost_adj 的更改所带来的影响进行评估，因此请单击 <STRONG>Parameter Change</STRONG>。将显示如下所示的 SPA Task Definition 页面。
<P> </P>
<DIV align=center><IMG alt="图 5" src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/103915.jpg"></DIV>
<P> </P></LI>
<LI>在该页面中，您需要输入 SPA 任务的必要信息。为任务命名：例如 STS1。</LI>
<LI>接下来，您需要输入 SQL 调优集的名称。单击旁边的手电筒图标，然后选择名为 CONTROL1 的 SIS。</LI>
<LI>在 <STRONG>Parameter Change</STRONG> 部分中，输入您需要更改的参数。单击手电筒图标，然后选择参数 <STRONG>optimizer_index_cost_adj</STRONG>。当前值已经填入，在 <STRONG>Changed Value</STRONG> 框中输入目标值。</LI>
<LI>接下来，决定这些更改的比较方式：按所用时间、按 CPU 时间等等。在本例中，选择 <STRONG>Buffer Gets</STRONG>。</LI>
<LI>最后，安排该 SPA 任务的执行时间。选择单选按钮 <STRONG>Immediate</STRONG>，表明您要立即运行这个任务。</LI>
<LI>所有详细信息都填好后，单击 <STRONG>Submit</STRONG>。这样就创建了一个您能够独立监视的作业，但您也可以停留在这个页面中并监视该 SPA 任务的状态，如下所示。
<P> </P>
<DIV align=center><IMG alt="图 6" src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/117608.jpg"></DIV>
<P> </P></LI>
<LI>单击 <STRONG>Refresh</STRONG> 查看该任务当前的状态。注意 <STRONG>Last Run Status</STRONG> 列下的图标。下面是对这些图标的说明：
<P> </P>
<DIV align=center><IMG alt="图 7" src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/117556.jpg"></DIV>
<P> </P></LI>
<LI>图中的图标表示该任务现在正在运行。不断单击 <STRONG>Refresh</STRONG>。当图标变为 <STRONG>Completed</STRONG>，显示为一个对号时，该 SPA 任务就完成了。</LI>
<LI>单击该 SPA 任务的名称 (<STRONG>STS1</STRONG>)，查看比较标准。将显示如下所示的屏幕。
<P> </P>
<DIV align=center><IMG alt="图 8" src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/117587.jpg"></DIV>
<P> </P></LI>
<LI>注意 <STRONG>Comparison Report</STRONG> 列下的眼镜图标。在这里，您可以比较参数更改前后执行 SQL 的结果。单击该图标。</LI>
<LI>这是最令人兴奋的部分。将显示如下所示的屏幕：
<P> </P>
<DIV align=center><IMG alt="图 9" src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/115673.jpg"></DIV></LI></OL>
<P>这就是您一直等待的结果。在左上角，您会看到更改前后两次运行 SQL 集的结果比较。总体而言，性能提升了 60%！这个结果就显示在这，非常明确。在屏幕的右手边，您会看到有多少 SQL 语句改变了执行计划。似乎大部分都改变了计划，只有少数没有。</P>
<P>屏幕底端显示了该任务分析的 SQL 语句的 SQL ID。SQL ID 前面的小箭头显示了这些 SQL 语句性能是提升了还是下降了，SQL ID 后面的数字显示了性能提升或下降的百分比。这些数据告诉您更改对每条 SQL 语句的确切影响。如果您愿意，可以通过单击 SQL ID 查看相应的 SQL。注意第一条 SQL，它受到的影响最大，如果单击该 SQL，您会看到与下面类似的屏幕：</P>
<P> </P>
<DIV align=center><IMG alt="图 10" src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/125531.jpg"></DIV>
<P><BR>这个屏幕显示了有关执行该 SQL 的大量统计信息。屏幕底部显示了执行计划的比较：</P>
<P> </P>
<DIV align=center><IMG alt="图 11" src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/105707.jpg"></DIV>
<P><BR>现在您可以看到，使用索引是如何强制减少缓冲区的。但是，情况总是那么乐观吗？看看另一条 SQL：</P>
<DIV align=center><IMG alt="图 12" src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/117622.jpg"></DIV>
<P><BR>与上一例的 31.95% 相比，此例改进甚微，只有 0.48%。原因是什么？为了找到答案，单击 SQL ID，将显示如下屏幕：</P>
<DIV align=center><IMG alt="图 13" src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/106893.jpg"></DIV>
<P><BR>在这里，您可以看到究竟发生了什么变化。所用时间实际上从 0.504 秒增加为 1.022 秒，而且都是因为 CPU 时间。为什么？如果您检查一下数据分布模式，就会看到 promo_id 是这样分布的：</P><PRE>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
</PRE><BR>promo_id 999 在表中出现了 887,837 次，将近 97%。当将计划改为包含索引扫描时，这个查询就比较困难了。如果对全表进行扫描，情况应该会好一些。因此，即使整体影响是积极的，也会有个别组件拖后腿。在决定是否要更改参数时，您应该考虑到这些 SQL 语句的重要性，这些语句性能既可能提升也可能下降。 <BR><BR>
<P>正如您所见，您希望评估对数据库参数进行重要更改所带来的影响。使用 SPA，您不必估计潜在的性能影响，连“猜测估计”也不必。您可以使用应用程序针对数据库执行的 SQL 语句客观地衡量。</P>
<P>现在看另一个案例：更改参数后，性能下降了，而不是提升了。下面是一个屏幕截图：</P>
<DIV align=center><IMG alt="图 14" src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/127533.jpg"></DIV>
<P><BR>这里，SQL 语句的运行情况都比更改之前要差。您可以利用（<A href="http://www.oracle.com/technetwork/cn/articles/sql/11g-sqlplanmanagement-097764-zhs.html"><FONT color=#000000>本文</FONT></A>中讨论的）SQL 计划管理解决这个问题。SPM 允许您选择优良的执行计划作为基准，从而保证执行计划的稳定性。随后，优化器会将这个基准用于相应 SQL 的所有执行过程。这个基准计划会一直使用，直到被禁用或者您创建了新的基准计划。另一个解决 SQL 性能下降问题的方法是使用 SQL Tuning Advisor，它能提出 SQL 调整建议或建议进行外部修改，如通过创建索引提高性能。</P>
<H2>用例</H2>SPA 在很多情形中都极为有用，包括数据库版本升级、部署数据库补丁集、数据库参数更改和优化器参数更改等等。 <BR><BR>
<P>例如，当您决定是否要提高优化器参数时，比如从 10.2 升级到 11.1，您肯定想了解这个更改会对您的 SQL 语句产生怎样的影响。执行该任务最好的工具就是 SPA。唯一不同的是，在上面的第 5 步中，不是选择 <STRONG>Database Parameter Changes</STRONG>，而是选择 <STRONG>Optimizer Changes</STRONG>，将显示如下所示屏幕。</P>
<DIV align=center><IMG alt="图 15" src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/125418.jpg"></DIV>
<P><BR>在此屏幕中，选择合适的源优化器版本和目标优化器版本，然后完成剩余的步骤。</P>
<P>何时使用这个新工具的最佳？简单的回答就是：在您进行任何更改时。与数据库重放不同，在数据库重放中您看不到实际的 SQL，而使用 SPA，您能够得到特定 SQL 或整个应用程序 SQL 负载的结果。您可以评估正反两方面的影响，达到最佳的可能更改状态而不危害您应用程序的性能。没有哪种选择是永远绝对正确或错误的，让人们难以进行决策的是对与错的程度。SPA 将对错程度推向某一个极端，从而使您更容易作出决策。</P>
<H3><BR>第 2 版新增特性：</H3>
<P>在您数据库里造成严重破坏的可怕查询又出现了。您找到了它，消灭了它。一切再次恢复正常。但考虑另外一个难题：如果查询从技术上来讲是善意的，但可能会像麦田里的蝗虫一样蚕食您的 I/O 带宽和 CPU，因此成为恶性查询，又该如何？如果您理解了所有这些内部 X$ 和 V$ 视图，对 10046 事件了如指掌，那么就可以制作出一些 SQL 脚本，迅速告诉您一切情况。对于我们这些凡人来说，我们无疑希望有更轻松的选择。</P>
<P>幸运的是，Oracle Enterprise Manager Database Control 11<EM>g</EM> 第 2 版具有实时 SQL 监视功能，可在 GUI 屏幕上为您显示目前正在运行的 SQL 语句，以及 CPU、IO 和等待事件等资源的所有相关详细信息。此外，它还会为您显示执行计划的具体步骤以及各步骤的资源统计信息。为避免屏幕上过于混乱，该特性仅监视占用 CPU 或 I/O 时间不低于 5 秒的 SQL。</P>我们来看一下它的工作原理。为了演示此特性，我们需要运行一些较为复杂的 SQL。我将使用 Oracle 示例附带的 SH 模式。然后，在 SQL*Plus 中运行此语句： <PRE>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;
</PRE>
<P>接下来，我们看看 SQL 监视。在 Database Control 中，单击 <STRONG>Performance</STRONG> 选项卡，向下滚动到选项卡底部，找到名为 Additional Monitoring Links 的超链接组。在该组中，单击 <STRONG>SQL Monitoring</STRONG>。这将显示一个如下所示的屏幕。</P>
<DIV style="TEXT-ALIGN: center"><IMG alt=11g-realtime-sqlmon-f1 src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/315041.png"></DIV>
<P><BR>注意 Status 列，如果您之前执行的 SQL 语句仍在执行，该列将显示一个日晷型的图标，表示 SQL 语句正在执行。如果单击 SQL ID，您将看到有关 SQL 的详细信息。将弹出一个名为 Monitored SQL Execution Details 的屏幕，如下所示。这是屏幕的顶端部分，显示了 SQL 的执行概况。</P>
<DIV style="TEXT-ALIGN: center"><IMG alt=11g-realtime-sqlmon-f2 src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/315042.png"></DIV>
<P><BR>每一条都向您实时显示了相应的统计信息。您可以将鼠标指针悬停在各条上，查看其代表的数字。<BR> </P>
<DIV style="TEXT-ALIGN: center"><IMG alt=11g-realtime-sqlmon-f3 src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/315043.png"></DIV><BR>
<P>右键单击屏幕将为您显示上下文相关的屏幕，如下所示。<BR><BR><IMG alt=11g-realtime-sqlmon-f4 src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/315044.png"></P>
<DIV style="TEXT-ALIGN: center"> </DIV>
<P><BR>在这个弹出菜单中，您可以选择其他相关信息（如 SQL 文本、会话等)，还可显示 Enterprise Manager 的其他常规屏幕。</P>
<P>以下是这个屏幕的底端部分，同样实时显示了执行计划。</P>
<DIV style="TEXT-ALIGN: center"><IMG alt=11g-realtime-sqlmon-f5 src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/315045.png"></DIV>
<P><BR>请仔细观察屏幕。其中不仅显示了执行计划，还显示了多种统计信息，例如各阶段的 CPU 和 IO 使用情况，并不仅限于整个语句。这有助于识别当前执行缓慢的语句所在的具体区域。</P>
<P>除了显示执行计划之外，还显示了并行查询的统计信息。请注意 Plan Statistics 选项卡旁边的 <STRONG>Parallel</STRONG> 选项卡。它应显示如下屏幕：</P>
<DIV style="TEXT-ALIGN: center"><IMG alt=11g-realtime-sqlmon-f6 src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/315046.png"></DIV><BR>
<P>其中显示了并行查询服务器、它们目前正处于何种类型的等待状态、它们所发出的 IO 请求、它们对每个并行查询从属进程要求的一致 get 数量。右键单击 IO 请求条，此时将看到与之前相同的弹出菜单，但略有不同 — 包含一个名为 Toggle IO Bytes 的新项。选择此项将按字节显示 IP，而非请求数量。</P>
<DIV style="TEXT-ALIGN: center"><IMG alt=11g-realtime-sqlmon-f7 src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/315047.png"></DIV>
<P><BR>IO Bytes 屏幕如下所示。</P>
<DIV style="TEXT-ALIGN: center"><IMG alt=11g-realtime-sqlmon-f8 src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/315048.png"></DIV>
<P><BR>如果并行性下降，您将在详细信息列中看到实际的并行度，但还会有一个红色的小图标，表明实际并行度和所请求的并行度存在差异，如下所示。如果您将鼠标指针悬停在 Parallel 值上，将看到所请求的并行度。</P>
<DIV style="TEXT-ALIGN: center"><IMG alt=11g-realtime-sqlmon-f9 src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/315049.png"></DIV><BR>
<P>如果希望向同事显示这份报表以便获得建议或进一步的分析，应该怎么办？您可以生成一份 html 报表。单击主页中的 <STRONG>Report</STRONG>，可将其显示为常规 HTML 文件。下面是该报表的节选：</P>
<DIV style="TEXT-ALIGN: center"><IMG alt=11g-realtime-sqlmon-f10 src="http://www.oracle.com/ocom/groups/public/@otn/documents/digitalasset/315050.png"></DIV>
<P><BR>报表中包含 SQL 语句的所有相关信息，包括您之前看到的并行查询从属进程。当然，这只是一个即时快照，而非您在 EM 屏幕中实时看到的移动图片。如果您希望查明执行中的哪个步骤占用了较长时间，那么 SQL 语句的实时监视尤为有用，这是一种性能调优的好方法，必将在您的工具箱中占据重要位置。</P></DIV>
页:
[1]