[Zope] Re: Interest in continued Oracle support in Zope [longish and kind of off-topic]

[Dario Lopez-Kästen]

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.