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

"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 Sun Dec 29 2002 - 15:02:15 CST