Have you calculated statistics for both schemas? Your import will have
estimated stats for the second schema but statistics differences may explain
the different plans.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Andre Doehn" <a.doehn_at_gmx.net> wrote in message
news:9vnv2h$geimg$1_at_ID-71421.news.dfncis.de...
> hi,
> ive problem with my oracle 8.1.7 db and a statement. ive created two users
> which schemata is the exactly the same! (i used exp/imp to import the db
> dump
> in the second "test" user)
>
> the optimizer mode of this db is FIRST_ROWS - I have a statement which
will
> be very fast
> executed when i used the first schema but on the second schema the
execution
> time
> of the statement took about 1 minute - ive created a explain plan for that
> statement
> for the first user:
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=51 Card=11
Bytes=1914)
> 1 0 SORT (GROUP BY) (Cost=51 Card=11 Bytes=1914)
> 2 1 NESTED LOOPS (Cost=24 Card=11 Bytes=1914)
> 3 2 NESTED LOOPS (Cost=4 Card=20 Bytes=2640)
> 4 3 NESTED LOOPS (Cost=3 Card=1 Bytes=119)
> 5 4 NESTED LOOPS (Cost=2 Card=1 Bytes=93)
> 6 5 TABLE ACCESS (FULL) OF 'TBL_ANGEBOT_GRUPPE' (Cost=1
> Card=1 Bytes=64)
> 7 5 TABLE ACCESS (FULL) OF 'TBL_GRUPPE_REF' (Cost=1
Card=1
> Bytes=29)
> 8 4 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_BIETER_GRUPPE'
> (Cost=1 Card=41 Bytes=1066)
> 9 8 INDEX (UNIQUE SCAN) OF 'PK_TBL_BIETER_GRUPPE'
(UNIQUE)
> 10 3 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_ANGEBOT' (Cost=1
> Card=2049 Bytes=26637)
> 11 10 INDEX (UNIQUE SCAN) OF 'PK_TBL_ANGEBOT' (UNIQUE)
> 12 2 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_BIETER_NACHFRAGE'
> (Cost=1 Card=53 Bytes=2226)
> 13 12 INDEX (UNIQUE SCAN) OF 'PK_TBL_BIETER_NACHFRAGE'
(UNIQUE)
>
> for the second user:
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=52 Card=1 Bytes=174)
> 1 0 SORT (GROUP BY) (Cost=52 Card=1 Bytes=174)
> 2 1 NESTED LOOPS (Cost=25 Card=1 Bytes=174)
> 3 2 HASH JOIN (Cost=24 Card=1 Bytes=161)
> 4 3 TABLE ACCESS (FULL) OF 'TBL_GRUPPE_REF' (Cost=1 Card=1
> Bytes=29)
> 5 3 MERGE JOIN (CARTESIAN) (Cost=22 Card=53 Bytes=6996)
> 6 5 NESTED LOOPS (Cost=2 Card=1 Bytes=90)
> 7 6 TABLE ACCESS (FULL) OF 'TBL_ANGEBOT_GRUPPE' (Cost=1
> Card=1 Bytes=64)
> 8 6 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_BIETER_GRUPPE'
> (Cost=1 Card=41 Bytes=1066)
> 9 8 INDEX (UNIQUE SCAN) OF 'PK_TBL_BIETER_GRUPPE'
> (UNIQUE)
> 10 5 SORT (JOIN) (Cost=21 Card=53 Bytes=2226)
> 11 10 TABLE ACCESS (FULL) OF 'TBL_BIETER_NACHFRAGE'
(Cost=20
> Card=53 Bytes=2226)
> 12 2 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_ANGEBOT' (Cost=1
> Card=2196 Bytes=28548)
> 13 12 INDEX (UNIQUE SCAN) OF 'PK_TBL_ANGEBOT' (UNIQUE)
>
> What does it mean? When i have two users with the same optimizer mode and
> the same statement I think the explain plan should be the same?
> here is my statement:
>
> select ag.T_ANGEBOT_GRUPPE_ID, ag.T_BIETER_GRUPPE_ID, ag.RECORD_STATE,
> ag.NUM_TIMESTAMP, ag.T_ISO_WAEHRUNG_ID, bg.T_RFX_GROUP_ID,
> ROUND(SUM(a.EUR_PREIS * (1 - ag.NUM_RABATT / 100) * (1 + bn.NUM_BONUSREL /
> 100) + bn.NUM_BONUSABS)) as NUM_TCO
> from TBL_ANGEBOT_GRUPPE ag, TBL_GRUPPE_REF gr, TBL_ANGEBOT a,
> TBL_BIETER_NACHFRAGE bn, TBL_BIETER_GRUPPE bg
> where ag.T_ANGEBOT_GRUPPE_ID = gr.T_ANGEBOT_GRUPPE_ID
> and ag.T_BIETER_GRUPPE_ID = bg.T_BIETER_GRUPPE_ID
> and gr.T_ANGEBOT_ID = a.T_ANGEBOT_ID
> and bn.T_BIETER_NACHFRAGE_ID = a.T_BIETER_NACHFRAGE_ID
> and ag.RECORD_STATE = 'A'
> and gr.RECORD_STATE = 'A'
> and a.RECORD_STATE = 'A'
> and bn.RECORD_STATE = 'A'
> group by ag.T_ANGEBOT_GRUPPE_ID, ag.T_BIETER_GRUPPE_ID, ag.RECORD_STATE,
> ag.NUM_TIMESTAMP, ag.T_ISO_WAEHRUNG_ID, bg.T_RFX_GROUP_ID
>
> any hint?
> thanks!
>
> bye
> andre
>
>
Received on Wed Dec 19 2001 - 02:54:45 CST
