--- title: SQL 中存在大量 union 语句在生成执行计划时占用内存过大-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 SQL 中存在大量 union 语句在生成执行计划时占用内存过大相关的常见问题和使用技巧，帮助您快速解决 SQL 中存在大量 union 语句在生成执行计划时占用内存过大的难题。 --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # SQL 中存在大量 union 语句在生成执行计划时占用内存过大 更新时间：2025-05-07 12:27 适用版本： V3.1.x、V3.2.x 内容类型：Troubleshoot ## 问题描述 在 OceanBase 数据库中，执行一条 SQL 语句，该 SQL 包含了 100 多个 UNION 操作。在一个拥有 100GB 内存的租户中执行该 SQL 时，无法成功执行，而且执行 explain 该 SQL 时，消耗的内存超过了40GB。 1. 执行 explain sql 期间内存上涨模块为 SqlExecutor，信息如下。 ```shell +---------------------+-------------+---------------------+-------------------+ | now() | svr_ip | mod_name | system_memory_sum | +---------------------+-------------+---------------------+-------------------+ | xxxx-xx-xx xx:xx:xx | xx.xx.xx.xx | SqlExecutor | 41282844160 | | xxxx-xx-xx xx:xx:xx | xx.xx.xx.xx | OB_KVSTORE_CACHE_MB | 2260729856 | | xxxx-xx-xx xx:xx:xx | xx.xx.xx.xx | OB_SQL_PHY_PLAN | 587104704 | | xxxx-xx-xx xx:xx:xx | xx.xx.xx.xx | MysqlRequesReco | 496923648 | | xxxx-xx-xx xx:xx:xx | xx.xx.xx.xx | OB_MEMSTORE | 289336320 | | xxxx-xx-xx xx:xx:xx | xx.xx.xx.xx | CostBasedRewrit | 218931008 | | xxxx-xx-xx xx:xx:xx | xx.xx.xx.xx | SqlPsCache | 148446528 | | xxxx-xx-xx xx:xx:xx | xx.xx.xx.xx | TransAudit | 104857600 | | xxxx-xx-xx xx:xx:xx | xx.xx.xx.xx | PartitLogServic | 64995840 | | xxxx-xx-xx xx:xx:xx | xx.xx.xx.xx | ReplayStatus | 54807104 | | xxxx-xx-xx xx:xx:xx | xx.xx.xx.xx | PRE_CALC_EXPR | 50018240 | | xxxx-xx-xx xx:xx:xx | xx.xx.xx.xx | OB_KVSTORE_CACHE | 41932800 | | xxxx-xx-xx xx:xx:xx | xx.xx.xx.xx | Election | 23063040 | | xxxx-xx-xx xx:xx:xx | xx.xx.xx.xx | OB_SQL_PLAN_CACHE | 19755328 | | xxxx-xx-xx xx:xx:xx | xx.xx.xx.xx | PartTranCtxMgr | 11220352 | | 2023-05-17 17:04:27 | xx.xx.xx.xx | HashNodePsCache | 11160576 | | xxxx-xx-xx xx:xx:xx | xx.xx.xx.xx | MemtableCallbac | 9709056 | | xxxx-xx-xx xx:xx:xx | xx.xx.xx.xx | HashNodePsInfo | 8362800 | | xxxx-xx-xx xx:xx:xx | xx.xx.xx.xx | LogAggreBuffer | 7321600 | | xxxx-xx-xx xx:xx:xx | xx.xx.xx.xx | SqlDtl | 6722560 | +---------------------+-------------+---------------------+-------------------+ ``` 2. explain SQL 的 Memleak 信息。 ```shell oceanbase::common::parray(char*, long, long*, int) oceanbase::common::ObMemLeakChecker::on_alloc(oceanbase::lib::AObject&) oceanbase::common::ObAllocator::alloc(long, oceanbase::lib::ObMemAttr const&) oceanbase::common::PageArena::alloc_new_page(long) oceanbase::common::PageArena::alloc_aligned(long, long) int oceanbase::sql::ObStmtFactory::create_stmt(oceanbase::sql::ObSelectStmt*&) oceanbase::sql::ObSelectStmt::deep_copy_stmt_struct(oceanbase::sql::ObStmtFactory&, oceanbase::sql::ObRawExprFactory&, oceanbase::sql::ObDMLStmt const&) oceanbase::sql::ObDMLStmt::deep_copy(oceanbase::sql::ObStmtFactory&, oceanbase::sql::ObRawExprFactory&, oceanbase::sql::ObDMLStmt const&) oceanbase::sql::TableItem::deep_copy(oceanbase::sql::ObStmtFactory&, oceanbase::sql::ObRawExprFactory&, oceanbase::sql::TableItem const&) oceanbase::sql::ObTransformUtils::deep_copy_table_items(oceanbase::sql::ObStmtFactory&, oceanbase::sql::ObRawExprFactory&, oceanbase::common::ObIArray const&, oceanbase::common::ObIArray&) oceanbase::sql::ObDMLStmt::deep_copy_stmt_struct(oceanbase::sql::ObStmtFactory&, oceanbase::sql::ObRawExprFactory&, oceanbase::sql::ObDMLStmt const&) oceanbase::sql::ObSelectStmt::deep_copy_stmt_struct(oceanbase::sql::ObStmtFactory&, oceanbase::sql::ObRawExprFactory&, oceanbase::sql::ObDMLStmt const&) oceanbase::sql::ObDMLStmt::deep_copy(oceanbase::sql::ObStmtFactory&, oceanbase::sql::ObRawExprFactory&, oceanbase::sql::ObDMLStmt const&) oceanbase::sql::ObTransformUtils::deep_copy_stmt(oceanbase::sql::ObStmtFactory&, oceanbase::sql::ObRawExprFactory&, oceanbase::sql::ObDMLStmt const*, oceanbase::sql::ObDMLStmt*&) oceanbase::sql::ObSelectStmt::deep_copy_stmt_struct(oceanbase::sql::ObStmtFactory&, oceanbase::sql::ObRawExprFactory&, oceanbase::sql::ObDMLStmt const&) oceanbase::sql::ObDMLStmt::deep_copy(oceanbase::sql::ObStmtFactory&, oceanbase::sql::ObRawExprFactory&, oceanbase::sql::ObDMLStmt const&) oceanbase::sql::ObTransformUtils::deep_copy_stmt(oceanbase::sql::ObStmtFactory&, oceanbase::sql::ObRawExprFactory&, oceanbase::sql::ObDMLStmt const*, oceanbase::sql::ObDMLStmt*&) oceanbase::sql::ObTransformRule::evaluate_cost(oceanbase::common::ObIArray&, oceanbase::sql::ObDMLStmt*&, double&, bool&, void*) oceanbase::sql::ObTransformRule::accept_transform(oceanbase::common::ObIArray&, oceanbase::sql::ObDMLStmt*&, oceanbase::sql::ObDMLStmt*, bool&, void*) oceanbase::sql::ObTransformGroupByPlacement::transform_groupby_push_down(oceanbase::common::ObIArray&, oceanbase::sql::ObDMLStmt*&, bool&) oceanbase::sql::ObTransformGroupByPlacement::transform_one_stmt(oceanbase::common::ObIArray&, oceanbase::sql::ObDMLStmt*&, bool&) oceanbase::sql::ObTransformRule::transform_self(oceanbase::common::ObIArray&, oceanbase::sql::ObDMLStmt*&) oceanbase::sql::ObTransformRule::transform_post_order(oceanbase::common::ObIArray&, long, oceanbase::sql::ObDMLStmt*&) oceanbase::sql::ObTransformRule::transform_stmt_recursively(oceanbase::common::ObIArray&, long, oceanbase::sql::ObDMLStmt*&) ?? ??:0 oceanbase::sql::ObTransformRule::transform_post_order(oceanbase::common::ObIArray&, long, oceanbase::sql::ObDMLStmt*&) oceanbase::sql::ObTransformRule::transform_stmt_recursively(oceanbase::common::ObIArray&, long, oceanbase::sql::ObDMLStmt*&) ?? ??:0 oceanbase::sql::ObTransformRule::transform_post_order(oceanbase::common::ObIArray&, long, oceanbase::sql::ObDMLStmt*&) oceanbase::sql::ObTransformRule::transform_stmt_recursively(oceanbase::common::ObIArray&, long, oceanbase::sql::ObDMLStmt*&) ?? ??:0 oceanbase::sql::ObTransformRule::transform_post_order(oceanbase::common::ObIArray&, long, oceanbase::sql::ObDMLStmt*&) oceanbase::sql::ObTransformRule::transform_stmt_recursively(oceanbase::common::ObIArray&, long, oceanbase::sql::ObDMLStmt*&) ?? ??:0 oceanbase::sql::ObTransformRule::transform_post_order(oceanbase::common::ObIArray&, long, oceanbase::sql::ObDMLStmt*&) oceanbase::sql::ObTransformRule::transform_stmt_recursively(oceanbase::common::ObIArray&, long, oceanbase::sql::ObDMLStmt*&) oceanbase::sql::ObTransformRule::transform(oceanbase::sql::ObDMLStmt*&, unsigned long&) oceanbase::sql::ObTransformerImpl::transform_rule_set_in_one_iteration(oceanbase::sql::ObDMLStmt*&, unsigned long, bool&) oceanbase::sql::ObTransformerImpl::transform_rule_set(oceanbase::sql::ObDMLStmt*&, unsigned long, long) oceanbase::sql::ObTransformerImpl::do_transform(oceanbase::sql::ObDMLStmt*&) oceanbase::sql::ObTransformerImpl::transform(oceanbase::sql::ObDMLStmt*&) oceanbase::sql::ObSql::transform_stmt(oceanbase::sql::ObSqlSchemaGuard*, oceanbase::share::ObIPartitionLocationCache*, oceanbase::storage::ObPartitionService*, oceanbase::common::ObStatManager*, oceanbase::common::ObOptStatManager*, oceanbase::common::ObAddr*, long, oceanbase::sql::ObPhysicalPlan*, oceanbase::sql::ObExecContext&, oceanbase::sql::ObDMLStmt*&) oceanbase::sql::ObSql::generate_physical_plan(ParseResult&, oceanbase::sql::ObPlanCacheCtx*, oceanbase::sql::ObSqlCtx&, oceanbase::sql::ObResultSet&, bool, bool) oceanbase::sql::ObSql::handle_physical_plan(oceanbase::common::ObString const&, oceanbase::sql::ObSqlCtx&, oceanbase::sql::ObResultSet&, oceanbase::sql::ObPlanCacheCtx&, int, bool) oceanbase::sql::ObSql::handle_text_query(oceanbase::common::ObString const&, oceanbase::sql::ObSqlCtx&, oceanbase::sql::ObResultSet&) oceanbase::observer::ObMPQuery::do_process(oceanbase::sql::ObSQLSessionInfo&, bool, bool, bool&, bool&) oceanbase::observer::ObMPQuery::process() oceanbase::rpc::frame::ObSqlProcessor::run() oceanbase::omt::ObWorkerProcessor::process_one(oceanbase::rpc::ObRequest&) oceanbase::omt::ObWorkerProcessor::process(oceanbase::rpc::ObRequest&) oceanbase::omt::ObThWorker::process_request(oceanbase::rpc::ObRequest&) ``` ## 适用版本 OceanBase 数据库企业版 V3.2.3 BP6 (oceanbase-3.2.3.3-106000102022111521) 及之前版本。 ## 问题原因 由于触发代价改写时，代价验证会进行深拷贝整个 stmt，导致内存消耗较大。 ## 解决方法 ### 解决方法 升级至问题已修复版本。目前已修复的版本包括 OceanBase 数据库企业版 V3.2.3 BP7 (oceanbase-3.2.3.3-107000092023011911) 及之后版本、V3.2.4 BP1 (oceanbase-3.2.4.1-101000052023010822) 及之后版本。 ### 应急方法 使用 OceanBase 数据库的 hint 提示 /+ NO_REWRITE/ 禁止改写。 上一篇 [libobcdc.log 内报错 tenant memory leak 的原因及解决方法](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000003237502) 下一篇 [SQL 执行占用内存过大](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000262283) ![有帮助](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) 咨询热线