You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Amit <mr...@gmail.com> on 2013/07/04 10:36:16 UTC
st.executeQuery() got stuck
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. 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.
--
Thanks,
Am
it
Re: st.executeQuery() got stuck
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Amit <mr...@gmail.com> writes:
> 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)
This means it is trying to access a data page that some other thread is
also accessing. It doesn't necessarily mean something is wrong, as
threads could occasionally be in this state under normal operation. If
the thread stays in this state for a long time, there's probably
something wrong, for example that a thread hasn't released the data page
when it was done with it.
It may be useful to check the CPU usage on the server when the query
doesn't return. If the CPU is mostly idle, the query execution is likely
blocked somewhere. If the CPU usage is high, it is more likely that the
optimizer has chosen a not so optimal execution plan for the query, or
that the query is doing unnecessary work for some reason. (Although, if
others access the database at the same time, it could be tricky to tell
if high CPU usage is because your query has gone wild, or if it's
because of the load others are putting on the database.)
--
Knut Anders
Re: st.executeQuery() got stuck
Posted by Amit <mr...@gmail.com>.
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 <Dy...@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 <ma...@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
Re: st.executeQuery() got stuck
Posted by Dyre Tjeldvoll <Dy...@oracle.com>.
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
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 <ma...@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
>
>
>
>
Re: st.executeQuery() got stuck
Posted by Amit <mr...@gmail.com>.
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"
LEFT OUTER JOIN "INTERNAL"."G_CORE_DOCUMENT"
ON G_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"
AND A."G_CORE_PARTY_ID" = "G_EVNT_MASTER_ORGANIZER_ID"
AND B."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=''
--
Thanks,
Amit
On Thu, Jul 4, 2013 at 4:06 PM, Dyre Tjeldvoll <Dy...@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
>
>
>
Re: st.executeQuery() got stuck
Posted by Dyre Tjeldvoll <Dy...@oracle.com>.
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