Oracle中，使用并行的目的一般是为了能够提高语句的执行效率。但是并不是所有的语句只要加上并行就能提高效率，有时候可能会起到相反的作用。因此在使用并行的时候，首先需要查清楚，我当前数据库的瓶颈是在哪里.

(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的问题，而并非是增加并发就能够解决问题的.