Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: neutral (asf.osuosl.org: local policy)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: RE: Derby Optimizer Hangs
Date: Wed, 11 Oct 2006 14:27:58 -0700
Message-ID: <97085FEE4C8BDB4AB6FA3E770EBC79BB591387@mail1.cyclonecommerce.com>
In-Reply-To: <452D600E.5000405@gmail.com>
Thread-Topic: Derby Optimizer Hangs
thread-index: Acbtexv8TffORs4AS0S/PDPc8Yw/TAAAMlOA
From: "Amit Patel" <apatel@us.axway.com>
To: "Derby Discussion" <derby-user@db.apache.org>

Thanks Army.

It turns out you are right.  I took several thread dumps and the
execution was in different places within the optimizer.  It looks like
it's just spending a lot of time figuring out what's best.  I'll try the
FIXED option, but I'm already in the process of re-writing the query to
lower the number of joins.

Amit

-----Original Message-----
From: Army [mailto:qozinx@gmail.com]=20
Sent: Wednesday, October 11, 2006 2:20 PM
To: Derby Discussion
Subject: Re: Derby Optimizer Hangs

Amit Patel wrote:
> I ran into a problem with Derby.  When I issue a large SQL statement
> with lots of WHERE conditions, the Derby optimizer looks like it's in
an
> infinite loop.

Do you know how long the query was running before you took the thread
dump?  If=20
you repeat the process of running the query and taking the thread dump,
do the=20
threads look the same every time?

> Does anyone know if this is a bug or can I tune Derby to get around
this
> issue?

It's hard to say with just the info given.  From a quick glance it looks
like=20
your joining 14 tables in this query.  If that's true, the Derby
optimizer is=20
going to try out all possible orderings for the 14 tables until it
either=20
finishes or "times out".  There are known issues in this area that could
cause=20
the optimizer to spend a *lot* of time trying to figure out what the
best plan=20
is--see for example DERBY-1906, DERBY-1907, DERBY-1905.  In such a case
it might=20
look like the optimizer has hung when in fact it's just busy trying out
all of=20
the various combinations.

Since you're using 10.2, one thing you can do is use optimizer overrides
to tell=20
the optimizer to skip optimization of join order--i.e. to just join the
tables=20
in the order in which they appear in the query.  To do that you can
specify the=20
"joinOrder" optimizer override as follows:

   SELECT DISTINCT t0.OID, t0.JDOVERSION,
t0.BINARYCOLLABORATIONHASHCODE,
   ...
   FROM --DERBY-PROPERTIES joinOrder=3DFIXED
   DBA.MESSAGES t0 INNER JOIN DBA.CUSTOMDATA t1 ON t0.OID =3D
t1.MESSAGEOID
   ...

Note that you must have an end-of-line after specifying the
joinOrder=3DFIXED=20
property.

If the query completes in a reasonable time with this property set, then
it's=20
probably the case that, in your original query, the optimizer is
spending time=20
trying out all of the various join orders for the 14 tables.

If the query still "hangs" with this property set, then it might be good
if you can:

   1. Reduce the number of tables and/or WHERE predicates to find the
minimum=20
number of tables/predicates required to reproduce the hang.

   2. Take note of how long the query actually runs.  Is it minutes,
hours, days?

   3. See the following wiki page for additional suggestions:

      http://wiki.apache.org/db-derby/PerformanceDiagnosisTips

As a general rule, more info is better.  The above wiki page describes
the kind=20
of information that is most helpful in debugging situations like these.
If you=20
can start there, someone (perhaps even you!) may be able to provide
further=20
insight as to what's happening in this query...

Army