在全面評估後再使用Oracle並行

Oracle中，使用並行的目的一般是為了能夠提高語句的執行效率。但是並不是所有的語句只要加上並行就能提高效率，有時候可能會起到相反的作用。因此在使用並行的時候，首先需要查清楚，我當前資料庫的瓶頸是在哪裡.

[@more@]下面是兩個使用並行後語句執行效率降低的例子.

(1)並行查詢表的記錄數
SQL>
SQL> select count(1) from xx_test;

COUNT(1)
----------
13926144

已用時間: 00: 00: 24.16
SQL>

可以看到不加併發的情況下，執行查詢語句的時間是24s

SQL> select event from v$session_wait where sid in (select sid from v$session where username='CHARSI');

EVENT
----------------------------------------------------------------
db file scattered read

SQL> select event from v$session_wait where sid in (select sid from v$session where username='CHARSI');

EVENT
----------------------------------------------------------------
db file sequential read

SQL> select event from v$session_wait where sid in (select sid from v$session where username='CHARSI');

上面是從另外一個視窗看到的那個查詢的等待事件(該資料庫是作為測試資料庫，只是我自己測試用的，因此根據session的使用者就只查到一條.)

增加兩個併發，如下：
SQL> select /*+ parallel(t,2) */ count(1) from xx_test t;

COUNT(1)
----------
13926144

已用時間: 00: 00: 24.70

SQL> select event from v$session_wait where sid in (select sid from v$session where username='CHARSI');

EVENT
----------------------------------------------------------------
direct path read
direct path read
PX Deq: Execute Reply

可以看到,兩個併發的執行效率並沒有提高;

使用4個併發,執行如下：
SQL> select /*+ parallel(t,4) */ count(1) from xx_test t;

COUNT(1)
----------
13926144

已用時間: 00: 00: 23.27
SQL>
SQL>

SQL> select event from v$session_wait where sid in (select sid from v$session where username='CHARSI');

EVENT
----------------------------------------------------------------
direct path read
direct path read
direct path read
direct path read
PX Deq: Execute Reply

可以看到，當併發提高到4個的時候，也只是稍有一點提高，也沒有顯著的提升.

再看看八個併發的情況：
SQL> select /*+ parallel(t,8) */ count(1) from xx_test t;

COUNT(1)
----------
13926144

已用時間: 00: 00: 36.67
SQL>

SQL> select event from v$session_wait where sid in (select sid from v$session where username='CHARSI');

EVENT
----------------------------------------------------------------
direct path read
direct path read
direct path read
direct path read
direct path read
direct path read
direct path read
direct path read
PX Deq: Execute Reply

可以看到，當併發提高到八個的時候，查詢的效率反倒降低了.

(2)執行表分析
使用dbms_stats.gather_table_stats進行表分析,設定其中degree的值來控制表分析的併發數.
degree引數預設的都是表的併發度.
SQL> select owner,table_name,degree from dba_tables where table_name='XX_TEST';

OWNER TABLE_NAME DEGREE
---------- ---------- --------------------
CHARSI XX_TEST 1

表的預設並行度為1,即沒有並行.

SQL> exec dbms_stats.gather_table_stats('CHARSI','XX_TEST');

PL/SQL 過程已成功完成。

已用時間: 00: 00: 38.79
SQL>

SQL> select event from v$session_wait where sid in (select sid from v$session where username='CHARSI');

EVENT
----------------------------------------------------------------
db file scattered read

沒有加並行執行表分析永樂38秒鐘;

我們來增加並行進行測試:
SQL> exec dbms_stats.gather_table_stats('CHARSI','XX_TEST',degree=>2);

PL/SQL 過程已成功完成。

已用時間: 00: 00: 57.11
SQL>

SQL> select event from v$session_wait where sid in (select sid from v$session where username='CHARSI');

EVENT
----------------------------------------------------------------
direct path read
db file sequential read
PX Deq: Execute Reply

SQL> select event from v$session_wait where sid in (select sid from v$session where username='CHARSI');

EVENT
----------------------------------------------------------------
db file sequential read
db file sequential read
PX Deq: Execute Reply

SQL> select event from v$session_wait where sid in (select sid from v$session where username='CHARSI');

EVENT
----------------------------------------------------------------
PX Deq: Execution Msg
PX Deq: Execution Msg
direct path read
db file sequential read
PX Deq: Execute Reply

SQL>
SQL> select event from v$session_wait where sid in (select sid from v$session where username='CHARSI');

EVENT
----------------------------------------------------------------
PX Deq: Execution Msg
PX Deq: Execution Msg
direct path read
direct path read
PX Deq: Execute Reply

我們可以看到，使用兩個併發的時候，花費的時間為57秒，效率反倒降低了。
另外附上在執行過程中的等待事件，有興趣的時候可以研究一下.

如果是4個併發的情況呢:
SQL> exec dbms_stats.gather_table_stats('CHARSI','XX_TEST',degree=>4);

PL/SQL 過程已成功完成。

已用時間: 00: 00: 57.58

SQL> select event from v$session_wait where sid in (select sid from v$session where username='CHARSI');

EVENT
----------------------------------------------------------------
db file sequential read
db file sequential read
db file sequential read
db file sequential read
PX Deq: Execute Reply

SQL> select event from v$session_wait where sid in (select sid from v$session where username='CHARSI');

EVENT
----------------------------------------------------------------
PX Deq: Execution Msg
db file sequential read
PX Deq: Execution Msg
db file sequential read
PX Deq: Execute Reply

SQL>
SQL>
SQL> select event from v$session_wait where sid in (select sid from v$session where username='CHARSI');

EVENT
----------------------------------------------------------------
PX Deq: Execution Msg
PX Deq: Execution Msg
PX Deq: Execution Msg
db file sequential read
PX Deq: Execution Msg
direct path read
db file sequential read
db file sequential read
PX Deq: Execute Reply

可以看到，表分析的時候，在這種情況下，如果加大併發的話，效率反倒降低了.

所以，執行SQL效率低的原因可能有很多,需要對症下藥，並不是對語句增加併發就能夠提高效率。比如上面的測試中，我們可以看到等待事件中都有"direct path read"的等待,可能等待的原因是因為IO的問題，需要解決IO的問題，而並非是增加併發就能夠解決問題的.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23850820/viewspace-1041505/，如需轉載，請註明出處，否則將追究法律責任。