| Help me( tuning PL/SQL) [message #65371] |
Thu, 26 August 2004 15:25  |
Prasad
Messages: 104
Registered: October 2000
|
Senior Member |
|
|
Hello All,
I have a procedure which will find the matching rows in the same table, when i run the following procedure,I ended up with performance issues.
TKPROF:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 0.00 0 0 0 0
Execute 1870 0.83 0.79 0 0 0 0
Fetch 3752 2793.21 3168.76 8925038 16975968 0 1884
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5629 2794.04 3169.57 8925038 16975968 0 1884
PROCEDURE:
Unique Indexes defined on columns sc_no and trades columns
declare
cursor c1 is select sc_no,trades,commodity||origin||pol||pod f1,count(1) cnt
from sc_ratesheet where sc_no between '03-3102-000' and '04-3094-000'
group by commodity,origin,pol,pod,sc_no,trades
order by sc_no;
cursor c3(i_f1 IN varchar2,i_sc_no IN varchar2) is select distinct sc_no||trades no1 from sc_ratesheet
where (commodity||origin||pol||pod)=i_f1 and
substr(sc_no,1,7) not in substr(i_sc_no,1,7);
i number := 0;
v_desc varchar2(10000) :='';
v_desc1 varchar2(10000) := '';
begin
for rc1 in c1 loop
if rc1.cnt > 1 then
for rc3 in c3(rc1.f1,rc1.sc_no) loop
if i = 0 then
v_desc1 := rc3.no1;
else
v_desc := v_desc1 || 'Corresponding matching SC number is' || rc3.no1;
dbms_output.put_line(v_desc);
end if;
i := i+1;
end loop;
end if;
end loop;
end;
Can any one suggest how to fine tune the above procedure.
Best Regards
Prasad
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
