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

层次查询SQL性能故障不断?给你份可靠的避坑指南!

近期频频遇到层次查询SQL的性能问题,结合历史故障案例,汇总了一些场景connect by常见的性能故障类型,在本文中做个分享。

作者:蒋健来源:DBAplus社群|2019-06-06 14:21

近期频频遇到层次查询SQL的性能问题,结合历史故障案例,汇总了一些场景connect by常见的性能故障类型,在本文中做个分享。

一、结果中过滤or生成树中过滤

过滤条件放置于where后,为在结果树生成完成后裁剪叶子节点;放置于connect by后,为在生成树的过程中裁剪子树。

频繁发生的现象是业务逻辑上其实并不需要先生成结果树再去过滤,由于开发人员对过滤条件放置于不同的位置(where 后,connect by后)产生的过滤效果混淆,导致了低效的性能。

下面这个SQL就是典型案例。用户反馈,zzzz.SYS_RC_ROUTE_DETAIL表上生产环境就3000+条数据,但SQL语句运行时却跑不出来结果:

  1. select  xxxxx 
  2.   from zzzz.SYS_RC_ROUTE_DETAIL t 
  3.  where t.route_id = (select a.route_id 
  4.                        from xxx.sys_rc_route a, xxx.g_wo_base b 
  5.                       where a.route_id = b.route_id 
  6.                         and b.work_order = 'yyyyyyyyy'
  7.  start with t.node_type = '0' 
  8. connect by nocycle prior next_node_id = node_id 

让客户运行了SQL一分钟后cancel掉,抓取了监视报告如下:

层次查询SQL性能故障不断?给你份可靠的避坑指南!

问题点很明显,表中nextnodeid = node_id的重复值很多,导致了海量的结果集。SQL运行的一分钟内,connect by尚未把完整的树生产完成,就已经有了3000W+数据,于是我们开始思考,在逻辑上是否有必要在构建完整的树后再过滤。

与业务部门沟通后,发现果然不需要。

以下数据可以测试下,3000行数据量,但是count(*) 会非常慢。

  1. SQL> create table test1 as 
  2. select 
  3.     mod(rownum,2)                     id, 
  4.     mod(rownum +1 ,2)                  id2 
  5. from 
  6.     dual 
  7. connect by level <= 3000 
  8. ;  2    3    4    5    6    7    8 
  9.  
  10. Table created. 
  11.  
  12. SQL> set timing on 
  13. SQL> select count(*) from test1  where id =0  start with id =0 connect by nocycle prior id = id2 ; 
  14.  
  15.   COUNT(*) 
  16. ---------- 
  17.       1500 
  18.  
  19. Elapsed: 00:09:26.88 
  20. SQL> 

结果中过滤如上所示,用了9分钟;而生成树中过滤则只用0.3s:

  1. SQL> select count(*) from test1  start with id =0 connect by nocycle prior id = id2 and id = 0 ; 
  2.  
  3.   COUNT(*) 
  4. ---------- 
  5.       1500 
  6.  
  7. Elapsed: 00:00:00.31 

很多情况下,两种写法的结果集可能是相同的,如下:

  1. create table test2 as 
  2.  select 
  3.       rownum                     id, 
  4.       rownum +1                 id2, 
  5.       rownum + 2               id3 
  6.  from 
  7.      dual 
  8.  connect by level <= 3000; 
  9.  
  10.  SQL> select id from test2 where id3 < 10 start with id = 3 connect by nocycle prior id2 = id; 
  11.  
  12.      ID 
  13.  ---------- 
  14.       1 
  15.       2 
  16.       3 
  17.       4 
  18.       5 
  19.       6 
  20.       7 
  21.  
  22.  7 rows selected. 
  23.  
  24.  SQL> select id from test2  start with id = 1 connect by nocycle prior id2 = id and id3 <10; 
  25.  
  26.      ID 
  27.  ---------- 
  28.       1 
  29.       2 
  30.       3 
  31.       4 
  32.       5 
  33.       6 
  34.       7 
  35.  
  36.  7 rows selected. 

但其实这两种写法在语义上差别很大,结果集也可能不相同,如下:

  1.   SQL> select id from test2 where id3 = 10 start with id = 3 connect by nocycle prior id2 = id; 
  2.  
  3.     ID 
  4. ---------- 
  5.      8 
  6.  
  7. Elapsed: 00:00:00.13 
  8.  
  9. SQL> select id from test2  start with id = 3 connect by nocycle prior id2 = id and id3=10; 
  10.  
  11.     ID 
  12. ---------- 
  13.      3 
  14.  
  15. Elapsed: 00:00:00.00 

二、CBO估算不准确

层次查询的SQL语句频繁出现的问题,就是CBO估算返回结果集偏差,引起执行计划不准确。虽然表上收集过统计信息,但是CBO对于结果集的估算跟实际值偏差非常大(几百上千的倍的差距),但是这个也不能全怪CBO,毕竟递归查询有多少层、有多少数据要裁剪,结合起来考虑,结果确实难以估量。

层次查询SQL性能故障不断?给你份可靠的避坑指南!

层次查询SQL性能故障不断?给你份可靠的避坑指南!

对于CBO估算不准的问题,我们考虑了对结果集相对特殊的参数,在SQL文本上做区分,应用识别特殊参数运行带hint地改造SQL,通过hint来指定返回结果集。这种情况不同于普通的数据倾斜,无法通过baseline给出一个不涉及应用改造的方案。

层次查询SQL性能故障不断?给你份可靠的避坑指南!

三、并行处理

层次查询的SQL直接使用parallel的hint,会遭遇并行串行化的问题,也就是不能真正并行。对于一些重要且耗时长的层次查询,可以考虑PIPELINED TABLE FUNCTION改写SQL的方式来实现。

以下脚本测试参考了陈焕生童鞋的blog以及oracle相关文档(Doc ID 2168864.1):

  1. drop table t1; 
  2. -- t1 with 100,000 rows 
  3. create table t1 
  4. as 
  5. select 
  6.     rownum                      id, 
  7.     lpad(rownum, 10, '0')       v1, 
  8.     trunc((rownum - 1)/100)     n1, 
  9.     rpad(rownum, 100)           padding 
  10. from 
  11.     dual 
  12. connect by level <= 100000 
  13.  
  14. begin 
  15.     dbms_stats.gather_table_stats(user,'T1'); 
  16. end
  17.  
  18. select /*+ monitor */ 
  19.     count(*) 
  20. from 
  21.     select 
  22.         CONNECT_BY_ROOT ltrim(id) root_id, 
  23.         CONNECT_BY_ISLEAF is_leaf, 
  24.         level as t1_level, 
  25.         a.v1 
  26.     from t1 a 
  27.     start with a.id <=1000 
  28.     connect by NOCYCLE id = prior id + 1000 
  29. ); 
  30.  
  31. create or replace package refcur_pkg 
  32. AS 
  33.     TYPE R_REC IS RECORD (row_id ROWID); 
  34.     TYPE refcur_t IS REF CURSOR RETURN R_REC; 
  35. END
  36.  
  37. create or replace package connect_by_parallel 
  38. as 
  39.    /*  Naviagates a shallow hiearchy in parallel, where we do a tree walk for each root */ 
  40.  
  41.     CURSOR C1 (p_rowid ROWID) IS     -- Cursor done for each subtree. This select is provided by the customer 
  42.     select  CONNECT_BY_ROOT ltrim(id) root_id, CONNECT_BY_ISLEAF is_leaf, level as t1_level, a.v1 
  43.           from t1 a 
  44.           start with rowid = p_rowid 
  45.           connect by NOCYCLE id = prior id + 1000; 
  46.  
  47.     TYPE T1_TAB is TABLE OF C1%ROWTYPE; 
  48.  
  49.     FUNCTION treeWalk (p_ref refcur_pkg.refcur_t) RETURN T1_TAB 
  50.              PIPELINED 
  51.     PARALLEL_ENABLE(PARTITION p_ref BY ANY); 
  52.  
  53. END connect_by_parallel; 
  54.  
  55. create or replace package body connect_by_parallel 
  56. as  
  57. FUNCTION treeWalk (p_ref refcur_pkg.refcur_t) RETURN T1_TAB 
  58.           PIPELINED PARALLEL_ENABLE(PARTITION p_ref BY ANY
  59. IS 
  60.   in_rec p_ref%ROWTYPE; 
  61. BEGIN 
  62.    execute immediate 'alter session set "_old_connect_by_enabled"=true'
  63.    LOOP -- for each root 
  64.     FETCH p_ref INTO in_rec; 
  65.     EXIT WHEN p_ref%NOTFOUND; 
  66.     FOR c1rec IN c1(in_rec.row_id)  LOOP -- retrieve rows of subtree 
  67.         PIPE ROW(c1rec); 
  68.     END LOOP; 
  69.   END LOOP; 
  70.   execute immediate 'alter session set "_old_connect_by_enabled"=false';  
  71.   RETURN
  72. END  treeWalk; 
  73.  
  74. END connect_by_parallel; 
  75.  
  76. SELECT 
  77.   /*+ monitor */ 
  78.   COUNT(*) 
  79. FROM TABLE(connect_by_parallel.treeWalk (CURSOR 
  80.   (SELECT /*+ parallel (a 100) */ 
  81.     rowid FROM t1 a WHERE id <= 100))) b; 

层次查询的SQL在整个SQL优化场景中占比相对较小,但这种类型的SQL优化却往往比较麻烦,本文分享的三个案例均为实战中总结,对于Oracle层次查询的SQL优化有极大的借鉴意义,特别是陈焕生提供的做并行的案例,含金量很高,感兴趣的童鞋可以测试下。

作者介绍

蒋健,云趣网络科技联合创始人,Oracle ACE,11g OCM,多年Oracle设计、管理及实施经验,精通数据库优化,Oracle CBO及并行原理。云趣鹰眼监控核心设计和开发者,资深Python Web开发者。

【编辑推荐】

  1. 10个简单步骤,完全理解SQL
  2. MySQL索引原理与应用:索引类型,存储结构与锁
  3. Python+SQL无敌组合,值得你Pick
  4. SQL和Python的集合操作对比:适合的就是***的!
  5. MySQL亿级数据数据库优化方案测试-银行交易流水记录的查询
【责任编辑:未丽燕 TEL:(010)68476606】

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

订阅专栏+更多

这就是5G

这就是5G

5G那些事儿
共15章 | armmay

118人订阅学习

16招轻松掌握PPT技巧

16招轻松掌握PPT技巧

GET职场加薪技能
共16章 | 晒书包

371人订阅学习

20个局域网建设改造案例

20个局域网建设改造案例

网络搭建技巧
共20章 | 捷哥CCIE

762人订阅学习

读 书 +更多

SQL Server 2005数据库管理与应用高手修炼指南

全书分为基础篇、高级篇和应用篇3个部分,共18章,有重点、分层次地讲解SQL Server 2005的基础知识、高级使用技巧和项目应用方法。第1~10...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微