Dieter Maurer wrote:
This problem does not look like a Zope or DCOracle problem but like an Oracle problem.
perhaps - I am starting to suspect Oraxcle 8/9 client problems (on linux)
When I had to work with Oracle 3 years ago, I have been *VERY* disappointed (this was Oracle 8i). Despite its high costs (100.000 USD), it was almost unfunctional:
* memory corruption inside the Oracle client libraries brought our Zope down within minutes of use
This was finally fixed by an alternate open protocol.
Can't comment on this, since I have not your knowlegde on how to identify and understand these issues (I do whish I had, though :-)
* the Oracle server died often, non-deterministically, after some amount of work
we had to regularly close the connection and reopen a new one in order not to let one server do too much work
We had this problem briefly on versions like, for instance Oracle 8.1.6. Basically from what I remember of those days, the last version of Oracle 7 (7.3 or so) was rock stable, but we were not using Oracle 7, next "stable" version was 8.0.5, which I think was the first version we had in serious production. You really wanted to avoid like the plague everything before 8.1.5 and 8.1.7. (8.1.5/8.1.7 were pretty good, and we still use a 8.1.7 in production - mostly because we have not had the opportunity to upgrade it). 8.1.6 was... let's just say it was very good. :-P
* the Oracle client library caught SIGCHILD making "system" and "popen" unreliable -- this could be fixed with a special configuration option, deeply hidden in the tons of Oracle documentation
jup remember that too :-)
* full-text reindexing after large imports often crashed non-deterministically
Never did that. We never really did consider doing this kind of stuff at all, and the tentative plans we had were to use Oracle Context, as it was called then. We were in contact with people that were sucessuflly using Context to do large scale text indexing , and who where investigating the feasability to do stuff doing genome code indexing etc, for research purposes. This was in 1998-1999...
* upgrading from one Oracle version to the next Oracle subversion was a nightmare
Wtried that once, as an experiment and I can't remember if it was from 8.1.5 to 8.1.6 or from 8.1.6 to 8.1.7. We very quickly decided that, as we suspected, it was much less work to install a new instance with the new version and then migrate the data and structures to the new instance. Just like when installing windows :-)
* some combinations of relational and full text subqueries let Oracle forget about its indexes. For example: while a query "Q1 and Q2" took seconds (Oracle recognized that it had indexes) the query "Q1 or Q2" (with the same "Q1" and "Q2") took days (!) (because Oracle had forgotten about the indexes and used full table scans -- which is a bad idea with hundread of millions of records and hundreds of gigabyte of data).
The Oracle support was unable to fix this problem within half a year. Oracle recommended to avoid the combination of relational and full text subqueries !
Well, unless you have a personal contact with Oracle support, then it basically sucks, even today - though MetaLink has become a bit more uable lately. The thing with oralce is that you have to know how to use it; just the sam as with Zope. In versions 8 and 9, that means that you have to take special care of HOW you construct your SQL. I know this from personal experience, both from personal mistakes, and from extensive work trying to fix other peoples code and SQL. I kid you not when I say that a very simple rewrite of an sql query can minimize the execution time from 5 minutes to less than 5 seconds. I am at this moment struggling with precisely such an application that is very badly desinged. in newer and latest oracles, the situation for a programmer is mich easier. Since the DB automagically calculates things like index statistics, and even (If I remember what I've been told) manages it's tablespaces automagially. All in all this means that you have much more freedom in how to write your queries than you had before. There are lots of "hidden", or at least non-obvious tricks to fully taking advantage of Oracle, but this situation is no different than using Zope. Anyways, we are not going to go away from Oracle any time soon. For one ting, we have invested knowlegde on Oracle that we cannot just throw away. Oracle provides us with services that we feel we cannot be without, such as warm backups, replication, etc, that are more or less ready to use OOTB, just to name one at random. I am not debating whether postgress is good or bad, mind. I am just expressing my need to have Zope work well in conjuntion with Oracle. And this is not only to satisfy my own current needs. I think it is good for Zope, and indirectly for all of us, if Oracle was on the list of things that Zope interacts solidly with. After all - in the very unlikely event that it would boil down to it costing too much to use Zope with Oracle, we are not going to drop Oracle... we probably would still be using Zope but not for all the more high-profile sites and applications we would be needing to create. So, I think i'll try to stick with both Oracle and Zope as long as possible :-) /dario -- -- ------------------------------------------------------------------- Dario Lopez-Kästen, IT Systems & Services Chalmers University of Tech.