3600971 - Slow table access

- Query on a table is extremely slow. 

- Planviz of the query shows that most of the time was spent on the following operations:

Name: FragmentScanEstimationOp
Name: PostFilterConjEstimationOp
Name: VisibilityFilterEstimationOp
Name: DictScanBasedEstimationOp

- Thread call stack of the query:

1 : UnifiedTable::MVCCObject::generateOLAPBitmapMVCC(ltt::pair<int, int> const&, bool&, TransactionManager::ConsistentView*) const+0x6f2 at UnifiedTable/MVCC/impl/MVCCObject.cpp:1566 (libhdbunifiedtable.so)
2 : UnifiedTable::MVCCObject::generateValidListMVCC(ltt::pair<int, int> const&, TransactionManager::ConsistentView*, UnifiedTable::FragmentReaderCallbackAPI const&) const+0x7b at UnifiedTable/MVCC/impl/MVCCObject.cpp:1630 (libhdbunifiedtable.so)
3 : UnifiedTable::MVCCReader::generateValidListMVCC(ltt::pair<int, int> const&, TransactionManager::ConsistentView*, UnifiedTable::FragmentReaderCallbackAPI const&) const+0x7 at UnifiedTable/MVCC/impl/MVCCReader.cpp:377 (libhdbunifiedtable.so)
4 : CSAccessor::Mvcc::MvccExecutor::getAllVisibleHex()+0x150 at ltt/ext/hnd/versioned_handle.hpp:509 (libhdbcsaccessor.so)
5 : CSMvccAccessorApi::getAllVisibleHex()+0xb at CSAccessor/Mvcc/CSMvccAccessorApi.cpp:32 (libhdbcsaccessor.so)
6 : hex::cs::CSVisibilityInterface::getValidUdivs()+0x70 at hex/cs/adapter/CSVisibilityInterface.cpp:39 (libhdbhexcs.so)
7 : hex::cs::CSVisibilityInterface::getExactNumOfVisibleRows() [clone .localalias] [clone .lto_priv.0]+0x13 at hex/cs/adapter/CSVisibilityInterface.cpp:49 (libhdbhexcs.so)
8 : hex::cs::VisibilityFilterEstimationOp::run(hex::RunContext) const+0x1b5 at hex/cs/table/VisibilityFilterEstimationOp.cpp:40 (libhdbhexcs.so)
9 : hex::NoDataOperator::xf_run(hex::RunContext) const+0x10 at hex/core/planex/impl/OperatorImpl.cpp:153 (libhdbhexcore.so)
10 : hex::impl::NextPtr::run(hex::Operator const&, hex::RunContext) const+0x70 at hex/core/planex/impl/OperatorImpl.hpp:228 (libhdbhexcs.so)
11 : hex::cs::PostFilterConjEstimationOp::run(hex::RunContext) const+0x11b at hex/core/planex/impl/OperatorImpl.hpp:389 (libhdbhexcs.so)
12 : hex::impl::NextPtr::run(hex::Operator const&, hex::RunContext) const+0x70 at hex/core/planex/impl/OperatorImpl.hpp:228 (libhdbhexcs.so)
13 : hex::cs::FragmentScanEstimationOp::run(hex::RunContext) const+0xc0 at hex/core/planex/impl/OperatorImpl.hpp:389 (libhdbhexcs.so)
14 : hex::impl::NextPtr::run(hex::Operator const&, hex::RunContext) const+0x70 at hex/core/planex/impl/OperatorImpl.hpp:228 (libhdbhexcs.so)
15 : hex::cs::TableScanEstimationScheduleOp::run(hex::RunContext) const+0x9b at hex/core/planex/impl/OperatorImpl.hpp:389 (libhdbhexcs.so)
16 : hex::OperatorGraph::runPipeline(unsigned int, hex::impl::LocalContext&) const+0x174 at hex/core/planex/build/OperatorGraph.hpp:252 (libhdbhexcore.so)
17 : hex::ExecutablePlan::open(hex::SelfOwningObjectHandle<hex::RuntimeParameters>, hex::FetchControl*) const+0xeb at hex/core/planex/build/OperatorGraph.hpp:263 (libhdbhexcore.so)
18 : ptime::Hex_search<(ptime::HexSearchType)0>::do_open(ptime::OperatorEnv&, ptime::QEParams, int) const+0x999 at ptime/query/plan_executor/dml/hex/qe_hex_search.cc:721 (libhdbrskernel.so)
19 : ptime::Table::open(ptime::Env&, ptime::QEParams, int) const+0x1c0 at ptime/query/plan_executor/dml/qe_table.cc:194 (libhdbrskernel.so)
20 : ptime::Query::result_t::open(ptime::Query::Plan*, bool, bool, ptime::Statement*, bool, bool, ptime::Query::RecompileOption)+0x1987 at ptime/query/query_result.cc:725 (libhdbrskernel.so)
21 : ptime::ResultSet::open_(bool, ptime::Query::RecompileOption, bool, bool, bool, bool)+0x277 at ptime/session/eapi/jdbc/ResultSet.cc:423 (libhdbrskernel.so)
22 : ptime::Statement::post_execute_(ptime::Statement::ExecuteType, Execution::Context&, ptime::ResultSet*, ptime::StatementResourceTrackingScope&, bool&, sqlscript::Phase&, int, bool, bool, bool, bool, bool, bool, ptime::Query::RecompileOption const&, int)+0x218 at ptime/session/eapi/jdbc/Statement.cc:2556 (libhdbrskernel.so)
23 : ptime::Statement::execute_(Execution::Context&, EAPI::CursorHoldability const&, bool, bool, bool)+0xaf0 at ptime/session/eapi/jdbc/Statement.cc:2892 (libhdbrskernel.so)
24 : ptime::PreparedStatement::execute(Execution::Context&, EAPI::CursorHoldability const&, bool, bool, bool, bool, bool, bool)+0x2a2 at ptime/session/eapi/jdbc/PreparedStatement.cc:551 (libhdbrskernel.so)
25 : ptime::ActionExecutor::executeQuery(Execution::Context&, ptime::Action&)+0x193 at ptime/session/sm_action_executor.cc:1408 (libhdbsqlsession.so)
26 : ptime::ActionExecutor::executeAction(Execution::Context&, ptime::SessionEvent*, ptime::Action&)+0x140 at ptime/session/sm_action_executor.cc:735 (libhdbsqlsession.so)
27 : ptime::SessionHandler::executeAction(Execution::Context&, SessionLayer::SessionEvent*, ptime::Action&)+0x2d1 at ptime/session/sm_handler.cc:2573 (libhdbsqlsession.so)
28 : .LTHUNK390.lto_priv.1+0x18b at ptime/session/sm_handler.cc:2301 (libhdbsqlsession.so)
29 : ptime::SessionHandler::receiveMessage_(Execution::Context&, ptime::ThreadStatusCallbackBase&, SessionLayer::CommEvent*, bool)+0x8f9 at ptime/session/sm_handler.cc:2015 (libhdbsqlsession.so)
30 : ptime::SessionHandler::receiveMessage(Execution::Context&, SessionLayer::CommEvent*, bool)+0x30 at ptime/session/sm_handler.cc:1854 (libhdbsqlsession.so)
31 : ptime::TcpReceiver::doWork(Execution::Context&, SessionLayer::CommMgr*)+0xdeb at ptime/session/tcp_receiver.cc:392 (libhdbsqlsession.so)
32 : ptime::TcpReceiver::runInner(void*)+0xd6 at ptime/session/tcp_receiver.cc:503 (libhdbsqlsession.so)
33 : non-virtual thunk to ptime::TcpReceiver::run(void*)+0x30 at ptime/session/tcp_receiver.cc:480 (libhdbsqlsession.so)
34 : TrexThreads::PoolThread::run()+0xf22 at TrexThreads/PoolThread.cpp:437 (libhdbbasement.so)
35 : TrexThreads::PoolThread::run(Execution::ThreadRC&)+0x10 at TrexThreads/PoolThread.cpp:121 (libhdbbasement.so)
36 : Execution::Thread::staticMainImp(Execution::Thread*)+0x610 at Basis/Execution/impl/Thread.cpp:612 (libhdbbasis.so)
37 : Execution::pthreadFunctionWrapper(Execution::Thread*)+0x1eb at Basis/Execution/impl/ThreadInterposition.cpp:684 (libhdbbasis.so)
38 : start_thread+0xd8 (libpthread.so.0)
39 : __GI___clone+0x3d (libc.so.6)

HANA 1.0,  2.0

KBA , HAN-DB-PERF , SAP HANA Database Performance , How To