db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Amit Patel" <apa...@us.axway.com>
Subject Derby Optimizer Hangs
Date Wed, 11 Oct 2006 18:48:18 GMT
Hello,

 

I ran into a problem with Derby.  When I issue a large SQL statement
with lots of WHERE conditions, the Derby optimizer looks like it's in an
infinite loop.  A Thread dump of where it is stuck is shown below.
Also, a sample of the SQL that causes it to hang is also below.  

 

Does anyone know if this is a bug or can I tune Derby to get around this
issue?

 

Thanks,

 

Amit Patel

 

Derby Version: 10.2.1.6

 

2006-10-11 18:30:03.666 GMT:

 Booting Derby version The Apache Software Foundation - Apache Derby -
10.2.1.6 - (452058): instance c065801d-010e-38a2-fae2-ffffb81c5a7c

on database directory C:\Source\CI5\Trunk\corelib\db\derby\Cyclone

 

Sample SQL that causes derby to hang:

 

SELECT DISTINCT t0.OID, t0.JDOVERSION, t0.BINARYCOLLABORATIONHASHCODE,
t0.CONSUMPTIONEXCHANGEPOINTOID, t0.CONSUMPTIONFILENAME,
t0.CONTENTMIMETYPE, t0.COREID, t0.CURRENTMESSAGEID,
t0.CURRENTPACKAGINGSTATEOID, t0.CURRENTSTATEOID, t0.DELIVEREDTIME,
t0.DIRECTION, t0.DOCUMENTCLASS, t0.ISDUPLICATE, t0.EDICONTROLID,
t0.EXPIRATIONTIME, t0.INTEGRATIONID, t0.ISCHILD, t0.ORIGINATIONTIME,
t0.PRODUCEDCOUNT, t0.PRODUCTIONFILENAME, t0.RECEIVERPARTYOID,
t0.RECEIVERPARTYID, t0.RECEIVERROUTINGID, t0.RECEIVERROUTINGIDTYPE,
t0.REFTOOID, t0.RESUBMITCOUNT, t0.RESUBMITINFO, t0.SENDERPARTYOID,
t0.SENDERPARTYID, t0.SENDERROUTINGID, t0.SENDERROUTINGIDTYPE, t0.TYPE
FROM DBA.MESSAGES t0 INNER JOIN DBA.CUSTOMDATA t1 ON t0.OID =
t1.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t2 ON t0.OID = t2.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t3 ON t0.OID = t3.MESSAGEOID INNER JOIN
DBA.CUSTOMDATA t4 ON t0.OID = t4.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t5
ON t0.OID = t5.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t6 ON t0.OID =
t6.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t7 ON t0.OID = t7.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t8 ON t0.OID = t8.MESSAGEOID INNER JOIN
DBA.CUSTOMDATA t9 ON t0.OID = t9.MESSAGEOID INNER JOIN DBA.CUSTOMDATA
t10 ON t0.OID = t10.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t11 ON t0.OID =
t11.MESSAGEOID INNER JOIN DBA.CUSTOMDATA t12 ON t0.OID = t12.MESSAGEOID
INNER JOIN DBA.CUSTOMDATA t13 ON t0.OID = t13.MESSAGEOID INNER JOIN
DBA.MESSAGESTATES t14 ON t0.OID = t14.MESSAGEOID WHERE
(t0.RECEIVERROUTINGID = ? AND t0.SENDERROUTINGID = ? AND t0.DIRECTION =
? AND t0.ORIGINATIONTIME >= ? AND t0.ORIGINATIONTIME <= ? AND
t0.DOCUMENTCLASS = ? AND t0.CONTENTMIMETYPE = ? AND t1.NAME = ? AND
t1.DATAVALUE = ? AND t2.NAME = ? AND t2.DATAVALUE = ? AND t3.NAME = ?
AND t3.DATAVALUE = ? AND t4.NAME = ? AND t4.DATAVALUE = ? AND t5.NAME =
? AND t5.DATAVALUE = ? AND t6.NAME = ? AND t6.DATAVALUE = ? AND t7.NAME
= ? AND t7.DATAVALUE = ? AND t8.NAME = ? AND t8.DATAVALUE = ? AND
t9.NAME = ? AND t9.DATAVALUE = ? AND t10.NAME = ? AND t10.DATAVALUE = ?
AND t11.NAME = ? AND t11.DATAVALUE = ? AND t12.NAME = ? AND
t12.DATAVALUE = ? AND t13.NAME = ? AND t13.DATAVALUE = ? AND t14.TYPE =
? AND t14.URL = ?) ORDER BY t0.ORIGINATIONTIME DESC

 

Thread Dump of thread executing this query in Derby:

 

"JettySocketListener0-0" prio=6 tid=0x27939748 nid=0x838 runnable
[0x2a9ce000..0x2a9cfbe8]

            at org.apache.derby.iapi.util.JBitSet.clearAll(Unknown
Source)

            at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.initBaseT
ableVisitor(Unknown Source)

            at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.valNodeRe
ferencesOptTable(Unknown Source)

            at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.isQualifi
er(Unknown Source)

            at
org.apache.derby.impl.sql.compile.PredicateList.hasOptimizableEquijoin(U
nknown Source)

            at
org.apache.derby.impl.sql.compile.HashJoinStrategy.findHashKeyColumns(Un
known Source)

            at
org.apache.derby.impl.sql.compile.HashJoinStrategy.feasible(Unknown
Source)

            at
org.apache.derby.impl.sql.compile.FromTable.feasibleJoinStrategy(Unknown
Source)

            at
org.apache.derby.impl.sql.compile.OptimizerImpl.costOptimizable(Unknown
Source)

            at
