Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> How Much of a Performance Boost Do you get in 9i in Calling PL/SQL from SQL?
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:
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?
-- Phil Singer | psinger1ATchartermiDOTnet Oracle DBA Remove the Obvious to replyReceived on Tue Dec 16 2003 - 20:55:25 CST