I have three different procedures written in PL/SQL (Oracle database). They do various calculation and analysis, end product of all three being “INSERT INTO TABLE_A” (so all functions are inserting into same table).
It’s a pretty straightforward PL/SQL code…

Procedure one has some 500 000 rows for calculation (P1).
Procedure two has some 250 000 rows for calculation (P2).
Procedure three has some 50 000 rows for calculation (P3).

FOR li_ IN 1 .. li_max LOOP

                Calculate
                .
                .
                .
                INSERT INTO TABLE_A
END LOOP;

Problem occurs when these procedures are executed differently – their execution time runs “wild”.

1) Opening procedure using oracle SQL developer (“play” button) – execution time 2 minutes each.
2) Running them in a loop, in one “master” procedure - execution time 2 minutes each.
3) Running them via DBMS_JOB – instant execution mode - two of them run normal (2 minutes), P1 goes “crazy” and runs over an hour.

Funny thing we’ve noticed is, when running the following code:

select sq.sql_text
from v$session se ,v$process p, v$sqlarea sq 
where SE.PADDR = P.ADDR and sq.sql_id=se.sql_id and (se.Wait_class!='Idle' or se.seconds_in_wait<5) 
and SE.MACHINE=(select sys_context('userenv', 'host') from dual)
order by status,state,event,seconds_in_Wait ;

A lot of time it seems like the procedure P1 is just standing on the “Declare”, like it’s not looping at all..

We did a lot of “googling”…
https://stackoverflow.com/questions/41841491/why-oracle-stored-procedure-execution-time-is-greatly-increased-depending-on-how
https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/12_tune.htm
http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html

changed parameters more than once (alter session set optimizer_features_enable= '11.1.0.7';), but nothing seems to work.

If anything, after playing with the parameters (and retrieving them to previous state) now we have some situations where the same procedure running in the same mode (option 1 for P1, for example) 8/10 times has normal execution time (2 minutes), but 2/10 it’ll work for an hour?!

Question would be:
- Why does Oracle act this way? If a procedure is well written shouldn’t it’s execution time be the same no matter how it’s executed?
- If the problem is in the parameters, how to see this?

Thanks in advance…

procedure MY_PROC is
begin
  ...
  ...
  ...
end;

procedure MY_PROC is
begin
  dbms_monitor.session_trace_enable(waits=>true);
  ...
  ...
  ...
  dbms_monitor.session_trace_disable
end;