SAP Knowledge Base Article - Preview

2755228 - Multi-join queries are slow, fail with Error: 701 - SAP ASE for Business Warehouse

Symptom

  • On SAP BW, Solution Manager systems, multi-JOIN queries are very slow and/or fail with Error 701 / SQL701: 

    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 failing sql query contains several (50-100 or more) JOIN clauses

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

  • 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
  • SAP NetWeaver Business Warehouse 7.x
  • SAP Solution Manager 7.2

Product

SAP Adaptive Server Enterprise 16.0 ; SAP NetWeaver Business Warehouse 7.2

Keywords

Solution Manager, "fact table hint", slow, performance, 701, "Error: 701", SQL701, "procedure cache", 0SPRINCU, 0SPRINSNP,  CR813639, CR#813639, 813639, TF, traceflag, 11836, forceplan_alternative_greedy, forceplan , 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.