Re: strange behavior of the optimizer
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: ORACLE-L@xxxxxxxxxxxxx, Joerg Jost <jost@xxxxxxxxxxxx>
- Date: Fri, 21 Jan 2005 17:44:20 +0100
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@xxxxxxxxxxxx> 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@xxxxxxxxxxx[1]=
m
http://www.unitrade.com[2]
------------------------------------------------------------------
=
--
//www.freelists.org/webpage/oracle-l[3]
--- Links ---
1 javascript:parent.opencompose('jost@xxxxxxxxxxx','','','')
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
