OceanBase 金融项目优化案例

领导让我帮忙支持下其他项目的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 将不同层级的数据关联起来。

  1 SELECT *
  2 FROM (SELECT *
  3       FROM (SELECT a.*, rownum rn
  4             FROM (SELECT x.*
  5                   FROM (WITH tgtgtgtg AS
  6                                  (SELECT org_code, org_name, org_id, parent_id, org_order, org_level
  7                                   FROM tgtgtgtg)
  8 
  9                         SELECT 1                                 AS lv,
 10                                v1.org_code || '-' || v1.org_name AS codename,
 11                                v1.ORG_ID,
 12                                v1.parent_id,
 13                                v1.org_order,
 14                                v1.org_code,
 15                                v1.org_level
 16                         FROM tgtgtgtg v1
 17                         WHERE v1.ORG_ID = '6000001'
 18 
 19                         UNION ALL
 20 
 21                         SELECT 2                                 AS lv,
 22                                v2.org_code || '-' || v2.org_name AS codename,
 23                                v2.ORG_ID,
 24                                v2.parent_id,
 25                                v2.org_order,
 26                                v2.org_code,
 27                                v2.org_level
 28                         FROM tgtgtgtg v1
 29                                  JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
 30                         WHERE v1.ORG_ID = '6000001'
 31 
 32                         UNION ALL
 33 
 34                         SELECT 3                                 AS lv,
 35                                v3.org_code || '-' || v3.org_name AS codename,
 36                                v3.ORG_ID,
 37                                v3.parent_id,
 38                                v3.org_order,
 39                                v3.org_code,
 40                                v3.org_level
 41                         FROM tgtgtgtg v1
 42                                  JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
 43                                  JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
 44                         WHERE v1.ORG_ID = '6000001'
 45 
 46                         UNION ALL
 47 
 48                         SELECT 4                                 AS lv,
 49                                v4.org_code || '-' || v4.org_name AS codename,
 50                                v4.ORG_ID,
 51                                v4.parent_id,
 52                                v4.org_order,
 53                                v4.org_code,
 54                                v4.org_level
 55                         FROM tgtgtgtg v1
 56                                  JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
 57                                  JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
 58                                  JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
 59                         WHERE v1.ORG_ID = '6000001'
 60 
 61                         UNION ALL
 62 
 63                         SELECT 5                                 AS lv,
 64                                v5.org_code || '-' || v5.org_name AS codename,
 65                                v5.ORG_ID,
 66                                v5.parent_id,
 67                                v5.org_order,
 68                                v5.org_code,
 69                                v5.org_level
 70                         FROM tgtgtgtg v1
 71                                  JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
 72                                  JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
 73                                  JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
 74                                  JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
 75                         WHERE v1.ORG_ID = '6000001'
 76 
 77                         UNION ALL
 78 
 79                         SELECT 6                                 AS lv,
 80                                v6.org_code || '-' || v6.org_name AS codename,
 81                                v6.ORG_ID,
 82                                v6.parent_id,
 83                                v6.org_order,
 84                                v6.org_code,
 85                                v6.org_level
 86                         FROM tgtgtgtg v1
 87                                  JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
 88                                  JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
 89                                  JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
 90                                  JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
 91                                  JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
 92                         WHERE v1.ORG_ID = '6000001'
 93 
 94                         UNION ALL
 95 
 96                         SELECT 7                                 AS lv,
 97                                v7.org_code || '-' || v7.org_name AS codename,
 98                                v7.ORG_ID,
 99                                v7.parent_id,
100                                v7.org_order,
101                                v7.org_code,
102                                v7.org_level
103                         FROM tgtgtgtg v1
104                                  JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
105                                  JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
106                                  JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
107                                  JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
108                                  JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
109                                  JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
110                         WHERE v1.ORG_ID = '6000001'
111 
112                         UNION ALL
113 
114                         SELECT 8                                 AS lv,
115                                v8.org_code || '-' || v8.org_name AS codename,
116                                v8.ORG_ID,
117                                v8.parent_id,
118                                v8.org_order,
119                                v8.org_code,
120                                v8.org_level
121                         FROM tgtgtgtg v1
122                                  JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
123                                  JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
124                                  JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
125                                  JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
126                                  JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
127                                  JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
128                                  JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id
129                         WHERE v1.ORG_ID = '6000001'
130 
131                         UNION ALL
132 
133                         SELECT 9                                 AS lv,
134                                v9.org_code || '-' || v9.org_name AS codename,
135                                v9.ORG_ID,
136                                v9.parent_id,
137                                v9.org_order,
138                                v9.org_code,
139                                v9.org_level
140                         FROM tgtgtgtg v1
141                                  JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
142                                  JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
143                                  JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
144                                  JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
145                                  JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
146                                  JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
147                                  JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id
148                                  JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id
149                         WHERE v1.ORG_ID = '6000001'
150 
151                         UNION ALL
152 
153                         SELECT 10                                  AS lv,
154                                v10.org_code || '-' || v10.org_name AS codename,
155                                v10.ORG_ID,
156                                v10.parent_id,
157                                v10.org_order,
158                                v10.org_code,
159                                v10.org_level
160                         FROM tgtgtgtg v1
161                                  JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
162                                  JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
163                                  JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
164                                  JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
165                                  JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
166                                  JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
167                                  JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id
168                                  JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id
169                                  JOIN tgtgtgtg v10 ON v9.ORG_ID = v10.parent_id
170                         WHERE v1.ORG_ID = '6000001'
171 
172                         UNION ALL
173 
174                         SELECT 11                                  AS lv,
175                                v11.org_code || '-' || v11.org_name AS codename,
176                                v11.ORG_ID,
177                                v11.parent_id,
178                                v11.org_order,
179                                v11.org_code,
180                                v11.org_level
181                         FROM tgtgtgtg v1
182                                  JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
183                                  JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
184                                  JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
185                                  JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
186                                  JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
187                                  JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
188                                  JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id
189                                  JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id
190                                  JOIN tgtgtgtg v10 ON v9.ORG_ID = v10.parent_id
191                                  JOIN tgtgtgtg v11 ON v10.ORG_ID = v11.parent_id
192                         WHERE v1.ORG_ID = '6000001'
193 
194                         UNION ALL
195 
196                         SELECT 12                                  AS lv,
197                                v12.org_code || '-' || v12.org_name AS codename,
198                                v12.ORG_ID,
199                                v12.parent_id,
200                                v12.org_order,
201                                v12.org_code,
202                                v12.org_level
203                         FROM tgtgtgtg v1
204                                  JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
205                                  JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
206                                  JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
207                                  JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
208                                  JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
209                                  JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
210                                  JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id
211                                  JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id
212                                  JOIN tgtgtgtg v10 ON v9.ORG_ID = v10.parent_id
213                                  JOIN tgtgtgtg v11 ON v10.ORG_ID = v11.parent_id
214                                  JOIN tgtgtgtg v12 ON v11.ORG_ID = v12.parent_id
215                         WHERE v1.ORG_ID = '6000001'
216 
217                         UNION ALL
218 
219                         SELECT 13                                  AS lv,
220                                v13.org_code || '-' || v13.org_name AS codename,
221                                v13.ORG_ID,
222                                v13.parent_id,
223                                v13.org_order,
224                                v13.org_code,
225                                v13.org_level
226                         FROM tgtgtgtg v1
227                                  JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
228                                  JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
229                                  JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
230                                  JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
231                                  JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
232                                  JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
233                                  JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id
234                                  JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id
235                                  JOIN tgtgtgtg v10 ON v9.ORG_ID = v10.parent_id
236                                  JOIN tgtgtgtg v11 ON v10.ORG_ID = v11.parent_id
237                                  JOIN tgtgtgtg v12 ON v11.ORG_ID = v12.parent_id
238                                  JOIN tgtgtgtg v13 ON v12.ORG_ID = v13.parent_id
239                         WHERE v1.ORG_ID = '6000001') x
240                   WHERE org_level <= 3
241                   ORDER BY CASE
242                                WHEN ',' || NVL(NULL, 'fingard') || ',' LIKE '%,' || ORG_ID || ',%' THEN
243                                    CASE
244                                        WHEN LENGTH(NVL(org_order, '')) = '9' THEN
245                                            org_order || ''
246                                        ELSE '1' || org_code
247                                        END
248                                WHEN LENGTH(NVL(org_order, '')) = '9' THEN
249                                    '99999999' || org_order || ''
250                                ELSE '999999991' || org_code END ) a)
251       WHERE rownum <= 10)
252 WHERE rn >= 1;

现场同学差集比较，确认改写后的SQL是等价的，执行时间从2.6秒降低到1.5秒能跑出结果。

原来18行的SQL改成了250多行后才优化了1秒的执行时间，实在没其他办法了，希望OB产研后续能CBO算子继续优化下。😂😂😂

__EOF__

本文作者： 小至尖尖SQL优化空间
本文链接： https://www.cnblogs.com/yuzhijian/p/18244465
关于博主： I am a good person!
版权声明： 本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主： 如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。