层次查询SQL性能故障不断？给你份可靠的避坑指南！-层次查询 oracle

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

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

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

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

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

select  xxxxx 
  from zzzz.SYS_RC_ROUTE_DETAIL t 
 where t.route_id = (select a.route_id 
                       from xxx.sys_rc_route a, xxx.g_wo_base b 
                      where a.route_id = b.route_id 
                        and b.work_order = 'yyyyyyyyy') 
 start with t.node_type = '0' 
connect by nocycle prior next_node_id = node_id

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

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

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

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

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

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

SQL> select count(*) from test1  start with id =0 connect by nocycle prior id = id2 and id = 0 ; 
 
  COUNT(*) 
---------- 
      1500 
 
Elapsed: 00:00:00.31

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

create table test2 as 
 select 
      rownum                     id, 
      rownum +1                 id2, 
      rownum + 2               id3 
 from 
     dual 
 connect by level <= 3000; 
 
 SQL> select id from test2 where id3 < 10 start with id = 3 connect by nocycle prior id2 = id; 
 
     ID 
 ---------- 
      1 
      2 
      3 
      4 
      5 
      6 
      7 
 
 7 rows selected. 
 
 SQL> select id from test2  start with id = 1 connect by nocycle prior id2 = id and id3 <10; 
 
     ID 
 ---------- 
      1 
      2 
      3 
      4 
      5 
      6 
      7 
 
 7 rows selected.

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

  SQL> select id from test2 where id3 = 10 start with id = 3 connect by nocycle prior id2 = id; 
 
    ID 
---------- 
     8 
 
Elapsed: 00:00:00.13 
 
SQL> select id from test2  start with id = 3 connect by nocycle prior id2 = id and id3=10; 
 
    ID 
---------- 
     3 
 
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)：

drop table t1; 
-- t1 with 100,000 rows 
create table t1 
as 
select 
    rownum                      id, 
    lpad(rownum, 10, '0')       v1, 
    trunc((rownum - 1)/100)     n1, 
    rpad(rownum, 100)           padding 
from 
    dual 
connect by level <= 100000 
; 
 
begin 
    dbms_stats.gather_table_stats(user,'T1'); 
end; 
/ 
 
select /*+ monitor */ 
    count(*) 
from 
( 
    select 
        CONNECT_BY_ROOT ltrim(id) root_id, 
        CONNECT_BY_ISLEAF is_leaf, 
        level as t1_level, 
        a.v1 
    from t1 a 
    start with a.id <=1000 
    connect by NOCYCLE id = prior id + 1000 
); 
 
create or replace package refcur_pkg 
AS 
    TYPE R_REC IS RECORD (row_id ROWID); 
    TYPE refcur_t IS REF CURSOR RETURN R_REC; 
END; 
/ 
 
create or replace package connect_by_parallel 
as 
   /*  Naviagates a shallow hiearchy in parallel, where we do a tree walk for each root */ 
 
    CURSOR C1 (p_rowid ROWID) IS     -- Cursor done for each subtree. This select is provided by the customer 
    select  CONNECT_BY_ROOT ltrim(id) root_id, CONNECT_BY_ISLEAF is_leaf, level as t1_level, a.v1 
          from t1 a 
          start with rowid = p_rowid 
          connect by NOCYCLE id = prior id + 1000; 
 
    TYPE T1_TAB is TABLE OF C1%ROWTYPE; 
 
    FUNCTION treeWalk (p_ref refcur_pkg.refcur_t) RETURN T1_TAB 
             PIPELINED 
    PARALLEL_ENABLE(PARTITION p_ref BY ANY); 
 
END connect_by_parallel; 
/ 
 
create or replace package body connect_by_parallel 
as  
FUNCTION treeWalk (p_ref refcur_pkg.refcur_t) RETURN T1_TAB 
          PIPELINED PARALLEL_ENABLE(PARTITION p_ref BY ANY) 
IS 
  in_rec p_ref%ROWTYPE; 
BEGIN 
   execute immediate 'alter session set "_old_connect_by_enabled"=true'; 
   LOOP -- for each root 
    FETCH p_ref INTO in_rec; 
    EXIT WHEN p_ref%NOTFOUND; 
    FOR c1rec IN c1(in_rec.row_id)  LOOP -- retrieve rows of subtree 
        PIPE ROW(c1rec); 
    END LOOP; 
  END LOOP; 
  execute immediate 'alter session set "_old_connect_by_enabled"=false';  
  RETURN; 
END  treeWalk; 
 
END connect_by_parallel; 
/ 
 
SELECT 
  /*+ monitor */ 
  COUNT(*) 
FROM TABLE(connect_by_parallel.treeWalk (CURSOR 
  (SELECT /*+ parallel (a 100) */ 
    rowid FROM t1 a WHERE id <= 100))) b;

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

作者介绍

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