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

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

近期频频遇到层次查询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以及***相关文档(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优化却往往比较麻烦，本文分享的三个案例均为实战中总结，对于***层次查询的SQL优化有极大的借鉴意义，特别是陈焕生提供的做并行的案例，含金量很高，感兴趣的童鞋可以测试下。

作者介绍

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。