Re: (DERBY-4908) Instability in CheckConstraintTest.testBuiltInFunctions

[Knut Anders Hatlen <kn...@oracle.com>]

dag.wanvik@oracle.com (Dag H. Wanvik) writes:

> Bryan Pendleton <bp...@gmail.com> writes:
>
> <good explanation of optimizer workings>
>
> Doesn't this still leave the question why the test is unstable?: What
> could make the optimizer choice vary from test run to test run? I can
> think of the maximum time the optimizer would spend before it gives up
> looking for further plans, and that this could lead to different choice
> depending on machine performance, load etc. Does this test involve a
> large join? 

I don't think the join is large, but the query that gets the unexpected
result is indeed a join:

        rs = st.executeQuery(
            " select  c.type from "
            + "sys.sysconstraints c, sys.systables t where "
            + "c.tableid = t.tableid and tablename='T1'");

I'm not 100% sure the failure was caused by wrong ordering, though,
since assertFullResultSet only reports the first mismatch it
finds. Perhaps it would be a good improvement to get assertFullResultSet
to dump the full result on a mismatch?

> If not, what else could make the optimizer choice non-predictable?

I think the memory footprint and the available memory are also used to
determine the cost of a hash scan (if it doesn't fit in main memory, it
will be considered more expensive).

-- 
Knut Anders

Re: (DERBY-4908) Instability in CheckConstraintTest.testBuiltInFunctions

[Lily Wei <li...@yahoo.com>]

> Bryan Pendleton <bp...@gmail.com> writes:
>
> <good explanation of optimizer workings>
>
> Doesn't this still leave the question why the test is unstable?: What
> could make the optimizer choice vary from test run to test run? I can
> think of the maximum time the optimizer would spend before it gives up
> looking for further plans, and that this could lead to different choice
> depending on machine performance, load etc. Does this test involve a
> large join? 

I don't think the join is large, but the query that gets the unexpected
result is indeed a join:

        rs = st.executeQuery(
            " select  c.type from "
            + "sys.sysconstraints c, sys.systables t where "
            + "c.tableid = t.tableid and tablename='T1'");

I'm not 100% sure the failure was caused by wrong ordering, though,
since assertFullResultSet only reports the first mismatch it
finds. Perhaps it would be a good improvement to get assertFullResultSet
to dump the full result on a mismatch?

> If not, what else could make the optimizer choice non-predictable?

I think the memory footprint and the available memory are also used to
determine the cost of a hash scan (if it doesn't fit in main memory, it
will be considered more expensive).

I would think the full result set will be nice to have. Test optimizer on the 
path choosing based on row size, memory size and else will be great as well.

Lily


      

Re: (DERBY-4908) Instability in CheckConstraintTest.testBuiltInFunctions

["Dag H. Wanvik" <da...@oracle.com>]

Knut Anders Hatlen <kn...@oracle.com> writes:

> I think the memory footprint and the available memory are also used to
> determine the cost of a hash scan (if it doesn't fit in main memory, it
> will be considered more expensive).

That seems a good condicate culprit here, especially when running the
entire test suite. I guess people run with different heap sizes.. and/or
we have some leakage. Not sure if anyone has tested to leaks recently?

Dag


--