db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jack Klebanoff <kleba...@Mutagen.Net>
Subject Re: Optimizer hints?
Date Sat, 18 Dec 2004 01:22:18 GMT
RPost wrote:

 >Do you have any specific examples of queries that could benefit from an
 >optimizer hint?

This thread started with an example of a query that could benefit from 
an optimizer hint. Gerald Khin has a query that blows up because the 
Derby  optimizer made the wrong choice. See 

Most database systems that have been around for a while have implemented 
optimizer hints. Sooner or later they come across performance critical 
queries where the optimizer makes a bad choice, but the optimizer cannot 
be fixed easily or quickly. (Adding an optimizer hint is generally 
faster than waiting for a new optimizer release).


 >Does anyone know of any documented instances where Derby has selected an
 >execution plan that is ineffective or suboptimal? Were these instances 
 >to be corrected by tuning the system using existing
 >Oracle allows hints to be provided as SQL comments.
 >Re providing an offline tool. Does this method imply that the statements
 >exist independent of any given running database instance. If so are you
 >proposing that a prepared statement be saved and made available for future
 >execution without preparing it again?
 >----- Original Message -----
 >From: "Satheesh Bandaram" <satheesh@Sourcery.Org>
 >To: "Derby Development" <derby-dev@db.apache.org>
 >Sent: Friday, December 17, 2004 2:41 PM
 >Subject: Optimizer hints?

> I have been thinking about adding optimizer hints to Derby. Though Derby
> optimizer does perform a reasonable job, it may be useful to have
> optimizer hints for cases 1) when updated index statistics is not
> available, or even incorrect 2) Rare cases, like this one?, when the
> optimizer doesn't do the job right 3) when applications issue queries
> not designed for Derby (like very large number of tables).
> Derby optimizer is primarily tasked to handle 1) access method (whether
> to use table scan or index) 2) join order and 3) join strategy (nested
> loop or HashJoin) A complete optimizer hints (should they actually be
> called optimizer overrides?) should be able to provide means of
> specifying all these. There are several ways to do this, including
>    1. Enhance the SQL to recognize additional keywords, like properties.
> These properties could specify optimizer hints. That is what Cloudscape
> had before, but this causes non-portable SQL to be written. Attempts to
> run these statements against any other SQL server could cause syntax
> errors. Not an ideal solution, according to me.
>    2. Provide optimizer hints as SQL comments. These comments are
> recognized only by Derby parser and other SQL engines would simply
> ignore these. There are some limitations in Derby parser currently to
> implement this option.
>    3. Provide an offline tool which registers hints for SQL statements.
> When those SQL statements are executed, Derby could look up previously
> registered hints for those statements and automatically apply them.
> Advantage of this scheme is that applications don't need to be modified
> to add hints and they can easily be removed when not needed. How exactly
> to register the hints could be worked out. I personally prefer this
> approach.
> Any comments?
> Satheesh
> Gerald Khin (JIRA) wrote:
> >HashJoinStrategy leads to java.lang.OutOfMemoryError
> >----------------------------------------------------
> >        Key: DERBY-106
> >        URL: http://nagoya.apache.org/jira/browse/DERBY-106
> >    Project: Derby
> >       Type: Bug
> >   Reporter: Gerald Khin
> >My application is running out of memory: I encounterd a
> java.lang.OutOfMemoryError. I used -Xmx256M. Unfortunatley, I cannot
> spend an arbitrary amount of JVM memory.
> >Then, I commented out the line in class OptimizerFactoryImpl which was
> adding the HashJoinStrategy to the set of Join strategies:
> >       if (joinStrategySet == null)
> >       {
> >//            JoinStrategy[] jss = new JoinStrategy[2];
> >           JoinStrategy[] jss = new JoinStrategy[1];
> >           jss[0] = new NestedLoopJoinStrategy();
> >//            jss[1] = new HashJoinStrategy();
> >           joinStrategySet = jss;
> >       }
> >And with these changes the OutOfMemoryError has gone away! And it works
> even with -Xmx128M!!!
> >So I guess that there is a major memory issue with this HashJoin
> strategy implementation.
> >If it turns out to be too complicated to make the memory consumption
> more predicatble or even bounded to some configurable limit, then I need
> at least as a workaround a way to turn off the HashJoin strategy
> completely: I did it by patching and building my own derby.jar, but if
> there would be an official solution with some kind of switch like a
> system property, it would be great!

View raw message