作者简介：李亚宗，瑞蓝创数据库工程师
原创内容未经授权不得随意使用，转载请联系小编并注明来源
操作背景
生产环境OceanBase（Oracle兼容模式）存在执行计划不稳定问题，Plan Cache容易被淘汰、新生成的执行计划存在性能退化风险。为保障SQL执行计划稳定，需要将调优验证通过的SQL Plan Baseline（SPM基线） 从源租户导出，迁移至目标租户进行固化。
OceanBase兼容Oracle DBMS_SPM 包，支持通过中转临时表方式完成基线跨租户/跨集群迁移，整体流程：创建基线中转表 → 源租户打包基线至临时表 → 导出临时表数据 → 目标租户导入临时表 → 从临时表解包生成基线。
一、源租户基线导出流程
1. 查询当前租户已存在的SQL基线
obclient(SYS@ora)[SYS]> SELECT * FROM DBA_SQL_PLAN_BASELINES;
+----------------------+----------------------------------+----------------------------------------------------------------------------+----------------------+---------+-------------+---------------------+-------------+---------+------------------------------+------------------------------+------------------+------------------+---------+----------+-------+------------+-----------+----------+----------------+--------+--------+------------+--------------+----------+-------------+------------+---------------+----------------+---------+--------------------+
| SIGNATURE | SQL_HANDLE | SQL_TEXT | PLAN_NAME | CREATOR | ORIGIN | PARSING_SCHEMA_NAME | DESCRIPTION | VERSION | CREATED | LAST_MODIFIED | LAST_EXECUTED | LAST_VERIFIED | ENABLED | ACCEPTED | FIXED | REPRODUCED | AUTOPURGE | ADAPTIVE | OPTIMIZER_COST | MODULE | ACTION | EXECUTIONS | ELAPSED_TIME | CPU_TIME | BUFFER_GETS | DISK_READS | DIRECT_WRITES | ROWS_PROCESSED | FETCHES | END_OF_FETCH_COUNT |
+----------------------+----------------------------------+----------------------------------------------------------------------------+----------------------+---------+-------------+---------------------+-------------+---------+------------------------------+------------------------------+------------------+------------------+---------+----------+-------+------------+-----------+----------+----------------+--------+--------+------------+--------------+----------+-------------+------------+---------------+----------------+---------+--------------------+
| 12622318878512683009 | 7B0C04E5D41B3A5688325885C5981C74 | SELECT * FROM sales WHERE quantity>? | 12622318878512683009 | SYS | MANUAL-LOAD | NULL | NULL | 4.3.5.3 | 10-JUL-2604.28.13.011530 PM | 10-JUL-2604.28.13.011530 PM | 1783672092873574 | 1783672092873574 | YES | YES | NO | YES | YES | NO | -1 | NULL | NULL | 2 | 11678 | 11439 | NULL | NULL | NULL | NULL | NULL | NULL |
| 9957455692016257262 | D868ABCC6751FF5A51DD4CCA8849A2B7 | SELECT p.name,s.quantity FROM products p JOIN sales s ON p.id=s.product_id | 9957455692016257262 | SYS | MANUAL-LOAD | NULL | NULL | 4.3.5.3 | 10-JUL-2604.29.43.077632 PM | 10-JUL-2604.29.43.077632 PM | 1783672121079192 | 1783672121079192 | YES | YES | NO | YES | YES | NO | -1 | NULL | NULL | 1 | 15498 | 8097 | NULL | NULL | NULL | NULL | NULL | NULL |
| 392011635145358154 | FF5B0A6A018C4776B09F076275FDD727 | SELECT * FROM products | 392011635145358154 | SYS | MANUAL-LOAD | NULL | NULL | 4.3.5.3 | 10-JUL-2604.23.14.184482 PM | 10-JUL-2604.23.14.184482 PM | 1783671594499549 | 1783671594499549 | YES | YES | NO | YES | YES | NO | -1 | NULL | NULL | 3 | 3807 | 3569 | NULL | NULL | NULL | NULL | NULL | NULL |
+----------------------+----------------------------------+----------------------------------------------------------------------------+----------------------+---------+-------------+---------------------+-------------+---------+------------------------------+------------------------------+------------------+------------------+---------+----------+-------+------------+-----------+----------+----------------+--------+--------+------------+--------------+----------+-------------+------------+---------------+----------------+---------+--------------------+
3rowsinset (0.007 sec)
2. 创建基线中转临时表
使用系统存储过程生成标准化中转表 TMP_BASELINE
obclient(SYS@ora)[SYS]> call DBMS_SPM.CREATE_STGTAB_BASELINE('TMP_BASELINE');
Query OK, 0 rows affected (3.203 sec)
3. 将基线打包写入中转表
方式1：导出指定单条基线（通过plan_hash_value）
obclient(SYS@ora)[SYS]> BEGIN
DBMS_SPM.PACK_STGTAB_BASELINE(table_name=>'TMP_BASELINE',plan_hash=>392011635145358154);
END;
/
Query OK, 1 row affected (0.195 sec)
obclient(SYS@ora)[SYS]> COMMIT;
Query OK, 0 rows affected (0.010 sec)
验证打包结果：
obclient(SYS@ora)[SYS]> SELECT * FROM TMP_BASELINE;
+-------------+----------------------------------+--------------------+----------+-----------+-------------+--------+------------+-----------------------------------------------------------------------------------------------------------------------+-------+------------------+------------------+----------------+------------+--------------+----------+------------------------+------------------------+--------+--------+--------+--------+--------+--------+
| BASELINE_ID | SQL_ID | PLAN_HASH_VALUE | SYS_VARS | PLAN_TYPE | DESCRIPTION | ORIGIN | DB_VERSION | OUTLINE_DATA | FLAGS | LAST_VERIFIED | LAST_EXECUTED | OPTIMIZER_COST | EXECUTIONS | ELAPSED_TIME | CPU_TIME | ORIGIN_SQL | CONSTRUCTED_SQL | SPARE1 | SPARE2 | SPARE3 | SPARE4 | SPARE5 | SPARE6 |
+-------------+----------------------------------+--------------------+----------+-----------+-------------+--------+------------+-----------------------------------------------------------------------------------------------------------------------+-------+------------------+------------------+----------------+------------+--------------+----------+------------------------+------------------------+--------+--------+--------+--------+--------+--------+
| 1 | FF5B0A6A018C4776B09F076275FDD727 | 392011635145358154 | NULL | 1 | NULL | 2 | 4.3.5.3 | /*+BEGIN_OUTLINE_DATA FULL(@"SEL$1" "SYS"."PRODUCTS"@"SEL$1") OPTIMIZER_FEATURES_ENABLE('4.3.5.3') END_OUTLINE_DATA*/ | 27 | 1783671594499549 | 1783671594499549 | -1 | 3 | 3807 | 3569 | SELECT * FROM products | SELECT * FROM products | NULL | NULL | NULL | NULL | NULL | NULL |
+-------------+----------------------------------+--------------------+----------+-----------+-------------+--------+------------+-----------------------------------------------------------------------------------------------------------------------+-------+------------------+------------------+----------------+------------+--------------+----------+------------------------+------------------------+--------+--------+--------+--------+--------+--------+
1 row in set (0.023 sec)
方式2：导出租户内全部基线（不指定plan_hash）
obclient(SYS@ora)[SYS]> BEGIN
DBMS_SPM.PACK_STGTAB_BASELINE(table_name=>'TMP_BASELINE');
END;
/
Query OK, 1 row affected (0.219 sec)
obclient(SYS@ora)[SYS]> COMMIT;
Query OK, 0 rows affected (0.006 sec)
查看全部基线打包数据：
obclient(SYS@ora)[SYS]> SELECT * FROM TMP_BASELINE;
+-------------+----------------------------------+----------------------+----------+-----------+-------------+--------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+------------------+------------------+----------------+------------+--------------+----------+----------------------------------------------------------------------------+----------------------------------------------------------------------------+--------+--------+--------+--------+--------+--------+
| BASELINE_ID | SQL_ID | PLAN_HASH_VALUE | SYS_VARS | PLAN_TYPE | DESCRIPTION | ORIGIN | DB_VERSION | OUTLINE_DATA | FLAGS | LAST_VERIFIED | LAST_EXECUTED | OPTIMIZER_COST | EXECUTIONS | ELAPSED_TIME | CPU_TIME | ORIGIN_SQL | CONSTRUCTED_SQL | SPARE1 | SPARE2 | SPARE3 | SPARE4 | SPARE5 | SPARE6 |
+-------------+----------------------------------+----------------------+----------+-----------+-------------+--------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+------------------+------------------+----------------+------------+--------------+----------+----------------------------------------------------------------------------+----------------------------------------------------------------------------+--------+--------+--------+--------+--------+--------+
| 1 | FF5B0A6A018C4776B09F076275FDD727 | 392011635145358154 | NULL | 1 | NULL | 2 | 4.3.5.3 | /*+BEGIN_OUTLINE_DATA FULL(@"SEL$1" "SYS"."PRODUCTS"@"SEL$1") OPTIMIZER_FEATURES_ENABLE('4.3.5.3') END_OUTLINE_DATA*/ | 27 | 1783671594499549 | 1783671594499549 | -1 | 3 | 3807 | 3569 | SELECT * FROM products | SELECT * FROM products | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | 7B0C04E5D41B3A5688325885C5981C74 | 12622318878512683009 | NULL | 1 | NULL | 2 | 4.3.5.3 | /*+BEGIN_OUTLINE_DATA FULL(@"SEL$1" "SYS"."SALES"@"SEL$1") OPTIMIZER_FEATURES_ENABLE('4.3.5.3') END_OUTLINE_DATA*/ | 27 | 1783672092873574 | 1783672092873574 | -1 | 2 | 11678 | 11439 | SELECT * FROM sales WHERE quantity>? | SELECT * FROM sales WHERE quantity>? | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | D868ABCC6751FF5A51DD4CCA8849A2B7 | 9957455692016257262 | NULL | 3 | NULL | 2 | 4.3.5.3 | /*+BEGIN_OUTLINE_DATA LEADING(@"SEL$C6D21C0F" ("P"@"SEL$1" "S"@"SEL$1")) USE_HASH(@"SEL$C6D21C0F" "S"@"SEL$1") PQ_DISTRIBUTE(@"SEL$C6D21C0F" "S"@"SEL$1" LOCAL LOCAL) FULL(@"SEL$C6D21C0F" "P"@"SEL$1") FULL(@"SEL$C6D21C0F" "S"@"SEL$1") OUTER_TO_INNER(@"SEL$1") OPTIMIZER_FEATURES_ENABLE('4.3.5.3') END_OUTLINE_DATA*/ | 27 | 1783672121079192 | 1783672121079192 | -1 | 1 | 15498 | 8097 | SELECT p.name,s.quantity FROM products p JOIN sales s ON p.id=s.product_id | SELECT p.name,s.quantity FROM products p JOIN sales s ON p.id=s.product_id | NULL | NULL | NULL | NULL | NULL | NULL |
+-------------+----------------------------------+----------------------+----------+-----------+-------------+--------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+------------------+------------------+----------------+------------+--------------+----------+----------------------------------------------------------------------------+----------------------------------------------------------------------------+--------+--------+--------+--------+--------+--------+
3 rows in set (0.007 sec)
4. 使用obdumper导出中转表CSV文件
[root@observer1 bin]# ./obdumper -h 192.168.5.51 -P 2883 -u SYS -t ora -c obcluster435 -p aaAA11__ -D SYS --table TMP_BASELINE -f dumpdata/export_baseline --csv --skip-check-dir
2026-07-10 17:12:00 [INFO] Parsed args:
[--csv] true
[--file-path] dumpdata/export_baseline
[--host] 192.168.5.51
[--port] 2883
[--user] SYS
[--tenant] ora
[--cluster] obcluster435
[--password] ******
[--database] SYS
[--sys-user] root
[--table] [TMP_BASELINE]
[--skip-check-dir] true
[--column-quote-mode] NON_NUMERIC
2026-07-10 17:12:02 [INFO] Trying to establish JDBC connection to `SYS@ora#obcluster435`...
2026-07-10 17:12:02 [INFO] Server Mode: OBORACLE-4.3.5.3
2026-07-10 17:12:02 [INFO] Start executing the task of '"SYS"' database...
2026-07-10 17:12:02 [INFO] Log files will be written to dumpdata/export_baseline/logs
2026-07-10 17:12:02 [INFO] Trying to establish JDBC connection to `SYS@ora#obcluster435`...
2026-07-10 17:12:02 [INFO] Server Mode: OBORACLE-4.3.5.3
2026-07-10 17:12:03 [INFO] Manifest "dumpdata/export_baseline/.MANIFEST.bin" has been saved
2026-07-10 17:12:03 [INFO] Querying table column metadata, this might take a while...
2026-07-10 17:12:04 [INFO] Filtering out empty tables...
2026-07-10 17:12:04 [INFO] Preloading all primary keys...
2026-07-10 17:12:04 [INFO] Version: 4.3.5
2026-07-10 17:12:04 [INFO] Parallel write is set to false.
2026-07-10 17:12:04 [INFO] A total of 1 TABLE need to be dumped.
2026-07-10 17:12:08 [INFO] Query table entry for table "TMP_BASELINE" success. Elapsed: 3.399 s
2026-07-10 17:12:08 [INFO] Generating sub-tasks for table "TMP_BASELINE" by partitions and primary/unique keys...
2026-07-10 17:12:08 [INFO] Generate 1 sub-tasks for table "TMP_BASELINE" succeed. Elapsed: 57.99 ms
2026-07-10 17:12:08 [INFO] TABLE "TMP_BASELINE" is dumping, and several objects are waiting. Remain: [0/1]
2026-07-10 17:12:08 [INFO] Start up the log reporter task succeed.
2026-07-10 17:12:08 [INFO] Start 32 record dump threads for 1 dump tasks finished
2026-07-10 17:12:08 [INFO] Dump data to "dumpdata/export_baseline/data/SYS/TABLE/TMP_BASELINE.csv" finished.
2026-07-10 17:12:08 [INFO] Merge file 'dumpdata/export_baseline/data/SYS/TABLE/TMP_BASELINE.csv' of table 'TMP_BASELINE' finished. Elapsed: 12.65 ms. Remain: [0/1]
2026-07-10 17:12:09 [INFO] ---------- Finished Tasks: 1 Running Tasks: 0 Progress: 100.00% Finished File Merge Tasks: 1 Running File Merge Tasks: 0----------
2026-07-10 17:12:09 [INFO]
All Dump Tasks In Database [SYS] Finished:
----------------------------------------------------------------------------------------------------------------------------
No.# | Type | Name | Count | Status
----------------------------------------------------------------------------------------------------------------------------
1 | TABLE | TMP_BASELINE | 4 | SUCCESS
----------------------------------------------------------------------------------------------------------------------------
Total Count: 4 End Time: 2026-07-10 17:12:09
2026-07-10 17:12:09 [INFO] Dump record finished. Total Elapsed: 4.634 s
2026-07-10 17:12:09 [INFO] System exit 0
二、目标租户（test1）基线导入流程
1. 预先检查目标租户基线（初始为空）
obclient(SYS@test1)[SYS]> select * from DBA_SQL_PLAN_BASELINES;
Empty set (0.049 sec)
2. 目标租户创建相同结构中转表
obclient(SYS@test1)[SYS]> call DBMS_SPM.CREATE_STGTAB_BASELINE('TMP_BASELINE');
3. obloader 将CSV数据导入中转表
注意：中转表
BASELINE_ID为自增列，导入时配置控制文件跳过自增主键冲突
[root@observer1 bin]# ./obloader -h 172.18.8.17 -P 2881 -u SYS -t test1 -p aaAA11__ -D SYS --table TMP_BASELINE -f dumpdata/export_baseline --csv --ctl-path /root/controls/
2026-07-10 19:14:49 [INFO] Parsed args:
[--csv] true
[--ctl-path] /root/controls/
[--file-path] dumpdata/export_baseline
[--host] 172.18.8.17
[--port] 2881
[--user] SYS
[--tenant] test1
[--password] ******
[--database] SYS
[--sys-user] root
[--table] [TMP_BASELINE]
2026-07-10 19:14:51 [INFO] Trying to establish JDBC connection to `SYS@test1`...
2026-07-10 19:14:51 [INFO] Server Mode: OBORACLE-4.2.5.5
2026-07-10 19:14:51 [INFO] Start executing the task of '"SYS"' database...
2026-07-10 19:14:52 [INFO] Log files will be written to dumpdata/export_baseline/logs
2026-07-10 19:14:52 [INFO] Trying to establish JDBC connection to `SYS@test1`...
2026-07-10 19:14:52 [INFO] Server Mode: OBORACLE-4.2.5.5
2026-07-10 19:14:52 [INFO] Parse ctrl definition: "/root/controls/TMP_BASELINE.ctrl" success
2026-07-10 19:14:53 [INFO] Manifest has been saved to "dumpdata/export_baseline/.MANIFEST.bin"
2026-07-10 19:14:53 [INFO] Version: 4.3.5
2026-07-10 19:14:53 [INFO] Querying table column metadata, this might take a while...
2026-07-10 19:14:53 [INFO] Listing all matched data files in dest path...
2026-07-10 19:14:53 [INFO] Binding table: "SYS"."TMP_BASELINE" to file: "file:/root/ob-loader-dumper-4.3.5-RELEASE/bin/dumpdata/export_baseline/data/SYS/TABLE/TMP_BASELINE.csv" finished
2026-07-10 19:14:53 [INFO] Find 1 data files in: "dumpdata/export_baseline" success. Elapsed: 36.41 ms
2026-07-10 19:14:53 [INFO] Splitting data files into 64 MB logical chunks...
2026-07-10 19:14:53 [INFO] Split 1 data files to 1 logical chunks success. Elapsed: 46.03 ms
2026-07-10 19:14:53 [INFO] Querying primary/unique constraints metadata for data replacing...
2026-07-10 19:14:53 [INFO] Partition calculation flag is true
2026-07-10 19:14:53 [INFO] Query entry for table: "TMP_BASELINE" finished. Remain: [0/1]
2026-07-10 19:14:53 [INFO] Query leader location of table: "TMP_BASELINE" finished. Remain: [0/1]
2026-07-10 19:14:53 [INFO] Waiting to calculate leader for 1 sub-files....
2026-07-10 19:14:53 [INFO] Use c.l.d.SleepingWaitStrategy as available cpu(s) is 16
2026-07-10 19:14:53 [INFO] Create 4096 slots for ring buffer and leader server [172.18.8.17:2881] succeed.
2026-07-10 19:14:53 [INFO] Start 32 writers for 32 threads and leader server [172.18.8.17:2881] succeed.
2026-07-10 19:14:53 [INFO] Start 1 readers for 32 threads succeed.
2026-07-10 19:14:54 [INFO] Reset batch size [0->200] for table "TMP_BASELINE".
2026-07-10 19:14:54 [INFO]
---------------------------------------------------------------------------------------------------------
OBServerLoadThrottle:
Server: 172.18.8.17:2881 Merge Status: false Mem Used Ratio: 9% Load Mode: FAST
---------------------------------------------------------------------------------------------------------
DummyGCThrottle:
Name: G1 Young Generation GC Count: 2 GC Cost: 00:00:00.106
Name: G1 Old Generation GC Count: 0 GC Cost: 00:00:00.000
---------------------------------------------------------------------------------------------------------
RingBufferThrottle: Blocking: false
---------------------------------------------------------------------------------------------------------
MemoryThrottle: [HEAP_MEM: 51.6 MB/4 GB], [NON_HEAP_MEM: 53.8 MB/488 MB], Block Possible: 0 %
---------------------------------------------------------------------------------------------------------
TpsThrottle: [Tps: 2147483647], Blocking: false
---------------------------------------------------------------------------------------------------------
2026-07-10 19:14:54 [INFO] File: "/root/ob-loader-dumper-4.3.5-RELEASE/bin/dumpdata/export_baseline/data/SYS/TABLE/TMP_BASELINE.csv" has been read finished.
2026-07-10 19:14:54 [INFO] Block Threads: 0
---------------------------------------------------------------------------------------------------------
ThreadSubmitTpsThrottle: [Tps: 1.0000], Blocking: false
---------------------------------------------------------------------------------------------------------
ThreadSubmitMemThrottle: [HEAP_MEM: 51.6 MB/4 GB], [NON_HEAP_MEM: 54.0 MB/488 MB], Block Possible: 0 %
---------------------------------------------------------------------------------------------------------
2026-07-10 19:14:55 [INFO] ---------- Finished Tasks: 1 Running Tasks: 0 Progress: 100.00% ----------
2026-07-10 19:14:55 [INFO]
All Load Tasks In Database [SYS] Finished:
----------------------------------------------------------------------------------------------------------------------------
No.# | Type | Name | Count | Status
----------------------------------------------------------------------------------------------------------------------------
1 | TABLE | TMP_BASELINE | 4 -> 4 | SUCCESS
----------------------------------------------------------------------------------------------------------------------------
Total Count: 4 End Time: 2026-07-10 19:14:55
2026-07-10 19:14:55 [INFO] Load record finished. Total Elapsed: 2.032 s
2026-07-10 19:14:55 [INFO] System exit 0
4. 从临时表解包，生成正式SQL基线
obclient(SYS@test1)[SYS]> call DBMS_SPM.UNPACK_STGTAB_BASELINE('TMP_BASELINE');
Query OK, 0 rows affected (0.021 sec)
5. 结果验证
执行 SELECT * FROM DBA_SQL_PLAN_BASELINES;，可以查看到基线已经成功导入目标租户，基线生效。
三、重要注意事项（补充）
-
版本兼容风险：本次迁移源4.3.5.3 → 目标4.2.5.5，跨小版本。OUTLINE中包含 OPTIMIZER_FEATURES_ENABLE版本标记，低版本数据库可能无法识别部分hint，导入后务必压测验证SQL性能。 -
Schema适配：基线内表名默认为原租户Schema，如果源、目标租户表不在同一Schema，基线可能失效，需要改写SQL或者调整表访问方式。 -
基线状态检查：导入后重点确认： ENABLED=YES、ACCEPTED=YES，只有同时满足两个条件，优化器才会优先选用该基线计划。 -
迁移完成清理：验证基线正常生效后，建议删除两端 TMP_BASELINE中转临时表，避免残留元数据。 -
替代方案对比：除SPM基线迁移外，也可通过 CREATE OUTLINE固化计划；但SPM基线支持批量迁移，适合大批量SQL治理场景。
END
瑞蓝创 OceanBase OBCP V4 精英训练营
点击下方图片立即了解详情
▼ 点击「阅读原文」，了解更多产品技术文章