Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> How Much of a Performance Boost Do you get in 9i in Calling PL/SQL from SQL?

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

From: Phil Singer <psinger1_at_chartermi.net>
Date: Tue, 16 Dec 2003 21:55:25 -0500
Message-ID: <3FDFC59D.5047E8E3@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?  

-- 
Phil Singer                |    psinger1ATchartermiDOTnet
Oracle DBA
Remove the Obvious to reply
Received on Tue Dec 16 2003 - 20:55:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US