如何解决层次查询SQL性能故障？

近期频频遇到层次查询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掉，抓取了监视报告如下：

问题点很明显，表中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，毕竟递归查询有多少层、有多少数据要裁剪，结合起来考虑，结果确实难以估量。

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

三、并行处理

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

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

作者介绍

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

数据库有很多种 类型，从最简单的存储有各种数据的 表格到能够进行海量 数据存储的大型 数据库系统都在各个方面得到了广泛的应用。