Also, I did jstack on derby process and got a thread in BLOCKED state, what is the reason for this?
"DRDAConnThread_5" prio=6 tid=0x27500c00 nid=0x178c in Object.wait() [0x2801f000] java.lang.Thread.State: BLOCKED (on object monitor) at java.lang.Object.wait(Native Method) at java.lang.Object.wait(Object.java:503) at org.apache.derby.impl.store.raw.data.BasePage.setExclusive(Unknown Source) - locked <0x18f464e8> (a org.apache.derby.impl.store.raw.data.StoredPage) at org.apache.derby.impl.store.raw.data.BaseContainer.latchPage(Unknown Source) at org.apache.derby.impl.store.raw.data.FileContainer.latchPage(Unknown Source) at org.apache.derby.impl.store.raw.data.FileContainer.getUserPage(Unknown Source) at org.apache.derby.impl.store.raw.data.FileContainer.getNextHeadPage(Unknown Source) at org.apache.derby.impl.store.raw.data.BaseContainer.getNextPage(Unknown Source) at org.apache.derby.impl.store.raw.data.BaseContainerHandle.getNextPage(Unknown Source) at org.apache.derby.impl.store.access.conglomerate.GenericScanController.positionAtNextPage(Unknown Source) at org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(Unknown Source) at org.apache.derby.impl.store.access.heap.HeapScan.fetchNextGroup(Unknown Source) at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(Unknown Source) at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(Unknown Source) at org.apache.derby.impl.sql.execute.NestedLoopJoinResultSet.getNextRowCore(Unknown Source) at org.apache.derby.impl.sql.execute.NestedLoopJoinResultSet.getNextRowCore(Unknown Source) at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown Source) at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown Source) at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source) - locked <0x1403f008> (a org.apache.derby.impl.jdbc.EmbedConnection40) at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source) at org.apache.derby.impl.drda.DRDAConnThread.writeFDODTA(Unknown Source) at org.apache.derby.impl.drda.DRDAConnThread.writeQRYDTA(Unknown Source) at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown Source) at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source) On Thu, Jul 4, 2013 at 5:34 PM, Dyre Tjeldvoll <[email protected]>wrote: > On 07/ 4/13 01:30 PM, Amit wrote: > >> Thanks Dyre for your reply. >> >> Yes, I mean it hangs in executeQuery(). >> The derby running as client/server mode. >> Other threads/connections accessing the same table simultaneously. >> The tables are not that large. Max records among the tables listed in >> the query is 5,000 >> >> This is the query - (The columns ends with "_ID" are indexed) >> SELECT"G_EVNT_MASTER_NAME", >> B."G_CORE_PARTY_NAME", >> A."G_CORE_PARTY_NAME", >> "G_EVNT_MASTER_START_DT", >> "G_EVNT_MASTER_ID", >> "G_EVNT_MASTER_OFFERING", >> "G_CORE_DOCUMENT_NAME", >> "G_CORE_DOCUMENT_URL" >> FROM"INTERNAL"."G_CUST_**OPPORTUNITY", >> "INTERNAL"."G_XREF_EVNT_OPPR", >> "INTERNAL"."G_CORE_PARTY"A, >> "INTERNAL"."G_CORE_PARTY"B, >> "INTERNAL"."G_XREF_EVNT_PARTY"**, >> "INTERNAL"."G_EVNT_MASTER" >> LEFTOUTERJOIN"INTERNAL"."G_**CORE_DOCUMENT" >> ONG_EVNT_MASTER_ID=G_CORE_**DOCUMENT_MEETING_ID >> WHERE"G_EVNT_MASTER_ID"="G_**XREF_EVNT_OPPR_EVENT_ID" >> AND"G_XREF_EVNT_OPPR_**OPORTUNITY_ID"="G_CUST_**OPPORTUNITY_ID" >> ANDA."G_CORE_PARTY_ID"="G_**EVNT_MASTER_ORGANIZER_ID" >> ANDB."G_CORE_PARTY_ID"="G_**XREF_EVNT_PARTY_PARTY_ID" >> >> AND"G_XREF_EVNT_PARTY_EVENT_**ID"="G_EVNT_MASTER_ID" >> AND"G_EVNT_MASTER_TYPE"='**Contact Event' >> AND"G_XREF_EVNT_PARTY_TYPE"='**Primary Contact' >> AND"G_CUST_OPPORTUNITY_ID"='**99223977' >> >> >> >> This is the derby.log - >> Booting Derby version The Apache Software Foundation - Apache Derby - >> 10.9.1.0 - (1344872): instance a816c00e-013f-a339-24f0-**0000697cf4d7 >> on database directory E:\database1 with class loader >> sun.misc.Launcher$**AppClassLoader@f08d0f >> Loaded from file:/E:/lib/drivers/derby.jar >> java.vendor=Oracle Corporation >> java.runtime.version=1.7.0_13-**b20 >> user.dir=E:\ >> derby.system.home=E:\ >> Database Class Loader started - derby.database.classpath='' >> > > Right. Please have a look at the tuning guide: > > http://db.apache.org/derby/**docs/10.10/tuning/**ttundepth33391.html<http://db.apache.org/derby/docs/10.10/tuning/ttundepth33391.html> > > But you should probably start by finding out if your server is buzy or > idle. E.g. by using jstack or some other java monitoring tool on the jvm > running the Derby server to see what it appears to be doing when the query > hangs. These are some possibilities: > > - Derby thread is starved - other threads take all the resources. Can > happen if Derby is sharing the jvm with another application like an > appserver. > > - Derby thread blocked. It is waiting for db lock or java monitor. > > - Derby thread busy. Optimizer may have chosen a bad plan. Maybe > statistics need to be updated. > > - Derby thread is idle. Possibly network problem between client and server. > > > HTH, > > Dyre > > > >> -- >> Thanks, >> Amit >> >> >> >> On Thu, Jul 4, 2013 at 4:06 PM, Dyre Tjeldvoll >> <[email protected] >> <mailto:Dyre.Tjeldvoll@oracle.**com<[email protected]>>> >> wrote: >> >> On 07/ 4/13 10:36 AM, Amit wrote: >> >> This is the code I am using to execute query. >> >> Statement st = con.createStatement(ResultSet.** >> __TYPE_FORWARD_ONLY, >> >> ResultSet.CONCUR_READ_ONLY); >> st.setFetchSize(100); >> ResultSet queryRs = st.executeQuery(sql); >> System.out.println("Success"); >> >> Usually the query takes 1-2 sec to execute. But very rare >> occasion the >> code got stuck at line 4. >> >> >> That would be println... I assume you mean that it hangs in >> executeQuery()? >> >> >> I have waited for 1 hr and then killed the >> thread. It's a production issue. I am using connection pool and >> my derby >> version is - 10.9.1.0 >> >> Can you help me narrow down the problem. >> >> >> Really hard without more information. If you can post the SQL >> involved it would be helpful. Is this client/server or embedded? >> Is the table large? Do you access it through an index? Are other >> threads/connections accessing the same table simultaneously? >> Anything in derby.log at the time of the hang? >> >> >> Regards, >> >> Dyre >> >> >> >> >> > -- Thanks, Mit
