explain plan differences...

From: Andre Doehn <a.doehn_at_gmx.net>
Date: Tue, 18 Dec 2001 18:41:45 +0100
Message-ID: <9vnv2h$geimg$1@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 Tue Dec 18 2001 - 11:41:45 CST