SAP Knowledge Base Article - Preview

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

Symptom

  • 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)
           . . . . . . .

 


" occurs when querying large SAP technical tables"> Read more...

Environment

SAP HANA, platform edition 2.0

SAP HANA, platform edition 1.0

Product

SAP HANA 1.0, platform edition ; SAP HANA, platform edition 2.0 ; SAP S/4HANA 1909

Keywords

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-PERF , SAP HANA Database Performance , HAN-DB , SAP HANA Database , Problem

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.