i have a problem in performance or procedure . the procedure have cursor declaration and then a body with loop with update statemant. when i run the procedure ,its take few hours to run all the row updates but when i put the code in anonymous block in toad , its take 3 minutes to update all the rows!! there is not difference between the codes except for that one is in procedure and the other is a block with declear and body. the procedure is like this:

CREATE OR REPLACE procedure name_proc
is
    v_start_lvl number;
    v_level_nick varchar2(50);
    v_rec_id number;
    v_parent_type varchar2(3);
    path_desc_num varchar2(1000);
    delim_cut number;
    error_var number;
    cursor strt_lvl is            
        select REC_ID from WR_LEVELS_PATH
        where start_level=-1;
    
BEGIN
for rec in strt_lvl loop
begin
     begin 
     v_rec_id:=rec.rec_id;
     select tr_parent
     into v_start_lvl 
     from
        (select tr_parent 
         from WR_LEVEL_PARENTS_REF 
         where rec.rec_id=REC_ID and PARENT_TYPE in ('STS','STM')
         order by PARENT_TYPE)
     where rownum=1;
     
     if (v_start_lvl is not null)
     then
        begin 
           update WR_LEVELS_PATH
           set start_level=v_start_lvl
           where rec_id=rec.rec_id;
        end;
     end if;
 
end;
end loop;
commit;

end;

does anyone have a explanation for this?? please!!!!

CodePudding user response：

If there is a consistent difference between running your code from within a procedure and running it from an anonymous PL/SQL block, I do not think you have included the true reason in your question. Just being in a procedure won't make your code slower, let alone many times slower.

Some things to check:

1. Are the fast and slow runs in the same execution environment? I.e., can you produce the difference making both calls manually from SQL*Developer?

2. Are both runs using the same database user?

3. Are both runs using the same input data? I.e., can you rollback after each run, switching between them both, and consistently get one fast and one slow?

4. Are there other users (or maybe another session owned by you) that might have had records locked in WR_LEVELS_PATH while it was running slow? Do you know how to check for blocked sessions in the database?

All of those differences are more likely to be the culprit than a strange side-effect of being in a procedure.

Also, unless you've omitted important things to simplify your question, your procedure code seems unnecessary. You can do all that logic with a single MERGE statement:

MERGE INTO  wr_levels_paths t
USING (
  SELECT wlp.rec_id, wlpr.tr_parent
  FROM   wr_levels_paths wlp
  CROSS APPLY ( SELECT wlpr.tr_parent
                FROM   wr_level_parents_ref wlpr
                WHERE  wlpr.rec_id = wlp.rec_id
                AND    wlpr.parent_type IN ('STS','STM')
                ORDER BY wlpr.parent_type
                FETCH FIRST 1 ROW ONLY ) wlpr
  WHERE  wlp.start_level = -1
  AND    wlpr.tr_parent IS NOT NULL ) u
ON ( t.rec_id = u.rec_id )
WHEN MATCHED THEN UPDATE SET t.start_level = u.tr_parent;

CodePudding user response：

I suspect REC_ID is a string not a number so there is an implicit conversion and loss of index.