on 07-14-2015 9:06 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
80 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.