--- title: 数据库执行计划频繁跳变问题排查与解决-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 数据库执行计划频繁跳变问题排查与解决相关的常见问题和使用技巧，帮助您快速解决 数据库执行计划频繁跳变问题排查与解决的难题。 image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # 数据库执行计划频繁跳变问题排查与解决 更新时间：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+ 的计划，计划淘汰的量/比率是比较高的。 ```sql 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 是物理计划生成相关，这个模块内存使用占比高，跟我们遇到的问题相符合，计划数量多，计划频繁淘汰。 如果其他模块的使用量高，可以多次查询内存的使用情况，动态关注内存的变化，如果持续升高，需要关注对应模式内存使用是否正常。 ```sql 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 的整体内存使用情况。 ```sql 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 默认淘汰的参数 ```sql 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演进没有成功 可以通过日志查看演进情况： ```shell [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 版本。 ## 解决方法 1. 提高 plan cache 的大小（通过调整 `ob_plan_cache_percentage` 参数，建议从 5% 增加到7%）。此外，可以关注 plan cache 中的 SQL 形态，例如是否有大 IN 且 IN 的个数变动很大，导致生成了多条 sql_id，如果有此种情况，可以评估是否可以优化。 2. 对于基线不优的情况，手动识别并删除类似基线计划不优的 SQL 基线。 3. 在用户场景中，若先生成了全表扫描基线而后添加索引，需密切监控索引加入后的SQL执行情况，确保经过多次演进后，基线计划能够更新为更优的索引计划，若发现基线不优，应及时删除基线。 ## 规避方式 1. 调整 `ob_plan_cache_percentage` 参数，适当增加 plan cache 的大小，减少计划被淘汰的概率。 2. 在添加索引后，主动监控 SQL 执行计划的变化，及时手动干预以优化基线计划。 3. 对于已知存在全表扫描基线的 SQL，在添加索引后，考虑手动删除旧基线，促使系统重新评估并采用更优的执行计划。 上一篇 [SPJ where 子查询的 where 条件中包含两参数的 substr 函数，做条件单调性判断时触发 core](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002396427) 下一篇 [SQL 包含多个 union all 执行耗时慢的原因和解决方法](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000003131916) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线