SQL 中存在大量 union 语句在生成执行计划时占用内存过大

问题描述

在 OceanBase 数据库中，执行一条 SQL 语句，该 SQL 包含了 100 多个 UNION 操作。在一个拥有 100GB 内存的租户中执行该 SQL 时，无法成功执行，而且执行 explain 该 SQL 时，消耗的内存超过了40GB。

1. 执行 explain sql 期间内存上涨模块为 SqlExecutor，信息如下。

    +---------------------+-------------+---------------------+-------------------+
   
    | 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 信息。

    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<char, oceanbase::common::ModulePageAllocator>::alloc_new_page(long)
    oceanbase::common::PageArena<char, oceanbase::common::ModulePageAllocator>::alloc_aligned(long, long)
    int oceanbase::sql::ObStmtFactory::create_stmt<oceanbase::sql::ObSelectStmt>(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<oceanbase::sql::TableItem*> const&, oceanbase::common::ObIArray<oceanbase::sql::TableItem*>&)
    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::ObParentDMLStmt>&, oceanbase::sql::ObDMLStmt*&, double&, bool&, void*) 
    oceanbase::sql::ObTransformRule::accept_transform(oceanbase::common::ObIArray<oceanbase::sql::ObParentDMLStmt>&, oceanbase::sql::ObDMLStmt*&, oceanbase::sql::ObDMLStmt*, bool&, void*)
    oceanbase::sql::ObTransformGroupByPlacement::transform_groupby_push_down(oceanbase::common::ObIArray<oceanbase::sql::ObParentDMLStmt>&, oceanbase::sql::ObDMLStmt*&, bool&)
    oceanbase::sql::ObTransformGroupByPlacement::transform_one_stmt(oceanbase::common::ObIArray<oceanbase::sql::ObParentDMLStmt>&, oceanbase::sql::ObDMLStmt*&, bool&)
    oceanbase::sql::ObTransformRule::transform_self(oceanbase::common::ObIArray<oceanbase::sql::ObParentDMLStmt>&, oceanbase::sql::ObDMLStmt*&)
    oceanbase::sql::ObTransformRule::transform_post_order(oceanbase::common::ObIArray<oceanbase::sql::ObParentDMLStmt>&, long, oceanbase::sql::ObDMLStmt*&)
    oceanbase::sql::ObTransformRule::transform_stmt_recursively(oceanbase::common::ObIArray<oceanbase::sql::ObParentDMLStmt>&, long, oceanbase::sql::ObDMLStmt*&)
    ?? ??:0
    oceanbase::sql::ObTransformRule::transform_post_order(oceanbase::common::ObIArray<oceanbase::sql::ObParentDMLStmt>&, long, oceanbase::sql::ObDMLStmt*&)
    oceanbase::sql::ObTransformRule::transform_stmt_recursively(oceanbase::common::ObIArray<oceanbase::sql::ObParentDMLStmt>&, long, oceanbase::sql::ObDMLStmt*&)
    ?? ??:0
    oceanbase::sql::ObTransformRule::transform_post_order(oceanbase::common::ObIArray<oceanbase::sql::ObParentDMLStmt>&, long, oceanbase::sql::ObDMLStmt*&)
    oceanbase::sql::ObTransformRule::transform_stmt_recursively(oceanbase::common::ObIArray<oceanbase::sql::ObParentDMLStmt>&, long, oceanbase::sql::ObDMLStmt*&)
    ?? ??:0
    oceanbase::sql::ObTransformRule::transform_post_order(oceanbase::common::ObIArray<oceanbase::sql::ObParentDMLStmt>&, long, oceanbase::sql::ObDMLStmt*&)
    oceanbase::sql::ObTransformRule::transform_stmt_recursively(oceanbase::common::ObIArray<oceanbase::sql::ObParentDMLStmt>&, long, oceanbase::sql::ObDMLStmt*&)
    ?? ??:0
    oceanbase::sql::ObTransformRule::transform_post_order(oceanbase::common::ObIArray<oceanbase::sql::ObParentDMLStmt>&, long, oceanbase::sql::ObDMLStmt*&)
    oceanbase::sql::ObTransformRule::transform_stmt_recursively(oceanbase::common::ObIArray<oceanbase::sql::ObParentDMLStmt>&, 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/ 禁止改写。