Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: A PL/SQL parameter puzzle
I agree. We had to adjust several areas to properly port the procedure
code. We understood well that hardware was not the solution as it is rarely
the root cause of database performance. The port required time, careful
analysis and knowledge to be successful as do most projects. Transaction
Management was probably the biggest difference between the two environments
that we had to consider. I certainly appreciate Oracle's approach as
opposed to SQLServer server's.
Kurt
-- ---------------------------------------------------- This mailbox protected from junk email by MailFrontier Desktop from MailFrontier, Inc. http://info.mailfrontier.com "HansF" <news.hans_at_telus.net> wrote in message news:w55ld.148701$df2.38347_at_edtnps89...Received on Fri Nov 12 2004 - 10:15:46 CST
> KurtisK wrote:
>
> >
> > It was interetesting to find that Oracle performed slower in many
> > scenarios whereas our preconception was that it would perform better
than
> > SQLServer. <... much snipped>
> >
>
> That observation is consistent across ports from SQL Server where the
> porting organization does not adjust the application to account for Oracle
> vs SQL Server differences.
>
> Proper porting would likely correct the performance issue. But that is
> rarely done due to laziness, time pressure and/or lack of knowledge. The
> end result is that more hardware is thrown at the Oracle solution and
> Oracle is then percieved as more expensive - a beautiful case of passing
> the buck (literally).
>
> For example: one of the most common scenarios involves building up the SQL
> statement, including literals used in comparison, and executing that
> resulting statement. If several users issue similar statements,
> differering only in literal values, an Oracle system must reparse the
> statement with significant overhead causing performance and scalability
> issues. The solution, which many developers do not understand, is to use
> bind variables.
>
> Other similar stupidities include looped fetches when pure SQL statement
> processing would work, commits in loops, creating temp tables on the fly,
> not using arrays, dragging data back to the application instead of using
> PL/SQL - and the list goes on.
>
> These scenarios, and others, are documented in Thomas Kytes 'Effective
> Oracle by Design' as well as most other books from members of the Oak
Table
> (http://www.oaktable.net)
>
> /Hans