db-derby-user mailing list archives

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


Mime
View raw message