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

Home -> Community -> Usenet -> c.d.o.server -> Re: Weird performance issue ....

Re: Weird performance issue ....

From: EdStevens <quetico_man_at_yahoo.com>
Date: 3 Oct 2005 10:21:00 -0700
Message-ID: <1128360060.622406.85460@g44g2000cwa.googlegroups.com>

vigneaua_at_hotmail.com wrote:
> 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
>
> select
> pttf.id as form_id,
> pttf.form_id as form_base_id,
> fffi.id as bookmark_id,
> fffi.field_length,
> fffi.field_height,
> fffi.bookmark as bookmark_name,
> fffi.item_sequence,
> ffi.element_name,
> fdynsql (
> pttf.id,
> pttf.policy_term_trans_id,
> ffi.element_name,
> fffi.bookmark,
> fffi.item_sequence,
> fffi.field_height,
> fffi.fillin_overflow_item_type_id
> ) as value,
> decode (foit.id, null, 0, 1) as is_overflowing,
> ffi.form_fillin_item_source_id as source_type_id,
> pt.policy_id,
> nvl(foit.id, 0) as overflow_type_id
> from form_fillin_item_r ffi
> inner join form_form_fillin_item_r fffi
> on (fffi.form_fillin_item_id = ffi.id
> and ffi.form_fillin_item_source_id = 1)
> inner join form_r f
> on f.id = fffi.form_id
> inner join policy_term_trans_form pttf
> on f.id = pttf.form_id
> inner join policy_term_trans ptt
> on pttf.policy_term_trans_id = ptt.id
> inner join policy_term pt
> on ptt.policy_term_id = pt.id
> inner join policy p
> on pt.policy_id = p.id
> left outer join fillin_overflow_item_type_r foit
> on foit.id = fffi.fillin_overflow_item_type_id
> where ptt.id = p_policy_term_trans_id

Could be a lot of things. Capture a 10046 trace on both servers and feed the results thru tkprof. Examine/compare the reports, starting with the explain plans. Received on Mon Oct 03 2005 - 12:21:00 CDT

Original text of this message

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