|
|
51CTO旗下网站
|
|
移动端

oracle并行查询一列的实现

在使用oracle数据库的过程中,有时会遇到SQL执行时间过长的问题,下文中使用oracle并行查询的方法,轻松解决了该问题。

作者:佚名来源:互联网|2010-10-27 13:16

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

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

  1.     select     count(ui.usin_uid_fk)  
  2.      from table1 av, table2 ui  
  3. where av.av_usse_activatedate >= to_date('20090102', '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 =XXX%' 

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

  1. COUNT(UI.USIN_UID_FK)  
  2. ---------------------  
  3.  1918591  
  4.  
  5. Elapsed: 00:19:03.07  
  6. Statistics  
  7. ----------------------------------------------------------  
  8. 0     recursive calls  
  9. 0     db block gets  
  10.      32921639     consistent gets  
  11.   352073     physical reads  
  12. 0     redo size  
  13.    395     bytes sent via SQL*Net to client  
  14.    503     bytes received via SQL*Net from client  
  15. 2     SQL*Net roundtrips to/from client  
  16. 0     sorts (memory)  
  17. 0     sorts (disk)  
  18. 1     rows processed  

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

  1. select /*+parallel (tbl_userinfo 4)*/ 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 'XXX%'; 

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

  1.  COUNT(UI.USIN_UID_FK)  
  2. ---------------------  
  3.  1918591  
  4.  
  5. Elapsed: 00:01:45.15  
  6.  
  7. Statistics  
  8. ----------------------------------------------------------  
  9. 0     recursive calls  
  10. 0     db block gets  
  11.  2571109     consistent gets  
  12.   124523     physical reads  
  13. 0     redo size  
  14.    395     bytes sent via SQL*Net to client  
  15.    504     bytes received via SQL*Net from client  
  16. 2     SQL*Net roundtrips to/from client  
  17. 0     sorts (memory)  
  18. 0     sorts (disk)  
  19. 1     rows processed  

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

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


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

  1.       COUNT(UI.USIN_UID_FK)  
  2. ---------------------  
  3.  1949033  
  4.  
  5. Elapsed: 00:00:20.64  
  6.  
  7. Statistics  
  8. ----------------------------------------------------------  
  9. 0     recursive calls  
  10. 0     db block gets  
  11.  2607524     consistent gets  
  12.       55299     physical reads  
  13. 0     redo size  
  14.    395     bytes sent via SQL*Net to client  
  15.    504     bytes received via SQL*Net from client  
  16. 2     SQL*Net roundtrips to/from client  
  17. 0     sorts (memory)  
  18. 0     sorts (disk)  
  19. 1     rows processed        
  20.  

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

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

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

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

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

【编辑推荐】

ORACLE ROWNUM语句的使用

Oracle索引的类型

创建Oracle索引的方法

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

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

【责任编辑:段燃 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

Jenkins Pipeline可持续化集成

Jenkins Pipeline可持续化集成

优化运维流水线
共3章 | youerning

82人订阅学习

笑熬浆糊之职场那些事

笑熬浆糊之职场那些事

IT人的职场心法
共22章 | Bear_Boss

66人订阅学习

Redis运维秘籍

Redis运维秘籍

运维标配技术
共15章 | one叶孤舟

146人订阅学习

读 书 +更多

超级网管员——网络管理

本书全面深入地介绍了在网络的日常管理中,网络管理员必须掌握的知识,包括系统基本管理、性能调优、故障恢复、域控制器管理、存储资源管理...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO播客