3193727 - Dynamic SQL with LOB takes too much Procedure Cache results in Error 701 and timeslice - SAP ASE

• Error 701 message:
      
  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.
             

• LOB parameter of dynamic SQL(s) took most memory from Proc Cache Header memory pool.
             
  High 'Active' number of memory pages with memory ModuleName "BLOB Management' in  monProcedureCacheModuleUsage output or sybmon command procmem output.
                 

• The heavy memory usage process had the following symptoms:

1. 1. High cpu for current statement (pss->pcpucur) due to keep allocating memory
   2. Routine tdsrecv_stream_LOBparam on stack
   3. 'dbcc pss' or sybmon pss command shows BLOB Management took most memory.
      
      Sample output in sybmon command 'pss 456' from a memory dump on error 701:
          
      spid: 456, kpid: 335479870, pss: 0x0x7f2860f35148, proc: 0x0x7f27878a0be8 dbid: 18
      task: 0x7f26a04524a8, threadpool: 0x7f26a022a400 (syb_default_pool)
      pmemusage: 28, ptempdb_pages: 0, pcputot: 23, pcpucur: 2421                      
      User: USER_NAME, Host: HOST_NAME, Program: java.lang.Thread, Command: 0 AWAITING COMMAND
      Status: (0x00010000 (P_USERPROC))
      ……
      pmctrl=0x0x7f2ad0581460
                       pmctrl_memp=0x0x7f2af6d17800 pmctrl_lastphdr=0x0x7f2b0940f000
                       pmctrl_pgcnt=981327, pmctrl_spid=456
                       pmctrl_allocid=243 (MEMC_LELOBVALUE_1) pmctrl_moduleid=14 (BLOB Management)       
      ---------------------------------------
      BLOB Management took 981327 (2k memory pages, about 1.9 Gbs) to stream LOB parameter for dynamic SQL for this process
      ---------------------------------------
   4. After raised error 701, the process does not release memory used by BLOB Management.

SAP Adaptive Server Enterprise (ASE)

CR 826455, 826455, CR# 826455 error 701, tdsrecv_stream_LOBparam, BLOB Management , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , BC-DB-SYB , Business Suite on Adaptive Server Enterprise , Known Error