Re: How Much of a Performance Boost Do you get in 9i in Calling PL/SQL from SQL?

Phil Singer <psinger1_at_chartermi.net> wrote in message news:<3FDFC59D.5047E8E3_at_chartermi.net>...
> Hi. I have a question which needs someone with experience to answer.
>
> Our applications are very heavy with stored procedures. To encourage
> code reusability and encapsulation and all those good things, we
> often have SQL queries include user functions. e.g.:
>
> SELECT ColA, colb, my_function(colc, cold)
> FROM ...
>
> etc.
>
> As everyone knows, this takes a performance hit from the required
> context switching. In the design of the applications, this was
> considered acceptable, given the Good Things this was giving us.
>
> Two things are now happening:
>
> 1) We are about to get the hardware needed to migrate to 9i from
> 8.1.7.4.
>
> 2) The application size has grown such that the acceptable
> performance hit on a query with 20 rows is no longer acceptable
> on a query with 150 rows.
>
> I have looked at one query, and seen that I can reduce the run time
> on that 150 row result set from 2.8 sec to .6 sec by rewriting the
> function with a complicated set of decode statements. I also know
> that 9i is supposed to reduce _some_ of the burden of these context
> switchs, so that pl/sql from sql is supposed to perform better.
> But, I as yet have seen no specifics (and I don't have the hardware
> as yet to test with).
>
> So, have folks found that 9i increases the performance enough that
> I am better of waiting to see if it will be enough, or has the
> boost tended to be small enough that I may as well start rewriting
> the SQL now?

I don't think fundamental flaws in the architecture of the application (ie embedding pl/sql functions executing selects) will be ever dealt with by upgrading. Your problem is not the context switch. Your problem is the recursive sql.

Sybrand Bakker
Senior Oracle DBA Received on Wed Dec 17 2003 - 06:26:33 CST