Skip to Content
avatar image
Former Member

Out of Memory when executing Stored Procedures after SPS 09 upgrade

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

@Lars Breddemann  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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    May 15, 2015 at 11:18 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 15, 2015 at 04:08 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 24, 2015 at 09:55 AM

    This message was moderated.

    Add comment
    10|10000 characters needed characters exceeded