Out of Memory when executing Stored Procedures after SPS 09 upgrade

[former_member199876]

I am facing a problem after the upgrade to SPS 09 (across all revisions including the latest 95).

We have a procedure with a simple insert statement, getting its data from a join between three partitioned tables.

The volume of the data is around 100 million.

    CREATE PROCEDURE "PROCEDURE_NAME" () LANGUAGE SQLSCRIPT 
    AS BEGIN
    DELETE FROM OUTPUT_TABLE;
    INSERT INTO OUTPUT_TABLE
    SELECT T1.A, T2.B, T3.C
    FROM T1
               LEFT OUTER JOIN T2 ON T1.X = T2.X
               LEFT OUTER JOIN T3 ON T1.Y = T3.Y;
    END

This procedure has been working without any issues in SPS07 & SPS08.

But after the upgrade to SPS 09, with the exact same data/code/system config, we are getting an OUT OF MEMORY error.

The interesting part is that the Insert works just fine, when we run it as a independent transaction (without the procedure wrapper) in the standard SQL Console.

    INSERT INTO OUTPUT_TABLE
    SELECT T1.A, T2.B, T3.C
    FROM T1
               LEFT OUTER JOIN T2 ON T1.X = T2.X
               LEFT OUTER JOIN T3 ON T1.Y = T3.Y;

It fails with OOM, only when executing the statement inside a procedure.

Another interesting point, is that, just the "Select" executes inside the procedure (without the results being inserted into  a table) without any errors.

    CREATE PROCEDURE "PROCEDURE_NAME" () LANGUAGE SQLSCRIPT
    AS BEGIN

    SELECT T1.A, T2.B, T3.C
    FROM T1
               LEFT OUTER JOIN T2 ON T1.X = T2.X
               LEFT OUTER JOIN T3 ON T1.Y = T3.Y;
    END

The addition of an INSERT seems to break something internally. This is happening only after the upgrade to SPS 09 (revision 95).

  Any insights would be really helpful.

Some of the errors being thrown in the log are

Memory           PoolAllocator.cpp(00681) : Out of memory for Pool/JoinEvaluator/JERequestedAttributes/Results, size 1739902592B, alignment=1B, flags 0x0
    0x1: 0x00007f51b79048a6 in Diagnose::CallStack::fillTrace(ltt::basic_ostream<char, ltt::char_traits<char> >&, void**, unsigned long, Diagnose::RegisterSet const*, void const*, bool)+0x142 at CallStack.cpp:846 (libhdbbasis.so)
     2: 0x00007f51b790f7b0 in ltt::operator<<(ltt::basic_ostream<char, ltt::char_traits<char> >&, Diagnose::CallStack const&)+0x20 at CallStackManager.cpp:565 (libhdbbasis.so)
     3: 0x00007f51b7c013f1 in MemoryManager::PoolAllocator::oomHandling(unsigned long, unsigned long, unsigned long, bool)+0x390 at PoolAllocator.cpp:681 (libhdbbasis.so)
     4: 0x00007f51b7c01aa8 in MemoryManager::PoolAllocator::allocateNoThrowImpl(unsigned long, void const*)+0x234 at PoolAllocator.cpp:1149 (libhdbbasis.so)
     5: 0x00007f51c404d46e in ltt::allocator::allocate(unsigned long)+0x2a at memory.cpp:83 (libhdbcs.so)
     6: 0x00007f51bcdb1eb4 in ltt::allocate_raw_chunk<TrexTypes::StringAttributeValue>::allocate_raw_chunk(unsigned long, ltt::allocator&)+0x30 at memory.hpp:798 (libhdbcs.so)
     7: 0x00007f51bcdb1ef7 in ltt::impl::VectorBase<TrexTypes::StringAttributeValue>::init_(unsigned long)+0x23 at vector.hpp:576 (libhdbcs.so)
     8: 0x00007f51bcdd91e3 in ltt::vector<TrexTypes::StringAttributeValue>::vector(unsigned long, TrexTypes::StringAttributeValue const&, ltt::allocator&)+0x30 at vector.hpp:792 (libhdbcs.so)
     9: 0x00007f51bcd4b1db in AttributeEngine::ValueArray::init(unsigned long, bool)+0x5d7 at vector.hpp:126 (libhdbcs.so)
    10: 0x00007f51c8377f52 in TRexAPI::DeltaIndexManager::jeGetValues(TRexConfig::IndexHandle&, TrexBase::IndexName const&, TRexConfig::Table, ltt_adp::vector<unsigned int, ltt::integral_constant<bool, true> > const&, ltt_adp::vector<TRexAPI::ValueMode, ltt::integral_constant<bool, true> > const*, ltt_adp::vector<TRexCommonObjects::QueryEntry, ltt::integral_constant<bool, true> > const*, unsigned int, unsigned int, 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&)+0x3d0 at DeltaIndexManager.cpp:4065 (libhdbcsapi.so)
    11: 0x00007f51ca5b5699 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+0x2e15 at JERequestedAttributes.cpp:1127 (libhdbcsapi.so)
    12: 0x00007f51cab591da 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&)+0x136 at JEPlanOperation.cpp:102 (libhdbcsapi.so)
    13: 0x00007f51d1137ffb in Executor::X2::runPopTask(Executor::X2::PopTaskInfo&, int&, ltt::allocator&, ltt::allocator&)+0xd47 at X2.cpp:3425 (libhdbexecutor.so)
    14: 0x00007f51d1139aa2 in Executor::X2::runPopJob(Executor::X2Job*)+0x80 at X2.cpp:2983 (libhdbexecutor.so)
    15: 0x00007f51d113a328 in Executor::X2Job::run(Execution::JobObject&)+0x224 at X2.cpp:5321 (libhdbexecutor.so)
    16: 0x00007f51b7a4a611 in Execution::JobObjectImpl::run(Execution::JobWorker*)+0x780 at JobExecutorImpl.cpp:843 (libhdbbasis.so)
    17: 0x00007f51b7a59082 in Execution::JobWorker::runJob(ltt::smartptr_handle<Execution::JobObjectForHandle>&)+0x2e0 at JobExecutorThreads.cpp:205 (libhdbbasis.so)
    18: 0x00007f51b7a59fd4 in Execution::JobWorker::run(void*&)+0x1a0 at JobExecutorThreads.cpp:370 (libhdbbasis.so)
    19: 0x00007f51b7a920c9 in Execution::Thread::staticMainImp(void**)+0x875 at Thread.cpp:496 (libhdbbasis.so)
    20: 0x00007f51b7a92d2d in Execution::Thread::staticMain(void*)+0x39 at ThreadMain.cpp:26 (libhdbbasis.so)
    [43089]{200158}[14/626536] 2015-05-14 11:03:21.086740 e Memory           ReportMemoryProblems.cpp(00799) : OUT OF MEMORY occurred.

[lbreddemann · Accepted solution]

I don't see a good reason for why this statement should fail in SQLScript when it works in SQL.

Please open a support incident for this.

- Lars

[former_member199876 · Accepted solution]

Thanks Lars. We'll raise a support incident.

[former_member747083]

Try to use HINT(now_csjoin)

[Former Member]

This message was moderated.

[Former Member]

One more remark: If an INSERT fails with memory issues although the underlying SELECT works fine, it can be related to the late_materialization_threshold_for_insert (SAP Note 1975448). If it is disabled or set too high, Pool/JoinEvaluator/JERequestedAttributes/Results can grow significantly. So please double-check if you have set this parameter to an inadequate non-default value.