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