db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kim Haase <camilla.ha...@oracle.com>
Subject Re: when should a user run SYSCS_INVALIDATE_STORED_STATEMENTS?
Date Wed, 24 Oct 2012 20:25:54 GMT
Thanks for both the question and the answer, Rick and Kathey. I'll 
reopen DERBY-5793 to incorporate this information.

Currently the only guidance is "This procedure can be especially useful 
after an upgrade." Is that still true or should I replace it?

Thanks again,

On 10/24/12 02:29 PM, Katherine Marsden wrote:
> On 10/24/2012 11:06 AM, Rick Hillegas wrote:
>> I'm wondering what we should tell users about the new
> I'd vote for 1 with a bit more of an explanation:
> I) Run SYSCS_INVALIDATE_STORED_STATEMENTS whenever you think that your
> metadata queries or triggers are mis-behaving, for example if they throw
> a NoSuchMethod error on execution. Derby stores plans for triggers and
> metadata queries in the database. These should be invalidated
> automatically on upgrade and at other necessary times. Should you
> encounter an instance where they are not, you have found a bug that you
> should report, but one that you can likely work around by running
>> 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.
>> III) Only run SYSCS_INVALIDATE_STORED_STATEMENTS when tech supports
>> tells you to.
>> IV) Something else?
>> What follows are some additional musings which led me to ask this
>> question.
>> Thanks,
>> -Rick
>> ---------------------------------------------------
>> 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
>> trigger.
>> 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