Parallel execution 并行查询的性能测试

环境介绍：

 

平台

win7 64位

内存

8gb

cpu

I72630Qm 4核8线程

数据库版本

11.2.0.1

Memory target

1.6g

 

什么是并行执行

引用官方文档的说明：

Parallel execution enables the application ofmultiple CPU and I/O resources to the execution of a single database operation.It dramatically reduces response time for data-intensive operations on largedatabases typically associated with a decision support system (DSS) and datawarehouses. You can also implement parallel execution on an online transactionprocessing (OLTP) system for batch processing or schema maintenance operationssuch as index creation. Parallel execution is sometimes called parallelism. Parallelism isthe idea of breaking down a task so that, instead of one process doing all ofthe work in a query, many processes do part of the work at the same time. Anexample of this is when four processes combine to calculate the total sales fora year, each process handles one quarter of the year instead of a singleprocess handling all four quarters by itself. The improvement in performancecan be quite significant. 

 

并行执行允许应用多个cpu和io资源去执行单一的数据库操作。它可以戏剧性的减少资源集中操作的响应时间，通常应用于dss系统或者数据仓库中。当然也可以在OLTP系统中用来批量处理或者schema object的维护操作（比如说索引创建）。并行执行有时被叫做parallelism。它将一个任务分解为多个任务单元，每个进程在同一时间执行任务。比如：分4个进程去统计1年中的销售总量，每个进程可以处理1个季度的的数据，而不是单个进程独自处理4个季度的数据。

（英语一般）

并行执行的示意图

select /*+ parallel(t 2) */ count(*) fromqbtb_zdry_sfxx t group by HJDQH order by 1 ;

 

一般情况下在包含排序、分组操作的时候，slave进程的数量是dop(Degree ofParallelism)的2倍。假设有排序操作，那么第一组进程负责读取数据，第二组进程负责接收数据并且排序，若还有其他的操作，那么在第二组进程开始工作的时候，第一组进程就可以再次利用了。

如果没有排序、分组操作，那么只会分配等同于DOP数量的slave进程。

 

下面开始并行测试。

单块硬盘

相关SQL：

zdrygk@ORCL> alter session settracefile_identifier=paral ;Session altered.zdrygk@ORCL> alter session set events '10046trace name context forever , level 12 ' ;Session altered.zdrygk@ORCL> set autotrace on ;zdrygk@ORCL> set timing on

 

查询测试

相关查询SQL：

select /*+ no_parallel */ count(*) fromqbtb_zdry_sfxx ;select /*+ parallel(t 2) */ count(*) fromqbtb_zdry_sfxx t;select /*+ parallel(t 4) */ count(*) fromqbtb_zdry_sfxx t;select /*+ parallel(t 8) */ count(*) fromqbtb_zdry_sfxx t;

具体执行计划以及10046跟踪文件可以看附录1

 

 

分析：

11g中，由于表中的数据很大，所以顺序执行（串行）与多线程执行都使用directread直接路径读取数据，绕过了buffer cache。详细介绍请参见参考资料。

表中没有建立索引，所以使用count计数的时候都使用全表扫描的方式来检索数据。

如上表所示，多线程与顺序执行消耗大致相同。但是并行度越高，消耗的时间也越多。

原因有两点：

1.     因为数据在单硬盘上面，没有条带，所以在查询过程中，i/o是瓶颈。因此使用多线程无法提升性能。

2.  并行执行过程中会以serverprocess 作为query coordinator并且会为session分配slaves（pnnn）进程。qc 根据rowid range 分配工作量给slaves 进程。slaves 读取数据并且将它们放在 table queue上面，然后由slaves或者qc 进行下一步的操作。它会执行一些额外的工作，因此比顺序执行消耗更多的时间。

启用10046sql trace level 12 可以跟踪多线程中每个slave process的执行情况，也就是多了几个跟踪slave 进程的trc文件，文件名类似于orcl_p000_3812_$TRACEFILE_IDENTIFIER里面会显示具体slave进程的统计信息，下面有一个完整的多线程统计信息见附录3。

多块硬盘

