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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: AW: AW: Slow performance of code using dblinks ver 8.1.6.2

Re: AW: AW: Slow performance of code using dblinks ver 8.1.6.2

From: Mohammad Rafiq <rafiq9857_at_hotmail.com>
Date: Mon, 26 Feb 2001 11:41:03 -0800
Message-ID: <F001.002BD93D.20010226114548@fatcity.com> 






Andreas,



Thanks for questions.



Yes, NLS param are almost same except 8i new parameters. There is no join
between local and remote tables however joining on all tables at remote 
machine.



Following is the customized code:Same code runs in 15 minutes between 
7.3.4.5 databases but took hours between 8.1.6.2 and 7.3.4.5 databases.
Any clue????



Regards


Rafiq




DECLARE


v_amount_applied_late1 number;
v_amount_applied_late2 number;
v_adjustment_amount number;
v_amt_due_rem_inv number;
v_mtx_acctno varchar2(3);





cursor c_invoice is


(



select cust.customer_name cust_name,


cust.customer_number cust_no,


ctt.name invoice_type_inv ,

ps.payment_schedule_id payment_sched_id_inv,
ps.class class_inv,
ps.due_date  due_date_inv,
ps.acctd_amount_due_remaining amt_due_remaining_inv,
ps.trx_number invnum,




ceil(to_date(sysdate,'DD-MON-RR') - ps.due_date) days_past_due,

ps.amount_adjusted amount_adjusted_inv,
ps.amount_applied amount_applied_inv,
ps.amount_credited amount_credited_inv,
ps.gl_date gl_date_inv,




decode( greatest(-9999,


ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),
least(0,


ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),1,


    0) amt_curr,


    decode( greatest(1,


    ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),
    least(10,


    ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),1,
    0) amt_pd10,


    decode( greatest(11,


    ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),
    least(20,


    ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),1,
    0) amt_pd20,


    decode( greatest(21,


    ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),
    least(30,


    ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),1,
    0) amt_pd30,


    decode( greatest(31,


    ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),
    least(60,


    ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),1,
    0) amt_pd60,


    decode( greatest(61,


    ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),
    least(90,


    ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),1,
    0) amt_pd90,


    decode( greatest(91,


    ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),
    least(9999,


    ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),1,
    0) amt_pd91


from

ra_cust_trx_types_at_OLAP_APPS_LINK.world ctt,
ra_customers_at_OLAP_APPS_LINK.world cust,
ar_payment_schedules_at_OLAP_APPS_LINK.world ps,
ra_cust_trx_line_gl_dist_at_OLAP_APPS_LINK.world gld,
gl_code_combinations_at_OLAP_APPS_LINK.world c
where  ps.gl_date <= to_date(sysdate,'DD-MON-RR')
and    ps.customer_id+0 = cust.customer_id
and    ps.cust_trx_type_id = ctt.cust_trx_type_id


and    ps.gl_date_closed > to_date(sysdate,'DD-MON-RR')
and    ps.customer_trx_id+0 = gld.customer_trx_id

and    gld.account_class = 'REC'
and    gld.latest_rec_flag = 'Y'
and    gld.code_combination_id = c.code_combination_id


UNION ALL



select cust.customer_name cust_name,


cust.customer_number cust_no,


initcap('Payment') invoice_type_inv,

ps.payment_schedule_id payment_schedule_id_inv,
ps.class class_inv,
ps.due_date due_date_inv,




nvl(-sum(app.acctd_amount_applied_from),0) amount_due_remaining_inv,
ps.trx_number invnum,


ceil(to_date(sysdate,'DD-MON-RR') - ps.due_date) days_past_due,

ps.amount_adjusted amount_adjusted_inv,
ps.amount_applied amount_applied_inv,
ps.amount_credited amount_credited_inv,
ps.gl_date gl_date_inv,




decode( greatest(-9999,


  ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),
  least(0,


  ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),1,
  0) amt_curr,


  decode( greatest(1,


  ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),
  least(10,


  ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),1,
  0) amt_pd10,


  decode( greatest(11,


  ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),
  least(20,


  ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),1,
  0) amt_pd20,


  decode( greatest(21,


  ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),
  least(30,


  ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),1,
  0) amt_pd30,


  decode( greatest(31,


  ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),
  least(60,


  ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),1,
  0) amt_pd60,


  decode( greatest(61,


  ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),
  least(90,


  ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),1,
  0) amt_pd90,


  decode( greatest(91,


  ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),
  least(9999,


  ceil(to_date(sysdate,'DD-MON-RR')-ps.due_date)),1,
  0) amt_pd91


from   ra_customers_at_OLAP_APPS_LINK.world cust,

ar_payment_schedules_at_OLAP_APPS_LINK.world ps,
ar_receivable_applications_at_OLAP_APPS_LINK.world app,
gl_code_combinations_at_OLAP_APPS_LINK.world c


where  app.gl_date+0 <= to_date(sysdate,'DD-MON-RR')
and ps.trx_number is not null


and    ps.customer_id = cust.customer_id(+)
and    ps.cash_receipt_id+0 = app.cash_receipt_id
and    app.code_combination_id = c.code_combination_id
and    app.status in ( 'ACC', 'UNAPP', 'UNID')
and    nvl(app.confirmed_flag, 'Y') = 'Y'
and    ps.gl_date_closed > to_date(sysdate,'DD-MON-RR')
and    (app.reversal_gl_date > to_date(sysdate,'DD-MON-RR') OR


        app.reversal_gl_date is null )


and    nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
GROUP BY cust.customer_name,


cust.customer_number,


cust.customer_id,

ps.payment_schedule_id,
ps.due_date,
ps.trx_number,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
ps.class,
decode( app.status, 'UNID', 'UNID',
        'UNAPP')




);



