db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject when should a user run SYSCS_INVALIDATE_STORED_STATEMENTS?
Date Wed, 24 Oct 2012 18:06:35 GMT
I'm wondering what we should tell users about the new 

I) Run SYSCS_INVALIDATE_STORED_STATEMENTS whenever you think that your 
metadata queries or triggers are mis-behaving.

II) Run SYSCS_INVALIDATE_STORED_STATEMENTS whenever you upgrade the 
version of Derby which you are using.

tells you to.

IV) Something else?

What follows are some additional musings which led me to ask this question.



My first step in buddy-testing the SYSCS_INVALIDATE_STORED_STATEMENTS 
procedure was to read the user documentation in the Reference Manual. 
The section on SYSCS_INVALIDATE_STORED_STATEMENTS talks about stored 
prepared statements and mentions the SYSSTATEMENTS table.

I don't think that our doc set explains, anywhere, what a stored 
prepared statement is. The Reference Manual section on SYSSTATEMENTS 
doesn't explain what they are or why Derby creates them. I imagine users 
must be a little confused by the explanation for SYSTATEMENTS.USINGTEXT, 
which refers to the otherwise undocumented CREATE STATEMENT and ALTER 
STATEMENT syntax. And I can't find any other explanation of stored 
prepared statements in our doc set.

So when I read the section on SYSCS_INVALIDATE_STORED_STATEMENTS, I 
imagined that a user might ask:

1) How do I know that I need to run this procedure?

2) Should I run this procedure every time I hard-upgrade the database?

3) Should I run this procedure whenever I upgrade the version of Derby 
which I'm using?

I think that the following is true:

a) Derby persists stored prepared statements in order to improve the 
performance of metadata queries and triggers the first time they run 
after the database has booted. That is, stored prepared statements are 
meant to save the first user the cost of compiling a metadata query or 

b) There are only 2 reasons for a user to forcibly recompile a stored 
prepared statement:

   i) Derby had a bug when it originally compiled the metadata query or 
trigger and now that bug has been fixed.

   ii) The persistent form of query plans has changed between versions 
of Derby and the old metadata and trigger plans need to be discarded.

View raw message