Re: Weird performance issue ....

Andre

If I understand - the problem is with a query returned from a stored proc and the data and code are the same ( I could be wrong as I'm not sure if by "a query making a user function call" you mean something different again).

If I am correct - did you examine the execution plans by extracting the SQL from the PL/SQL stored proc ? If so this might not be the true story - you might not be aware but all SQL inside stored procs is run on the basis of ALL_ROWS by default IIRC rather than then optimiser default in the init.ora as PL/SQL is assumed to be a "batch" environment. Try explain plan with the sql after adding the /*+ ALL_ROWS */ hint to the sql and see if it's still the same on each box.

From the Oracle db performance tuning guide and reference manual

"The optimizer goal applies only to queries submitted directly. Use hints to determine the access path for any SQL statements submitted from within PL/SQL. The ALTER SESSION... SET OPTIMIZER_MODE statement does not affect SQL that is run from within PL/SQL."

Andy

<vigneaua_at_hotmail.com> wrote in message
news:1128352913.422389.86910_at_f14g2000cwb.googlegroups.com...
> All servers are running 9.2.0.4, one is Windows platform the other one
> Unix. Not taking about power since where the issue is, the server
> outperforms our DEV box by 10 times easily.
> We are developing an application using Windows platform for a customer
> using Unix.
> I made a copy (exp) of our development schema that performs fine and
> sent it to the customer to import in his environment so we get the same
> data, scripts and everything.
> There is a tremendous performance difference when within a package we
> have a procedure opening a cursor to get a result set from a query
> making a user function call.
> When the result is getting back it seems like it has to pause for long
> seconds many times before it ends.
> Does any have seen this? It runs in less that a second on our DEV
> server and over 12 seconds on the customer server. And increasing
> exponentially as new data is added. The 2 servers show the same explain
> plan.
> You have an example of the query below, if you would like to have the
> script of the function I can also provide this to you.
> I will greatly appreciate all the help I can get ....
> Thanks
> Andre

<snip> Received on Wed Oct 05 2005 - 08:55:26 CDT