领导让我帮忙支持下其他项目的SQL优化工作，呦西，是收集案例的好时候。??
下面SQL都是在不能远程的情况下，按照原SQL的逻辑等价改写完成否发给现场同事验证。
案例一
慢SQL，4.32秒：
- SELECT MY_.*, RM
- FROM (SELECT ROWNUM RM, V_.*
- FROM (SELECT *
- FROM (select count(1) processidnum,
- t.processid,
- t.proc_name_ as procname
- FROM tkdkdkdk t
- WHERE 1 = 1
- and (t.ASSIGNEE_ = 'server' or exists(select 1
- FROM pepepep p
- WHERE p.task_ = t.ID_
- and (p.agent_userid_ = 'server' or
- (substr(p.groupid_, 6) in
- (select role_code
- FROM upupupup
- WHERE user_code = 'server') or
- p.userid_ = 'server'))))
- GROUP BY t.processid, t.proc_name_)) V_
- WHERE ROWNUM <= 100000) MY_
- WHERE RM >= 1;
慢SQL执行计划：
改写优化，445ms：
- SELECT *
- FROM (SELECT *
- FROM (SELECT a.*,
- rownum rn
- FROM (SELECT count(1) processidnum,
- t.processid,
- t.proc_name_ AS procname
- FROM tkdkdkdk t
- LEFT JOIN
- (SELECT distinct p.task_
- FROM pepepep p
- LEFT JOIN
- (SELECT role_code
- FROM upupupup
- WHERE user_code = 'server'
- GROUP BY role_code) tsu
- ON (substr(p.groupid_, 6) = tsu.role_code)
- WHERE (p.agent_userid_ = 'server'
- OR (tsu.role_code is NOT null
- OR p.userid_ = 'server'))) x
- ON t.ID_ = x.task_
- WHERE 1 = 1
- AND (t.ASSIGNEE_ = 'server'
- OR x.task_ is NOT NULL)
- GROUP BY t.processid, t.proc_name_) a)
- WHERE rownum <= 100000)
- WHERE rn >= 1;
改写优化后执行计划：
优化思路：
1、原SQL有很多子查询，可能会导致计划走NL，改成JOIN后让CBO自动判断是否走HASH还是NL。
2、换了个标准的分页框架。
案例二
慢SQL，2.6秒：
- SELECT MY_.*, RM
- FROM (SELECT ROWNUM RM, V_.*
- FROM (SELECT *
- FROM (select t.*, t.org_code || '-' || t.org_name as codename
- FROM (select tc.*
- FROM tgtgtgtg tc
- start with TC.ORG_ID = '6000001'
- connect by prior ORG_ID = tc.parent_id) t
- WHERE org_level <= 3
- ORDER BY CASE
- WHEN ',' || nvl(null, 'fingard') || ',' like '%,' || ORG_ID || ',%' THEN CASE
- WHEN length(nvl(org_order, '')) = '9'
- then org_order || ''
- else '1' || org_code end
- when length(nvl(org_order, '')) = '9' then '99999999' || org_order || ''
- else '999999991' || org_code end)) V_
- WHERE ROWNUM <= 10) MY_;
- WHERE RM >= 1;
改写优化一，3.4秒：
- SELECT MY_.*, RM
- FROM (SELECT ROWNUM RM, V_.*
- FROM (SELECT *
- FROM (select a.*, a.org_code || '-' || a.org_name as codename
- FROM (WITH t(
- lv,
- codename,
- ORG_ID,
- parent_id,
- org_order,
- org_code,
- org_name,
- org_level
- ) AS (SELECT 1 as lv,
- tc.org_code || '-' || tc.org_name AS codename,
- tc.org_name,
- tc.ORG_ID,
- tc.parent_id,
- tc.org_order,
- tc.org_code,
- tc.org_level
- FROM tgtgtgtg tc
- WHERE tc.ORG_ID = '6000001'
- UNION ALL
- SELECT t.lv + 1,
- e.org_code || '-' || e.org_name AS codename,
- e.org_name,
- e.ORG_ID,
- e.parent_id,
- e.org_order,
- e.org_code,
- e.org_level
- FROM tgtgtgtg e
- INNER JOIN t ON t.ORG_ID = e.parent_id)
- SELECT *
- FROM t) a
- WHERE a.org_level <= 3
- ORDER BY CASE
- WHEN ',' || nvl(null, 'fingard') || ',' like '%,' || ORG_ID || ',%' THEN CASE
- WHEN length(nvl(org_order, '')) = '9'
- then org_order || ''
- else '1' || org_code end
- when length(nvl(org_order, '')) = '9' then '99999999' || org_order || ''
- else '999999991' || org_code end)) V_
- WHERE ROWNUM <= 10) MY_;
- WHERE RM >= 1;
使用CTE递归改写方案在PostgreSQL上是个通用的做法，也能取得比较好的性能效果。
但是在OB上反而效果更差点，NL算子性能不够强，使用NESTED-LOOP JOIN 性能反而没有NESTED-LOOP CONNECT BY 算子好。
OB研发在NESTED-LOOP JOIN算子上还有继续优化的空间。
改写优化二，1.5秒：
既然使用NL性能不够理想的情况下，就要想办法使用HASH来优化SQL整体的执行效率。
将自动递归的方式改成手动。
1、首先需要知道数据整体的层级有多少。
- SELECT DISTINCT lv
- FROM (SELECT level lv
- FROM tgtgtgtg tc
- START WITH TC.ORG_ID = '6000001'
- CONNECT BY PRIOR ORG_ID = tc.parent_id) t;
2、了解到整体的数据是13层，然后使用self join 将不同层级的数据关联起来。
- SELECT *
- FROM (SELECT *
- FROM (SELECT a.*, rownum rn
- FROM (SELECT x.*
- FROM (WITH tgtgtgtg AS
- (SELECT org_code, org_name, org_id, parent_id, org_order, org_level
- FROM tgtgtgtg)
- SELECT 1 AS lv,
- v1.org_code || '-' || v1.org_name AS codename,
- v1.ORG_ID,
- v1.parent_id,
- v1.org_order,
- v1.org_code,
- v1.org_level
- FROM tgtgtgtg v1
- WHERE v1.ORG_ID = '6000001'
-
- UNION ALL
-
- SELECT 2 AS lv,
- v2.org_code || '-' || v2.org_name AS codename,
- v2.ORG_ID,
- v2.parent_id,
- v2.org_order,
- v2.org_code,
- v2.org_level
- FROM tgtgtgtg v1
- JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
- WHERE v1.ORG_ID = '6000001'
-
- UNION ALL
-
- SELECT 3 AS lv,
- v3.org_code || '-' || v3.org_name AS codename,
- v3.ORG_ID,
- v3.parent_id,
- v3.org_order,
- v3.org_code,
- v3.org_level
- FROM tgtgtgtg v1
- JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
- JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
- WHERE v1.ORG_ID = '6000001'
-
- UNION ALL
-
- SELECT 4 AS lv,
- v4.org_code || '-' || v4.org_name AS codename,
- v4.ORG_ID,
- v4.parent_id,
- v4.org_order,
- v4.org_code,
- v4.org_level
- FROM tgtgtgtg v1
- JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
- JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
- JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
- WHERE v1.ORG_ID = '6000001'
-
- UNION ALL
-
- SELECT 5 AS lv,
- v5.org_code || '-' || v5.org_name AS codename,
- v5.ORG_ID,
- v5.parent_id,
- v5.org_order,
- v5.org_code,
- v5.org_level
- FROM tgtgtgtg v1
- JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
- JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
- JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
- JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
- WHERE v1.ORG_ID = '6000001'
-
- UNION ALL
-
- SELECT 6 AS lv,
- v6.org_code || '-' || v6.org_name AS codename,
- v6.ORG_ID,
- v6.parent_id,
- v6.org_order,
- v6.org_code,
- v6.org_level
- FROM tgtgtgtg v1
- JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
- JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
- JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
- JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
- JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
- WHERE v1.ORG_ID = '6000001'
-
- UNION ALL
-
- SELECT 7 AS lv,
- v7.org_code || '-' || v7.org_name AS codename,
- v7.ORG_ID,
- v7.parent_id,
- v7.org_order,
- v7.org_code,
- v7.org_level
- FROM tgtgtgtg v1
- JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
- JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
- JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
- JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
- JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
- JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
- WHERE v1.ORG_ID = '6000001'
-
- UNION ALL
-
- SELECT 8 AS lv,
- v8.org_code || '-' || v8.org_name AS codename,
- v8.ORG_ID,
- v8.parent_id,
- v8.org_order,
- v8.org_code,
- v8.org_level
- FROM tgtgtgtg v1
- JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
- JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
- JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
- JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
- JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
- JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
- JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id
- WHERE v1.ORG_ID = '6000001'
-
- UNION ALL
-
- SELECT 9 AS lv,
- v9.org_code || '-' || v9.org_name AS codename,
- v9.ORG_ID,
- v9.parent_id,
- v9.org_order,
- v9.org_code,
- v9.org_level
- FROM tgtgtgtg v1
- JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
- JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
- JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
- JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
- JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
- JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
- JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id
- JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id
- WHERE v1.ORG_ID = '6000001'
-
- UNION ALL
-
- SELECT 10 AS lv,
- v10.org_code || '-' || v10.org_name AS codename,
- v10.ORG_ID,
- v10.parent_id,
- v10.org_order,
- v10.org_code,
- v10.org_level
- FROM tgtgtgtg v1
- JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
- JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
- JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
- JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
- JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
- JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
- JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id
- JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id
- JOIN tgtgtgtg v10 ON v9.ORG_ID = v10.parent_id
- WHERE v1.ORG_ID = '6000001'
-
- UNION ALL
-
- SELECT 11 AS lv,
- v11.org_code || '-' || v11.org_name AS codename,
- v11.ORG_ID,
- v11.parent_id,
- v11.org_order,
- v11.org_code,
- v11.org_level
- FROM tgtgtgtg v1
- JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
- JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
- JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
- JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
- JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
- JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
- JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id
- JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id
- JOIN tgtgtgtg v10 ON v9.ORG_ID = v10.parent_id
- JOIN tgtgtgtg v11 ON v10.ORG_ID = v11.parent_id
- WHERE v1.ORG_ID = '6000001'
-
- UNION ALL
-
- SELECT 12 AS lv,
- v12.org_code || '-' || v12.org_name AS codename,
- v12.ORG_ID,
- v12.parent_id,
- v12.org_order,
- v12.org_code,
- v12.org_level
- FROM tgtgtgtg v1
- JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
- JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
- JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
- JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
- JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
- JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
- JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id
- JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id
- JOIN tgtgtgtg v10 ON v9.ORG_ID = v10.parent_id
- JOIN tgtgtgtg v11 ON v10.ORG_ID = v11.parent_id
- JOIN tgtgtgtg v12 ON v11.ORG_ID = v12.parent_id
- WHERE v1.ORG_ID = '6000001'
-
- UNION ALL
-
- SELECT 13 AS lv,
- v13.org_code || '-' || v13.org_name AS codename,
- v13.ORG_ID,
- v13.parent_id,
- v13.org_order,
- v13.org_code,
- v13.org_level
- FROM tgtgtgtg v1
- JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
- JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
- JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
- JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
- JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
- JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
- JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id
- JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id
- JOIN tgtgtgtg v10 ON v9.ORG_ID = v10.parent_id
- JOIN tgtgtgtg v11 ON v10.ORG_ID = v11.parent_id
- JOIN tgtgtgtg v12 ON v11.ORG_ID = v12.parent_id
- JOIN tgtgtgtg v13 ON v12.ORG_ID = v13.parent_id
- WHERE v1.ORG_ID = '6000001') x
- WHERE org_level <= 3
- ORDER BY CASE
- WHEN ',' || NVL(NULL, 'fingard') || ',' LIKE '%,' || ORG_ID || ',%' THEN
- CASE
- WHEN LENGTH(NVL(org_order, '')) = '9' THEN
- org_order || ''
- ELSE '1' || org_code
- END
- WHEN LENGTH(NVL(org_order, '')) = '9' THEN
- '99999999' || org_order || ''
- ELSE '999999991' || org_code END ) a)
- WHERE rownum <= 10)
- WHERE rn >= 1;
现场同学差集比较，确认改写后的SQL是等价的，执行时间从2.6秒降低到1.5秒能跑出结果。
原来18行的SQL改成了250多行后才优化了1秒的执行时间，实在没其他办法了，希望OB产研后续能CBO算子继续优化下。??????
经验首页