Jan 2

Written by: 中文技术资料库
1/2/2012 2:08 PM 

原文： “HOW TO TUNE ONE SQL FOR VARIOUS SIZE OF DATABASES ” 原文投稿日期：2010年11月5日 星期五
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

我被询问了很多次，怎样使用同一条SQL语句并且针对不同规模的数据库对它进行调优。这个问题的实质就是你只有一份程序代码，并且要将它发布到不同公司的不同规模的数据库中，SQL在A公司执行性能很好却在B公司执行性能很差的现象是十分常见的。如果你针对B公司调优SQL，新的SQL语法和执行计划可能会导致在A公司的执行性能变差。我们陷入了一个窘境，不得不牺牲一个数据库的性能来提高另外一个数据库的性能。一些开发人员可能会选择一个让A公司和B公司都可以接受但均未达到最佳性能的SQL语法和执行计划。如果我们要用相同的源代码发布到更多的公司,情况会变得更糟。 

因此我们将会讨论Oracle 11g中叫做SQL Plan Baselines的新特性。这个新特性被用来在环境变化时稳定SQL的性能，例如数据库升级，不同的数据规模，硬件改变或者软件设置等。 

SQL Plan Baselines

SQL Plan Baselines的概念很简单；它的思想就是如果你不能确定新的执行计划会比现在的执行计划更好的话，那么就不要立即使用任何新的执行计划。为了实现这个概念，Oracle必须建立plan baseline来保存一条SQL的所有执行计划。由于环境改变导致新的执行计划被SQL优化器产生之后，新的执行计划不会被立即使用但是会保留在plan baseline中，稍后为存在的状态为“Accepted”的执行计划做测试。如果测试后新的执行计划表现更好，它会被标记为“Accepted”。然后，如果Oracle的SQL优化器为这条SQL再次产生一个相同的执行计划，新执行计划会被立即使用。 

SQL Plan Baselines会自动调优有性能问题的SQL吗？

因为SQL Plan Baselines的思想是“如果未经测试，不会立即使用新的执行计划”，它只能阻止你的SQL语句不会由于突然的变化导致性能下降，当Oracle的SQL优化器不能在第一次产生一个好的执行计划时就无能为力。 

Quest SQL Optimizer是怎样通过SQL Plan Baselines来调优SQL的？

Oracle提供了一个方法允许用户手动加载执行计划到SQL plan baseline。手动加载的执行计划没有做性能测试，但是被当成accepted的执行计划被添加到已经存在的或者新的SQL plan baselines。好消息是你可以手动加载同一条SQL在应用提示后产生的不同的执行计划；因此，你可以添加提示调优SQL来影响Oracle SQL 优化器产生一个新的执行计划，然后用下面的PL/SQL代码手动加载新的执行计划和SQL文本。 

从SQL Area加载SQL和执行计划

DECLARE
    report int;
BEGIN
report:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id=>'3r9rzkpzqv6fg',
   sql_text=>'select * 
                     from employee 
                     where emp_dept in (select dpt_id 
                                                      from department,emp_small 
                                                      where dpt_id < :B1 and emp_dept = dpt_id)');
END;

 

SQL ID= ‘3r9rzkpzqv6fg’的地方也可以是同一句SQL加提示后，被执行并且缓存到SQL Area里面的，如下所示： 

select *
 from employee
 where emp_dept in (select /*+ ORDERED */ dpt_id
                      from department,emp_small 
                     where dpt_id < :B1 and emp_dept = dpt_id)

在手动加载新的执行计划到baseline之后，设置它的FIXED属性为“YES”，所以Oracle会在下一次当初始的（没有修改的）应用程序SQL执行时使用这个baseline执行计划。

在Plan Control 调优模块中，Quest SQL Optimizer利用这个机制对有性能问题的SQL应用不同的提示组合。这个庞大的提示组合远非人类可以胜任；我们的人工智能算法被设计成尝试所有能应用到SQL的最可能的组合。产生的替代执行计划和Oracle SQL 优化器产生的初始执行计划一起做测试，有更好性能的新执行计划可以被发布到数据库，不用改变源代码。这个技术的美妙之处就在于你只用一个版本的SQL源代码，利用不同的plan baselines却可以调优不同规模的数据库。 

让我们用employee (599998 条记录) 表和employee50000 (50000条记录)表在相同的环境下来模拟不同规模的数据库。Employee表的大小是employee50000表的10倍，两个表都有相同的索引配置。我们可以看到相同的SQL可以产生不同的执行计划（图一和图二），因此你可以看到Oracle基于成本的SQL optimizer动态的本质；由于表的大小不同，相同的SQL语法可以导致重大的执行计划改变。 

如图-1，你可以看到有599998条记录的Employee表上的SQL在调优之前需要6分6秒才能执行完。 

图 - 1 

 

如图-2，你可以看到有50000条记录的Employee50000表上的SQL在调优之前需要15.81秒才能执行完。

 

图– 2 

如图-3，我们找到了一条只需要4.79秒的执行计划，它比图-1中的初始执行计划快了76倍。

 

图- 3

如图-4，我们找到了一条只需要0.09秒的执行计划，它比图-2中的初始执行计划快了175倍。 

图 - 4 

针对不同规模数据库的多版本Baselines Plans

在图-3和图-4中我们发现Employee表和Employee50000表上的SQL各自最好的执行计划是不同的。如果程序包提供商想要为不同规模的数据库提供他们的程序包并且保证最好的性能，他们必须要为那些严重影响应用程序性能的关键SQL语句建立一个Baseline plans库。库将保留多个版本的Baseline plans，根据客户数据库的规模来发布合适的执行计划。

实际上，这个方法可以通过一个规划好的算法自动执行，算法察觉到表的大小之后相应地激活合适的SQL Baseline plan。下面的例子显示一条SQL有两个SQL Baseline plans；一旦表的大小达到转换点，用户可以在Plan A 和Plan B之间切换。

 

请记住这是一个只有一个表大小可变的简化例子。如果用户想要一个更精确的解决方案来处理多个可变大小的表，这个图将会包含多维的Baseline plans和转换点。除非SQL对应用程序的性能至关重要，我相信大部分用户在他们的应用程序中不需要那个特别的调优方案。