db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "RPost" <rp0...@pacbell.net>
Subject Re: Optimizer hints?
Date Sat, 18 Dec 2004 02:20:09 GMT
Jack - I have to disagree.

Gerald's post is at the end of your response and I sure don't see any query
in it.

I checked your link to jira's DERBY-106 and don't see either a query there
or an attached file that provides the query.

What am I missing?

Gerald did say that when he prevented the optimizer from considering a
hashjoin his query worked even with a memory setting of 128M.This doesn't
really tell us much. There could be a bug in the optimizer or in the
hashjoin code itself. It could also mean that the table is too big for the
property settings that are being used.

As you said: '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.

Exactly! The first order of business is to identify the conditions where the
Derby optimizer makes a bad choice. Until these conditions are identified
there is nothing to fix.

Here is what I would like to know:

1. What is the environment (sysinfo)?
2. What are the JVM settings being used
3. What query is being executed?
4. What execution plan is Derby using now? If we don't know what choice the
optimizer is making I don't know how it can be considered wrong.

5. How big is the table? (You ask this in your initial reply)

I think hints are great. I use them in Oracle all the time.

I just don't agree that we have enough information yet to diagnose Gerald's
problem let alone propose hints as a possible solution.

----- Original Message ----- 
From: "Jack Klebanoff" <klebanof@Mutagen.Net>
To: "Derby Development" <derby-dev@db.apache.org>
Sent: Friday, December 17, 2004 5:22 PM
Subject: Re: Optimizer hints?


> 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
> http://nagoya.apache.org/jira/browse/DERBY-106
>
> 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).
>
> Jack
>
>  >
>  >Does anyone know of any documented instances where Derby has selected an
>  >execution plan that is ineffective or suboptimal? Were these instances
> able
>  >to be corrected by tuning the system using existing
>  >properties?
>  >
>  >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!
> >
> >
> >
>


Mime
View raw message