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

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/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

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/e...ad/oad_crs.asp
> http://www.outreach.washington.edu/e...oa/aoa_crs.asp
> damorgan@x.washington.edu
> (replace 'x' with a 'u' to reply)

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

Brian Peasland
dba@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"

I'd change the parameter hash_join_enabled to false
and it remove the HASH JOIN but the FILTER stay ...
and the execution is very slow ...

I'll recompute the stats tonight with dbms_stats
and check what happen !

Thanks

You're right Daniel,

I'd gather the stats and everythings come back Ok
and with the FILTER on the plan, the cost pass from 5680 (8.1.7)
to 4226 (9.2)

Thanks a lot

Chris