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
[EMAIL PROTECTED] ctt,
[EMAIL PROTECTED] cust,
[EMAIL PROTECTED] ps,
[EMAIL PROTECTED] gld,
[EMAIL PROTECTED] 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 [EMAIL PROTECTED] cust,
[EMAIL PROTECTED] ps,
[EMAIL PROTECTED] app,
[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED]
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: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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:[EMAIL PROTECTED]]
> 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: [EMAIL PROTECTED]
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> 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:[EMAIL PROTECTED]]
> > 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: [EMAIL PROTECTED]
> > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > 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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
