Re: Different execution plans for similar schemas on same query

Leigh Gold wrote:

> I did run the 'analyze table' and 'analyze index' after the import, will
> using dbms_stats makes some different?
>
> Interestingly, I ran the same query on different schemas on a 8.1.6 WinNt,
> and AIX 8.17, the 8.1.6 also use index scan, and the 8.1.7 use the full
> table scan. Coincident, perhaps?
>
> Leigh
>
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3CE410F2.CAF8BF5D_at_exesolutions.com...
> > Leigh Gold wrote:
> >
> > > Using the same query on two similar schemas (exp/imp) on 2 similar
> server
> > > (Win NT), I encountered 2 different execution plans, one uses full table
> > > scan (on oracle 8.1.7) and the other index scan (8.1.6). The different
> > > between these 2 schemas are that 1 run on 8.1.6 and the other 8.1.7.
> > >
> > > Have the optimizer changed with 8.1.7?
> > >
> > > The db_block_size is the same, both are analyzed with same sample size
> > > (33%). What else should I check?
> > >
> > > Thanks,
> > > Leigh
> >
> > Yes the optimizer has changed. In fact the optmimizer changed within 8.1.6
> > depending on which point release you had.
> >
> > But without current statistics the optimizer is just guessing and I
> suspect
> > that after running the import you did not run DBMS_STATS.
> >
> > Daniel Morgan
> >

As I understand it Oracle is keeping ANALYZE around only for backward compatibility. You should replace that usage with DBMS_STATS.

Daniel Morgan Received on Fri May 17 2002 - 11:48:43 CDT