Parallel execution 并行查询的性能测试(2)

多块硬盘

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

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

相关sql：

[sql] view plaincopyprint?

1. sys@ORCL> create tablespace zdry_tbs2  
2.   2  datafile 'F:\oradat\zdry_tbs2.dbf' size 2g  
3.   3  autoextend on next 1g maxsize 31g ;  
4. sys@ORCL> alter user zdrygk quota unlimitedon zdry_tbs2;  
5. User altered.  
6. CREATE TABLE sfxx2  
7. PARTITION BY HASH(sfzh)  
8. (PARTITION p1 TABLESPACE users, PARTITION p2tablespace zdry_tbs2) as  
9. select * from qbtb_zdry_sfxx where 1=2  
10. /  
11. zdrygk@ORCL> insert into /*+ append */sfxx2select * from qbtb_zdry_sfxx ;  
12. 3022273 rows created.  
13. zdrygk@ORCL> commit;  
14. Commit complete.  

查询测试

相关查询SQL：

[sql] view plaincopyprint?

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

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

分析：

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

[sql] view plaincopyprint?

1. create or replace procedure parallel_test1(sql_in in varchar2,numbers_in in number) is  
2.   n_ number;  
3.   stat_1number ;  
4.   stat_2number ;  
5. begin  
6.   stat_1:= dbms_utility.get_time ;  
7.   for iin 1 .. numbers_in loop   
8.    execute immediate sql_in into n_ ;  
9.   endloop ;  
10.   stat_2:= dbms_utility.get_time ;  
11.  dbms_output.put_line('Total Elapsed : ' || (stat_2-stat_1)/100) ;  
12.  dbms_output.put_line('Avg  Elapsed : ' || (stat_2-stat_1)/100/numbers_in) ;  
13. end  ;  
14.    
15. SQL> exec parallel_test1('select /*+no_parallel */ count(*) from sfxx2 t',10) ;  
16.    
17. Total Elapsed : 243.41  
18. Avg  Elapsed : 24.341  
19.    
20. PL/SQL procedure successfully completed  
21.    
22. SQL>  
23. SQL> exec parallel_test1('select /*+parallel(t 2) */ count(*) from sfxx2 t',10) ;  
24.    
25. Total Elapsed : 131.02  
26. Avg  Elapsed : 13.102  
27.    
28. PL/SQL procedure successfully completed  
29. SQL> exec parallel_test1('select /*+parallel(t 4) */ count(*) from sfxx2 t',10) ;  
30.    
31. Total Elapsed : 144.88  
32. Avg  Elapsed : 14.488  
33.    
34. PL/SQL procedure successfully completed  
35.    
36. SQL> exec parallel_test1('select /*+parallel(t 8) */ count(*) from sfxx2 t',10) ;  
37.    
38. Total Elapsed : 155.7  
39. Avg  Elapsed : 15.57  
40.    
41. 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