oracle并行查询一列的实现

在oracle数据库中碰到SQL执行时间过长。根本无法得到结果集的问题。服务器压力也没有很高，估计又是一个非常消耗磁盘的查询。通过oracle并行查询一列的方法，解决了这个问题。

果然，发现是一个200w的表和一个超过1100w表的HASH JOIN .
简单的帮助优化了一个SQL后，SQL如下:

    select     count(ui.usin_uid_fk)  
     from table1 av, table2 ui  
where av.av_usse_activatedate >= to_date('20090102', 'yyyymmdd')  
     and av.av_usse_activatedate < to_date('20090401', 'yyyymmdd')  
     and av.av_usse_uid_fk = ui.usin_uid_fk  
     and ui.usin_mcnc_fk =XXX%' 

不难想象执行的不是很理想。近20分钟的执行时间，真是让人崩溃。

COUNT(UI.USIN_UID_FK)  
---------------------  
 1918591  
 
Elapsed: 00:19:03.07  
Statistics  
----------------------------------------------------------  
0     recursive calls  
0     db block gets  
     32921639     consistent gets  
  352073     physical reads  
0     redo size  
   395     bytes sent via SQL*Net to client  
   503     bytes received via SQL*Net from client  
2     SQL*Net roundtrips to/from client  
0     sorts (memory)  
0     sorts (disk)  
1     rows processed  

对于那张TABLE2的大表（符合条件的超过1100w），决定试图通过并行来提高执行速度。SQL如下：

select /*+parallel (tbl_userinfo 4)*/ count(ui.usin_uid_fk)  
from table1 av, table2 ui  
where av.av_usse_activatedate >= to_date('20090101', 'yyyymmdd')  
and av.av_usse_activatedate < to_date('20090401', 'yyyymmdd')  
and av.av_usse_uid_fk = ui.usin_uid_fk  
and ui.usin_mcnc_fk like 'XXX%'; 

执行效果还是非常明显的。从19分钟多到1分45秒！其中consistent gets更是减少了一个数量级。
    

 COUNT(UI.USIN_UID_FK)  
---------------------  
 1918591  
 
Elapsed: 00:01:45.15  
 
Statistics  
----------------------------------------------------------  
0     recursive calls  
0     db block gets  
 2571109     consistent gets  
  124523     physical reads  
0     redo size  
   395     bytes sent via SQL*Net to client  
   504     bytes received via SQL*Net from client  
2     SQL*Net roundtrips to/from client  
0     sorts (memory)  
0     sorts (disk)  
1     rows processed  

因为这个服务器为2×4核心的cpu，应该可以算是8个CPU，所以应该可以通过增加并行度来进一步减少执行时间。如下SQL：

    SQL> select /*+parallel (tbl_userinfo 8)*/ count(ui.usin_uid_fk)  
     2  from table1 av, table2 ui  
     3     where av.av_usse_activatedate >= to_date('20090101', 'yyyymmdd')  
     4  and av.av_usse_activatedate < to_date('20090401', 'yyyymmdd')  
     5  and av.av_usse_uid_fk = ui.usin_uid_fk  
     6  and ui.usin_mcnc_fk like '460%';  
 
COUNT(UI.USIN_UID_FK)  
---------------------  
 1949033  
 
Elapsed: 00:00:20.60  
 
Statistics  
----------------------------------------------------------  
0     recursive calls  
0     db block gets  
  2607524     consistent gets  
      55050     physical reads  
0     redo size  
   395     bytes sent via SQL*Net to client  
   503     bytes received via SQL*Net from client  
2     SQL*Net roundtrips to/from client  
0     sorts (memory)  
0     sorts (disk)  
1     rows processed  

可以说还是比较理想的。只有20S左右了。虽然最大并行度可以到CPU*2，但是效果未必会好。进一步做一个16个并行度的SQL执行测试。

      COUNT(UI.USIN_UID_FK)  
---------------------  
 1949033  
 
Elapsed: 00:00:20.64  
 
Statistics  
----------------------------------------------------------  
0     recursive calls  
0     db block gets  
 2607524     consistent gets  
      55299     physical reads  
0     redo size  
   395     bytes sent via SQL*Net to client  
   504     bytes received via SQL*Net from client  
2     SQL*Net roundtrips to/from client  
0     sorts (memory)  
0     sorts (disk)  
1     rows processed        
 

没有任何提高，并且执行时间还稍高于并行度为8的SQL。

通过以上测试我们不难发现：

在处理大量数据查询，例如出现HASH JOIN的情况下，oracle并行非常有效果的。也就是说并行查询在数据仓库这样的应用中会“大显身手”。

但是oracle并行的使用还是有很多限制的。例如相对较小的数据查询和连接是会适得其反的。盲目增加并行度也是大忌，相对来讲，并行度和CPU数相同比较好。这里的CPU数应该是指的核心数。例如服务器中有一个CPU是4核心的，并行度为4是好的。

技术很难有十全十美的，最重要的是对于特定技术的使用要恰到好处，保证扬长避短。
 

【编辑推荐】

ORACLE ROWNUM语句的使用

Oracle索引的类型

创建Oracle索引的方法

C#连接Oracle数据库查询数据

使用oracle存储过程分页的实例