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
<[email protected] <mailto:[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
