并行设置不当导致数据处理速度变慢

问题描述

系统迁移，由单节点4cpu服务器迁移到双节点rac，32颗cpu系统中，迁移后对特定数据新服务器的处理速度反而不如老服务器。

问题分析

出现两侧代码相同但执行结果相差很大的原因是表SAVE_POINT_CACHE并行度的设置

表SAVE_POINT_CACHE中数据非常少，只有几十条

在系统中表SAVE_POINT_CACHE的并行度设置为default

根据oracle对并行度的算法

Default = parallel_threads_per_cpu * cpu_count

 

 

我们系统中两边的设置都是如下的情况

NAME                                 TYPE        VALUE

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

parallel_threads_per_cpu             integer     2

 

也就是每个cpu的并行度为2

老系统中系统共有4颗cpu

 

prtdiag -v结果片段

========================= CPUs

           Run   E$  CPU    CPU 

Brd  CPU   MHz   MB Impl.   Mask

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

 A  0, 16 1500 32.0 US-IV+   2.4

 B  1, 17 1500 32.0 US-IV+   2.4

 A  2, 18 1500 32.0 US-IV+   2.4

 B  3, 19 1500 32.0 US-IV+   2.4

并行度的结果就是4*2=8

 

Rac新系统中每个服务器有32颗cpu，

并行度的结果就是2*32=64

 

在并行度设置为default情况下测试代码结果如下：

 

老系统时间为26.89秒

SQL> set timing on

SQL> BEGIN

  2    insert into save_point_cache values('dummytask_a',sysdate, 1);

  3    insert into save_point_cache values('begin_a',sysdate, 1);

  4    commit;

  5 

  6    FOR lc IN 1 .. 1000

  7    LOOP

  8      SAVE_POINT.SAVE_TASK ( 'dummytask_a', sysdate, lc );

  9      COMMIT;

 10    END LOOP;

 11 

 12    insert into save_point_cache values('end_a',sysdate, 1);

 13    commit;

 14  END;

 15  /

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:26.89

 

Rac新系统时间为4:32.17

SQL> set timing on

SQL> BEGIN

  2    insert into save_point_cache values('dummytask_a',sysdate, 1);

  3    insert into save_point_cache values('begin_a',sysdate, 1);

  4    commit;

  5 

  6    FOR lc IN 1 .. 1000

  7    LOOP

  8      SAVE_POINT.SAVE_TASK ( 'dummytask_a', sysdate, lc );

  9      COMMIT;

 10    END LOOP;

 11 

 12    insert into save_point_cache values('end_a',sysdate, 1);

 13    commit;

 14  END;

 15  /

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:04:32.17

 

并且在代码运行期间主要的等待事件为

PX Deq Credit: send blkd

PX Deq: reap credit

 

对于这两个等待事件，oracle的解释如下：

 

PX Deq: reap credit

Waiting Process: Slave

This wait event indicates that we are doing a non-blocking test to see if any channel has returned a message. We should see that although there is a high number of these events, the time accumulated to it should be zero (or really low).

 

 

Tips to Reduce Waits for "PX DEQ CREDIT SEND BLKD" at Database Level

The wait events  "PX Deq Credit: need buffer"  and  "PX Deq Credit: send blkd" are occur when data or  messages are exchanged between process that are part of a px query..

 

There are at least 3 different main area's that can cause this waits.

 

We see high waits if a lot of data and message are exchanged between parallel processes. The cause can be that the execution plan is bad or there are problem with the parallel execution setup.

There is a problem with the resource like the CPU or the interconnect. As example with a CPU utilization around 100% the process are limited by the CPU and can not send the data fast enough.

If parallel queries are hang where one process waits for "PX Deq Credit: need buffer" as example.

 

出现问题的主要原因是并行处理sql过程中cpu之间数据交换等待导致处理速度变慢

 

处理方法：

关闭表save_point_cache的并行

在两边同时关闭表的并行重新测试

 

SQL> alter table save_point_cache noparallel;

Table altered.

 

98老系统用时1.60秒

SQL> set timing on

SQL> BEGIN

  2    insert into save_point_cache values('dummytask_a',sysdate, 1);

  3    insert into save_point_cache values('begin_a',sysdate, 1);

  4    commit;

  5 

  6    FOR lc IN 1 .. 1000

  7    LOOP

  8      SAVE_POINT.SAVE_TASK ( 'dummytask_a', sysdate, lc );

  9      COMMIT;

 10    END LOOP;

 11 

 12    insert into save_point_cache values('end_a',sysdate, 1);

 13    commit;

 14  END;

 15  /

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:01.60

 

Rac新系统用时1.14秒

SQL> set timing on

SQL> BEGIN

  2    insert into save_point_cache values('dummytask_a',sysdate, 1);

  3    insert into save_point_cache values('begin_a',sysdate, 1);

  4    commit;

  5 

  6    FOR lc IN 1 .. 1000

  7    LOOP

  8      SAVE_POINT.SAVE_TASK ( 'dummytask_a', sysdate, lc );

  9      COMMIT;

 10    END LOOP;

 11 

 12    insert into save_point_cache values('end_a',sysdate, 1);

 13    commit;

 14  END;

 15  /

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:01.14

 

速度都非常理想

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13177610/viewspace-680373/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13177610/viewspace-680373/