DBA日记第三部像Oracle一样思考 3月26日简单任务（3）令人惊讶的结果

最近系统的业务量不大，所以客户也同意我们白天就做测试。为了防止误操作，客户把SCOTT账号提供给我做测试。今天我准备做几个测试：

l 提交批量测试：测试批量大小对插入性能的影响，分别测试批量为800、1500、3000、5000条记录的响应时间

做这种单条SQL执行时间很短的测试，最好的办法是使用profiler工具，将要测试的内容写在一个存储过程里，通过profiler工具来计算平均执行一次所消耗的时间。我首先为每个测试项目都谢了一个小的PL/SQL过程，然后开了7个终端，运行这个存储过程，在第八个终端上的测试过程与其他不同，增加了PROFILER的脚本，这一就能够很方便的采集到每条SQL的执行情况了。

首先测试SEQUENCE，我分别对各种CACHE值进行了测试。首先编写了一个测试用的存储过程：

create or replace procedure testSeq(N integer)

i integer;

b integer;

v varchar2(20);

begin

i:=0;

v:=to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss');

dbms_output.put_line(v);

loop

exit when i>N;

i:=i+1;

select sm_idseq.nextval into b from dual;

end loop;

v:=to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss');

dbms_output.put_line(v);

end;

脚本执行结束后，可以通过下列脚本查看存储过程中每一行执行的情况：

column RUN_COMMENT format a40 truncate;

select runid, run_date, RUN_COMMENT from plsql_profiler_runs order by runid;

column unit_name format a15 truncate;

column occured format 999999 ;

column line# format 99999 ;

column tot_time format 999999.999999 ;

select p.unit_name, p.occured, p.tot_time, p.line# line,

substr(s.text, 1,75) text

from

(select u.unit_name, d.TOTAL_OCCUR occured,

(d.TOTAL_TIME/1000000000) tot_time, d.line#

from plsql_profiler_units u, plsql_profiler_data d

where d.RUNID=u.runid and d.UNIT_NUMBER = u.unit_number

and d.TOTAL_OCCUR >0

and u.runid= &RUN_ID) p,

user_source s

where p.unit_name = s.name(+) and p.line# = s.line (+)

order by p.unit_name, p.line#;

其中的参数，run_id来自于plsql_profiler_units，可以通过我们执行PROFILER的时候使用的名称来查找刚才的测试对应的run_id，一般来说还有个更简单的查找方法，就是找最后一个run_id，因为run_id是通过sequence产生的，我们刚刚做过的测试肯定是最后一个。

UNIT_NAME OCCURED TOT_TIME LINE TEXT

--------------- ------- -------------- ----------

<anonymous> 1 .000781 4

<anonymous> 1 .009996 5

<anonymous> 1 .002121 6

TESTLOG 1 .001145 6 i:=0;

TESTLOG 50001 40.602085 8 exit when i>=N;

TESTLOG 50000 59.097742 9 i:=i+1;

TESTLOG 50000 2948.819922 10 select sm_idseq.nextval into v from dual;

PROFILER可以计算出每一行执行的次数，以及总共消耗的时间。从而可以为我们提供准确的测试数据。SEQUENCE的测试结果如下：

从测试结果来看，sequence缓冲区的增加可以提高sequence的访问性能，不过超过1000后，加大cache参数，性能提升幅度不大。目前sequence的cache已经达到了1000，再加大cache，对性能影响不大。看样子sequence的cache是不需要再加大了。

第一项测试虽然也在预料之中，不过测试结果还是让我感到有点失望。哪怕能提升2、3%也是好的啊，看样子只能寄希望于后面的测试项目了。第二项测试的是sm_histable表的核心参数，测试比对的是完全按照目前的参数创建的一张测试表，和修改了freelists,initrans,initial,next这几个参数的测试表。表的分区方式，以及存储的表空间等属性都没有修改，索引也完全按照生产环境创建。这次测试的结果让人感到十分惊诧：

调整这几个参数后，并发插入的性能居然提升了14.5%，这有点出乎我的预料。基本上达到了我预期的最高值。兴奋之余，我马上进行了HASH分区的测试，我修改了这张表的定义，将表分区从10个修改为8个，分别存储在4个表空间上，这4个表空间分别属于不同的RAID组，我并没有将5个RAID组全部使用，因为另外一个RAID组上，存放了REDO LOG文件，这么设计是为了达到REDO LOG和数据文件互相不干扰的目的。

仅这两项优化，性能总体的提升就已经达到了27%，这大大出乎了我的意料。按照这个测试结果，仅依靠这两项调整，这个项目就可以交差了。刚刚这两项测试都是在南坪系统上进行的，我又把这两项测试在人和的系统上再次进行了测试，测试结果也令人满意，在人和系统上，表核心参数的调整后性能提升了15.5%，不过第二项HASH分区的性能提升没有南坪高，只有10.27%，不过总体性能提升也接近了24%。

