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

That's a point worth raising - but the strategy changed in 8.1.6 with the default value of

    _optimizer_mode_force (approximately) from FALSE to TRUE.

The optimisation used in pl/sql is now the same as in the calling session.

BUT - if you still have to upgrade from 8.1.5 to 8.1.7, then you may get surprised by this because loads of SQL statements within pl/sql change their execution path for no apparent reason.

--
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




Karsten Schmidt wrote in message ...


>Hi,

> you might be getting this problem, because pl/sql stored procedures

>by default run with the 'all rows' optimizer target.

> sql*plus runs in choose mode by default.

>

> If I remember correctly, there is a flag in the sql trace file that

>indicates the optimizer mode a particular statement is running under.

>

> It might be easier to to do an 'alter session set optimizer mode

>....'

> within the stored procedure rather than re-writing all the sql as

>dynamic.

>

> Granted - this is rather academic since you already found a

>workaround.

>

>Karsten

>