Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

The dynamic statement '*sh1731692265_0874276115ss*' could not be found.

Hello,

Our application is getting a lot of the following errors :

Caused by:
com.sybase.jdbc4.jdbc.SybBatchUpdateException: JZ0BE: BatchUpdateException:
Error occurred while executing batch statement: The dynamic statement
'*sh1731692265_0874276115ss*' could not be found. This may be due to
insufficient metadata descriptors. Increase the serverwide 'number of open
objects' parameter or the 'procedure cache size' parameter.

sp_monitorconfig 'all' shows that number of open objects is used less then 60%.

I've increased the procedure cache size, but the problem persists.

It is always the same query, and the problem can be reproduced.

I have executed :

dbcc procbuf

and found the name mentioned in the error :

PROCBUF:

address=0xfffffd6db143acd8   id=1731692265   pbdbid=2 

pbplannum=95848061

pbuid=1   pbihash=0x0   pbnhash=0x0   pbprev=0xfffffd6db1cf8eb0

pbtimestamp: Jul 14 2015  3:43PM

pbnext=0xfffffd6dad0ee6e8   pbprochdr=0xfffffd6e9bd16000   pbparam=0x0   pbtype=4

pbstate=1   pbstat=0x0   pbusecnt=0   pbspid=0   pbdes=0xfffffd6dac1e2bb8   pbxdes=0x0

pbsqt_desc=0x0

pbname='*sh1731692265_0874276115ss*'   pbprocnum=1  pbsharecnt=0  pboldplan=0

pbhold=0  prbuf_link->next =fffffd6dac1e2d90  prbuf_link->prev=fffffd6dac1e2d90

PROC_HDRs:

Memory usage for all PROC_HDRs starting at 0xfffffd6e9bd16000

--------------------------------------------------------

PROC_HDR at 0xfffffd6e9bd16000

p_hdrstep=0x0   p_hdrseq=0xfffffd6eb4d8f1d8   p_hdrcrt=0x0

p_hdrpbuf=0xfffffd6db143acd8   p_hdrtmps=0x0   p_hdrcaller=0x0

p_hdrelease=0   p_hdrtabid=1731692265   p_hdrsub=0x0

p_hdrsubcnt=0   p_css=0x0   p_csscnt=0   

p_hdrstatus=1 ((0x00000001 (CACHED)))

p_hdrstat2=0 ((0x00000000))

p_lastpg=0  p_lastoff=3  p_procnum=0  p_hdrdbid=59 p_tmpdbid=0

p_lostcnt=14   p_hdrtype=1 p_pmctrlp=0xfffffd6e60bcbbe8

Memory Allocation Map:

0 mempgptr=0xfffffd6e9bd16000   pma_chunksize=2048   byte_count=2008   byte_save=2008

1 mempgptr=0xfffffd6ea97c2000   pma_chunksize=4096   byte_count=3168   byte_save=3168

2 mempgptr=0xfffffd6e8576d000   pma_chunksize=4096   byte_count=3824   byte_save=3824

3 mempgptr=0xfffffd6eb4d8e800   pma_chunksize=8192   byte_count=2992   byte_save=2992

Summary of memory usage:

    Total # of PROCHDRs used            : 1

    Total # of pages used               : 9

    Total # of bytes used               : 11992

    Total # of lost bytes for alignment : 14

    Total # of pages lost for alignment : 0

    Total percentage of memory lost     : 0

I want to remove this procedure from the cache.

The only command I know that accepts an id is purgesqlcache, but I assume that this removes an entrie from the statement cache.

Anyway, I gave it a try.

> dbcc purgesqlcache(1731692265);

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
Statement (id=1731692265) not found in the cache.

Ok, as expected.

I ran dbcc proc_cache(free_unused) which removed the proc_buf above. (and a lot more :-) ).

I have to wait until tomorrow when our programmers are back at work to know if this solved the problem.

In the meanwhile I want to ask if there is something I'm missing, or if there are anything else I should try/execute when this problem persists/returns.

We're running ASE 15.7 SP134 on solaris intel.

Thanks,

Luc.

Former Member
Not what you were looking for? View more on this topic or Ask a question