数据库性能调整

设计数据库SQL，提高性能CURSOR_SHARING

2007-09-10 16:33

总是有人问我这个问题：“怎么能让应用运行得更快?”所有人都希望有一个“fast = true”开关，认为“数据库调优”就意味着让你调整数据库。实际上，根据我的经验，80%以上（甚至经常是100%）的性能问题都出现在设计和实现级，而不是数据库级。通过修改应用，我常常能让性能呈数量级地增长。但是，如果只是在数据库级做修改，就不太可能得到这么大幅度的提高。在对数据库上运行的应用进行调优之前，先不要对数据库进行调优。 随着时间的推移，数据库级也有了一些开关，有助于减轻编程错误带来的影响。例如，Oracle 8.1.6增加了一个新参数CURSOR_SHARING=FORCE。如果你愿意，这个特性会实现一个自动绑定器（auto-binder）。如果有一个查询编写为SELECT * FROM EMP WHERE EMPNO = 1234，自动绑定器会悄无声息地把它改写成SELECT * FROM EMP WHERE EMPNO = :x。这确实能动态地大大减少硬解析数，并减少前面讨论的库闩等待时间——但是（凡事总有个“但是”），它可能有一些副作用。游标共享的一个常见副作用如下所示： 这里到底发生了什么？为什么到第二个查询时SQL*Plus报告的列突然变得这么大？要知道，这还是同一个查询呀！如果查看一下游标共享设置为我们做了些什么，原因就会很清楚了（还会明白其他一些问题）： 游标共享会删除查询中的信息。它找到每一个直接量（literal），包括内置求子串函数（substr）的参数，直接量就是我们使用的常量。它把这些直接量从查询中删除，并代之以绑定变量。SQL引擎再也不知道这个列是长度为1的子串，它的长度是不确定的。另外，可以看到where rownum = 1现在也已经绑定。看上去似乎不错；不过，优化器把一个重要的信息也一并删除了。它不知道“这个查询将获取一行”；现在只认为“这个查询将返回前N行，而N可能是任何值”。实际上，如果加上SQL_TRACE=TRUE后再运行这些查询，你会发现每个查询使用的查询计划都不同，它们完成的工作量也大相径庭。考虑以下查询： 查询计划有一些微小的差别（有时甚至完全不同）；另外它们的工作量也有很大差异。所以，打开游标共享确实需要特别谨慎（而且需要进行充分测试）。游标共享可能会改变应用的行为（例如，列宽发生变化），而且由于它删除了SQL中的所有直接量，甚至包括那些绝对不会变化的直接量，所以可能会对查询计划带来负面影响。 另外，与解析和优化大量各不相同的查询相比，尽管使用CURSOR_SHARING = FORCE会 让运行速度更快，但同时我也发现，倘若开发人员确实在查询中使用了绑定变量，查询的速度就比使用游标共享要快。这不是因为游标共享代码的效率不高，而是因 为程序本身的效率低下。在许多情况下，如果应用没有使用绑定变量，也不会高效地解析和重用游标。因为应用认为每个查询都是惟一的（并把查询分别建立为不同 的语句），所以绝对不会多次使用一个游标。事实上，如果程序员刚开始就使用了绑定变量，他（或她）就能只解析一次查询，然后多次重用它。正是这种解析开销 降低了总体性能。 实质上讲，一定要记住重要的一点，只打开CURSOR_SHARING = FORCE并不一定能解决你的问题。而且游标共享还可能带来新的问题：在有些情况下CURSOR_SHARING是一个非常有用的工具，但它不是银弹。开发得很好的应用从不需要游标共享。从长远来看，要尽可能地使用绑定变量，而在需要时才使用常量，这才是正确的做法。 注意    世上没有银弹——要记住，根本没有。如果有的话，自然就会默认地采用那种做法，这样也就无所谓银弹了。 就算是确实能在数据库级放几个开关（这种开关真的很少），但是有些问题与并发控制和执行不佳的查询（可能是因为查询写得不好，也可能是因为数据的结构性差）有关，这些问题用开关是解决不了的。这些情况往往需要重写（而且时常需要重建）。移动数据文件、修改多块读计数（multiblock read count）和其他数据库级开关对应用的总体性能通常影响很小。你想让用户接受你的应用，可能需要让性能提升2倍、3倍、……、n倍才行。你的应用是不是只慢了10%，这种情况多不多？如果只是慢10%，没有人会有太多抱怨。但是如果慢了5倍，就会让人很不高兴。再说一遍，如果只是移动数据文件，性能不会提升5倍。要想达到这个目的，只能通过调整应用才能办到，可能要让它大幅减少I/O操作。 在 整个开发阶段，你都要把性能作为一个目标精心地设计，合理地构建，并且不断地测试。绝对不能把它当作马后炮，事后才想起来。我真是很奇怪，为什么那么多人 根本不对应用调优，就草率地把应用交付到客户手里，匆匆上马，并运行起来。我见过一些应用除了主键索引外，居然没有其他的任何索引。从来没有对查询执行过 调优，也没有执行过压力测试。应用的用户数很少，从未让更多的用户试用过。这些应用总是把调优当成产品安装的一部分。对我来说，这种做法绝对不可接受。最 终用户应该第一天就拿到一个响应迅速、充分优化的系统。肯定还有许多“产品问题”需要处理，但不能让用户从一开始就领教糟糕的性能。对用户来说，一个新应 用里有几个bug尚能容忍，但你别指望他们能耐心地在屏幕前等待漫长的时间。