3239943 - SQL error "SQL code: 4" occurred while accessing table "< >" occurs when querying large SAP technical tables

• ABAP Dumps (ST22), out of memory and/or composite out of memory events, are observed on a SAP HANA DB & S/4HANA system when queries are made to large traditional table objects such as the following :
      
  'ANEA, ANEK, ANEP, ANLC, BSAD, BSID, BSAK, BSIK, BSAS, BSIS,COEP, COSP, COSS, COVP FAGLFLEXT, FMGLFLEXT, GLT0, JVGLFLEXT, PSGLFLEXT, MARC, MARD, MARDH, MBEW, MBEWH, MBVMBEW, MCHB, MCHBH, MKPF, MSEG'
     
        
           
  Example Dump
        
        Category               External Error
        Runtime Errors         DBSQL_SQL_INTERNAL_DB_ERROR
        Except.                CX_SY_OPEN_SQL_DB
        Date and Time          21.08.2022 07:55:31 (UTC+7)
        ----------------------------------------------------------------------------------------------------
        ----------------------------------------------------------------------------------------------------
        |Short Text                                                                                        |
        |    SQL error "SQL code: 4" occurred while accessing table "COEP".                                |
        ----------------------------------------------------------------------------------------------------
        
        ----------------------------------------------------------------------------------------------------
        |What happened?                                                                                    |
        |    An internal error has occured on the database while preparing or                              |
        |    executing an SQL command. You can find more details from the SQL                              |
        |    error.                                                                                        |
        |                                                                                                  |
        |                                                                                                  |
        |    Database error number: SQL code: 4                                                            |
        |    Database error text: "SQL message: cannot allocate enough memory:  [9] Memory                 |
        |     allocation failed;exception 1000002: Allocation failed ; $size$=3772277184;                  |
        |     $name$=Results; $type$=pool; $inuse_count$=56; $allocated_size$=60475489184;                 |
        |     $alignment$=8; $failure_type$=STATEMENT_MEMORY"                                              |
        
        ----------------------------------------------------------------------------------------------------
           
        ----------------------------------------------------------------------------------------------------
        |Chain of Exception Objects                                                                        |
        ----------------------------------------------------------------------------------------------------
        |Level        Class                                                                                |
        |Attrib. Name                   Value                                                              |
        ----------------------------------------------------------------------------------------------------
        |1            CX_SY_OPEN_SQL_DB                                                                    |
        |(Program)                      YCOMPA09000001                                                     |
        |(Include)                      YCOMPA09000001                                                     |
        |(Row)                          724                                                                |
        |(Short Text)                   The database registered an internal error. SQL code 4. The error   |
        |                               text is: cannot allocate enough memory:  [9] Memory allocation     |
        |                               failed;exception 1000002: Allocation failed ; $size$=3772277184;   |
        |                               $name$=Results; $type$=pool; $inuse_count$=56; $allocated_size     |
        |IS_RESUMABLE                                                                                      |
        |KERNEL_ERRID                                                                                      |
        |SQLCODE                        4                                                                  |
        |SQLMSG                         cannot allocate enough memory:  [9] Memory allocation              |
        |                               failed;exception 1000002: Allocation failed ; $size$=3772277184;   |
        |                               $name$=Results; $type$=pool; $inuse_count$=56;                     |
        |                               $allocated_size$=60475489184; $alignment$=8; $failure_type$=STATEME|
        |                               NT_MEMORY_LI...                                                    
        ----------------------------------------------------------------------------------------------------

