cancel
Showing results for 
Search instead for 
Did you mean: 

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

luc_vanderveurst
Participant
0 Kudos

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.

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

I assume jConnect is in the mix - do you know version?  You can run java -jar jconn4.jar (assume it is 7.07).

Sounds like Dynamic prepare is enabled - to test the theory see if you can set connection property DYNAMIC_PREPARE=false and run that query.  See if the error goes away.

Cheers,

-Paul


jayrijnbergen
Active Participant
0 Kudos

Anything reported in the ASE logfile?

You can enable logging for error 701: running out of procedure cache

This error is normally not reported in the ASE logfile, but would be useful to have logged

exec sp_altermessage 701, 'with_log', true

luc_vanderveurst
Participant
0 Kudos

Hello Jeroen,

No, nothing is reported in the errorlog.

I have altered that message. Executing the query still doesn't report something in the errorlog.

We have this with only one query, it's a small table, so I copied it into a table with a different name, renamed the original table and created a view on the new table with the original name.

That worked and eliminated our production problem.

The problem was still reproducable on the old table.

Difference with the new situation is that triggers were added to the new table and didn't cause problems.

So I dropped and recreated the update trigger on the old table, and then the problem disappeared.

Conclusion is that there must have been some corruption in the trigger code.

The original situation is restored and the query is running fine.

Thanks,

Luc.

former_member198560
Active Participant
0 Kudos

Please Run dbcc upgrade_object() for each database to explicitly re-compile the objects as Post Upgrade activity.