v_invoice c_invoice%ROWTYPE;



BEGIN



open c_invoice;


loop


fetch c_invoice into v_invoice;


EXIT WHEN c_invoice%NOTFOUND;


v_amount_applied_late1:=0;
v_amount_applied_late2:=0;
v_adjustment_amount:=0;
v_amt_due_rem_inv:=v_invoice.amt_due_remaining_inv;





IF


v_invoice.amount_applied_inv is not null THEN
IF v_invoice.invoice_type_inv!='Payment' THEN
  select nvl(


  sum(


  decode(v_invoice.class_inv,'CM', ra.acctd_amount_applied_from,
  ra.acctd_amount_applied_to)


  +nvl(ra.acctd_earned_discount_taken,0)
  +nvl(ra.acctd_unearned_discount_taken,0)
  *


  decode(v_invoice.class_inv,'CM',


  decode(ra.application_type,'CM',-1,1),1)),0)
  into v_amount_applied_late1


  from ar_receivable_applications_at_OLAP_APPS_LINK.world ra
  where


  (ra.applied_payment_schedule_id=v_invoice.payment_sched_id_inv or
   ra.payment_schedule_id=v_invoice.payment_sched_id_inv)
  and ra.status='APP'


  and nvl(ra.confirmed_flag,'Y')='Y'


  and ra.gl_date+0>to_date(sysdate, 'DD-MON-RR');
END IF;



END IF;


IF v_invoice.amount_applied_inv is null THEN
IF v_invoice.amount_credited_inv is not null THEN
  IF v_invoice.invoice_type_inv!='Payment' THEN
   select nvl(


   sum(


   (ra.acctd_amount_applied_to+


    nvl(ra.acctd_earned_discount_taken,0)+
    nvl(ra.acctd_unearned_discount_taken,0))
    *


    decode(v_invoice.class_inv,'CM', 


decode(ra.application_type,'CM',-1,1),1)


    ),0)


    into v_amount_applied_late2


    from ar_receivable_applications_at_OLAP_APPS_LINK.world ra
    where (ra.applied_payment_schedule_id=v_invoice.payment_sched_id_inv
    or ra.payment_schedule_id=v_invoice.payment_sched_id_inv)
    and ra.status||''='APP'


    and nvl(ra.confirmed_flag,'Y')='Y'


    and ra.gl_date+0>to_date(sysdate,'DD-MON-RR')
   ;


   END IF;



END if;


END IF;


IF v_invoice.amount_adjusted_inv is not null  THEN
IF v_invoice.invoice_type_inv!='Payment' THEN
  select nvl(sum(nvl(acctd_amount,0)),0) into
  v_adjustment_amount


  from ar_adjustments_at_OLAP_APPS_LINK.world
  where gl_date>to_date(sysdate, 'DD-MON-RR')
  and status='A'


  and payment_schedule_id=v_invoice.payment_sched_id_inv
  ;


END IF;



END IF;



