db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Amit <mramitk...@gmail.com>
Subject Re: st.executeQuery() got stuck
Date Thu, 04 Jul 2013 13:45:14 GMT
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 <Dyre.Tjeldvoll@oracle.com>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
>> <Dyre.Tjeldvoll@oracle.com <mailto:Dyre.Tjeldvoll@oracle.**com<Dyre.Tjeldvoll@oracle.com>>>
>> 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

Mime
View raw message