並行設定不當導致資料處理速度變慢

問題描述

系統遷移，由單節點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/，如需轉載，請註明出處，否則將追究法律責任。