Re: Different execution plans for similar schemas on same query

From: Leigh Gold <intan_5ee_at_yahoo.com>
Date: Thu, 16 May 2002 19:28:59 -0600
Message-ID: <ac1m8u$lvh$1@news3.cadvision.com>

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
>
Received on Thu May 16 2002 - 20:28:59 CDT