db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Optimizer hints?
Date Tue, 21 Dec 2004 00:40:47 GMT
Hash: SHA1

I don't have specific queries, but I can give examples where the
optimizer may not have the right information to pick a good plan:

1) queries involving tables with no indexes have no data distribution
~   maintained by the system, so any guess the optimizer makes may be
~   wrong for a dataset.
2) queries involving indexes basically use the shape of the tree to
~   estimate data distribution.  The cost information for an estimate
~   of number of rows between key1 and key2, assumes uniform distribution
~   throughout the tree.
3) because of #2, multi-column indexes (key1, key2) don't provide data
~   distribution information on key2 - so again the optimizer just uses
~   a default distribution guess.

RPost wrote:
| Do you have any specific examples of queries that could benefit from an
| optimizer hint?
| 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
| 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!
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org


View raw message