• Memory pressure, OOM dumps or composite oom dumps are observed on the same query each time due to expensive join engine results :
    
  indexserver_host.30040.rtedump.20220822-030256.0009265.compositelimit_oom.trc
    
              
              
            [MEMORY_LIMIT_VIOLATION]  Information about current memory composite-limit violation: (2022-08-22 03:02:56 253 Local)
            Composite limit violation (OUT OF MEMORY) occurred.
            Composite limit=120gb (128849018880b)
            Root allocator name=Connection/300142/Statement/1289102763561369
            Host: <>
            Executable: hdbindexserver
            PID: 9265
            Failed to allocate 2.31gb (2489068160b).
            
            Allocation failure type: STATEMENT_MEMORY_LIMIT
            
            Current callstack:
               0: 0x00007f68c0a9d5f4 in MemoryManager::LimitInfo::incrementLimitRelevantUsageDoWork(unsigned long)+0x250 at Basis/MemoryManager/impl/mmLimitInfo.cpp:370 (libhdbbasis.so)
               1: 0x00007f68c0a9d842 in MemoryManager::LimitInfo::incrementCoreStriping(unsigned long)+0x10 at Basis/MemoryManager/impl/mmLimitInfo.cpp:309 (libhdbbasis.so)
               2: 0x00007f68c0a9d99d in MemoryManager::LimitInfo::incrementInterleavedSlots(unsigned long)+0x39 at Basis/MemoryManager/impl/mmLimitInfo.cpp:253 (libhdbbasis.so)
               3: 0x00007f68c0a54927 in MemoryManager::LimitRelevantUsageScope::incrementUsage(unsigned long)+0xd3 at Basis/MemoryManager/impl/mmLimitInfo.cpp:197 (libhdbbasis.so)
               4: 0x00007f68c0ad11b1 in MemoryManager::MemoryPool::allocate(unsigned long, unsigned short, ltt::allocator_statistics&, bool&, bool, void const*)+0x4f0 at Basis/MemoryManager/impl/mmMemoryPool.cpp:1601 (libhdbbasis.so)
               5: 0x00007f68c0ae4693 in MemoryManager::MemoryPool::allocateAligned(unsigned long, unsigned long, unsigned short, ltt::allocator_statistics&, bool&, bool, void const*)+0x330 at Basis/MemoryManager/impl/mmMemoryPool.cpp:2096 (libhdbbasis.so)
               6: 0x00007f68c0afce52 in MemoryManager::PoolAllocator::allocate_aligned_nothrow_impl(unsigned long, unsigned long, void const*)+0x50 at Basis/MemoryManager/impl/mmPoolAllocator.cpp:1752 (libhdbbasis.so)
               7: 0x00007f68c0657883 in non-virtual thunk to ltt::allocator::allocate_aligned(unsigned long, unsigned long, void const*)+0x30 at ltt/memory.hpp:340 (libhdbbasis.so)
               8: 0x00007f68cdb51ce4 in ltt::allocate_raw_chunk<TrexTypes::StringAttributeValue, 8u>::allocate_raw_chunk(unsigned long, ltt::memory_resource&)+0x40 at ltt/impl/memory_resource.cpp:33 (libhdbcs.so)
               9: 0x00007f68d5e8e32b in AttributeEngine::ValueArray::init(unsigned long, bool)+0x1277 at ltt/vector.hpp:57 (libhdbcs.so)
              10: 0x00007f68e0b72064 in JoinEvaluator::TableAccess::jeGetValues(TRexConfig::IndexHandle&, TrexBase::IndexName const&, TRexConfig::Table const&, ltt_adp::vector<unsigned int, ltt::integral_constant<bool, true> > const&, ltt_adp::vector<JoinEvaluator::ValueMode, ltt::integral_constant<bool, true> > const*, ltt_adp::vector<TRexCommonObjects::QueryEntry, ltt::integral_constant<bool, true> > const*, unsigned int, unsigned int, unsigned int, ltt::map<unsigned int, unsigned int, ltt::less<unsigned int>, ltt::rb_tree_balancier> const*, TRexCommonObjects::LeanMainDelta<int> const&, TRexUtils::IndexVectorAligned const*, TRexUtils::BitVector const*, TRexCommonObjects::LeanMainDelta<TrexStore::UdivList*> const*, ltt_adp::vector<TRexCommonObjects::ColumnBase*, ltt::integral_constant<bool, true> >&, ltt_adp::vector<AttributeEngine::ValueArray*, ltt::integral_constant<bool, true> >&, int&, TRexConfig::IndexSyncPoint const*, ltt::allocator&)+0x280 at JoinEvaluator/TableAccess/TableAccess.cpp:1540 (libhdbcsapi.so)
              11: 0x00007f68e0a3877b in JoinEvaluator::JERequestedAttributes::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+0x2a27 at JoinEvaluator/JERequestedAttributes.cpp:1086 (libhdbcsapi.so)
              12: 0x00007f68e0dea331 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&)+0x2c0 at JoinEvaluator/JEPlanOperation.cpp:303 (libhdbcsapi.so)
              13: 0x00007f68de8e11b8 in Executor::X2::runPopTask(Executor::X2::PopTaskInfo&, int&, ltt::allocator&, ltt::allocator&)+0x2864 at executor/X2.cpp:2631 (libhdbexecutor.so)
              14: 0x00007f68de8e187e in Executor::X2::runPopJob(Executor::X2Job*)+0x6a at executor/X2.cpp:2331 (libhdbexecutor.so)
              15: 0x00007f68de8f69e6 in Executor::X2Job::run(Execution::Context&, Execution::JobObject&)+0x3d2 at executor/X2.cpp:5580 (libhdbexecutor.so)
              16: 0x00007f68c085e530 in Execution::JobObjectImpl::run(Execution::JobWorker*)+0x15b0 at Basis/Execution/impl/JobExecutionLog.hpp:155 (libhdbbasis.so)
              17: 0x00007f68c086cc73 in Execution::JobWorker::runJob(ltt::smartptr_handle<Execution::JobObjectForHandle>&)+0x860 at Basis/Execution/impl/JobExecutorThreads.cpp:366 (libhdbbasis.so)
              18: 0x00007f68c086e803 in Execution::JobWorker::run(Execution::ThreadRC&)+0x870 at Basis/Execution/impl/JobExecutorThreads.cpp:1354 (libhdbbasis.so)
              19: 0x00007f68c08ba6c6 in Execution::Thread::staticMainImp(Execution::Thread*)+0x542 at Basis/Execution/impl/Thread.cpp:574 (libhdbbasis.so)
              20: 0x00007f68c08c1ca5 in Execution::pthreadFunctionWrapper(Execution::Thread*)+0x1c1 at Basis/Execution/impl/ThreadInterposition.cpp:693 (libhdbbasis.so)
              21: 0x0000000000000000 in <no symbol>+0x0 (<unknown>)
               
            Top "limited composite allocator" allocators (component, name, size). Ordered descending by exclusive_size_in_use.
             1: Statement Execution & Intermediate Results: Connection/300142/Statement/1289102763561369/IMPLICIT                                  117.95gb (126655101896b)
             2: System:                                     Connection/300142/Statement/1289102763561369/Pool/RowEngine/QueryExecution/SearchAlloc 1002.25kb (1026312b)
             3: Statement Execution & Intermediate Results: Connection/300142/Statement/1289102763561369/Pool/JoinEvaluator                        110.81kb (113472b)
             4: Statement Execution & Intermediate Results: Connection/300142/Statement/1289102763561369                                           384b    (limit=120gb,   limitSumAllocated=117.95gb)

