SAP Knowledge Base Article - Preview

2755228 - Multi-join queries are very slow and time out - SAP ASE for Business Warehouse

Symptom

  • On SAP BW, Solution Manager systems, multi-JOIN queries are very slow and time out.

  • The affected sql query contains 50-100 or more JOIN clauses.

  • The optcriteria alternative_greedy_search is enabled under the sap_olap optimization goal.

  • In some cases, multi-join queries may fail with Error: 701, Severity: 17, State: 3 There is not enough procedure cache to run this procedure, trigger, or SQL batch. Retry later, or ask your SA to reconfigure ASE with more procedure cache.
  • Almost all of the configured procedure cache space is used up for query compilation.
  • The corresponding ASE process (spid) is in COMPILING status and may terminate with Error 701.
  • The server encounters several Error 701s soonafter and becomes inaccessible. ASE db needs to be restarted to clear the problem.
  • The solution described in SAP KBA 2593791 - Error 701 "There is not enough procedure cache..." to run large BW queries - SAP BW on ASE, is already implemented.


Read more...

Environment

  • SAP Adaptive Server Enterprise (ASE) 16.0 for Business Warehouse
  • SAP NetWeaver 7.x
  • SAP Solution Manager 7.2

Product

SAP Adaptive Server Enterprise 16.0 ; SAP NetWeaver Business Warehouse 7.2

Keywords

time-out, "time out", "fact table hint", slow, performance, 701, "procedure cache", 0SPRINCU, 0SPRINSNP,  CR813639, CR#813639, 813639, TF, traceflag, 11836, forceplan_alternative_greedy, forceplan, sap_olap , KBA , BW-SYS-DB-SYB , BW on Adaptive Server Enterprise , Bug Filed

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.