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

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

近期频频遇到层次查询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开发者。

责任编辑:未丽燕 来源: DBAplus社群
相关推荐

2018-07-03 10:49:22

性能故障排查

2021-02-26 00:46:11

CIO数据决策数字化转型

2020-12-16 10:00:59

Serverless数字化云原生

2018-01-20 20:46:33

2023-05-24 10:06:42

多云实践避坑

2022-03-04 18:11:16

信服云

2021-05-07 21:53:44

Python 程序pyinstaller

2021-02-22 17:00:31

Service Mes微服务开发

2021-05-08 12:30:03

Pythonexe代码

2020-06-12 11:03:22

Python开发工具

2021-04-28 09:26:25

公有云DTS工具

2020-08-26 07:37:25

Nacos微服务SpringBoot

2023-11-01 15:32:58

2019-04-24 17:45:24

微服务容器青云

2018-03-26 11:14:13

程序猿bug代码

2019-02-12 15:07:42

屏幕参数PC

2021-08-02 08:53:26

设计师面试求职

2020-09-13 09:19:10

LinuxPython3.6

2020-06-23 08:11:40

PyTorch模型神经网络

2020-06-19 11:20:17

开发避坑支付宝
点赞
收藏

51CTO技术栈公众号