Home » RDBMS Server » Performance Tuning » Help me( tuning PL/SQL)
Help me( tuning PL/SQL) [message #65371] Thu, 26 August 2004 15:25 Go to next message
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
Re: Help me( tuning PL/SQL) [message #65372 is a reply to message #65371] Fri, 27 August 2004 05:01 Go to previous message
shoblock
Messages: 325
Registered: April 2004
Senior Member
stop concatenating columns together in where clauses - it prevents indexes from being used.
Previous Topic: wait event "SQL*Net message from dblink" for a Siebel7 Oracle 9.2.0.4
Next Topic: SGA shared pool
Goto Forum:
  


Current Time: Thu Mar 28 10:45:05 CDT 2024