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

Wed, 13 Mar 2002 17:18:29 +0100
Jean-Paul ARGUDO <jean-paul.argudo@idealx.com>
Survey results on Oracle/M$NT4 to PG72/RH72 migration
On Wed, 2002-03-13 at 21:18, Jean-Paul ARGUDO wrote:
> 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!).

Could you elaborate here ?

I know they do some of it using triggers and bitmap indexes, do you mean
this ?

> 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.

What kind of work do you do in these cursors ?

Is it inserts, updates, deletes, complicated selects ...

> 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.

Could you make ona sample test case with minimal schema/data that
demonstrates this behaviour so I can try to optimise it ?

> 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...

Did you do any tests ?

How much faster did it get ?

> 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.

AFAIK some SQL/C type precompilers and other frontend tools for other
databases do generate stored procedures for PREPAREd CURSORs.

I'm afraid ECPG does not :(

But making ECPG do it might be one way to fix this until real prepared
queries will be available to frontend.

> 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.

The treasure is currently locked up in backend behind FE/BE protocol 

---------------------
Hannu




14 Mar 2002 09:08:55 +0500
Hannu Krosing <hannu@krosing.net>
Re: Survey results on Oracle/M$NT4 to PG72/RH72 migration
On Thu, Mar 14, 2002 at 09:08:55AM +0500, Hannu Krosing wrote:
> AFAIK some SQL/C type precompilers and other frontend tools for other
> databases do generate stored procedures for PREPAREd CURSORs.

You mean ECPG should/could replace a PEPARE statement with a CREATE
FUNCTION and then the usage of the cursor with the usage of that
function?

Should be possible, but needs some work.

> I'm afraid ECPG does not :(

That's correct of course.

Michael

-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Fri, 15 Mar 2002 08:57:02 +0100
Michael Meskes <meskes@postgresql.org>
Re: Survey results on Oracle/M$NT4 to PG72/RH72 migration
> > AFAIK some SQL/C type precompilers and other frontend tools for other
> > databases do generate stored procedures for PREPAREd CURSORs.
> 
> You mean ECPG should/could replace a PEPARE statement with a CREATE
> FUNCTION and then the usage of the cursor with the usage of that
> function?
> 
> Should be possible, but needs some work.

Wow Michael, this would be much much much appreciated. :-)
 
> > I'm afraid ECPG does not :(
> 
> That's correct of course.
> Michael

Thanks. Then we know our conclusions on the survey are right.

We hope functionality about prepared cursors, bind variables, etc will
come soon in PG :-)

We actually think about solutions to patch PostgreSQL and contribute
this way, adding a feature we need for business.  

Thanks.

-- 
Jean-Paul ARGUDO                                IDEALX S.A.S
Consultant bases de donnï¿½es                     15-17, av. de Sï¿½gur
http://www.idealx.com                           F-75007 PARIS

Fri, 15 Mar 2002 10:25:09 +0100
Jean-Paul ARGUDO <jean-paul.argudo@IDEALX.com>
Re: Survey results on Oracle/M$NT4 to PG72/RH72 migration
On Fri, Mar 15, 2002 at 10:25:09AM +0100, Jean-Paul ARGUDO wrote:
> > You mean ECPG should/could replace a PEPARE statement with a CREATE
> > FUNCTION and then the usage of the cursor with the usage of that
> > function?
> > 
> > Should be possible, but needs some work.
> 
> Wow Michael, this would be much much much appreciated. :-)

Problem is I have no idea when I will find time to care about such an
addition. It certainly won't be possible prior May or so. Sorry.

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Fri, 15 Mar 2002 11:34:37 +0100
Michael Meskes <meskes@postgresql.org>
Re: Survey results on Oracle/M$NT4 to PG72/RH72 migration