| Pl/sql procedure experience a high parse count using oracle 9i db-link. [message #184580] |
Thu, 27 July 2006 02:46  |
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  |
michael_bialik
Messages: 621
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
