OceanBase 金融專案最佳化案例

小至尖尖發表於2024-06-12

領導讓我幫忙支援下其他專案的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運算元繼續最佳化下。😂😂😂

OceanBase 金融專案最佳化案例(union all 改寫)
2024-07-17
金融量化專案案例 -- 雙均線策略制定
2023-10-01
OceanBase金融SQL、億萬級別據量最佳化案例（Row_number 開窗 + 分頁SQL）
2023-11-07
SQL
幫任總DBA太太最佳化條金融SQL案例（DM資料庫案例）
2024-03-31
SQL資料庫
OB案例、金融行業核心系統跑批SQL最佳化
2024-10-23
行業SQL
vue專案最佳化
2023-02-23
Vue
專案效能最佳化方案
2024-06-13
專案策劃書案例
2024-07-16
flask專案之圖書案例
2018-07-16
Flask
3. 投票案例專案（合集）
2023-02-18
專案管理之風險管理案例-專案交付風險
2024-07-22
專案管理
完整的python專案例項-python完整專案
2020-10-28
Python
hadoop專案開發案例舉例
2019-01-27
Hadoop
[應用案例]OT專案-IT導彈
2019-05-11
Flask前後端分離專案案例
2020-07-24
Flask後端
union all 最佳化案例
2024-03-17
python小專案案例-開發Python專案案例，這8個庫不容錯過，功能強大效能優質
2020-11-01
Python
Android專案接入MapLibre Native的簡單案例
2024-06-27
Android
RyuBook1.0案例一:SwitchingHub專案原始碼分析
2018-08-01
原始碼
SQL最佳化案例-使用with as最佳化Subquery Unnesting（七）
2018-10-31
SQL
金融日 | 看金融機構資料安全建設典型案例
2022-09-09
金融科技行業 OKR 實用案例
2024-08-30
行業OKR
秒合約開發系統專案案例|秒合約現成案例
2023-09-18
Makefile 專案構建最佳化原理與應用
2024-11-15
真實專案案例：HeyShop品牌電商小程式
2018-05-07
常見的七種Hadoop和Spark專案案例
2018-06-27
HadoopSpark
Rollup處理並打包JS檔案專案例項
2018-05-31
JS
Profinet IO從站資料轉EtherCAT專案案例
2024-06-29
規則引擎模式的.NET開源專案案例
2022-03-01
模式
金融科技推動中國綠色金融發展：案例與展望
2021-06-22
最新開發 Thinkphp3.2 金融類專案【用錢管家】
2019-05-11
PHP
SQL最佳化案例-union代替or（九）
2018-11-26
SQL
Oracle "腦殘" CBO 最佳化案例
2024-04-24
Oracle
OB_MYSQL UPDATE 最佳化案例
2024-06-18
MySql
TDengine 使用者案例合集 | 智慧環保專案的時序資料處理難點與最佳化實踐
2023-09-21
webpack專案開發完後，如何最佳化打包速度？
2024-06-20
Web
大型金融服務集團RPA案例分享
2019-10-16
Python專案開發案例集錦及配套程式碼
2019-11-27
Python

OceanBase 金融專案最佳化案例

相關文章