OceanBase 金融项目优化案例

本文主要是介绍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 procnameFROM tkdkdkdk tWHERE 1 = 1and (t.ASSIGNEE_ = 'server' or exists(select 1FROM pepepep pWHERE p.task_ = t.ID_and (p.agent_userid_ = 'server' or(substr(p.groupid_, 6) in(select role_codeFROM upupupupWHERE user_code = 'server') orp.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 rnFROM (SELECT count(1)        processidnum,t.processid,t.proc_name_ AS procnameFROM tkdkdkdk tLEFT JOIN(SELECT distinct p.task_FROM pepepep pLEFT JOIN(SELECT role_codeFROM upupupupWHERE user_code = 'server'GROUP BY role_code) tsuON (substr(p.groupid_, 6) = tsu.role_code)WHERE (p.agent_userid_ = 'server'OR (tsu.role_code is NOT nullOR p.userid_ = 'server'))) xON t.ID_ = x.task_WHERE 1 = 1AND (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 codenameFROM (select tc.*FROM tgtgtgtg tcstart with TC.ORG_ID = '6000001'connect by prior ORG_ID = tc.parent_id) tWHERE org_level <= 3ORDER BY CASEWHEN ',' || nvl(null, 'fingard') || ',' like '%,' || ORG_ID || ',%' THEN CASEWHEN length(nvl(org_order, '')) = '9'then org_order || ''else '1' || org_code endwhen 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 codenameFROM (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_levelFROM tgtgtgtg tcWHERE tc.ORG_ID = '6000001'UNION ALLSELECT 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_levelFROM tgtgtgtg eINNER JOIN t ON t.ORG_ID = e.parent_id)SELECT *FROM t) aWHERE a.org_level <= 3ORDER BY CASEWHEN ',' || nvl(null, 'fingard') || ',' like '%,' || ORG_ID || ',%' THEN CASEWHEN length(nvl(org_order, '')) = '9'then org_order || ''else '1' || org_code endwhen 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 lvFROM tgtgtgtg tcSTART 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 rn4             FROM (SELECT x.*5                   FROM (WITH tgtgtgtg AS6                                  (SELECT org_code, org_name, org_id, parent_id, org_order, org_level7                                   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_level16                         FROM tgtgtgtg v117                         WHERE v1.ORG_ID = '6000001'18 19                         UNION ALL20 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_level28                         FROM tgtgtgtg v129                                  JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id30                         WHERE v1.ORG_ID = '6000001'31 32                         UNION ALL33 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_level41                         FROM tgtgtgtg v142                                  JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id43                                  JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id44                         WHERE v1.ORG_ID = '6000001'45 46                         UNION ALL47 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_level55                         FROM tgtgtgtg v156                                  JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id57                                  JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id58                                  JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id59                         WHERE v1.ORG_ID = '6000001'60 61                         UNION ALL62 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_level70                         FROM tgtgtgtg v171                                  JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id72                                  JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id73                                  JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id74                                  JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id75                         WHERE v1.ORG_ID = '6000001'76 77                         UNION ALL78 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_level86                         FROM tgtgtgtg v187                                  JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id88                                  JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id89                                  JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id90                                  JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id91                                  JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id92                         WHERE v1.ORG_ID = '6000001'93 94                         UNION ALL95 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算子继续优化下。😂😂😂

文章转载自：小至尖尖

原文链接：https://www.cnblogs.com/yuzhijian/p/18244465

体验地址：引迈 - JNPF快速开发平台_低代码开发平台_零代码开发平台_流程设计器_表单引擎_工作流引擎_软件架构

这篇关于OceanBase 金融项目优化案例的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---