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)
Received on Mon Nov 10 2003 - 11:15:23 CST
