划线反馈

SQL 执行占用内存过大

更新时间：2025-05-07 12:31

适用版本：V2.1.x、V2.2.x、V3.1.x、V3.2.x内容类型：Troubleshoot

问题描述

在 OceanBase 数据库中执行以下 SQL 语句。

select a.* from jxzs.vw_ac43 a where 1=1 and exists(select 1 from jxzs.ab07 k where k.aaz061=a.aaz061 and k.aaz625='3000000222067265');

在执行期间，发现 SQL_EXEC_CTX_ID 占用的内存约为 300 GB。

SQL 语句的执行计划如下。

|ID|OPERATOR                    |NAME                            |EST. ROWS|COST      |
---------------------------------------------------------------------------------------
|0 |HASH RIGHT SEMI JOIN        |                                |2        |4129906795|
|1 | PX COORDINATOR             |                                |1        |46        |
|2 |  EXCHANGE OUT DISTR        |:EX10000                        |1        |46        |
|3 |   SUBPLAN SCAN             |VIEW7                           |1        |46        |
|4 |    PX PARTITION ITERATOR   |                                |1        |46        |
|5 |     TABLE SCAN             |K(IDX_AB07_AAZ625)              |1        |46        |
|6 | PX COORDINATOR             |                                |48779191 |4109451647|
|7 |  EXCHANGE OUT DISTR        |:EX20000                        |48779191 |3897938817|
|8 |   SUBPLAN SCAN             |VW_AC43                         |48779191 |3897938817|
|9 |    PX PARTITION ITERATOR   |                                |48779191 |3897205679|
|10|     UNION ALL              |                                |48779191 |3897205679|
|11|      HASH JOIN             |                                |10036448 |272316630 |
|12|       TABLE SCAN           |B                               |10036448 |28245596  |
|13|       TABLE SCAN           |A                               |29056783 |213880438 |
|14|      NESTED-LOOP ANTI JOIN |                                |0        |2216907709|
|15|       NESTED-LOOP JOIN     |                                |0        |2216907709|
|16|        HASH RIGHT ANTI JOIN|                                |0        |2216907709|
|17|         SUBPLAN SCAN       |VIEW1                           |778695443|737634818 |
|18|          TABLE SCAN        |B(IDX_AC08_AAC001_M1)           |778695443|725931238 |
|19|         TABLE SCAN         |A                               |519130295|725931238 |
|20|        TABLE GET           |B                               |1        |36        |
|21|       SUBPLAN SCAN         |VIEW2                           |1        |92        |
|22|        TABLE SCAN          |B(IDX_AC43_AAZ686)              |1        |92        |
|23|      TABLE SCAN            |A                               |38742743 |213880388 |
|24|      NESTED-LOOP ANTI JOIN |                                |0        |783830823 |
|25|       HASH RIGHT ANTI JOIN |                                |1594755  |709046552 |
|26|        SUBPLAN SCAN        |VIEW4                           |203399400|219801120 |
|27|         TABLE SCAN         |B                               |203399400|216744082 |
|28|        TABLE SCAN          |A                               |204994155|245704890 |
|29|       SUBPLAN SCAN         |VIEW3                           |1        |46        |
|30|        TABLE SCAN          |B(IDX_AC96_AAC001_AAZ686_AAE792)|1        |46        |
|31|      NESTED-LOOP ANTI JOIN |                                |0        |409536995 |
|32|       HASH RIGHT ANTI JOIN |                                |0        |409536995 |
|33|        SUBPLAN SCAN        |VIEW5                           |143851091|136265820 |
|34|         TABLE SCAN         |B                               |143851091|134103777 |
|35|        TABLE SCAN          |A                               |95900727 |134103777 |
|36|       SUBPLAN SCAN         |VIEW6                           |1        |92        |
|37|        TABLE SCAN          |B(IDX_AC43_AAZ686)              |1        |92        |

通过执行计划可知 SQL 语句满足三个触发条件。

命中索引。
抽出的 range 个数大于 2。
需要 rescan 的场景，例如存在 NESTED-LOOP JOIN。

