XPLAIN and statement recompilation (invalidation)

[Kristian Waagan <kr...@oracle.com>]

  Hi,

When testing the automatic index statistics update prototype, 
lang.XplainStatisticsTest started failing intermittently.
A quick investigation suggests this is caused by a recompilation request 
being raised at that "appropriate" place in the code. In this case the 
recompilation is caused by the invalidation request made by the index 
stats daemon when writing the new statistics to the data dictionary.
On my machine the test fails one to two times out of 25 when run standalone.

The result of the above event is that an entry for the query is added 
twice to SYSXPLAIN_STATEMENTS; once when the recompilation of the query 
is triggered, and once when the query has completed. Both happens due to 
an internal result set being closed.

It is simple enough to avoid this in the test, but the question is if 
this can be a problem in the more general case?
In the worst case, the contents of SYSXPLAIN_STATEMENTS (and potentially 
other XPLAIN tables) may not correspond completely to the queries issued 
by the user(s).

For the curious, the code causing the addition of an entry to the XPLAIN 
table is [1] (see around line 389 in the close-method).

I'll probably file a JIRA to track this, as I don't think the code was 
intended to work in this way.
I don't plan to fix it (now) as I suspect the user impact is low.


-- 
Kristian

[1] 
http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoRowsResultSetImpl.java?view=markup 

Re: XPLAIN and statement recompilation (invalidation)

[Kristian Waagan <kr...@oracle.com>]

  On 14.10.10 03:46, Bryan Pendleton wrote:
>> The result of the above event is that an entry for the query is added
> > twice to SYSXPLAIN_STATEMENTS; once when the recompilation
>> of the query is triggered, and once when the query has completed.
>
> This surprises me. I don't understand why re-compilation of the query
> is getting all the way to the execution phase; I would have expected
> the compilation to be performed without ever opening and closing
> execution-phase result sets.
>
> I think it would be great if you could log this and even better if
> we could come up with a simple and predictable test case that 
> demonstrates
> it, as it sounds like the index statistics technique is somewhat
> unpredictable.

Hi Bryan,

I don't think I'll have time to follow up on this now, but I have logged 
it as DERBY-4849 and attached the little information I have to that JIRA 
issue.
Unfortunately, I don't know how to reproduce this easier at this point.


-- 
Kristian

>
> This is a very interesting finding, and thanks for tracking
> it down and raising it!
>
> bryan

Re: XPLAIN and statement recompilation (invalidation)

[Bryan Pendleton <bp...@gmail.com>]

> The result of the above event is that an entry for the query is added
 > twice to SYSXPLAIN_STATEMENTS; once when the recompilation
> of the query is triggered, and once when the query has completed.

This surprises me. I don't understand why re-compilation of the query
is getting all the way to the execution phase; I would have expected
the compilation to be performed without ever opening and closing
execution-phase result sets.

I think it would be great if you could log this and even better if
we could come up with a simple and predictable test case that demonstrates
it, as it sounds like the index statistics technique is somewhat
unpredictable.

This is a very interesting finding, and thanks for tracking
it down and raising it!

bryan