3

I noticed that Oracle takes a while to compile a stored procedure but it runs much faster than its PostgreSQL PGSQL counterpart.

With PostgreSQL, the same procedure (i.e. it's all in SQL-92 format with functions in the select and where clauses) takes less time to compile but longer to run.

Is there a metrics web site where I can find side by side Oracle vs PostgreSQL performance metrics on stored procedures, SQL parsing, views and ref_cursors?

Is the PostgreSQL PGSQL compiler lacking in optimization routines? What are the main differences between how Oracle handles stored procedures and how PostgreSQL handles them?

I'm thinking of writing a PGSQL function library that will allow PL/SQL code to be compiled and run in PGSQL. (i.e. DECODE, NVL, GREATEST, TO_CHAR, TO_NUMBER, all PL/SQL functions) but I don't know if this is feasible.

Improve this question
1
  • 1
    iirc Oracle performs more compile time type checking, while PostgreSQL has to check more at runtime. But for the full story you'd better check the documentation. Commented Dec 3, 2011 at 23:39

3 Answers 3

Reset to default
1

This is a hard question to answer fully because it can run pretty deep, but I'll add my 2 cents towards a high level contribution to an answer. First off I really like PostgreSQL and I really like Oracle. However PL/SQL is so much more deep of a language/environment than PL/PGSQL provides or really any other database engines procedure language that I have ever ran into for that matter. Oracle since at least 10G uses a optimizing compiler for PL/SQL. Which most likely contributes to why it compiles slower in your use cases. PL/SQL also has native compilation. You can compile the PL/SQL code down to machine code with a simple compiler directive. This is good for computation intensive logic not for SQL logic. My point of all this is Oracle has spent lots of resources on making PL/SQL a real treat from a functionality standpoint and a performance stand point and I only touched on two of many examples.PL/SQL is light years ahead of PG/SQL is what it sums up to and I don't imagine as nice as PG/SQL is catching up to Oracle any time soon.

I doubt you will find a side by side comparison, though I think this would be really nice. The effort to do so wouldn't probably be worth most people's time. Also I wouldn't re-write what is already out there.

http://www.pgsql.cz/index.php/Oracle_functionality_(en)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the link. No need to reinvent the wheel.
1

There is no official benchmark for stored procedures like TPC for SQL (see tpc.org). I'm also not aware of any database application with specific PL/SQL and pgSQL implementations which could be used as benchmark.

Both languages are compiled and optimized into intermediate code and then ran by an interpreter. PL/SQL can be compiled to machine code which doesn't improve overall performance as much as one might think, because the interpreter is quite efficient and typical applications spend most time in the SQL engine and not in the procedural code (see AskTom article).

When procedural code calls SQL it happens just like in any other program, using statements and bind parameters for input and output. Oracle is able to keep these SQLs "prepared" which means that the cursors are ready to be used again without an additional SQL "soft parse" (usually a SQL "hard parse" happens only when the database runs a SQL for the first time since it was started).

When functions are used in select or where clauses, the database has to switch back-and-forth between the SQL and procedural engines. This can consume more processing time then the code itself.

A major difference between the two compilers is that Oracle maintains a dependency tree, which causes PL/SQL to automaticly recompile when underlying objects are changed. Compilation errors are detected without actually running the code, which is not the case with Postgres (see Documentation)

Improve this answer

1 Comment

Do you know if there is a way to visualize the dependency tree as some IDEs come with this in their toolset. i.e. Maven dependency plugin. A state machine compiler would be nice too. i.e. complang.org/ragel
0

Is there a metrics web site where I can find side by side Oracle vs PostgreSQL performance metrics on stored procedures, SQL parsing, views and ref_cursors?

Publicly benchmarking Oracle performance is probably a breach of your licensing terms. If you're a corporate user make sure legal check it out before you do anything like that.

I'm thinking of writing a PGSQL function library that will allow PL/SQL code to be compiled and run in PGSQL. (i.e. DECODE, NVL, GREATEST, TO_CHAR, TO_NUMBER, all PL/SQL functions) but I don't know if this is feasible.

Do check the manual and make sure you need all of these, since some are already implemented as built-in functions. Also, I seem to recall an add-on pack of functions to improve Oracle compatibility.

Lastly, don't forget PostgreSQL offers choices on how to write functions. Simple ones can be written in pure SQL (and automatically inlined in some cases) and for heavy lifting, I prefer to use Perl (you might like Python or Java).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.