基于湖库一体架构，统一管理结构化、半结构化与非结构化等多模态数据，一个系统承载事务处理、实时分析与 AI 工作负载。
划线反馈
数据库执行计划频繁跳变问题排查与解决
更新时间：2026-03-05 11:41
分享
适用版本：V4.0.x、V4.1.x、V4.2.x、V4.3.x、V4.4.x内容类型：Troubleshoot
问题现象
在 OceanBase 数据库集群中，简单的单表查询 SQL，计划淘汰频繁，不断生成新的，且倾向于选择执行时间较长的全表扫描计划。此现象在长时间观察中持续出现，即使开启了 SPM（SQL Plan Management）功能，也无法有效稳定执行计划的选择。
关键信息
如何确定 plan cache 被频繁的淘汰
执行命令 grep cache_evict_by_glitch_node' observer.log|grep T100x，如下的例子中可以看到，libarary cache 每分钟都发生了淘汰（淘汰频繁）， 根据 cache_evict_num 可以确定每次淘汰的数量。
如何查看当前租户的 plan cache 中的计划量和 memory 使用情况
下面看到每个节点的计划总量，结合上面每分钟淘汰 2700+ 的计划，计划淘汰的量/比率是比较高的。
ocp_monitor@[oceanbase]> select svr_ip, tenant_id, plan_num, mem_limit, mem_hold from oceanbase.__all_virtual_plan_cache_stat where tenant_id = 1002;
+---------------+-----------+----------+------------+------------+
| svr_ip | tenant_id | plan_num | mem_limit | mem_hold |
+---------------+-----------+----------+------------+------------+
| 10.102.xx.xxx | 1002 | 9712 | 3189013215 | 2847465472 |
| 10.102.xx.xx | 1002 | 7977 | 3189013215 | 2845835264 |
+---------------+-----------+----------+------------+------------+
2 rows in set, 2 warnings (0.019 sec)
如何查看 libarary cache（PLAN_CACHE_CTX_ID）中，各个模块的内存占用情况
其中 SqlPhyPlan 是物理计划生成相关，这个模块内存使用占比高，跟我们遇到的问题相符合，计划数量多，计划频繁淘汰。 如果其他模块的使用量高，可以多次查询内存的使用情况，动态关注内存的变化，如果持续升高，需要关注对应模式内存使用是否正常。
ocp_monitor@[oceanbase]> select * from __all_virtual_memory_info where svr_ip ='10.102.49.140' and tenant_id = 1002 and ctx_name = 'PLAN_CACHE_CTX_ID ' order by hold desc;
+-----------+---------------+----------+--------+----------------+-------------------+----------+--------+----------------+------------------+------------+------------+-------+
| tenant_id | svr_ip | svr_port | ctx_id | label | ctx_name | mod_type | mod_id | mod_name | zone | hold | used | count |
+-----------+---------------+----------+--------+----------------+-------------------+----------+--------+----------------+------------------+------------+------------+-------+
| 1002 | 10.102.xx.xxx | 2882 | 5 | SqlPhyPlan | PLAN_CACHE_CTX_ID | user | 0 | SqlPhyPlan | cn-hangzhou-i-z0 | 1516720576 | 1348859890 | 77716 |
| 1002 | 10.102.xx.xxx | 2882 | 5 | SqlPlanCache | PLAN_CACHE_CTX_ID | user | 0 | SqlPlanCache | cn-hangzhou-i-z0 | 71651648 | 62097632 | 5970 |
| 1002 | 10.102.xx.xxx | 2882 | 5 | OB_SQL_SPM_OBJ | PLAN_CACHE_CTX_ID | user | 0 | OB_SQL_SPM_OBJ | cn-hangzhou-i-z0 | 61295680 | 59399793 | 7458 |
| 1002 | 10.102.xx.xxx | 2882 | 5 | CreateContext | PLAN_CACHE_CTX_ID | user | 0 | CreateContext | cn-hangzhou-i-z0 | 34004352 | 31003968 | 15627 |
| 1002 | 10.102.xx.xxx | 2882 | 5 | SqlPhyPlObj | PLAN_CACHE_CTX_ID | user | 0 | SqlPhyPlObj | cn-hangzhou-i-z0 | 571648 | 520520 | 37 |
| 1002 | 10.102.xx.xxx | 2882 | 5 | OB_SQLSTAT_OBJ | PLAN_CACHE_CTX_ID | user | 0 | OB_SQLSTAT_OBJ | cn-hangzhou-i-z0 | 552704 | 539648 | 68 |
| 1002 | 10.102.xx.xxx | 2882 | 5 | PlanCache | PLAN_CACHE_CTX_ID | user | 0 | PlanCache | cn-hangzhou-i-z0 | 1984 | 1600 | 2 |
| 1002 | 10.102.xx.xxx | 2882 | 5 | KvstorCacheMb | PLAN_CACHE_CTX_ID | user | 0 | KvstorCacheMb | cn-hangzhou-i-z0 | 0 | 0 | 0 |
+-----------+---------------+----------+--------+----------------+-------------------+----------+--------+----------------+------------------+------------+------------+-------+
查看 PLAN_CACHE_CTX_ID 的整体内存使用情况。
ocp_monitor@[oceanbase]> select *, hold/1024/1024/1024, used/1024/1024/1024 from gv$ob_memory where tenant_id = 1002 and mod_name='SqlPhyPlan' and svr_ip = '10.102.xx.xxx';
+-----------+---------------+----------+-------------------+------------+-------+------------+------------+---------------------+---------------------+
| TENANT_ID | SVR_IP | SVR_PORT | CTX_NAME | MOD_NAME | COUNT | HOLD | USED | hold/1024/1024/1024 | used/1024/1024/1024 |
+-----------+---------------+----------+-------------------+------------+-------+------------+------------+---------------------+---------------------+
| 1002 | 10.102.xx.xxx | 2882 | PLAN_CACHE_CTX_ID | SqlPhyPlan | 78987 | 1532357120 | 1364080067 | 1.427118778229 | 1.270398559979 |
+-----------+---------------+----------+-------------------+------------+-------+------------+------------+---------------------+---------------------+
如何查看 plan cache 默认淘汰的参数
ocp_monitor@[oceanbase]> select * from __all_virtual_sys_variable where tenant_id = 1002 and name like '%plan_cache%';
+-----------+------+-------------------------------------+----------------------------+----------------------------+-----------+-------+-------------------------------------------------------------------------------------------+-------+---------+---------+
| tenant_id | zone | name | gmt_create | gmt_modified | data_type | value | info | flags | min_val | max_val |
+-----------+------+-------------------------------------+----------------------------+----------------------------+-----------+-------+-------------------------------------------------------------------------------------------+-------+---------+---------+
| 1002 | | ob_enable_plan_cache | 2024-11-20 17:07:59.575839 | 2024-11-20 17:07:59.575839 | 5 | 1 | whether use plan cache in session | 131 | | |
| 1002 | | ob_plan_cache_evict_high_percentage | 2024-11-20 17:07:59.575839 | 2024-11-20 17:07:59.575839 | 5 | 90 | memory usage percentage of plan_cache_limit at which plan cache eviction will be trigger | 129 | 0 | 100 |
| 1002 | | ob_plan_cache_evict_low_percentage | 2024-11-20 17:07:59.575839 | 2024-11-20 17:07:59.575839 | 5 | 50 | memory usage percentage of plan_cache_limit at which plan cache eviction will be stopped | 129 | 0 | 100 |
| 1002 | | ob_plan_cache_percentage | 2024-11-20 17:07:59.575839 | 2024-11-20 17:07:59.575839 | 5 | 5 | percentage of tenant memory resources that can be used by plan cache | 129 | 0 | 100 |
+-----------+------+-------------------------------------+----------------------------+----------------------------+-----------+-------+-------------------------------------------------------------------------------------------+-------+---------+---------+
4 rows in set (0.049 sec)
如何确认SPM演进没有成功
可以通过日志查看演进情况：
[log@xxxxxxxxxxxxxxx /home/admin/oceanbase/log]
$grep YBxxx-xxxxx-xxxxx-xxxxx observer.log.2025071411*
observer.log.20250714112703258:[2025-07-14 11:26:06.032045] INFO [SQL.PC] add_evolving_plan (ob_spm_evolution_plan.cpp:594) [8181][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=1] succ to add evolving plan(plan.get_plan_type()=1, plan_set_=0x7f992e7fc050, ctx.sql_ctx_.spm_ctx_.bl_key_={db_id:500014, constructed_sql:"select dcpcyclemo0_.id as id1_7_, dcpcyclemo0_.datecreated as datecrea2_7_, dcpcyclemo0_.datemodified as datemodi3_7_, dcpcyclemo0_.deleted as deleted4_7_, dcpcyclemo0_.media_out_url as media_ou5_7_, dcpcyclemo0_.media_url as media_ur6_7_, dcpcyclemo0_.media_type as media_ty7_7_, dcpcyclemo0_.moment_id as moment_i8_7_, dcpcyclemo0_.snapshort_url as snapshor9_7_, dcpcyclemo0_.sort_order as sort_or10_7_ from divx_community.dcp_cycle_moment_media dcpcyclemo0_ where (dcpcyclemo0_.moment_id in (?)) and dcpcyclemo0_.deleted=?", sql_id:"3CAE5552E2A2A242D72273B97AB24C49", format_sql:"SELECT DCPCYCLEMO0_.ID AS ID1_7_ , DCPCYCLEMO0_.DATECREATED AS DATECREA2_7_ , DCPCYCLEMO0_.DATEMODIFIED AS DATEMODI3_7_ , DCPCYCLEMO0_.DELETED AS DELETED4_7_ , DCPCYCLEMO0_.MEDIA_OUT_URL AS MEDIA_OU5_7_ , DCPCYCLEMO0_.MEDIA_URL AS MEDIA_UR6_7_ , DCPCYCLEMO0_.MEDIA_TYPE AS MEDIA_TY7_7_ , DCPCYCLEMO0_.MOMENT_ID AS MOMENT_I8_7_ , DCPCYCLEMO0_.SNAPSHORT_URL AS SNAPSHOR9_7_ , DCPCYCLEMO0_.SORT_ORDER AS SORT_OR10_7_ FROM DIVX_COMMUNITY.DCP_CYCLE_MOMENT_MEDIA DCPCYCLEMO0_ WHERE ( DCPCYCLEMO0_.MOMENT_ID IN (...) ) AND DCPCYCLEMO0_.DELETED = ? ", format_sql_id:"3C031AC4A01472FD2A1E0F9F45D0C387", namespace:9}, plan.stat_.outline_data_=/*+BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "dcpcyclemo0_"@"SEL$1" "idx_dcp_cycle_moment_media_del_mid") OPTIMIZER_FEATURES_ENABLE('4.2.5.0') END_OUTLINE_DATA*/)
observer.log.20250714112703258:[2025-07-14 11:26:06.032070] WDIAG [SQL] handle_text_query (ob_sql.cpp:2773) [8181][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=18][errcode=-5434] fail to handle physical plan(ret=-5434)
observer.log.20250714112703258:[2025-07-14 11:26:06.032081] WDIAG [SQL] stmt_query (ob_sql.cpp:227) [8181][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=10][errcode=-5434] fail to handle text query(stmt=select dcpcyclemo0_.id as id1_7_, dcpcyclemo0_.datecreated as datecrea2_7_, dcpcyclemo0_.datemodified as datemodi3_7_, dcpcyclemo0_.deleted as deleted4_7_, dcpcyclemo0_.media_out_url as media_ou5_7_, dcpcyclemo0_.media_url as media_ur6_7_, dcpcyclemo0_.media_type as media_ty7_7_, dcpcyclemo0_.moment_id as moment_i8_7_, dcpcyclemo0_.snapshort_url as snapshor9_7_, dcpcyclemo0_.sort_order as sort_or10_7_ from divx_community.dcp_cycle_moment_media dcpcyclemo0_ where (dcpcyclemo0_.moment_id in (2104803)) and dcpcyclemo0_.deleted=0, ret=-5434)
observer.log.20250714112703258:[2025-07-14 11:26:06.032100] WDIAG [SERVER] after_func (ob_query_retry_ctrl.cpp:1043) [8181][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=16][errcode=-5434] [RETRY] check if need retry(v={force_local_retry:false, stmt_retry_times:0, local_retry_times:0, err_:-5434, err_:"OB_SQL_RETRY_SPM", retry_type:1, client_ret:-5434}, need_retry=true)
observer.log.20250714112703258:[2025-07-14 11:26:06.032110] WDIAG [SERVER] do_process (obmp_query.cpp:782) [8181][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=9][errcode=-5434] run stmt_query failed, check if need retry(ret=-5434, cli_ret=-5434, retry_ctrl_.need_retry()=1, sql=select dcpcyclemo0_.id as id1_7_, dcpcyclemo0_.datecreated as datecrea2_7_, dcpcyclemo0_.datemodified as datemodi3_7_, dcpcyclemo0_.deleted as deleted4_7_, dcpcyclemo0_.media_out_url as media_ou5_7_, dcpcyclemo0_.media_url as media_ur6_7_, dcpcyclemo0_.media_type as media_ty7_7_, dcpcyclemo0_.moment_id as moment_i8_7_, dcpcyclemo0_.snapshort_url as snapshor9_7_, dcpcyclemo0_.sort_order as sort_or10_7_ from divx_community.dcp_cycle_moment_media dcpcyclemo0_ where (dcpcyclemo0_.moment_id in (2104803)) and dcpcyclemo0_.deleted=0)
observer.log.20250714112703258:[2025-07-14 11:26:06.032120] WDIAG [SERVER] do_process (obmp_query.cpp:894) [8181][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=1][errcode=-5434] try to execute again(ret=-5434, type=1, retry_type=1, timeout_remain=17999997004)
observer.log.20250714112703258:[2025-07-14 11:26:06.033065] INFO [SQL.PC] inner_add_cache_obj (ob_pcv_set.cpp:247) [8181][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=4] has identical pcv(is_same=true, pcv=0x7f992e7fa960)
observer.log.20250714112703258:[2025-07-14 11:26:06.033095] WDIAG [SQL] handle_text_query (ob_sql.cpp:2773) [8181][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=0][errcode=-5434] fail to handle physical plan(ret=-5434)
observer.log.20250714112703258:[2025-07-14 11:26:06.033104] WDIAG [SQL] stmt_query (ob_sql.cpp:227) [8181][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=8][errcode=-5434] fail to handle text query(stmt=select dcpcyclemo0_.id as id1_7_, dcpcyclemo0_.datecreated as datecrea2_7_, dcpcyclemo0_.datemodified as datemodi3_7_, dcpcyclemo0_.deleted as deleted4_7_, dcpcyclemo0_.media_out_url as media_ou5_7_, dcpcyclemo0_.media_url as media_ur6_7_, dcpcyclemo0_.media_type as media_ty7_7_, dcpcyclemo0_.moment_id as moment_i8_7_, dcpcyclemo0_.snapshort_url as snapshor9_7_, dcpcyclemo0_.sort_order as sort_or10_7_ from divx_community.dcp_cycle_moment_media dcpcyclemo0_ where (dcpcyclemo0_.moment_id in (2104803)) and dcpcyclemo0_.deleted=0, ret=-5434)
observer.log.20250714112703258:[2025-07-14 11:26:06.033110] WDIAG [SERVER] after_func (ob_query_retry_ctrl.cpp:1043) [8181][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=4][errcode=-5434] [RETRY] check if need retry(v={force_local_retry:false, stmt_retry_times:1, local_retry_times:1, err_:-5434, err_:"OB_SQL_RETRY_SPM", retry_type:1, client_ret:-5434}, need_retry=true)
observer.log.20250714112703258:[2025-07-14 11:26:06.033118] WDIAG [SERVER] do_process (obmp_query.cpp:782) [8181][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=7][errcode=-5434] run stmt_query failed, check if need retry(ret=-5434, cli_ret=-5434, retry_ctrl_.need_retry()=1, sql=select dcpcyclemo0_.id as id1_7_, dcpcyclemo0_.datecreated as datecrea2_7_, dcpcyclemo0_.datemodified as datemodi3_7_, dcpcyclemo0_.deleted as deleted4_7_, dcpcyclemo0_.media_out_url as media_ou5_7_, dcpcyclemo0_.media_url as media_ur6_7_, dcpcyclemo0_.media_type as media_ty7_7_, dcpcyclemo0_.moment_id as moment_i8_7_, dcpcyclemo0_.snapshort_url as snapshor9_7_, dcpcyclemo0_.sort_order as sort_or10_7_ from divx_community.dcp_cycle_moment_media dcpcyclemo0_ where (dcpcyclemo0_.moment_id in (2104803)) and dcpcyclemo0_.deleted=0)
observer.log.20250714112703258:[2025-07-14 11:26:06.033124] WDIAG [SERVER] do_process (obmp_query.cpp:894) [8181][T1002_L0_G0][T1002][YBxxx-xxxxx-xxxxx-xxxxx] [lt=0][errcode=-5434] try to execute again(ret=-5434, type=1, retry_type=1, timeout_remain=17999996001)
日志中 succ to add evolving plan 代表开始演进计划，fail to handle physical plan(ret=-5434) 代表开始回放基线计划，succ to add baseline plan 基线回放成功，evolution ended 演进结束时。
另外也可以通过查询 __all_virtual_spm_evo_result 表查看 SPM 演进结果。
问题原因
SPM 基线中的计划是全表扫描计划（基线生成时，表的索引并没有创建）。开启在线演进模式后， SPM 在演进期间会生成演进计划，并且将基线计划加载到内存中，之后需要完成 150 次演进，且在演进期间，要求计划始终存在于内存中。 但是由于租户的 plan cache 内存不断淘汰，计划被淘汰的速度过快，使得走索引计划未能在规定的 150 次执行期内被确认为新的基线计划，导致演进失败。失败后，使用基线中的全表扫计划，导致执行性能差。
此外，当前的 plan cache 使用 libarary cache 中的内存，随着 Oceanbase 功能的不断增强，同时存在其他模块（如 SPM、SqlPhyPlan、SQLSTAT 等）也在共用此部分内存。当内存使用达到预先设置的值时，会进行淘汰，淘汰的时候，当前是不区分哪个模块的，这也加剧了计划频繁被淘汰的情况。
问题的风险及影响
SQL 执行计划不优，可能导致查询性能下降，影响业务运行效率。
适用版本
OceanBase 数据库 V4.x 版本。
解决方法
提高 plan cache 的大小（通过调整
ob_plan_cache_percentage参数，建议从 5% 增加到7%）。此外，可以关注 plan cache 中的 SQL 形态，例如是否有大 IN 且 IN 的个数变动很大，导致生成了多条 sql_id，如果有此种情况，可以评估是否可以优化。对于基线不优的情况，手动识别并删除类似基线计划不优的 SQL 基线。
在用户场景中，若先生成了全表扫描基线而后添加索引，需密切监控索引加入后的SQL执行情况，确保经过多次演进后，基线计划能够更新为更优的索引计划，若发现基线不优，应及时删除基线。
规避方式
调整
ob_plan_cache_percentage参数，适当增加 plan cache 的大小，减少计划被淘汰的概率。在添加索引后，主动监控 SQL 执行计划的变化，及时手动干预以优化基线计划。
对于已知存在全表扫描基线的 SQL，在添加索引后，考虑手动删除旧基线，促使系统重新评估并采用更优的执行计划。
