Unexplained Performance Nightmare !!! Suggestions/solutions Please!!

Hi,

   I am writing to the group as a last resort. I've gained enormous info. from the various threads in the past and I hope this query of mine will
be solved too.

   Platform : Oracle 8.1.6 on IBM AIX

   I am performing a aggregate query that joins 3 tables and a view using simple equi-joins and has a /*+ ORDERED */ hint. I use a plain INSERT into SELECT * to perform the insert from the query. No parallel hint or anything of that sort.

   When I run the INSERT from SQL*Plus, it gets done in about 25 minutes.
This is the structure.

 SET TRANSACTION USE ROLLBACK SEGMENT rbs1;  

 INSERT INTO SELECT /*+ ORDERED */
  col1, col2,SUM(col3)...
 FROM a , b, c,d
 ...
 where fiscal_month between 199901 and 200203.

  COMMIT;   The above when run from SQL*Plus takes 25 minuts to complete.

  HERE'S THE PROBLEM.   I put the EXACT same statement in a procedure/package and it takes   65 minutes to complete. The structure of the procedure is as follows.

  CREATE OR REPLACE PROCEDURE proc1
  (
    fm1 IN NUMBER, fm2 In NUMBER, if_par IN VARCHAR2   )
  IS

   BEGIN

        COMMIT;
        DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT('RBS1');


        /* Note : the IF is never executed because I pass in another
         parameter ,if_par that fails the IF test so all that is 
         run is the INSERT. I put this here to show you the structure
         of the procedure I have */
        IF if_par ...
        THEN

        END IF;

         INSERT INTO SELECT /*+ ORDERED */
        col1, col2,SUM(col3)... 
        FROM a , b, c,d
         ...
        where fiscal_month between fm1 and fm2.

        COMMIT;

    END;   That's it . It takes about an hour to complete.

  I've run this repeatedly. WHAT IS GOING ON? AM I missing something?

   Thanks,

   Venkat Received on Fri Dec 27 2002 - 18:36:37 CST