Thanks for your suggestions. I am going through the tuning manual to
get a better understanding of the Optimizer and tuning.
For the time being I plan to use a hint to get a more efficient
explaing plan
On Nov 3, 7:06 pm, "joel garry" <joel-ga..._at_home.com> wrote:
> barraboombarrabin wrote:
> > Hi,
> > I have a couple of queries where there explain plan that I get from
> > TOAD or SQL Plus is different from the explain plan that is being used
> > when the same select statement is executed from PL/SQL package (which I
> > determined by looking at V$SQL_PLAN table). The table is truncated and
> > loaded with data every day and during the data load process the indexes
> > are dropped and recreated after the data load. After the index creation
> > is done, the table in question is also analyzed using dbms_stats.
> > I would like to use a hint only as a last resort.
> > I would appreciate it if someone can help me with some steps that can
> > be taken to ensure that the explain plan within the PL/SQL is the
> > desired one without using the hint.Explaining a plan is just what might happen ("With bind variables in
> general, the EXPLAIN PLAN output might not represent the real execution
> plan." - Performance Tuning Manual). V$SQL_PLAN is what actually used
> by the optimizer. See metalink Note:186548.1.
>
> See the Performance Tuning Manual about Plan Stability (which is hints,
> of course).
>
> There may be an argument for just letting the CBO do its thing if you
> are getting fresh data and stats every day. Do you have any evidence
> that the plan might change with an expected different data
> distribution?
>
> jg
> --
> @home.com is bogus.http://www.networkworld.com/graphics/2006/subnetmask.jpg
Received on Wed Dec 27 2006 - 15:08:46 CST
