Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 82777 invoked from network); 21 Aug 2010 01:42:14 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 21 Aug 2010 01:42:14 -0000 Received: (qmail 1127 invoked by uid 500); 21 Aug 2010 01:42:14 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 1045 invoked by uid 500); 21 Aug 2010 01:42:13 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 1038 invoked by uid 99); 21 Aug 2010 01:42:13 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 21 Aug 2010 01:42:13 +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 (athena.apache.org: domain of bpendleton.derby@gmail.com designates 209.85.160.44 as permitted sender) Received: from [209.85.160.44] (HELO mail-pw0-f44.google.com) (209.85.160.44) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 21 Aug 2010 01:42:05 +0000 Received: by pwi1 with SMTP id 1so1770894pwi.31 for ; Fri, 20 Aug 2010 18:41:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from :user-agent:mime-version:to:subject:references:in-reply-to :content-type:content-transfer-encoding; bh=AFHn0LRrOfpvYR7uCm9klvsRPn7+R59BRc3JI6+UfWA=; b=RZADUThagU7rz7Szp5mX0ZpMg5lEyKyGixXjmtzfQLQ1iYyMNt5SXd3m3ikbZHTSPY H6WXjHrxcG71oGfoHPLA6bD1Jx73AVlLG8bDpnceM109QW70jkl8iQgOQbPzPbX3/Sov DFO4idUCo3SRglnkZ+0IIZJhypYKGxliRfa7g= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:subject:references :in-reply-to:content-type:content-transfer-encoding; b=OkhOTBqzCE8O9BLuhb7oJJ/Mq8ArlFTknsP9zwWjSG467Vmpjn7Sdo7EDdpnFTmEFA kJ4EOdFrN0Jwyul5/hSOBG0eb8dqfnNCaNIcx7VmSOSsNEJknXtcEWtlQBm8dKW0I0vT 95ukR/K6oFYcTQoUq+zqQK78KJXFwryg7zNj4= Received: by 10.142.103.14 with SMTP id a14mr1651656wfc.245.1282354903857; Fri, 20 Aug 2010 18:41:43 -0700 (PDT) Received: from [192.168.0.103] (c-67-170-231-73.hsd1.ca.comcast.net [67.170.231.73]) by mx.google.com with ESMTPS id b19sm4034480wff.7.2010.08.20.18.41.42 (version=TLSv1/SSLv3 cipher=RC4-MD5); Fri, 20 Aug 2010 18:41:42 -0700 (PDT) Message-ID: <4C6F2ED5.1010202@gmail.com> Date: Fri, 20 Aug 2010 18:41:41 -0700 From: Bryan Pendleton User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.11) Gecko/20100713 Thunderbird/3.0.6 MIME-Version: 1.0 To: Derby Discussion Subject: Re: Execution Plan Changes Dramatically between Executions References: <4C2F5D65.3060800@sbcglobal.net> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit > So the question is, why does the optimizer suddenly get much smarter > just because I restarted the database? Is it caching the execution > plan perhaps? Can I make it be this smart all the time somehow? Derby definitely caches the compiled form of the statement, and will re-use 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