客户新上线的一套重要生产系统，某个存储过程每小时调用约11万次，每次调用的逻辑读超过了10000，消耗的CPU占数据库CPU Time的25-30%。很显然，这样一个存储过程是值得优化的。
不幸的是，这个存储过程的业务逻辑很复杂，光是参数就有9个。而存储过程所在的包其代码超过了1万行。通过复查代码的方式，耗时耗力，还不一定能够找出问题。
诊断性能问题，首要的是了解这个存储过程所大概要完成的业务功能，然后通过trace或instrument收集足够详细的性能数据。从客户和开发商那里了解到，存在性能问题的存储过程，主要用于系统之间的数据查询接口，根据不同的参数输入查询不同的数据，那么，对于这种小数据量的存储过程，可以考虑使用10046事件来分析是哪些SQL产生了这么多的逻辑读。
但是通过10046事件，发现存储过程中实际执行的SQL并不多，同时并没有逻辑读高的SQL语句。也许问题并没有出现在SQL语句中，而是出现在存储过程中其他非SQL部分。虽然用10046没有找到SQL语句，但还是有重大发现，在过程执行时，大量的逻辑读来自于current方式的读，这显示不是通常的SELECT语句所产生的。接下来我们用dbms_profiler来分析存储过程：
- select dbms_profiler.start_profiler from dual;
- exec intf.CRM_SERVICE_INTF.QueryService(.....);
- exec dbms_profiler.stop_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个不同的存储过程，但是实现的功能是一致的：
- create or replace procedure p1 ( v_out out clob)
- is
- v_lob clob;
- begin
- v_lob:='';
- for rec in (select object_name from dba_objects where rownum< =1000) loop
- v_lob:=v_lob || rec.object_name;
- end loop;
- v_out:=v_lob;
- end;
- /
- create or replace procedure p2 ( v_out out clob)
- is
- v_lob varchar2(32767);
- begin
- v_lob:='';
- for rec in (select object_name from dba_objects where rownum<=1000) loop
- v_lob:=v_lob || rec.object_name;
- end loop;
- v_out:=v_lob;
- end;
- /
- create or replace procedure p3 ( v_out out clob)
- is
- v_lob clob;
- v_str varchar2(32767);
- v_cnt number;
- begin
- v_lob:='';
- v_cnt:=0;
- v_str:='';
- for rec in (select object_name from dba_objects where rownum<=1000) loop
- v_str:=v_str || rec.object_name;
- v_cnt:=v_cnt+1;
- if v_cnt = 50 then
- v_cnt:=0;
- v_lob:=v_lob || v_str;
- v_str:='';
- end if;
- end loop;
- if v_cnt <>0 then
- v_lob:=v_lob || v_str;
- end if;
- v_out:=v_lob;
- end;
- /
第1个存储过程，P1，完全使用clob类型来拼接字符串；第2个存储过程，P2，使用varchar2类型拼接字符串，只有在过程最后将varchar2转成clob；第3个存储过程，P3，先使用varchar2类型拼接成较长的字符串，然后再用较长的字符串用clob类型来拼接。
下面看看P1和P2的差距：
- SQL> var v_lob1 clob;
- SQL> var v_lob2 clob;
- SQL> begin
- 2 sys.runstats_pkg.rs_start;
- 3 p1(:v_lob1);
- 4 sys.runstats_pkg.rs_middle;
- 5 p2(:v_lob2);
- 6 sys.runstats_pkg.rs_stop;
- 7 end;
- 8 /
- Run1 ran in 5 hsecs
- Run2 ran in 0 hsecs
- run 1 ran in 50000000% of the time
- Name Run1 Run2 Diff
- STAT...lob reads 999 0 -999
- LATCH.cache buffers lru chain 1,000 0 -1,000
- STAT...free buffer requested 1,012 9 -1,003
- STAT...lob writes 2,000 1 -1,999
- STAT...lob writes unaligned 2,000 1 -1,999
- LATCH.object queue header oper 2,024 9 -2,015
- STAT...consistent gets 3,581 580 -3,001
- STAT...consistent gets from ca 3,581 580 -3,001
- STAT...consistent changes 5,051 42 -5,009
- STAT...db block changes 5,061 52 -5,009
- STAT...calls to get snapshot s 7,008 10 -6,998
- STAT...db block gets from cach 14,075 57 -14,018
- STAT...db block gets 14,075 57 -14,018
- STAT...session logical reads 17,656 637 -17,019
- LATCH.cache buffers chains 46,151 1,083 -45,068
- STAT...session uga memory 138,608 15,072 -123,536
- STAT...session uga memory max 315,052 131,024 -184,028
- STAT...session pga memory max 393,216 131,072 -262,144
- STAT...session pga memory 393,216 65,536 -327,680
- Run1 latches total versus runs -- difference and pct
- Run1 Run2 Diff Pct
- 49,789 1,674 -48,115 2,974.25%
- PL/SQL 过程已成功完成。
从上面的数据来看，差距是巨大的，执行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的差异：
- SQL> var v_lob1 clob;
- SQL> var v_lob2 clob;
- SQL> begin
- 2 sys.runstats_pkg.rs_start;
- 3 p1(:v_lob1);
- 4 sys.runstats_pkg.rs_middle;
- 5 p3(:v_lob2);
- 6 sys.runstats_pkg.rs_stop;
- 7 end;
- 8 /
- Run1 ran in 7 hsecs
- Run2 ran in 2 hsecs
- run 1 ran in 350% of the time
- Name Run1 Run2 Diff
- STAT...lob reads 999 19 -980
- LATCH.cache buffers lru chain 1,002 20 -982
- STAT...free buffer requested 1,012 29 -983
- STAT...lob writes 2,000 40 -1,960
- STAT...lob writes unaligned 2,000 40 -1,960
- LATCH.object queue header oper 2,016 49 -1,967
- STAT...consistent gets from ca 3,579 639 -2,940
- STAT...consistent gets 3,579 639 -2,940
- STAT...consistent changes 5,051 143 -4,908
- STAT...db block changes 5,061 153 -4,908
- STAT...calls to get snapshot s 7,007 146 -6,861
- STAT...db block gets 14,075 346 -13,729
- STAT...db block gets from cach 14,075 346 -13,729
- STAT...session logical reads 17,654 985 -16,669
- LATCH.cache buffers chains 46,147 1,999 -44,148
- STAT...session uga memory 189,012 65,464 -123,548
- STAT...session pga memory 196,608 65,536 -131,072
- STAT...session pga memory max 262,144 65,536 -196,608
- STAT...session uga memory max 315,052 65,560 -249,492
- Run1 latches total versus runs -- difference and pct
- Run1 Run2 Diff Pct
- 49,380 2,264 -47,116 2,181.10%
二者的差异同样很明显。
不仅如此，在存储过程中，执行P1过程之后，甚至在临时表空间中产生了临时段，而这个临时段是不会自动清除的，经测试，即使没有用于返回结果的clob参数，在过程内部生成的clob所占的临时段也不会自动清除，只有会话退出才会清除掉。如果更深入跟踪，也许可以发现大量的逻辑读来源于这个临时段。
- SQL> select tablespace,contents,segtype,blocks from v$sort_usage;
- TABLESPACE CONTENTS SEGTYPE BLOCKS
- ------------------------------- --------- --------- ----------
- TEMP TEMPORARY LOB_DATA 128
通过验证，证明clob类型的数据的确是引起客户系统中存储过程大量逻辑读和Latch争用的原因，找到了这个原因，优化就相对简单了。
在PL/SQL存储过程中，clob是相当方便的一种数据类型，由于其能够存储超长字符数据的特性，使得在这种用于数据交换的存储过程中用得较多。然而，从上面的数据中，不难发现，如果大量使用clob运算，将普通的字符串拼接成clob，其CPU消耗、逻辑读、甚至是latch的获取都是非常高的，对性能影响非常大。实际上在10g中，存储过程中的varchar2类型，其长度最大可以达到32767，所以如果返回的结果确保不超过这个长度，完全可以使用varchar2类型，只是在返回时再转换为clob，正如上面的存储过程P2所做的那样。如果其长度超过了32767，也可以如存储过程P3一样，先将短小的字符串拼接成较大的字符串，然后将较大的字符串拼接到clob中。
我的结论：
1.存储过程中，clob数据类型，和varchar2类型使用方法是一样的。
在迫不得已情况下，比如还是要频繁的把clob和varchar2拼接，也最好采用先将varchar2拼大，再拼到clob（而不是一开始就从clob开始诶个拼接），然后clob再拼没有满足条件的varchar2，根据测试，时间损失微乎其微。
2.如果要做字符串拼接，直接从clob开始拼接是不明智的，应该定义一个32767的varchar2，达到限制之后拼入clob再重新开始拼varchar2。例：
- for rec in (select object_name from dba_objects where rownum<=1000) loop
- v_str:=v_str || rec.object_name;
- v_cnt:=v_cnt+1;
- if v_cnt = 50 then
- v_cnt:=0;
- v_lob:=v_lob || v_str;
- v_str:='';
- end if;
- end loop;
- if v_cnt <>0 then
- v_lob:=v_lob || v_str;
- end if;
- v_out:=v_lob;
扫一扫
3081
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