org.apache.derby.impl.sql.compile.FromBaseTable.optimizeIt(Unknown
Source)

            at
org.apache.derby.impl.sql.compile.ProjectRestrictNode.optimizeIt(Unknown
Source)

            at
org.apache.derby.impl.sql.compile.OptimizerImpl.costPermutation(Unknown
Source)

            at
org.apache.derby.impl.sql.compile.SelectNode.optimize(Unknown Source)

            at
org.apache.derby.impl.sql.compile.DMLStatementNode.optimize(Unknown
Source)

            at
org.apache.derby.impl.sql.compile.CursorNode.optimize(Unknown Source)

            at
org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)

            at
org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)

            at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareI
nternalStatement(Unknown Source)

            at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)

            at
org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
Source)

            at
org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
Source)

            at
org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)

            at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)

            - locked <0x0570e888> (a
org.apache.derby.impl.jdbc.EmbedConnection30)

            at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)

            at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingCon
nection.java:382)

            at
com.solarmetric.jdbc.PoolConnection.prepareStatement(PoolConnection.java
:114)

            at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingCon
nection.java:380)

            at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingCon
nection.java:380)

            at
com.solarmetric.jdbc.LoggingConnectionDecorator$LoggingConnection.prepar
eStatement(LoggingConnectionDecorator.java:374)

            at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingCon
nection.java:380)

            at
kodo.jdbc.runtime.JDBCStoreManager$RefCountConnection.prepareStatement(J
DBCStoreManager.java:1645)

            at
com.solarmetric.jdbc.DelegatingConnection.prepareStatement(DelegatingCon
nection.java:366)

            at
kodo.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:449)

            at kodo.jdbc.sql.Select.execute(Select.java:1578)

            at kodo.jdbc.sql.Select.execute(Select.java:1533)

            at
kodo.jdbc.runtime.SelectResultObjectProvider.open(SelectResultObjectProv
ider.java:102)

            at
com.solarmetric.rop.WindowResultList.<init>(WindowResultList.java:46)

            at
kodo.jdbc.runtime.JDBCFetchConfigurationImpl.newResultList(JDBCFetchConf
igurationImpl.java:164)

            at
kodo.runtime.CopyOnWriteFetchConfiguration.newResultList(CopyOnWriteFetc
hConfiguration.java:340)

            at kodo.query.AbstractQuery.execute(AbstractQuery.java:1081)

            at
kodo.query.AbstractQuery.executeWithMap(AbstractQuery.java:877)

            at
com.cyclonecommerce.persistence.providers.jdo.JdoQuery.retrievePagedResu
lts(JdoQuery.java:201)

            at
com.cyclonecommerce.persistence.query.AbstractQuery.executePagedQuery(Ab
stractQuery.java:156)

            at
com.cyclonecommerce.persistence.PagedView.getPage(PagedView.java:82)

            at
com.cyclonecommerce.ui.messages.MessageQuery.findMessages(MessageQuery.j
ava:1206)

            at
com.cyclonecommerce.ui.messages.MessageMetadataQueryAction.serializedAct
(MessageMetadataQueryAction.java:97)

            at
com.cyclonecommerce.ui.core.actions.CycloneAbstractAction.act(CycloneAbs
tractAction.java:46)

            at
org.apache.cocoon.components.treeprocessor.sitemap.ActTypeNode.invoke(Ac
tTypeNode.java:124)

            at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:46)

            at
org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.i
nvoke(PreparableMatchNode.java:130)

            at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:46)

            at
org.apache.cocoon.components.treeprocessor.sitemap.MatchNode.invoke(Matc
hNode.java:107)

            at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:68)

            at
org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(P
ipelineNode.java:142)

            at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:68)

            at
org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(
PipelinesNode.java:92)

            at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process
(ConcreteTreeProcessor.java:234)

            at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process
(ConcreteTreeProcessor.java:176)

            at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreePro
cessor.java:248)

            at
org.apache.cocoon.components.treeprocessor.sitemap.MountNode.invoke(Moun
tNode.java:117)

            at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:46)

            at
org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.i
nvoke(PreparableMatchNode.java:130)

            at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:46)

            at
org.apache.cocoon.components.treeprocessor.sitemap.MatchNode.invoke(Matc
hNode.java:107)

            at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:68)

            at
org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(P
ipelineNode.java:142)

            at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:68)

            at
org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(
PipelinesNode.java:92)

            at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process
(ConcreteTreeProcessor.java:234)

            at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process
(ConcreteTreeProcessor.java:176)

            at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreePro
cessor.java:248)

            at org.apache.cocoon.Cocoon.process(Cocoon.java:679)

            at
org.apache.cocoon.servlet.CocoonServlet.service(CocoonServlet.java:1183)

            at
javax.servlet.http.HttpServlet.service(HttpServlet.java:689)

            at
org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:428)

            at
org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationH
andler.java:473)

            at
org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:568)

            at
org.mortbay.http.HttpContext.handle(HttpContext.java:1530)

            at
org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationCon
text.java:633)

            at
org.mortbay.http.HttpContext.handle(HttpContext.java:1482)

            at org.mortbay.http.HttpServer.service(HttpServer.java:909)

            at
org.mortbay.http.HttpConnection.service(HttpConnection.java:817)

            at
org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:983)

            at
org.mortbay.http.HttpConnection.handle(HttpConnection.java:834)

            at
org.mortbay.http.SocketListener.handleConnection(SocketListener.java:244
)

            at
org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:359)

            at
org.mortbay.util.ThreadPool$PoolThread.primRun(ThreadPool.java:537)

            at
com.cyclonecommerce.lang.EventedThread.run(EventedThread.java:71)

 

 

 

 

 


Mime
View raw message