Re: A PL/SQL parameter puzzle

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 Received on Fri Nov 12 2004 - 09:59:24 CST