Survey results on Oracle/M$NT4 to PG72/RH72 migration

Hi all,

Here are the results of our survey on a migration from Oracle 8.0 / W$
NT4 SP5 to PostgreSQL 7.2 / Red Hat 7.2.

You'll probably remember of a thread I initiated in this list a couple
of weeks ago, this is the same survey for the same customer. Now, the
survey is finished.

So, we migrated all Oracle's specific syntaxes succesfully, including
CONNECT BY statements (thanks to all hackers at OpenACS project (visit
http://www.openacs.org) for the good code!).

We migrated succesfully Oracle Pro*C thanks to fantastic ECPG (Thanks
Michael).

The overall performance of PostgreSQL, is 33% slower than the Oracle/Nt
solution. One must say we faced a well tuned Oracle, tuned for best
performance. Even SQL queries were very well tuned, using Oracle
pragmas for example (ex: %USE HASH foobar%).

Since our customer accepted up to 50%, this is a success for us,
technicaly on this point.

BUT, we faced a real problem. On some batches, in ECPG, Pro*C
structures uses intensively CURSORs loops. In Oracle, CURSORs
can be PREPARED. Thus, it seems Oracle only computes once the query plan
for the cursor, even if it is closed and re-opened. Maybe some kind of
stored query plan / caching / whatever makes it possible.

This seems not be the case in ECPG. In each COMMIT, the cursors are
closed (they dont even need to close cursors in Oracle!). And at each
BEGIN TRANSACTION PostgreSQL seems to compute again parsing and query
plan..

So this finaly makes the batch work taking 300% the time Oracle needs.
We clearly see our ECPG programs waits for PostgreSQL in the functions
were CURSORs are opened. Then, we know the problem is not in ECPG but in
PG backend.

This is unaceptable for our customer. Many batches are launched during
the night and have to be completed in 5h (between 0h and 5h). With a
ratio of 3, this is not worth think about migration anymore :-(

We know we could have much better performances with something else than
ECPG, for example, using C or TCL stored procedures, placing the
SQL work wuch closer from the PG backend, using SPI, etc... But this is
not possible. We have to make it under ECPG, there are tons of Pro*C
code to migrate, and we must make it the same. With ECPG/Pro*C compiled
programs, we can stop executions, renice programs, etc, what we would
loose putting work in stored procedures.

So, I'd really like some of you validate this thing about cursor. We
have a strange feeling blended of pride for only a 1,33 ratio face to
the giant Oracle, and a feeling of something unfinished, because only
of a feature not yet implemented...

I read many times the current TODO list. I think our problem is
somewhere between the CURSOR thread and the CACHE thread in the TODO.

We would really appreciate some of you validate this behaviour about
CURSORs, this would validate we didn't spent 40 day/man for nothing, and
that we reached a certain good explanation of the problem, that we have
not dug just next to the treasure.

Thanks a lot anyway for such good database.

PS: Bad english, I know... :-)
I hope the customer will accept put the survey in GNU/Linuxdoc, as
all of PG folks can read it. It is still under a NDA.

--
Jean-Paul ARGUDO