v_amt_due_rem_inv:=nvl(v_amt_due_rem_inv,0)+
nvl(v_amount_applied_late1,0)+nvl(v_amount_applied_late2,0)-
nvl(v_adjustment_amount,0);





v_mtx_acctno:=(substr(v_invoice.cust_no,2,1)||substr(v_invoice.cust_no,3,1)||
substr(v_invoice.cust_no,4,1))


;



IF v_amt_due_rem_inv!=0 THEN


INSERT INTO HAR_ATB_ITEMS (



cust_name,


custno,


invoice,


invoice_type,

due_date,
amt_outs,
amt_curr,
amt_pd10,
amt_pd20,
amt_pd30,
amt_pd60,
amt_pd90,
amt_pd91,




asof_date,


mtx_acctno)


VALUES(


v_invoice.cust_name,
v_invoice.cust_no,
v_invoice.invnum,
v_invoice.invoice_type_inv,
v_invoice.due_date_inv,
v_amt_due_rem_inv,
v_invoice.amt_curr*v_amt_due_rem_inv,
v_invoice.amt_pd10*v_amt_due_rem_inv,
v_invoice.amt_pd20*v_amt_due_rem_inv,
v_invoice.amt_pd30*v_amt_due_rem_inv,
v_invoice.amt_pd60*v_amt_due_rem_inv,
v_invoice.amt_pd90*v_amt_due_rem_inv,
v_invoice.amt_pd91*v_amt_due_rem_inv,




sysdate,


v_mtx_acctno


)


;


END IF;



END LOOP;


close c_invoice;


COMMIT;



END;



.


/







Reply-To: ORACLE-L_at_fatcity.com


To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: Mon, 26 Feb 2001 09:50:29 -0800



Hi!



Sorry, just a few questions more instead of an answer.
Do you have the same NLS settings on both instances?
Are there joins between local tables and/or remote tables in
your select statement?



What does the result set ( how many rows, columns ) of your select look
like?



regards A.H.





 > ----------
 > Von:         Mohammad Rafiq[SMTP:rafiq9857_at_hotmail.com]
 > Gesendet:    Freitag, 23. Februar 2001 22:00
 > An:  Multiple recipients of list ORACLE-L
 > Betreff:     Re: AW: Slow performance of code using dblinks ver 8.1.6.2
 >
 > Thanks for your only response from the list. Same code is running in 15
 > minutes using db_links between 7.3.4.5 databases but very very slow when
 > running from 8.1.6.2 to 7.3.4.5 database. This is basicaly an Ananymouse
 > PL/SQL block which select rows from 7.3.4.5 database and insert into a
 > table
 > 8.1.6.2 database...If you or any list guru has any idea ,please
 > help/comment.
 > Have a nice weekend...
 > Regards
 > Rafiq
 >
 >
 > Reply-To: ORACLE-L_at_fatcity.com
 > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
 > Date: Thu, 22 Feb 2001 10:31:46 -0800
 >
 > Hi!
 >
 > Got similar problems concerning database links,
 > mainly slow performance, about 10% of the throughput compared to same
 > processing without db_links.
 > There seems to be no - or at least only poor - useful documentation for
 > database links and/or
 > about tuning their performance.
 >
 > Would be a nice new thread for ORACLE-L : "Database Links: Tuning /
 > Problems
 > / Tips & Traps ..."
 >
 > The only thing that helped ( in our case) , was to rewrite the code, in
 > order to "partition"
 > the old big program ( which did all ) into a bunch of small programs (
 > each
 > doing now only a few things).
 > Also consider rewriting  your programs in such a way, that you can have
 > multiple
 > instances of them running simultaneously.
 > You know, "divide et impera".
 >
 > But I'm sure, there must be a secret switch somewhere deep in Oracle's
 > guts,
 > Iabelled "Boost DB_LINK Performance". I just haven't found it yet ;-)
 >
 > A.H.
 >
 >
 >  > ----------
 >  > Von:      Mohammad Rafiq[SMTP:rafiq9857_at_hotmail.com]
 >  > Gesendet:         Donnerstag, 22. Februar 2001 15:56
 >  > An:       Multiple recipients of list ORACLE-L
 >  > Betreff:  Slow performance of code using dblinks ver 8.1.6.2
 >  >
 >  > Hi All
 >  > I am placing my following question once again as no response received
 > so
 >  > far.....I just wanted to know any possible problem when using db_links
 >  > from
 >  > 8.1.6.2 database to 7.3.4.5 database. No specific reference found at
 >  > metalink....
 >  >
 >  > Thanks
 >  > Rafiq
 >  >
 >  >
 >  > Reply-To: ORACLE-L_at_fatcity.com
 >  > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
 >  > Date: Wed, 21 Feb 2001 07:15:30 -0800
 >  >
 >  > Platform HP 11.0.32
 >  > Database 8.1.6.2
 >  >
 >  > Recently we have migrated our Datawarehouse application/database from
 >  > HP-10.20/Oracle 7.3.4.5 to above platform and testing code to check 