因为单块硬盘使用多线程没有效果，io成为了瓶颈。这里使用了移动硬盘来分离io的压力（通常生产系统上面都是使用条带的，数据可以平均的分布到多块磁盘上，这个时候多线程能够更好的发挥其作用）。

与以上的情况有所不同，这里使用hash partition技术来平均分布每个硬盘上的数据。

相关sql：

sys@ORCL> create tablespace zdry_tbs2  2  datafile 'F:\oradat\zdry_tbs2.dbf' size 2g  3  autoextend on next 1g maxsize 31g ;sys@ORCL> alter user zdrygk quota unlimitedon zdry_tbs2;User altered.CREATE TABLE sfxx2PARTITION BY HASH(sfzh)(PARTITION p1 TABLESPACE users, PARTITION p2tablespace zdry_tbs2) asselect * from qbtb_zdry_sfxx where 1=2/zdrygk@ORCL> insert into /*+ append */sfxx2select * from qbtb_zdry_sfxx ;3022273 rows created.zdrygk@ORCL> commit;Commit complete.

查询测试

相关查询SQL：

select /*+ no_parallel */ count(*) from sfxx2 t;select /*+ parallel(t 2) */ count(*) from sfxx2t;select /*+ parallel(t 4) */ count(*) from sfxx2t;select /*+ parallel(t 8) */ count(*) from sfxx2t;

具体执行计划以及10046跟踪文件可以看附录2

分析：

如上图所示，顺序执行所消耗的时间最高。性能最高的是并行度为2的时候，因为数据只分布在2块硬盘上，所以它的效率最高。依照原理应该是并行度2以上的执行效率应该越来越低才对，但是DOP8的反而比DOP4要高，可能是因为当时cpu比较忙或者硬盘在执行其他的IO所导致，所以执行了下面的测试。每个查询执行10次，比较平均消耗的时间。

create or replace procedure parallel_test1(sql_in in varchar2,numbers_in in number) is  n_ number;  stat_1number ;  stat_2number ;begin  stat_1:= dbms_utility.get_time ;  for iin 1 .. numbers_in loop    execute immediate sql_in into n_ ;  endloop ;  stat_2:= dbms_utility.get_time ; dbms_output.put_line('Total Elapsed : ' || (stat_2-stat_1)/100) ; dbms_output.put_line('Avg  Elapsed : ' || (stat_2-stat_1)/100/numbers_in) ;end  ; SQL> exec parallel_test1('select /*+no_parallel */ count(*) from sfxx2 t',10) ; Total Elapsed : 243.41Avg  Elapsed : 24.341 PL/SQL procedure successfully completed SQL>SQL> exec parallel_test1('select /*+parallel(t 2) */ count(*) from sfxx2 t',10) ; Total Elapsed : 131.02Avg  Elapsed : 13.102 PL/SQL procedure successfully completedSQL> exec parallel_test1('select /*+parallel(t 4) */ count(*) from sfxx2 t',10) ; Total Elapsed : 144.88Avg  Elapsed : 14.488 PL/SQL procedure successfully completed SQL> exec parallel_test1('select /*+parallel(t 8) */ count(*) from sfxx2 t',10) ; Total Elapsed : 155.7Avg  Elapsed : 15.57 PL/SQL procedure successfully completed

正如上面所料。

 

您是否还有疑惑，是否使用hash partition 在单块磁盘下使用多线程也会有性能提升呢？恭喜你，你的思维已经沦落到与我保持同步了，请看附录4。

完整文档（包括附录）打包下载：

http://download.csdn.net/detail/renfengjun/4962702

附录

附录1 单块磁盘下的查询计划

附录2 多块磁盘下的查询计划

附录3 完整的多线程trace 统计

附录4 单磁盘hash partition + parallel execution

参考文档

Oracle®PerformanceSurvival Guide chapter 13 parallel sql：

http://download.csdn.net/detail/onlinedog/1798602

 

VLDB andPartitioning Guide chapter 8 Using Parallel Excution：

http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel.htm#i1009828

 

DavidDai，Oracle 11g中 Direct path reads特性说明：

http://blog.csdn.net/tianlesoftware/article/details/7690369