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 -> Weird performance issue ....

Weird performance issue ....

From: <vigneaua_at_hotmail.com>
Date: 3 Oct 2005 08:21:53 -0700
Message-ID: <1128352913.422389.86910@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

        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
Received on Mon Oct 03 2005 - 10:21:53 CDT

Original text of this message

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