any

 >  > possible problem/performance issues.
 >  >
 >  > General complaint is about those code using dblinks/network. These
 > codes
 >  > were running fine in previous environment. We are getting data from
 >  > 7.3.4.5
 >  > databases.
 >  >
 >  > Please advise where to look to fix this issue. As per Unix Admin 


packet

 >  > size/other network configuration is same as other boxes with HP-10.20
 > and
 >  > version 7.3.4.5.
 >  > In one situation a 15 minutes job takes more than 5 hours and still 


not

 >  > finishing.
 >  > Any pointer/help shall be appreciated.
 >  > Regards
 >  > Rafiq
 >  >
 >  > _________________________________________________________________
 >  > Get your FREE download of MSN Explorer at http://explorer.msn.com
 >  >
 >  > --
 >  > Please see the official ORACLE-L FAQ: http://www.orafaq.com
 >  > --
 >  > Author: Mohammad Rafiq
 >  >   INET: rafiq9857_at_hotmail.com
 >  >
 >  > Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
 >  > San Diego, California        -- Public Internet access / Mailing Lists
 >  > --------------------------------------------------------------------
 >  > To REMOVE yourself from this mailing list, send an E-Mail message
 >  > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
 >  > the message BODY, include a line containing: UNSUB ORACLE-L
 >  > (or the name of mailing list you want to be removed from).  You may
 >  > also send the HELP command for other information (like subscribing).
 >  >
 >  > _________________________________________________________________
 >  > Get your FREE download of MSN Explorer at http://explorer.msn.com
 >  >
 >  > --
 >  > Please see the official ORACLE-L FAQ: http://www.orafaq.com
 >  > --
 >  > Author: Mohammad Rafiq
 >  >   INET: rafiq9857_at_hotmail.com
 >  >
 >  > Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
 >  > San Diego, California        -- Public Internet access / Mailing Lists
 >  > --------------------------------------------------------------------
 >  > To REMOVE yourself from this mailing list, send an E-Mail message
 >  > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
 >  > the message BODY, include a line containing: UNSUB ORACLE-L
 >  > (or the name of mailing list you want to be removed from).  You may
 >  > also send the HELP command for other information (like subscribing).
 >  >
 > --
 > Please see the official ORACLE-L FAQ: http://www.orafaq.com
 > --
 > Author: Haunschmidt Andreas VASL/FAS
 >    INET: Andreas.Haunschmidt_at_voest.co.at
 >
 > Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
 > San Diego, California        -- Public Internet access / Mailing Lists
 > --------------------------------------------------------------------
 > To REMOVE yourself from this mailing list, send an E-Mail message
 > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
 > the message BODY, include a line containing: UNSUB ORACLE-L
 > (or the name of mailing list you want to be removed from).  You may
 > also send the HELP command for other information (like subscribing).
 >
 > _________________________________________________________________
 > Get your FREE download of MSN Explorer at http://explorer.msn.com
 >
 > --
 > Please see the official ORACLE-L FAQ: http://www.orafaq.com
 > --
 > Author: Mohammad Rafiq
 >   INET: rafiq9857_at_hotmail.com
 >
 > Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
 > San Diego, California        -- Public Internet access / Mailing Lists
 > --------------------------------------------------------------------
 > To REMOVE yourself from this mailing list, send an E-Mail message
 > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
 > the message BODY, include a line containing: UNSUB ORACLE-L
 > (or the name of mailing list you want to be removed from).  You may
 > also send the HELP command for other information (like subscribing).
 >



--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Haunschmidt Andreas VASL/FAS
   INET: Andreas.Haunschmidt_at_voest.co.at

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mohammad Rafiq
  INET: rafiq9857_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).


Received on Mon Feb 26 2001 - 13:41:03 CST












Original text of this message













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