You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Rick Hillegas <ri...@oracle.com> on 2012/10/24 20:06:35 UTC
when should a user run SYSCS_INVALIDATE_STORED_STATEMENTS?
I'm wondering what we should tell users about the new
SYSCS_INVALIDATE_STORED_STATEMENTS procedure. Should we say:
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.
Re: when should a user run SYSCS_INVALIDATE_STORED_STATEMENTS?
Posted by Rick Hillegas <ri...@oracle.com>.
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.
Thanks,
-Rick
> Mamta
>
> On Wed, Oct 24, 2012 at 1:25 PM, Kim Haase<ca...@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
>>> SYSCS_INVALIDATE_STORED_STATEMENTS.
>>>
>>>
>>>> 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.
>>>>
>>>>
>>>
Re: when should a user run SYSCS_INVALIDATE_STORED_STATEMENTS?
Posted by Mamta Satoor <ms...@gmail.com>.
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.
Mamta
On Wed, Oct 24, 2012 at 1:25 PM, Kim Haase <ca...@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
>> SYSCS_INVALIDATE_STORED_STATEMENTS.
>>
>>
>>>
>>> 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.
>>>
>>>
>>
>>
>
Re: when should a user run SYSCS_INVALIDATE_STORED_STATEMENTS?
Posted by Kim Haase <ca...@oracle.com>.
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
> SYSCS_INVALIDATE_STORED_STATEMENTS.
>
>
>>
>> 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.
>>
>>
>
>
Re: when should a user run SYSCS_INVALIDATE_STORED_STATEMENTS?
Posted by Katherine Marsden <km...@sbcglobal.net>.
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
SYSCS_INVALIDATE_STORED_STATEMENTS.
>
> 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.
>
>