Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: A PL/SQL parameter puzzle

Re: A PL/SQL parameter puzzle

Date: Fri, 12 Nov 2004 09:28:08 -0500
Message-ID: <>

We performed a very successful port of SQLServer to Oracle. And not just simple SQL statements. We ported over 500 stored procs that do XML shredding of incoming XML requests into data that is then validated inside the procedures and then either used to update tables or just return data. Of course we had to do some things differently in Oracle. Some inline functions used in our SQL statements caused major performance drags in Oracle so we had to work around that issue. Oracle's XML technology is much different syntantically speaking, so that all changed. etc, etc.

It was interetesting to find that Oracle performed slower in many scenarios whereas our preconception was that it would perform better than SQLServer. This of course all depends on what you are doing with the code which Oracle is much more flexible and scalable in many ways.



This mailbox protected from junk email by MailFrontier Desktop
from MailFrontier, Inc.

"Craig Warman" <> wrote in message

> Hans - Your point is well taken. The locking strategies of SQL Server and
> Oracle are completely orthogonal to one another, and I agree that that
> ports fail due to that reason.
> This is one case where I do happen to feel that the port to a second
> database platform will succeed. Here is why. In this particular case,
> developers implemented their use of the database strictly as a datastore;
> they avoided any use of platform-specific locking strategies or
> functionality (or so they thought, since they used this particular set of
> SQL Server function calls!). They also developed an abstraction layer
> between their code and the database. This was done because they knew all
> along that their system may need to be ported to a new platform - and sure
> enough, they're faced with it now.
> Everyone knows that the system doesn't run as fast as it could, but in
> of this 'generic' SQL code implementation, have been in production with
> 1000's of users across a wide region of the country for several years now.
> I can't argue with that. With enough processor and memory, you can
> most performance problems. They have a *lot* of processor and memory. It
> won't be an issue.
> So I'd like to get past all that now. It looks like I've got two good
> suggestions in response to my original question - One from a prior post
> suggests using a TYPE definition (I'm looking into that one) and your
> recommendation to consider the use of constants. So, as a follow-up,
> that this is a Perl-based system (I didn't mention that earlier, sorry)
> you suggesting perhaps that we define constants at the application level
> use those in our calls to the database?
> Craig
> On 11/11/04 9:09 PM, in article mXUkd.148032$9b.3839_at_edtnps84, "HansF"
> <> wrote:
> > Sybrand's response is a bit harsh, but that's not unusual. He, like a
> > number of others here, have attempted to respond to the 'quick and easy
> > port from xyz' many times in the past. It behooves you to check the
> > archives of this group at (or other sources).
> >
> > Regardless of his choice of words, he is teling you clearly that porting
> > Server code to Oracle generally fails. The reason is simple - the two
> > systems have entirely different internal mechanisms to handle things
> > locking and concurrency. In fact, Oracle tends to avoid locking things
> > same way as SQL Server so that readers do not block writers and vice
> >
> > (Just like lipstick on a pig, satisfaction with a port will only come
> > when it's applied properly and it takes a skilled artist to do that.
> >
> > There are a lot of implications, many clearly indicated in Thomas Kyte's
> > books: "Expert One on One Oracle" and "Effective Oracle By Design".
> > books describe the challenges and the necessary tactics to avoid many of
> > the issues and come complete with working, testable examples.
> >
> > The biggest implication is that a traditional port [in either direction]
> > will usually have serious performance issues that only show up under
> > testing (which frequently means, only when in production). Once in
> > production, those issues frequently result in catastrophic failure and
> > system abandonment or rewrite, usually after someone has been fired.
> >
> > Now back to your question - consider a global package spec pinned at
> > that defines those yy values as constants (read only variables). A
> > yuk, but possibly workable?
> >
> > Good luck.
> >
> > /Hans
Received on Fri Nov 12 2004 - 08:28:08 CST

Original text of this message