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 Re: when should a user run SYSCS_INVALIDATE_STORED_STATEMENTS?
Date Thu, 25 Oct 2012 18:24:54 GMT
On 10/25/12 10:31 AM, Mamta Satoor wrote:
> In addition to the text suggested by Kathey, I am wondering if we
> should this same info in ReleaseNotes as well. Since users typically
> will read the ReleaseNote at the time of ugrade, they will have the
> knowledge on what to do if in case metadata queries or triggers are
> misbehaving.
Hi Mamta,

I think that this is a good idea. I expect that the 10.10.1 release 
notes will list SYSCS_INVALIDATE_STORED_STATEMENTS as one of the 
features of the release. The paragraph describing 
SYSCS_INVALIDATE_STORED_STATEMENTS should include this advice.

> Mamta
> On Wed, Oct 24, 2012 at 1:25 PM, Kim Haase<camilla.haase@oracle.com>  wrote:
>> 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,
>> Kim
>> 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
>>>> SYSCS_INVALIDATE_STORED_STATEMENTS procedure. Should we say:
>>> 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