麒麟v10 上部署 TiDB v5.1.2 生产环境优化实践
793
2023-05-10
层次查询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小时内删除侵权内容。