• Largest heap allocators are observed to be under the "Pool/JoinEvaluator/. . . " family
      
        Top 20 allocators:
        Pool/JoinEvaluator/JERequestedAttributes/Results         : 116.11gb (124680351760b) (388 blocks)
        Pool/malloc/libhdbcs.so                                  : 1.30gb (1400151896b) (272 blocks)
        Pool/JoinEvaluator/JECreateNTuple                        : 539.81mb (566036560b) (4 blocks)
         . . . . . . .

SAP HANA, platform edition 2.0

SAP HANA, platform edition 1.0

DBSQL_SQL_INTERNAL_DB_ERROR, ANEA, ANEK, ANEP, ANLC, BSAD, BSID, BSAK, BSIK, BSAS, BSIS,COEP, COSP, COSS, COVP FAGLFLEXT, FMGLFLEXT, GLT0, JVGLFLEXT, PSGLFLEXT, MARC, MARD, MARDH, MBEW, MBEWH, MBVMBEW, MCHB, MCHBH, MKPF, MSEG, NSDM_V_MARC, NSDM_V_MARD, NSDM_V_MARDH, NSDM_V_MCHB, NSDM_V_MCHBH, NSDM_V_MKPF, NSDM_V_MSEG, SE16,  , KBA , HAN-DB-ENG , SAP HANA DB Engines , HAN-DB-PERF , SAP HANA Database Performance , Problem