3235406 - OOMs and memory pressure caused by 'Statement Execution & Intermediate Results' despite configured 'statement_memory_limit' threshold

• Memory contention and out of memory events are observed on a system even with a 'statement_memory_limit' set in place to restrict large allocations by single and individual statements consumed under 'Statement Execution & Intermediate Results'.
     
     [MEMORY_OOM]  Information about current out of memory situation: (2022-07-28 03:32:12 127 Local)
     OUT OF MEMORY occurred.
     Host: <>
     Executable: hdbindexserver
     PID: 26755
      
     Current callstack:
      1: 0x00007fcff6284085 in MemoryManager::AllocatorConfigurationSPI::releaseMemoryMostProbablyDueToAWrongConfiguration()+0x21 at mmAllocatorConfigurationImpl.cpp:1675 (libhdbbasis.so)
      2: 0x00007fcff60a26a2 in MemoryManager::MemoryPool::reserveMemoryAndAllocateBigOrHugeBlock(unsigned long, bool, unsigned short, ltt::allocator_statistics::SubStats&, MemoryManager::ImpliInfo, void const*, Synchronization::LockHandle<Synchronization::Mutex, false>&) [clone .constprop.0]+0x270 at mmMemoryPool.cpp:2236 (libhdbbasis.so)
      3: 0x00007fcff61e8ccc in MemoryManager::MemoryPool::allocateBigOrHugeBlock(unsigned long, unsigned short, ltt::allocator_statistics&, MemoryManager::ImpliInfo, void const*, bool, Synchronization::LockHandle<Synchronization::Mutex, false>&, bool)+0x228 at mmMemoryPool.cpp:2157 (libhdbbasis.so)
      4: 0x00007fcff61e9bf5 in MemoryManager::MemoryPool::allocate(unsigned long, unsigned short, ltt::allocator_statistics&, bool&, bool, void const*)+0x511 at mmMemoryPool.cpp:1200 (libhdbbasis.so)
      5: 0x00007fcff61ea8df in MemoryManager::MemoryPool::allocateAligned(unsigned long, unsigned long, unsigned short, ltt::allocator_statistics&, bool&, bool, void const*)+0x46b at mmMemoryPool.cpp:1511 (libhdbbasis.so)
      6: 0x00007fcff61bc756 in MemoryManager::PoolAllocator::allocateAlignedNoThrowImpl(unsigned long, unsigned long, void const*)+0x52 at mmPoolAllocator.cpp:1805 (libhdbbasis.so)
      7: 0x00007fd0086e0fe9 in ltt::allocator::allocateAligned(unsigned long, unsigned long)+0x25 at memory.cpp:287 (libhdbcsapi.so)
      8: 0x00007fd0097796d1 in ltt::vector<unsigned long>::reserve_(unsigned long)+0x90 at memory.hpp:1206 (libhdbcsapi.so)
      9: 0x00007fd008aaa48f in JoinEvaluator::TableAccess::jeReadIndex(JoinEvaluator::JEDeltaIndexManagerParams&, TRexCommonObjects::LeanMainDelta<int> const&, TRexCommonObjects::MdVBV const*, TRexCommonObjects::LeanMainDelta<TrexStore::UdivList*> const*, bool, TRexUtils::IndexVectorAligned const&, TRexUtils::IndexVectorAligned&, TRexUtils::IndexVectorAligned&, unsigned int&, TRexConfig::IndexSyncPoint const*)+0xc9b at vector.hpp:750 (libhdbcsapi.so)
     10: 0x00007fd008be95e5 in JoinEvaluator::JEUtils::jeReadIndex(JoinEvaluator::JEDeltaIndexManagerParams&, JoinEvaluator::IndexSnapshot const&, TRexCommonObjects::MdVBV const*, bool, unsigned long, TRexUtils::IndexVectorAligned const&, TRexUtils::IndexVectorAligned&, TRexUtils::IndexVectorAligned&, ltt_adp::basic_string<char, ltt::char_traits<char>, ltt::integral_constant<bool, true> >*)+0x411 at JEUtils.cpp:4629 (libhdbcsapi.so)
     11: 0x00007fd0087a819c in JoinEvaluator::JEStep2::readIndex(JoinEvaluator::JEStep2Impl::RuntimeData&, unsigned int, TRexUtils::IndexVectorAligned const&) const+0x258 at JEStep2.cpp:2162 (libhdbcsapi.so)
     12: 0x00007fd0087ac355 in JoinEvaluator::JEStep2::applyReducerReadWithReducerVids(JoinEvaluator::JEStep2Impl::RuntimeData&, unsigned int) const+0xc1 at JEStep2.cpp:1970 (libhdbcsapi.so)
     13: 0x00007fd0087ac5a9 in JoinEvaluator::JEStep2::applyReducer(JoinEvaluator::JEStep2Impl::RuntimeData&, unsigned int) const+0xc5 at JEStep2.cpp:1854 (libhdbcsapi.so)
     14: 0x00007fd0087b3c08 in JoinEvaluator::JEStep2::executePopConst(ltt_adp::vector<Executor::PlanData*, ltt::integral_constant<bool, true> > const&, ltt_adp::vector<Executor::PlanData*, ltt::integral_constant<bool, true> > const&, Executor::ExecutionInfo const&) const+0x4c4 at JEStep2.cpp:847 (libhdbcsapi.so)
     15: 0x00007fd0087c4151 in JoinEvaluator::JEPlanOperation::executePop(ltt_adp::vector<Executor::PlanData*, ltt::integral_constant<bool, true> > const&, ltt_adp::vector<Executor::PlanData*, ltt::integral_constant<bool, true> > const&, TRexCommonObjects::TRexApiError&, Executor::ExecutionInfo const&)+0x1d0 at JEPlanOperation.cpp:303 (libhdbcsapi.so)
     16: 0x00007fd020319ccf in Executor::X2::runPopTask(Executor::X2::PopTaskInfo&, int&, ltt::allocator&, ltt::allocator&)+0x27eb at X2.cpp:2534 (libhdbexecutor.so)
     17: 0x00007fd02031a2a4 in Executor::X2::runPopJob(Executor::X2Job*)+0x60 at X2.cpp:2235 (libhdbexecutor.so)
     18: 0x00007fd02030368f in Executor::X2Job::run(Execution::Context&, Execution::JobObject&)+0x3db at X2.cpp:5287 (libhdbexecutor.so)
     19: 0x00007fcff6403cbf in Execution::JobObjectImpl::run(Execution::JobWorker*)+0x218b at JobExecutorImpl.cpp:1194 (libhdbbasis.so)
     20: 0x00007fcff63ed1ea in Execution::JobWorker::runJob(ltt::smartptr_handle<Execution::JobObjectForHandle>&)+0x336 at JobExecutorThreads.cpp:382 (libhdbbasis.so)
     21: 0x00007fcff63e7678 in Execution::JobWorker::run(void*&)+0x764 at JobExecutorThreads.cpp:1282 (libhdbbasis.so)
     22: 0x00007fcff63a5d8d in Execution::Thread::staticMainImp(Execution::Thread*)+0x529 at Thread.cpp:522 (libhdbbasis.so)
     23: 0x00007fcff6395dc9 in Execution::pthreadFunctionWrapper(Execution::Thread*)+0x165 at ThreadInterposition.cpp:639 (libhdbbasis.so)
     24: 0x00007fcff5a1371a in start_thread+0xc6 (libpthread.so.0)
     25: 0x00007fcff40e917d in __clone+0x69 (libc.so.6)
      
     Memory consumption information of last failing ProvideMemory, PM-INX=1331519:
     IPMM short info:
      
     GLOBAL_ALLOCATION_LIMIT (GAL) = 1.77tb (1952163299328b), SHARED_MEMORY = 3.18gb (3421794304b), CODE_SIZE = 3.25gb (3495489536b), OOM_RESERVATION_ALLOCATOR_SIZE = 96.14mb (100810752b)
      
     PID=26435 (hdbnameserver), PAL=1.78tb (1965022642176b), AB=4.58gb (4923285504b), UA=0, U=3.25gb (3497558318b), FSL=0b, FMC=0b
     PID=26699 (hdbcompileserver), PAL=1.78tb (1965022642176b), AB=603.44mb (632762368b), UA=0, U=220.29mb (230992835b), FSL=0b, FMC=0b, Blocked=834.62mb (875171840b)
     PID=26702 (hdbpreprocessor), PAL=1.78tb (1965022642176b), AB=405.75mb (425459712b), UA=0, U=246.93mb (258930634b), FSL=0b, FMC=0b
     PID=26758 (hdbscriptserver), PAL=1.78tb (1965022642176b), AB=2.29gb (2461958144b), UA=0, U=1.92gb (2071661878b), FSL=0b, FMC=0b
     PID=26752 (hdbdpserver), PAL=1.78tb (1965022642176b), AB=1.67gb (1797668864b), UA=0, U=1.35gb (1455310296b), FSL=0b, FMC=0b
     PID=26755 (hdbindexserver), PAL=1.78tb (1965022642176b), AB=1.71tb (1889649414144b), UA=0, U=1.66tb (1826082864490b), FSL=0b, FMC=0b
     PID=26761 (hdbxsengine), PAL=1.78tb (1965022642176b), AB=40.63gb (43636469760b), UA=0, U=32.33gb (34718148918b), FSL=0b, FMC=0b
     PID=28372 (hdbwebdispatcher), PAL=1.78tb (1965022642176b), AB=1.37gb (1476587520b), UA=0, U=1.22gb (1312584821b), FSL=0b, FMC=0b
      
     Total allocated memory= 1.77tb (1951920889856b)
     Total used memory     = 1.70tb (1876545336030b)
     Sum AB                = 1945003606016
     Sum Used              = 1869628052190
     Heap memory fragmentation: 3
      
     Top "M_HEAP_MEMORY" allocators (component, name, size). Ordered descending by exclusive_size_in_use.
      1: Statement Execution & Intermediate Results: Pool/JoinEvaluator/JERequestedAttributes/Results                     994.68gb (1068032633632b) (953.59mb (999919616b) precharged, 947mb (993005568b) in preparation)
      2: Statement Execution & Intermediate Results: Pool/ESX                                                             89.75gb (96371737072b) (32mb (33554432b) precharged, 128kb (131072b) in preparation)
      3: Statement Execution & Intermediate Results: Pool/SearchAPI/Itab Search                                           62.76gb (67389636444b) (4mb (4194304b) precharged, 64kb (65536b) in preparation)
      4: System:                                     Pool/PersistenceManager/PersistentSpace/DefaultLPA/DataPage          48.86gb (52466196480b) (116mb (121634816b) precharged, 98.37mb (103153664b) in preparation)
      5: Statement Execution & Intermediate Results: Pool/itab                                                            46.40gb (49828492408b) (4mb (4194304b) precharged, 256kb (262144b) in preparation)
      6: System:                                     Pool/parallel/ihm                                                    43.18gb (46372432453b) (32mb (33554432b) precharged, 9.78mb (10256384b) in preparation)
      7: System:                                     AllocateOnlyAllocator-unlimited/FLA-UL<24592,1>/MemoryMapLevel3Nodes 36.43gb (39124199744b)
      8: Column Store Tables:                        Pool/ColumnStore/Main/Compressed/Indirect                            35.31gb (37924226880b) (8mb (8388608b) precharged, 5.91mb (6197248b) in preparation)
      9: Column Store Tables:                        Pool/ColumnStore/Main/Index/Single                                   29.98gb (32201451408b) (17.07mb (17899520b) precharged, 17.07mb (17899520b) in preparation)
     10: Column Store Tables:                        Pool/malloc/libhdbcs.so                                              23.07gb (24772399520b) (31.88mb (33435648b) precharged, 28.88mb (30289920b) in preparation)
     11: Column Store Tables:                        Pool/ColumnStore/Main/Dictionary/RoDict                              20.18gb (21678266046b) (4mb (4194304b) precharged, 1024kb (1048576b) in preparation)
     12: Statement Execution & Intermediate Results: Pool/ColumnStore/Transient                                           16.75gb (17985221760b)
     13: Column Store Tables:                        Pool/PersistenceManager/UnifiedTableContainer                        15.98gb (17159810710b)
     14: Column Store Tables:                        Pool/ColumnStore/Main/Uncompressed                                   15.77gb (16936099648b) (526.04mb (551596032b) precharged, 526.04mb (551596032b) in preparation)
     15: System:                                     Pool/parallel/compactcol                                             14.77gb (15869940272b)
     16: Statement Execution & Intermediate Results: Pool/JoinEvaluator/TranslationTable                                  13.80gb (14821969872b)
     17: System:                                     Pool/malloc/libhdbolap.so                                            12.80gb (13749310708b)
     18: System:                                     Pool/RowEngine/RowTableManager/MVCCManager/MVCCAllocator             12.51gb (13439409632b)
     19: System:                                     Pool/parallel/aggregates                                             12.06gb (12953498584b)
     20: Column Store Tables:                        Pool/ColumnStore/Main/Rowid                                          9.69gb (10414408664b)