上午的实验时间过得很快，不知不觉已经快1点钟了，突然感觉周围静悄悄的，抬眼一看，刚才还坐了30多人的办公室里除了我就没有别人了。这个时候才感觉肚子有点饿了。为了填饱肚子，只能放下现在正在进行的实验，出去吃饭。在路上，我还在回味着刚才的实验，心情十分兴奋。突然看到路边有个麻辣烫的摊子，想想吃饭的地方离这里还挺远，不如就随便在小摊上吃点，回去继续完成实验。说实在的，我对重庆的麻辣风味还是不太习惯，吃了一小盘麻辣烫，觉得舌头都已经彻底麻木了。

回到办公室，我碰到了华为的现场工程师，我问他上午系统是否有异常，他说上午他也经常到监控台边上去看看，没有发现什么异常。听到这样的消息，我的心就更踏实了，按照我上午的测试，每次测试的时间都持续近20分钟，对主生产系统产生的影响不大，说明整个系统的处理能力是足够的。

下午的测试没有取得太多的成果，对于一个插入任务的记录数的测试，从800条一直提升到5000条，性能提升微乎其微，原本以为插入任务的记录数在这套系统中是通过参数控制的，可以不修改程序就进行调整。根据我以往的经验，一次提交的记录总数有一个最优值，一般在这个最优值之下，加大记录数，总体插入性能会有所提高，超过这个最优值，反而会逐渐下降。这个最优值是和数据库的整体情况已经参数配置、REDO LOG的性能等相关的。不过在一般情况下，这个值应该是超过1000的。看样子华为的技术人员在系统上线前已经做了充分的测试，选择了一个相对合理的值，在这一点上，我们确实没有多大的优化余地了。

于是我接着做了BULK INSERT操作的测试，根据以往的经验，BULK INSERT对于性能的提升是十分大的，由于BULK INSERT只是作为今后改进的建议，不作为本次优化的重点，因此原本考虑仅仅通过BULK INSERT的调整就达到优化目标的想法就只能作罢了。我写了一个BULK INSERT的测试脚本：

CREATE OR REPLACE PROCEDURE TESTFORALL (N INTEGER)

TYPE T_SM_ID IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER;

TYPE T_SM_SUBID IS TABLE OF NUMBER(3) INDEX BY BINARY_INTEGER;

TYPE T_ORGADDR IS TABLE OF VARCHAR2(21) INDEX BY BINARY_INTEGER;

TYPE T_DESTADDR IS TABLE OF VARCHAR2(21) INDEX BY BINARY_INTEGER;

TYPE T_ID_HINT IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER;

V_SM_ID T_SM_ID;

V_SM_SUBID T_SM_SUBID;

V_ORGADDR T_ORGADDR;

V_DESTADDR T_DESTADDR;

V_ID_HINT T_ID_HINT;

I INTEGER;

BEGIN

FOR I IN 1.. N

LOOP

V_SM_ID(I):=I;

V_SM_SUBID(I):=12;

V_ORGADDR(I):='444555565';

V_DESTADDR(I):='555555';

SELECT SM_IDSEQ.NEXTVAL INTO V_ID_HINT(I) FROM DUAL;

END LOOP;

FOR I IN 1..N LOOP

INSERT INTO SM_HISTABLE0101 (SM_ID,SM_SUBID,ORGADDR,DESTADDR,ID_HINT) VALUES

(V_SM_ID(I),V_SM_SUBID(I),V_ORGADDR(I),V_DESTADDR(I),V_ID_HINT(I));

END LOOP;

COMMIT;

FORALL I IN 1..N

INSERT INTO SM_HISTABLE0101 (SM_ID,SM_SUBID,ORGADDR,DESTADDR,ID_HINT) VALUES

(V_SM_ID(I),V_SM_SUBID(I),V_ORGADDR(I),V_DESTADDR(I),V_ID_HINT(I));

COMMIT;

END;

做完实验的时候，看看时间刚刚下午3点多钟。于是我联系了一下余经理，把实验的结果告诉了她。她一听十分高兴，建议马上开个会，如果测试的结果得到确认，明天就可以调整一下表结构，测试一下性能。

我马上把刚才的测试数据做了一个简单的PPT,赶到会议室的时候已经快4点了，余经理和华为的工程师正在会议室里等着我。看到我演示的结果，余经理和华为的工程师都感到十分兴奋。由于这套系统使用了日表，因此只要重建明天使用的表就可以完成优化动作了。为了确保安全，后天和以后的表暂时不做调整，等明天使用结果出来后再决定是否统一修改日表创建的脚本。

DBA日记 第三部 像Oracle一样思考 3月26日 简单任务 （3）令人惊讶的结果

DBA日记第三部像Oracle一样思考 3月26日简单任务（3）令人惊讶的结果