Home » RDBMS Server » Performance Tuning » Pl/sql procedure experience a high parse count using oracle 9i db-link.
Pl/sql procedure experience a high parse count using oracle 9i db-link. [message #184580] Thu, 27 July 2006 02:46 Go to next message
Josepha
Messages: 1
Registered: July 2006
Junior Member
Hi,

The following procedure is in oracle 9i database. This procedure uses a db-link to query 8i database. The query experience a high " PARSE " rate at the remote ( 8i ) database. Enclosed is the code and the tkprof report.

My question is :

1. Is the high parsing at the remote database normal ?. 2. If not what is the performance impact. Your help is much appreciated.

Thanks!


Procedure used
==================

CREATE OR REPLACE PROCEDURE TMOFF_PARSING9ito8i AS
CURSOR C IS
select accountid from rlc_tmp;
v_accountno NUMBER(10);

BEGIN
FOR R IN C LOOP

SELECT CMF_KA.ACCOUNT_NO
INTO v_accountno
FROM RL_ACCOUNTS, CMF@arbor9ioff CMF_KA
WHERE RL_ACCOUNTS.ACCOUNTID = R.ACCOUNTID
AND
CMF_KA.ACCOUNT_NO = RL_ACCOUNTS.TMA_CMF_11;

insert into jjoff(my_account) values(v_accountno);

commit;


END LOOP;
exception when no_data_found then
dbms_output.put_line('No data available for this record');
null;
END;
/




TKPROF :
============

call count cpu elapsed disk query current rows
------- ------ --- -------- ----- ----- -------- -----
Parse 9275 2.50 2.52 0 0 0 0
Execute 9275 0.65 0.73 0 0 0 0
Fetch 18549 0.67 0.57 34 18550 0 9275
------- ------ ---- -------- ----- ------ ------ ------
total 37099 3.82 3.82 34 18550 0 9275
Re: Pl/sql procedure experience a high parse count using oracle 9i db-link. [message #184661 is a reply to message #184580] Thu, 27 July 2006 06:54 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Can you try following code?

CREATE OR REPLACE PROCEDURE TMOFF_PARSING9ito8i AS
CURSOR C IS
select rt.accountid, CMF_KA.ACCOUNT_NO
from rlc_tmp rt, RL_ACCOUNTS ra, CMF@arbor9ioff CMF_KA
WHERE ra.accountid(+) = rt.accountid AND
CMF_KA.ACCOUNT_NO(+) = RA.TMA_CMF_11 ;
v_accountno NUMBER(10);

BEGIN
FOR R IN C LOOP
if R.ACCOUNT_NO is null then
dbms_output.put_line('No data available for this record');
else
insert into jjoff(my_account) values(R.ACCOUNT_NO);
commit;
end if;
END LOOP;
END;

HTH.
Michael
Previous Topic: what is BITMAP CONVERSION TO ROWIDS in Plan?
Next Topic: Optimizing Hierachical query
Goto Forum:
  


Current Time: Sat Dec 10 12:44:08 CST 2016

Total time taken to generate the page: 0.14664 seconds