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 ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.