Memleak 信息如下所示。

 oceanbase::common::lbt(char*, 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>::extend_page(long) at 0_cxx.cxx:?
 oceanbase::common::PageArena<char, oceanbase::common::ModulePageAllocator>::alloc_aligned(long, long) at 0_cxx.cxx:?
 oceanbase::common::ObSEArrayImpl<bool, 2l, oceanbase::common::ModulePageAllocator, true>::internal_malloc_(long)
 oceanbase::common::ObSEArrayImpl<bool, 2l, oceanbase::common::ModulePageAllocator, true>::reserve(long)
 oceanbase::sql::ObQueryRange::store_range(oceanbase::common::ObNewRange*, bool, oceanbase::sql::ObQueryRange::ObSearchState&, oceanbase::common::ObSEArray<oceanbase::common::ObNewRange*, 1l, oceanbase::common::ModulePageAllocator, false>&, oceanbase::common::ObSEArray<bool, 2l, oceanbase::common::ModulePageAllocator, true>&)
 oceanbase::sql::ObQueryRange::generate_cur_range(oceanbase::sql::ObQueryRange::ObSearchState&, long, bool, oceanbase::common::ObSEArray<oceanbase::common::ObNewRange*, 1l, oceanbase::common::ModulePageAllocator, false>&, oceanbase::common::ObSEArray<bool, 2l, oceanbase::common::ModulePageAllocator, true>&)
 oceanbase::sql::ObQueryRange::and_first_in_key(oceanbase::sql::ObQueryRange::ObSearchState&, oceanbase::sql::ObKeyPart*, oceanbase::common::ObSEArray<oceanbase::common::ObNewRange*, 1l, oceanbase::common::ModulePageAllocator, false>&, oceanbase::common::ObSEArray<bool, 2l, oceanbase::common::ModulePageAllocator, true>&, oceanbase::common::ObDataTypeCastParams const&)
 oceanbase::sql::ObQueryRange::and_first_search(oceanbase::sql::ObQueryRange::ObSearchState&, oceanbase::sql::ObKeyPart*, oceanbase::common::ObSEArray<oceanbase::common::ObNewRange*, 1l, oceanbase::common::ModulePageAllocator, false>&, oceanbase::common::ObSEArray<bool, 2l, oceanbase::common::ModulePageAllocator, true>&, oceanbase::common::ObDataTypeCastParams const&)
 oceanbase::sql::ObQueryRange::and_first_search(oceanbase::sql::ObQueryRange::ObSearchState&, oceanbase::sql::ObKeyPart*, oceanbase::common::ObSEArray<oceanbase::common::ObNewRange*, 1l, oceanbase::common::ModulePageAllocator, false>&, oceanbase::common::ObSEArray<bool, 2l, oceanbase::common::ModulePageAllocator, true>&, oceanbase::common::ObDataTypeCastParams const&)
 oceanbase::sql::ObQueryRange::and_first_search(oceanbase::sql::ObQueryRange::ObSearchState&, oceanbase::sql::ObKeyPart*, oceanbase::common::ObSEArray<oceanbase::common::ObNewRange*, 1l, oceanbase::common::ModulePageAllocator, false>&, oceanbase::common::ObSEArray<bool, 2l, oceanbase::common::ModulePageAllocator, true>&, oceanbase::common::ObDataTypeCastParams const&)
 oceanbase::sql::ObQueryRange::get_tablet_ranges(oceanbase::common::ObSEArray<oceanbase::common::ObNewRange*, 1l, oceanbase::common::ModulePageAllocator, false>&, oceanbase::common::ObSEArray<bool, 2l, oceanbase::common::ModulePageAllocator, true>&, oceanbase::common::ObDataTypeCastParams const&)
 oceanbase::sql::ObSQLUtils::extract_pre_query_range(oceanbase::sql::ObQueryRange const&, oceanbase::common::ObIAllocator&, oceanbase::common::Ob2DArray<oceanbase::common::ObObjParam, 2096128, oceanbase::common::ObWrapperAllocator, false, oceanbase::common::ObSEArray<oceanbase::common::ObObjParam*, 1l, oceanbase::common::ObWrapperAllocator, false> > const&, oceanbase::common::ObSEArray<oceanbase::common::ObNewRange*, 1l, oceanbase::common::ModulePageAllocator, false>&, oceanbase::common::ObSEArray<bool, 2l, oceanbase::common::ModulePageAllocator, true>, oceanbase::common::ObDataTypeCastParams const&)
 oceanbase::sql::ObTableScanOp::prepare_single_scan_range() at ??:?
 oceanbase::sql::ObTableScanOp::local_iter_rescan()
 oceanbase::sql::ObTableScanOp::rescan()
 oceanbase::sql::ObOperator::rescan()
 oceanbase::sql::ObNestedLoopJoinOp::rescan_right_operator()
 oceanbase::sql::ObNestedLoopJoinOp::join_row_with_semi_join()
 oceanbase::sql::ObNestedLoopJoinOp::inner_get_next_row() 
 oceanbase::sql::ObOperator::get_next_row()
 oceanbase::sql::ObMergeUnionOp::all_get_next_row()
 oceanbase::sql::ObMergeUnionOp::inner_get_next_row()
 oceanbase::sql::ObOperator::get_next_row()
 oceanbase::sql::ObGranuleIteratorOp::try_get_rows(long) 
 oceanbase::sql::ObOperator::get_next_row()
 oceanbase::sql::ObSubPlanScanOp::inner_get_next_row() 
 oceanbase::sql::ObOperator::get_next_row()
 oceanbase::sql::ObPxTransmitOp::inner_get_next_row()
 oceanbase::sql::ObOperator::get_next_row()
 oceanbase::sql::ObPxTransmitOp::next_row()
 oceanbase::sql::ObPxTransmitOp::send_rows_one_by_one(oceanbase::sql::ObSliceIdxCalc&)
 oceanbase::sql::ObPxReduceTransmitOp::do_transmit()
 oceanbase::sql::ObPxTaskProcess::execute(oceanbase::sql::ObOpSpec&)
 oceanbase::sql::ObPxTaskProcess::do_process()
 oceanbase::sql::ObPxTaskProcess::process()
 oceanbase::sql::ObPxLocalWorker::run(oceanbase::sql::ObPxRpcInitTaskArgs&)
 oceanbase::sql::ObPxSubCoord::dispatch_task_to_local_thread(oceanbase::sql::ObPxRpcInitSqcArgs&, oceanbase::sql::ObSqcCtx&, oceanbase::sql::ObPxSqcMeta&) 
 oceanbase::sql::ObInitFastSqcP::startup_normal_sqc(oceanbase::sql::ObPxSqcHandler&)
 oceanbase::sql::ObInitFastSqcP::process()
 oceanbase::obrpc::ObRpcProcessorBase::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::omt::ObThWorker::worker(long&, long&, int&)
 non-virtual thunk to oceanbase::omt::ObThWorker::run(long) at ??:?
 std::_Function_handler<void (), oceanbase::lib::UserThread::start()::$_14::operator()(oblib::Thread*) const::{lambda()#1}>::_M_invoke(std::_Any_data const&)
 eeentry(boost::context::detail::transfer_t)
 finish at object_set.cpp:?

适用版本

OceanBase 数据库 V2.2.77 版本、V3.1.2 BP11 (oceanbase-3.1.2-111000052023010412) 之前版本、V3.2.3 BP9 (oceanbase-3.2.3.3-109000182023071410) 之前版本、V3.2.4 BP4 Hotfix5 (oceanbase-3.2.4.4-104050012023073116) 之前版本。

问题原因

OceanBase 数据库 Bug 引起的内存动态泄漏。

解决方法

升级到问题已修复的版本。

目前已修复的版本包括 OceanBase 数据库企业版 V3.1.2 BP11 (oceanbase-3.1.2-111000052023010412) 及后续版本、V3.2.3 BP9 (oceanbase-3.2.3.3-109000182023071410) 及之后版本、V3.2.4 BP4 Hotfix5 (oceanbase-3.2.4.4-104050012023073116) 及之后版本。

规避方法

添加 hint，走主表：在 SQL 语句中添加 hint，强制查询走主表，可以减少 SQL_EXEC_CTX_ID 的内存占用。

应急方法

重启节点。

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

分布式计划执行时中间结果占用内存过大

OceanBase 数据库