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@xxxxxxxxxxx> wrote in message
news:1128352913.422389.86910@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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>


.

Relevant Pages

  • Re: Pls recommend a replacement for Microsoft Query
    ... SQL Server certainly does. ... The best approach is to create a stored procedure, ... on the server and call the stored proc from Excel. ... use MS Query to call the proc (even create it if you have the ...
    (microsoft.public.excel)
  • RE: SQL Sate: 22012
    ... > stored proc that executes the same query. ... One server returns no records (as ...
    (microsoft.public.sqlserver.server)
  • Re: Performance Benchmarks?
    ... Are attribute relationships defined properly on the dimensions? ... If you run Profiler against the server, how long is spent in "Query ... I have created aggregations (Partitions tab in cube design). ...
    (microsoft.public.sqlserver.olap)
  • High CPU in client (Excel, OWC, Proclarity, etc.) accessing Analysis Services
    ... the OWC10 with 3 dimensions on the row axis, ... The largest size of any of these 4 dimensions < 360 members. ... Performance Guide to optimize the query, the cube, the server, etc. ...
    (microsoft.public.sqlserver.olap)
  • Re: Performance Benchmarks?
    ... adding memory on the server can help you; more data can be cached on the ... Here is my test query. ... )} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ... I have created aggregations (Partitions tab in cube design). ...
    (microsoft.public.sqlserver.olap)