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

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

drdeadpan_at_yahoo.com (Dr Deadpan) wrote in message news:<a944d23e.0212291302.5b72bfa0_at_posting.google.com>...
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<aujmc3$j2p$1$830fa79f_at_news.demon.co.uk>...
> > 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.
> >
>
> I ran tkprof and the plans WERE indeed different.
> Rather than use HINTS to force
> the 'correct' plan, I used dynamic SQL WITHOUT binding the input variables
> (goes totally against what is recommended when using Dynamic SQL)
> and was able to bring the time back to normal. COnsidering teh routine
> is run once a day, parse times were really of no real concern.
>
> Thanks for your input. You're right again as usual. Hope to
> attend your seminars sometime in the future.
>
> Regards,
>
> Venkat
Received on Mon Dec 30 2002 - 03:12:01 CST