性能分析：Oracle的CLOB使用与临时段访问及其性能优化

编辑手记：在系统测试、上线和优化的过程中，抓住核心环节、不放过任何可疑，这是DBA的基本要求之一，在这个案例中，高频度调用的存储过程引起了注意。

客户新上线的一套重要生产系统，某个存储过程每小时调用约11万次，每次调用的逻辑读超过了10，000，消耗的 CPU 占数据库 CPU Time 的25-30%。很显然，这样一个存储过程是值得优化的。

不幸的是，这个存储过程的业务逻辑很复杂，光是参数就有9个。而存储过程所在的包其代码超过了1万行。通过复查代码的方式，耗时耗力，还不一定能够找出问题。

诊断性能问题，首要的是了解这个存储过程所大概要完成的业务功能，然后通过 trace 或 instrument 收集足够详细的性能数据。从客户和开发商那里了解到，存在性能问题的存储过程，主要用于系统之间的数据查询接口，根据不同的参数输入查询不同的数据，那么，对于这种小数据量的存储过程，可以考虑使用10046事件来分析是哪些 SQL 产生了这么多的逻辑读。

但是通过10046事件，发现存储过程中实际执行的 SQL 并不多，同时并没有逻辑读高的 SQL语句。也许问题并没有出现在 SQL语句中，而是出现在存储过程中其他非 SQL 部分。虽然用10046没有找到 SQL 语句，但还是有重大发现，在过程执行时，大量的逻辑读来自于 current方式的读，这显示不是通常的 SELECT 语句所产生的。接下来我们用 dbms_profiler 来分析存储过程：

然后使用来自 MOS 文档 “Implementing and Using the PL/SQL Profiler [ID 243755.1]”中的 profiler.sql 脚本（通过文末原文链接可以下载这个脚本），生成一个profiler的结果文件，格式为 html。下面是部分的内容：

点击其显示的代码行(line)，跳转到相应的源代码，发现大量的字符串拼接代码，很明显是用于拼接成 XML 格式。这很容易理解，因为现在系统之间的文本数据交互，xml 几乎成了标准。从上面的截图中也可以看到很多类型于 xml:=xml || ‘xxxx’ 这样的代码。是这样的代码引起的问题吗？

检查代码发现，这里用于拼接字符串的变量 xml，被定义为 clob 类型，这引起了我极大的关注。在 oracle 的标准数据类型中，lob 类型由于其能够存储大数据的本质，导致其内部格式和操作是最复杂的。有理由怀疑是 clob 的大量拼接引起的问题。

在有怀疑对象后，我们可以构造下面的测试来进行验证。首先创建下面3个不同的存储过程，但是实现的功能是一致的：

第1个存储过程，P1，完全使用 clob 类型来拼接字符串；第2个存储过程，P2，使用 varchar2 类型拼接字符串，只有在过程最后将 varchar2 转成clob；第3个存储过程，P3，先使用varchar2 类型拼接成较长的字符串，然后再用较长的字符串用 clob 类型来拼接。

下面看看P1和P2的差距：

从上面的数据来看，差距是巨大的，执行P1时产生的一致性读(consistent gets)和当前模式读(db block gets)，都远远大于执行P2时产生的一致读和当前模式读。特别是大量的 db block gets 验证了之前使用10046事件跟踪存储过程执行得到的结果。

上面的数据中，甚至是会话消耗的内存都有很明显的差异。同时，还有其他对性能显著影响的地方，cache buffers chains latch，cache buffers lru chain latch，object queue header operation latch，这3种 latch 的获取次数是相当多的，在大并发时无疑将引起争用。实际上，在客户的这套系统中，这3种 latch 的争用经常出现，只是目前还没形成特别严重的后果。

再看看P1和P3的差异：

二者的差异同样很明显。

不仅如此，在存储过程中，执行 P1 过程之后，甚至在临时表空间中产生了临时段，而这个临时段是不会自动清除的，经测试，即使没有用于返回结果的 clob 参数，在过程内部生成的 clob 所占的临时段也不会自动清除，只有会话退出才会清除掉。如果更深入跟踪，也许可以发现大量的逻辑读来源于这个临时段。

通过验证，证明clob类型的数据的确是引起客户系统中存储过程大量逻辑读和Latch争用的原因，找到了这个原因，优化就相对简单了。

在 PL/SQL 存储过程中，clob 是相当方便的一种数据类型，由于其能够存储超长字符数据的特性，使得在这种用于数据交换的存储过程中用得较多。然而，从上面的数据中，不难发现，如果大量使用 clob 运算，将普通的字符串拼接成 clob，其 CPU 消耗、逻辑读、甚至是latch的获取都是非常高的，对性能影响非常大。实际上在10g中，存储过程中的 varchar2类型，其长度最大可以达到 32767，所以如果返回的结果确保不超过这个长度，完全可以使用 varchar2 类型，只是在返回时再转换为 clob，正如上面的存储过程 P2 所做的那样。如果其长度超过了32767，也可以如存储过程 P3 一样，先将短小的字符串拼接成较大的字符串，然后将较大的字符串拼接到 clob 中。