Return-Path: 
 <derby-user-return-13057-apmail-db-derby-user-archive=db.apache.org@db.apache.org>
Delivered-To: apmail-db-derby-user-archive@www.apache.org
Received: (qmail 30349 invoked from network); 21 Aug 2010 04:37:08 -0000
Received: from unknown (HELO mail.apache.org) (140.211.11.3)
  by 140.211.11.9 with SMTP; 21 Aug 2010 04:37:08 -0000
Received: (qmail 84349 invoked by uid 500); 21 Aug 2010 04:37:08 -0000
Delivered-To: apmail-db-derby-user-archive@db.apache.org
Received: (qmail 84201 invoked by uid 500); 21 Aug 2010 04:37:04 -0000
Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
list-help: <mailto:derby-user-help@db.apache.org>
list-unsubscribe: <mailto:derby-user-unsubscribe@db.apache.org>
List-Post: <mailto:derby-user@db.apache.org>
List-Id: <derby-user.db.apache.org>
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Delivered-To: mailing list derby-user@db.apache.org
Received: (qmail 84194 invoked by uid 99); 21 Aug 2010 04:37:03 -0000
Received: from Unknown (HELO nike.apache.org) (192.87.106.230)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 21 Aug 2010 04:37:03 +0000
X-ASF-Spam-Status: No, hits=0.0 required=10.0
	tests=FREEMAIL_FROM,RCVD_IN_DNSWL_NONE,SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (nike.apache.org: domain of simonzmail@gmail.com designates
 209.85.214.172 as permitted sender)
Received: from [209.85.214.172] (HELO mail-iw0-f172.google.com)
 (209.85.214.172)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 21 Aug 2010 04:36:38 +0000
Received: by iwn3 with SMTP id 3so4021524iwn.31
        for <derby-user@db.apache.org>; Fri, 20 Aug 2010 21:36:17 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=gmail.com; s=gamma;
        h=domainkey-signature:mime-version:received:received:in-reply-to
         :references:date:message-id:subject:from:to:content-type
         :content-transfer-encoding;
        bh=4GQibMT7a6fA7t9jXVk/f+01QcKD+XuAw1vXr4bz+gc=;
        b=gbwWOcc7AiyFNXPNrBSlJzlNcThzHacHy28525BknTK/wz5y+IrybGZ2cVNCApIlA2
         8Jc5EBo9R6L/QQqwYjoZ+n5wtsePlEZs/nBwI5xkGCeD0C/xVD/vU9bD+d1C0Y/BriXb
         dk/rXc4qDGLc/jB0Hq7gbEhGkGRmLi90tJrBA=
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=mime-version:in-reply-to:references:date:message-id:subject:from:to
         :content-type:content-transfer-encoding;
        b=M/cKGZT0mTpRHM4GYSBUjm3BRUpJikjkOzUbN2t0My9kppEvKnG0Hpb5iASfGQozIl
         qetFMcRXcGheg5t+4zg0cEKnWlP8jHFLf/RgE0osIo3+Omum1U3zYotBk+eKLsR0f60N
         Olc/Mh3mxPc495XBCcUeV5G3yZ0oDhw2iAfQw=
MIME-Version: 1.0
Received: by 10.231.183.67 with SMTP id cf3mr2634066ibb.187.1282365377406;
 Fri, 20 Aug 2010 21:36:17 -0700 (PDT)
Received: by 10.231.174.135 with HTTP; Fri, 20 Aug 2010 21:36:17 -0700 (PDT)
In-Reply-To: <4C6F2ED5.1010202@gmail.com>
References: <AANLkTileuPImOUL6OV7K_YeJqRkRK8Yrea49-rV7T8UA@mail.gmail.com>
	<4C2F5D65.3060800@sbcglobal.net>
	<AANLkTikB0AqeDVvoyYpJowj1xZNkv4WW5xJed0ds_GWe@mail.gmail.com>
	<4C6F2ED5.1010202@gmail.com>
Date: Sat, 21 Aug 2010 14:36:17 +1000
Message-ID: <AANLkTik84oYoZTokEsiaBgY2Yp=jZZF2fJbCwfcvA2YF@mail.gmail.com>
Subject: Re: Execution Plan Changes Dramatically between Executions
From: Simon <simonzmail@gmail.com>
To: Derby Discussion <derby-user@db.apache.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Virus-Checked: Checked by ClamAV on apache.org

Thanks Bryan!

This was so obvious in retrospect - I was totally befuddled by the
"heisenbug" nature of it (problem goes away when you try to examine
it!).

I've made a rather crude workaround:  I calculate a random number N then ad=
d

WHERE <N> =3D <N>

to the end of the query - it prevents the statement from being cached
but doesn't seem to slow down the query at all.  Now I get good
performance all the time!

I'll see if I can wrap this up into a test case.   It does seem like
"correct" behavior in a technical sense, but the outcome is certainly
weird / undesirable so if there's something that can be done to make
the optimizer avoid plans that have such a poor worst case performance
(even if they are the "best" plan for the specific data at that
moment) then that would definitely be a plus.

Cheers,

Simon

On Sat, Aug 21, 2010 at 11:41 AM, Bryan Pendleton
<bpendleton.derby@gmail.com> wrote:
>> So the question is, why does the optimizer suddenly get much smarter
>> just because I restarted the database? =A0Is it caching the execution
>> plan perhaps? =A0Can I make it be this smart all the time somehow?
>
> Derby definitely caches the compiled form of the statement, and will re-u=
se
> it.
>
> There is a way to completely disable that cache, I believe, check
> this bug report from some details:
>
> https://issues.apache.org/jira/browse/DERBY-4280
>
> Given the dramatic behavior changes in your situation, it would be
> awfully useful if you could package up whatever information you can
> about this and log it in the Derby Jira for future users benefit.
>
> Ideally, if you could contribute a test program which demonstrates
> the behavior that you describe, that would be great! But even things
> like query plans of the good and bad behavior, descriptions of the
> schemas, etc., can be of help.
>
> thanks,
>
> bryan
>