2231450 - SQL701 "There is not enough procedure cache to run this procedure.." for large IN queries - SAP ASE for Business Suite

You are running SAP applications on SAP ASE database.

Certain SAP jobs fail with SQL701 errors indicating insufficient procedure cache. Increasing procedure cache does not help. Huge OR queries are involved in excessive procedure cache usage.

• ASE engines show 100% cpu_busy utilization
• Front-end transactions/program running the culprit queries time-out
• Corresponding ASE processses however, cannot be killed

Examples of affected SAP programs would be: ERP, MRP transactions, PI jobs accessing BC_MSG  

Examples of affected ASE query constructs would be:

• CREATE PROC ABC AS SELECT TOP 20000 * FROM "TABLE1" WHERE "MANDT" = ? AND ( "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ?  OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? OR "CONTRACT" LIKE ? ...)
• CREATE PROC [E1D] AS SELECT <COLUMN LIST> FROM "TABLE2" WHERE "MANDT" = ? AND "ERA" = ? AND "SBU" = ? AND ( "MATNR" LIKE ? OR "MATNR" IN ( ? , ? , ? , ? , ? , ? , ? , ? ,? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,....)
• SELECT <COLUMN LIST> FROM "TABLE3" WHERE "MANDT" = ? AND "MATNR" <> ? AND ( "SERNR" LIKE ? OR "SERNR" IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
  , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?,...........)

You will also notice excessively high procedure cache usage by the Optimizer module, when the usage is checked in the monProcedureCacheModuleUsage table.
Note the high number of "Active" pages (2K page units) for the Optimizer module, in the example below:

select * from monProcedureCacheModuleUsage

InstanceID ModuleID    Active      HWM         NumPagesReused ModuleName
---------- ----------- ----------- ----------- -------------- ------------------------------
          0           1           0        2996              0 Parser
          0           2           0        6259              0 Utilities
          0           3           0           0              0 Diagnostics
          0           4     5382624     7291458              0 Optimizer

          0           5      501204      766648              0 Execution
          0           6           0        4925              0 Access
          0           7          10        8306              0 Backup
          0           8           0         368              0 Recovery
          0           9           0           9              0 Replication
          0          10       17385      251553         273338 Procedural Objects
          0          11        8297      124058              0 Sort
          0          12          11        1310              0 HK GC
          0          13           0           0              0 HK Chores
          0          14           0      166843              0 BLOB Management
          0          15        7305        7308              0 Partition Conditions
          0          16           1           1              0 Pdes Local HashTab
          0          17       58929      488519          19062 Statement Cache
          0          18           2          21              0 CIS
          0          19           5          17              0 Frame Management
          0          20           0           0              0 AuxBuf Management
          0          21           0           4              0 Network
          0          22        9300        9300              0 Procmem Control
          0          23        6417        6417              0 Data change
          0          24      206366     1332141        2218345 Dynamic SQL
          0          25           0           0              0 Cluster Threshold Manager
          0          26           2           2              0 Multiple Temporary Database
          0          27           0           0              0 Workload Manager
          0          28           0           0              0 Transaction
          0          29           0           0              0 Cluster Membership Service

• SAP Adaptive Server Enterprise (ASE) 15.7 for Business Suite
• SAP Adaptive Server Enterprise (ASE) 16.0 for Business Suite
• SAP NetWeaver (NW) - All versions 
• SAP Enterprise Resource Planning (ERP) 6.0
• SAP NetWeaver JAVA PI - All versions

SQL701, procedure cache, LIKE, 791363, 791364, 799039, memalloc, monProcedureCacheModuleUsage , KBA , BC-DB-SYB , Business Suite on Adaptive Server Enterprise , Problem