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.