[Zope-DB] Re: Zope-DCOracle2 vs Tomcat-JDBC performance

[Dario Lopez-Kästen]

----- Original Message ----- 
From: "Dieter Maurer" <dieter@handshake.de>


> Dario Lopez-Kästen wrote at 2003-6-4 23:10 +0200:
>  > We have very similar problems. We have 4 threads. 3 of them are usually
>  > constantly frozen (ie they have been on hold for like 70.000 seconds,
and so
>  > on), so Zope effetively is running in one thread.
>
> Can you check what these threads are doing?
> This may be possible with a thread sensitive debugger.
> Analyse the backtraces of the various threads.

hm... unfortunaltely, I really don't know how to do such a thing. Do you
have any pointers to docs that explain those features?

> Several years ago, working with Oracle 8.i, we had some
> queries that took days to execute.
> This has been Oracle's fault.
> A large class of queries was affected that combined
> a relational subquery with a fulltext subquery.

hm... we do have some badly written SQL that take a long time to execute. I
do not blaim this on Oracle, but reather the consultant that wrote the SQL.

We have done the following to alleviate the problems:

* decrease the number of ZOracle Connection objects in use.
  We no have only one per Oracle Schema

* Re-writing the worst sql to be faster

* Use an external method that by passes Zope security
  mechanisms for some queries that return long result sets
  (more than 50 rows with more than 10 columns each)

This has increased the stability of our application a lot.

However, there still seems to be problems with queries that take a long time
to execute. It seems that the DA sort of looses the connection to the
database and just sits there hanging. This happens on both ends, apparently
as we see oracle connectson that just sit there doing nothing.

Why this is happening I do not know, but it seems to happen. If any one has
some solid advise on how to collect relevant data that might help out in
analysing and finding where things hang, I am all ears.

/dario

- --------------------------------------------------------------------
Dario Lopez-Kästen, IT Systems & Services Chalmers University of Tech.