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

蒋健 2019-06-06 14:06:07
作者介绍

蒋健,云趣网络科技联合创始人,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 /*+ 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优化有极大的借鉴意义,特别是陈焕生提供的做并行的案例,含金量很高,感兴趣的童鞋可以测试下。

活动预告