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

Your pl/sql call is probably using a different execution path because you have changed from literal values to bind variables. This usually affects the selectivity estimates that Oracle makes on range scans, with the result that join mechanisms may change.

Step 1: repeat the two tests with sql_trace set to true, exit from sql*plus, then use tkprof on the trace files.

Note - if you use the explain= option, you should get two execution plans per SQL statement. If the two plans for the pl/sql version differ, the first one is correct, the second one is wrong.

If you can identify where the SQL and pl/sql plans differ, then you may be able to supply a suitable set of hints to make the pl/sql emulate the pure SQL version.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Dr Deadpan wrote in message ...


>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