Re: strange behavior of the optimizer

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Fri, 21 Jan 2005 17:44:20 +0100
Message-Id: <200501211644.j0LGiKLV021699@webmail.nexlink.net>

 I would try it in FIRST_ROWS mode, if you always expect few rows to be returned.
Regards,

Stephane Faroult

RoughSea Ltd
http://www.roughsea.com

On Fri, 21 Jan 2005 17:10 , Joerg Jost <jost_at_unitrade.com> sent:

Hi list,=0A=0Ahere is an output of the autotrace from the following sta= tement:
select * from fa where fakt_art =3D 'BES' and fakt_nr between 5300001 a= nd
5300010=0AExecution Plan
----------------------------------------------------------=0A 0 =
SELECT
STATEMENT Optimizer=3DCHOOSE (Cost=3D4 Card=3D1 Bytes=3D30=0A1)=0A 1 = 0 TABLE
ACCESS (BY INDEX ROWID) OF 'FA' (Cost=3D4 Card=3D1 Bytes=0A =3D= 301)=0A=0A 2
1 INDEX (RANGE SCAN) OF 'KEY_FA' (UNIQUE) (Cost=3D3 Card=3D1)=0A=0A= Statistics
----------------------------------------------------------=0A =
0
recursive calls=0A 0 db block gets=0A 13 consistent = gets
3 physical reads=0A 0 redo size=0A 3880 bytes sent vi= a
SQL*Net to client=0A 249 bytes received via SQL*Net from client=

2 SQL*Net roundtrips to/from client=0A 0 sorts (memory) 0 sorts (disk)=0A 9 rows processed=0A=0A=0ANow the same stat= ement, just
another type of writing:=0A=0Aselect * from fa where fakt_art =3D 'BES'= and
fakt_nr in (5300001 , 5300010=0A,
5300002,5300003,5300004,5300005,5300006,5300007,5300008,5300009)

Execution Plan=0A------------------------------------------------------=

---

0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D8783 Card=3D14471 By= te
s=3D4355771)=0A=0A 1 0 TABLE ACCESS (FULL) OF 'FA' (Cost=3D8783 = Card=3D14471
Bytes=3D43=0A 55771)=0A=0AStatistics
----------------------------------------------------------=0A =
0
recursive calls=0A 0 db block gets=0A 91957 consistent = gets
54288 physical reads=0A 52 redo size=0A 3884 bytes sen= t via
SQL*Net to client=0A 249 bytes received via SQL*Net from client=

2 SQL*Net roundtrips to/from client=0A 0 sorts (memory) 0 sorts (disk)=0A 9 rows processed=0A=0A=0A=0AWhat can we do= to get the
optimizer back to the right way? =0AThe table and the indexes are all analyzed. The fields fakt_art and fakt_nr=0A =0Aare the columns that bu= ild
together the primary key.=0A=0AThx in advance=0A=0AJ=F6rg=0A=0A=0ASyste= m:=0AOracle 9.2.0.5
AIX 5.3L=0AOptimizer_Feature_Enabled =3D 8.1.7 (due to some strange thi= ngs with
views =0A:-()=0A=0A--
-----------------------------------------------------------------=0ASE
PADERSOFT GmbH & Co. KG=0AVattmannstra=DFe 7, 33100 Paderborn=0APhone: = (+49) 52
51 / 30 1 6333=0AFax: (+49) 52 51 / 30 16 399=0AeMail: jost_at_unitrade.co[1]= m
http://www.unitrade.com[2]

---

=

--

http://www.freelists.org/webpage/oracle-l[3]

• Links --- 1 javascript:parent.opencompose('jost_at_unitrade.co','','','') 2 modules/refer.pl?redirect=http%3A%2F%2Fwww.unitrade.com 3 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l
  --
  http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 21 2005 - 11:27:11 CST