Re: Execution plan differ from oracle 8.1.7 to 9.2

Also might want to check any optimizer-related INIT.ORA parameters. IIRC, the *default* values changed for some of these.

HTH,
Brian

Daniel Morgan wrote:
>
> Chris wrote:
>
> >Hi,
> >
> >I'm setting up a new oracle server with oracle 9.2 on win2K
> >
> >here's an exection plan on my old database (8.1.7) :
> >
> >Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
> >
> >INSERT STATEMENT Optimizer Mode=CHOOSE 11 49
> > NESTED LOOPS 11 341 49
> > TABLE ACCESS BY INDEX ROWID PROSPECT 11 143 5
> > INDEX RANGE SCAN PROSPECT_I_PRO_POSTAL_PRO 11 3
> > TABLE ACCESS BY INDEX ROWID TRANSAC 460 8 K 4
> > INDEX RANGE SCAN TRANSAC_ID_UEF_NUM_IDX 460 3
> >
> >here's the execution plan on my new server for the same sql :
> >
> >Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
> >
> >INSERT STATEMENT Optimizer Mode=CHOOSE 477 1369
> > FILTER
> > HASH JOIN 477 16 K 1369
> > TABLE ACCESS BY INDEX ROWID TRANSAC 458 8 K 1357
> > INDEX RANGE SCAN TRANSAC_TYPE_IDX 13 137
> > TABLE ACCESS BY INDEX ROWID PROSPECT 130 K 2 M 10
> > INDEX RANGE SCAN PROSPECT_I_PRO_POSTAL_PRO 23 K 2
> >
> >Maybe someone know why i got a Filter and Hash join on the new one ...
> >i don't have any hint on my query
> >It kill the performance
> >
> >Here's the sql command :
> >
> >INSERT INTO rpt_transac_correspondance
> > (id_uef, job_id, t_type, dt_tran)
> > SELECT id_uef, :v001, t_type, dt_tran
> > FROM prospect, transac trs
> > WHERE pro_no_id_uef = trs.id_uef
> > AND TO_DATE (dt_tran, 'yyyymmdd') BETWEEN TO_DATE (:v002,
> >'yyyy/mm/dd')
> > AND TO_DATE (:v003,
> >'yyyy/mm/dd')
> > AND (pro_postal LIKE 'H2Y' || '%')
> > AND t_type IN (4)
> >
> >
> >Thanks
> >Chris
> >
> >
> The CBO has changed dramatically since 8.1.7. That is why. Are your 9i
> statistics from
> DBMS_STATS and are they current?
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"