客户新上线的一套重要生产系统,某个存储过程每小时调用约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。下面是部分的内容:
profiler1

点击其显示的代码行(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中。

Trackback

2 comments untill now

  1. “但是通过10046事件,发现存储过程中实际执行的SQL并不多,同时并没有逻辑读高的SQL语句。也许问题并没有出现在SQL语句中,而是出现在存储过程中其他非SQL部分。”

    很好的经验分享呀!

  2. xiao nan @ 2011-11-29 16:09

    您好!看了这个案例学习到很多,由于经验不多,有点细节还希望您赐教:1.具体比较p1,p2,p3性能那,您能详细的说说吗,包括过程的创建,运行什么工具得出结果,具体怎么看数据得出结论;2.“对于这种小数据量的存储过程,可以考虑使用10046事件来分析是哪些SQL产生了这么多的逻辑读。”关于这里,如果大数据量的数据应该怎么分析;3.10046的跟踪结果是怎样的一个分析思路4.一致性读为什么说明不是select的问题,逻辑读的几种类型各自说明什么问题;5.mos文档在哪里如何查看。6.复查代码指的是什么方式。

Add your comment now