Oracle 11g新特性:SQL Performance Analyzer

数据库 Oracle
本文详细介绍了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 页面的屏幕截图:

 

#p#

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,您会看到与下面类似的屏幕:

 

#p#

这个屏幕显示了有关执行该 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 将对错程度推向某一个极端,从而使您更容易作出决策。

【编辑推荐】

  1. 详解Oracle 11g R1中数据泵增强
  2. 解析Oracle 11g闪回数据归档新功能
  3. Oracle 11g R1中的自动数据库维护任务管理
责任编辑:杨鹏飞 来源: oracle.com
相关推荐

2012-05-07 13:45:45

Oraclen 11g虚拟列数据库

2009-01-11 08:16:00

自适应游标共享新特性Oracle 11g

2010-04-16 09:50:26

Oracle 11g

2009-11-20 12:55:08

Oracle 11g功

2009-09-02 15:35:57

Oracle 11g

2009-04-15 09:49:20

2009-03-05 11:02:12

NTP列式压缩云计算

2010-01-11 10:08:14

Oracle 11g

2010-01-19 10:17:41

Oracle 11g

2010-01-13 16:08:09

Oracle 11g 数据卫士

2011-08-23 15:56:19

SQL Server Oracle 11g

2009-09-15 09:52:25

Oracle 11g分

2010-04-30 13:35:28

Oracle 11g

2010-04-13 15:56:03

Oracle 11g

2011-04-15 09:11:21

2010-01-25 10:11:32

Oracle 11g

2010-04-07 17:27:38

Oracle 11g

2010-04-01 15:19:56

Oracle 11g压

2009-03-12 10:28:32

数据库Oracle11g自我调整

2010-05-10 14:46:54

Oracle 11g
点赞
收藏

51CTO技术栈公众号