作者介绍
蒋健，云趣网络科技联合创始人，Oracle ACE，11g OCM，多年Oracle设计、管理及实施经验，精通数据库优化，Oracle CBO及并行原理。云趣鹰眼监控核心设计和开发者，资深Python Web开发者。
近期频频遇到层次查询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
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
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
COUNT(*)
FROM TABLE(connect_by_parallel.treeWalk (CURSOR
(SELECT
rowid FROM t1 a WHERE id <= 100))) b;
层次查询的SQL在整个SQL优化场景中占比相对较小，但这种类型的SQL优化却往往比较麻烦，本文分享的三个案例均为实战中总结，对于Oracle层次查询的SQL优化有极大的借鉴意义，特别是陈焕生提供的做并行的案例，含金量很高，感兴趣的童鞋可以测试下。
掌握数据库新思维，获得更优操作启发
不妨来DAMS学点独家技能
↓↓扫码可了解更多详情及报名↓↓
2019 DAMS 中国数据智能管理峰会-上海站