• M_CONTEXT_MEMORY reveals multiple, individual statement ids and transactions consuming memory on the system. When added up, this amounts to 1236.5 GB consumed at once on the DB by parallel sessions, but not one of them reached near the configured 'statement_memory_limit' threshold of 160 GB in this example. "you can limit the memory consumption of single SQL statements. " ---> 2222250 - FAQ: SAP HANA Workload Management 
    
      Top "M_CONTEXT_MEMORY" allocators (component, name, size). Ordered descending by exclusive_size_in_use.
       1: Statement Execution & Intermediate Results: Connection/363980/Statement/1563286075794503/IMPLICIT           93.61gb (100522295730b
       2: Statement Execution & Intermediate Results: Connection/363973/Statement/1563252497655630/IMPLICIT           83.17gb (89310559673b)
       3: Statement Execution & Intermediate Results: Connection/363975/Statement/1563261679975392/IMPLICIT           81.03gb (87012763110b)
       4: Statement Execution & Intermediate Results: Connection/363983/Statement/1563296780856464/IMPLICIT           78.54gb (84334307159b)
       5: Statement Execution & Intermediate Results: Connection/363999/Statement/1563367029569226/IMPLICIT           78.48gb (84278063132b)
       6: Statement Execution & Intermediate Results: Connection/352301/Statement/1513121542406214/IMPLICIT           76.30gb (81930435378b)
       7: Statement Execution & Intermediate Results: Connection/364005/Statement/1563390482822234/IMPLICIT           75.20gb (80754298759b)
       8: Statement Execution & Intermediate Results: Connection/363986/Statement/1563310270535994/IMPLICIT           74.87gb (80399366900b)
       9: Statement Execution & Intermediate Results: Connection/363971/Statement/1563246626624216/IMPLICIT           74.63gb (80141619305b)
      10: Statement Execution & Intermediate Results: WorkloadCtx/844442118306155/Statement/1116092989092862/IMPLICIT 74.30gb (79788551208b)
      11: Statement Execution & Intermediate Results: Connection/363970/Statement/1563242856497081/IMPLICIT           72.90gb (78277879624b)
      12: Statement Execution & Intermediate Results: Connection/364000/Statement/1563370307374354/IMPLICIT           71.99gb (77304483562b)
      13: Statement Execution & Intermediate Results: Connection/363615/Statement/1561716955055395/IMPLICIT           63.90gb (68619290606b)
      14: Statement Execution & Intermediate Results: Connection/363614/Statement/1561714322679975/IMPLICIT           56.71gb (60896377201b)
      15: Statement Execution & Intermediate Results: Connection/363461/Statement/1561053278248696/IMPLICIT           48.23gb (51793359131b)
      16: Statement Execution & Intermediate Results: Connection/363963/Statement/1563210771078123/IMPLICIT           37.46gb (40229935454b)
      17: Statement Execution & Intermediate Results: Connection/363960/Statement/1563198042586695/IMPLICIT           36gb (38659561737b)
      18: Statement Execution & Intermediate Results: Connection/363149/Statement/1559713105665736/IMPLICIT           21.84gb (23457636230b)
      19: Statement Execution & Intermediate Results: WorkloadCtx/844442118039681/Statement/991119662096937/IMPLICIT  19.81gb (21281747544b)
      20: Statement Execution & Intermediate Results: Connection/322317/Statement/1384342064642609/IMPLICIT           17.53gb (18829004748b)

SAP HANA, platform edition 2.0

SAP HANA, platform edition 1.0

KBA , HAN-DB , SAP HANA Database , Problem