Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Problem with understanding Optimization methods.

Re: Problem with understanding Optimization methods.

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Thu, 08 Jan 2004 03:54:25 -0800
Message-ID: <F001.005DC244.20040108035425@fatcity.com>


Content-Type: text/plain;
 charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Bambi,=20

Yes it is expected behaviour, but only when it is guaranteed that no = rows will be missed because of unindexed null entries. I wanted to point out that RBO is too "dumb" to realize that even though = it ordered by column A which could be null, the column B in composite = index was not null, thus causing every row to be indexed and RBO didn't = use the index.

Tanel.

  Tanel,=20

  Wouldn't this be expected behaviour when both columns of the table = were indexed?

  Bambi.
    -----Original Message-----
    From: Tanel Poder [mailto:tanel.poder.003_at_mail.ee]     Sent: Wednesday, January 07, 2004 3:30 PM     To: Multiple recipients of list ORACLE-L     Subject: Fw: Problem with understanding Optimization methods.

    (I suspect my last post didn't go through - sorry for long repost = again)

    No, but there's an order by clause on COL3 and apparently an index = as well, so RBO thinks it's cheaper to read keys+rows in order using = index full scan than reading all rows and sorting them. Note that = optimizer needs to know that all table's rows have corresponding entries = in indexes well, in order to order full table using an index (for the = reason that indexes do not index rows where all relevant column values = are NULLs). An example follows:

    SQL> create table t (a number, b number);

    Table created.

    SQL> create index i on t(a);

    Index created.

    SQL> select * from t order by a;

    Execution Plan


       0      SELECT STATEMENT Optimizer=3DCHOOSE
       1    0   SORT (ORDER BY)
       2    1     TABLE ACCESS (FULL) OF 'T'



    SQL> alter table t modify a not null;

    Table altered.

    SQL> select * from t order by a;

    Execution Plan


       0      SELECT STATEMENT Optimizer=3DCHOOSE
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T'
       2    1     INDEX (FULL SCAN) OF 'I' (NON-UNIQUE)


    When I added a not null constraint, RBO knows, that every table row = will be in the index and can use it for sorting.

    SQL> alter table t modify a null;

    Column A can be null again.

    Table altered.

    SQL> drop index i;

    Index dropped.

    Lets create a composite index on both columns.

    SQL> create index i on t(a,b);

    Index created.

    SQL> select * from t order by a;

    Execution Plan


       0      SELECT STATEMENT Optimizer=3DCHOOSE
       1    0   SORT (ORDER BY)
       2    1     TABLE ACCESS (FULL) OF 'T'

    And set one of the columns not null (now all entries will be = indexed, since at least one field of index entries will have a value)

    SQL> alter table t modify b not null;

    Table altered.

    SQL> select * from t order by a;

    Execution Plan


       0      SELECT STATEMENT Optimizer=3DCHOOSE
       1    0   SORT (ORDER BY)
       2    1     TABLE ACCESS (FULL) OF 'T'

    Hm, no change?

    SQL> analyze table t compute statistics;

    Table analyzed.

    SQL> select * from t order by a;

    Execution Plan


       0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D2 Card=3D1 = Bytes=3D26)

       1 0 INDEX (FULL SCAN) OF 'I' (NON-UNIQUE) (Cost=3D1 Card=3D1 = Bytes

              =3D26)

    Rule based optimizer was too dumb to take into account a NOT NULL = constraint of another field of a composite index. So, here's one reason = for upgrading to CBO :)

    Tanel.

> Actually from what is given I'd expect the optimizer to select a =
full table scan in anycase, there's no where clause.

    >=20

> Dick Goulet
> Senior Oracle DBA
> Oracle Certified 8i DBA
    >=20

> -----Original Message-----
> Sent: Wednesday, January 07, 2004 1:09 AM
> To: Multiple recipients of list ORACLE-L
    >=20
    >=20

> You can find out by employing the event 10053, lev 8. Looking =
from afar, however, it seems more

> likely that you haven't configured your CBO properly. Here is =
something you can try:

    >=20

> Execute the following commands:
    >=20

> alter session set optimizer_index_caching=3D40; =20
> alter session set optimizer_index_cost_adj=3D25;
    >=20
    >=20
    >=20

> After that, retry the query. If I'm correct, optimizer will now =
know that index I/O is much cheaper

> then the table one and will be much more likely to select full =
index scan over the full table scan.

> When you're really, really bored, you can read Practical Oracle 8i =
- Building Efficient Databases,

> it has a few pages about the parameters above. Read the Gospel of =
Jonathan and enjoy.

    >=20
    >=20
    >=20

> On 2004.01.07 00:29, Denham Eva wrote:
> > Hello Listers,
> >=20
> > A normal sql query from a data warehouse tool called Sagent.=20
> > SELECT COL1, COL2, COL3
> > FROM TABLE
> > ORDER BY 3;
> >=20
> > The table has approximately 2 mil records.
> > table has 22 indexes.
> >=20
> > The database is set up optimizer CHOOSE.
> > I run DBMS_Stats.Gather_Schema_Stats('SchemaName') regularly.
> > OS is Win2k
> > ORACLE 81741
> >=20
> > OK, when doing a explain plan on the above sql, I get the =
following...

> > SELECT STATEMENT Optimizer Mode=3DCHOOSE
> > SORT ORDER BY
> > TABLE ACCESS FULL TABLENAME -- Very slow =
and takes

> > hours!
> >=20
> > When adding the hint /*+RULE*/ for example I get
> > SELECT STATEMENT Optimizer Mode=3DHint:RULE
> > TABLE ACCESS BY INDEX ROWID TABLENAME
> > INDEX FULL SCAN =
TABLE_INDEX --
> > Much faster!!!
> >=20
> > Have I given enough info that anyone can explain why the CHOOSE =
mode insists

> > on doing a TABLE ACCESS FULL?
> > Is there anything I can do to improve performance? Please =
remember that this

> > query comes from a Data Warehouse tool and hence does not appear =
to accept

> > hints.
> >=20
> > Any help will be much appreciated!
> > Denham
> > =20
> > --=20
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --=20
> > Author: Denham Eva
> > INET: EVAD_at_TFMC.co.za
> >=20
> > Fat City Network Services -- 858-538-5051 =
http://www.fatcity.com

> > San Diego, California -- Mailing list and web hosting =
services

> > =


> > To REMOVE yourself from this mailing list, send an E-Mail =
message

> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and =
in

> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You =
may

> > also send the HELP command for other information (like =
subscribing).

> >=20

    >=20

> --=20
> Mladen Gogala
> Oracle DBA
> --=20
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --=20
> Author: Mladen Gogala
> INET: mgogala_at_adelphia.net

    >=20
> Fat City Network Services -- 858-538-5051 =
http://www.fatcity.com

> San Diego, California -- Mailing list and web hosting =
services

> =


> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and =
in

> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You =
may

> also send the HELP command for other information (like =
subscribing).

> --=20
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --=20
> Author: Goulet, Dick
> INET: DGoulet_at_vicr.com

    >=20
> Fat City Network Services -- 858-538-5051 =
http://www.fatcity.com

> San Diego, California -- Mailing list and web hosting =
services

> =


> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and =
in

> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You =
may

> also send the HELP command for other information (like =
subscribing).

>

------=_NextPart_000_2405_01C3D5EF.2714A3F0 Content-Type: text/html;
 charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#e0e0e0>
<DIV><FONT face=3DArial size=3D2>Bambi, </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Yes it is expected behaviour, but only =
when it is=20
guaranteed that no rows will be missed because of unindexed null=20 entries.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>I wanted to point out that RBO is too = "dumb" to=20
realize that even though it ordered by column A which could be null, the =

column&nbsp;B in composite index was not null, thus causing every row to = be=20
indexed and RBO didn't use the index.</FONT></DIV>

<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Tanel.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<BLOCKQUOTE dir=3Dltr=20

style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; = BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">   <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>   <DIV=20
  style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: = black"><B>From:</B>=20
  <A title=3Dbbellow_at_chi.navtech.com =
href=3D"mailto:bbellow_at_chi.navtech.com">Bellow,=20   Bambi</A> </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A = title=3DORACLE-L_at_fatcity.com=20
  href=3D"mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list = ORACLE-L</A>=20
  </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Thursday, January 08, = 2004 12:14=20
  AM</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> RE: Problem with = understanding=20
  Optimization methods.</DIV>
  <DIV><BR></DIV>
  <DIV><SPAN class=3D889541222-07012004><FONT face=3DArial = color=3D#0000ff=20
  size=3D2>Tanel, </FONT></SPAN></DIV>
  <DIV><SPAN class=3D889541222-07012004><FONT face=3DArial = color=3D#0000ff=20
  size=3D2></FONT></SPAN>&nbsp;</DIV>
  <DIV><SPAN class=3D889541222-07012004><FONT face=3DArial = color=3D#0000ff=20
  size=3D2>Wouldn't this be expected behaviour when both columns of the = table were=20
  indexed?</FONT></SPAN></DIV>
  <DIV><SPAN class=3D889541222-07012004><FONT face=3DArial = color=3D#0000ff=20
  size=3D2></FONT></SPAN>&nbsp;</DIV>
  <DIV><SPAN class=3D889541222-07012004><FONT face=3DArial = color=3D#0000ff=20
  size=3D2>Bambi.</FONT></SPAN></DIV>
  <BLOCKQUOTE>
    <DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT = face=3DTahoma=20

    size=3D2>-----Original Message-----<BR><B>From:</B> Tanel Poder=20     [mailto:tanel.poder.003_at_mail.ee]<BR><B>Sent:</B> Wednesday, January = 07, 2004=20

    3:30 PM<BR><B>To:</B> Multiple recipients of list=20     ORACLE-L<BR><B>Subject:</B> Fw: Problem with understanding = Optimization=20

    methods.<BR><BR></FONT></DIV>
    <DIV><FONT face=3DArial size=3D2>(I suspect my last post didn't go = through -=20

    sorry for long repost again)</FONT></DIV>     <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>     <DIV><FONT face=3DArial size=3D2>No, but there's an order by clause = on COL3 and=20

    apparently an index as well, so RBO thinks it's cheaper to read = keys+rows in=20

    order using index full scan than reading all rows and sorting them. = Note=20

    that optimizer needs to know that all table's rows have = corresponding=20

    entries in indexes well, in order to order full table using an index = (for=20

    the reason that indexes do not index rows where all relevant column = values=20

    are NULLs). An example follows:</FONT></DIV>     <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>     <DIV><FONT face=3D"Courier New" size=3D2>SQL&gt; <STRONG>create = table t (a=20

    number, b number);</STRONG></FONT></DIV>

    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3D"Courier New" size=3D2>Table created.</FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3D"Courier New" size=3D2>SQL&gt; <STRONG>create =
index i on=20
    t(a);</STRONG></FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3D"Courier New" size=3D2>Index created.</FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3D"Courier New" size=3D2>SQL&gt; select * from t =
order by=20

    a;</FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>     <DIV><FONT face=3D"Courier New" size=3D2>Execution=20     =

Plan<BR>----------------------------------------------------------<BR>&nb=
sp;&nbsp;=20

    0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT STATEMENT=20     Optimizer=3DCHOOSE<BR>&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; = SORT=20
    (ORDER BY)<BR>&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp; = 1&nbsp;&nbsp;&nbsp;&nbsp;=20

    TABLE ACCESS (FULL) OF 'T'</FONT></DIV>

    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3D"Courier New" size=3D2>SQL&gt; <STRONG>alter table =
t modify a=20

    not null;</STRONG></FONT></DIV>

    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3D"Courier New" size=3D2>Table altered.</FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3D"Courier New" size=3D2>SQL&gt; select * from t =
order by=20

    a;</FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>     <DIV><FONT face=3D"Courier New" size=3D2>Execution=20     =

Plan<BR>----------------------------------------------------------<BR>&nb=
sp;&nbsp;=20

    0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT STATEMENT=20     Optimizer=3DCHOOSE<BR>&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; = TABLE=20
    ACCESS (BY INDEX ROWID) OF 'T'<BR>&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;=20     1&nbsp;&nbsp;&nbsp;&nbsp; INDEX (FULL SCAN) OF 'I' = (NON-UNIQUE)</FONT></DIV>

    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3DArial size=3D2><EM>When I added a not null =
constraint, RBO=20

    knows, that every table row will be in the index and can use it for=20     sorting.</EM></FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>     <DIV><FONT face=3D"Courier New" size=3D2>SQL&gt; alter table t = modify a=20

    null;</FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3DArial size=3D2><EM>Column A can be null=20
    again.</EM></FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3D"Courier New" size=3D2>Table altered.</FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3D"Courier New" size=3D2>SQL&gt; drop index =
i;</FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3D"Courier New" size=3D2>Index dropped.</FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>     <DIV><FONT face=3DArial size=3D2><EM>Lets create&nbsp;a composite = index on both=20

    columns.</EM></FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>     <DIV><FONT face=3D"Courier New" size=3D2>SQL&gt; <STRONG>create = index i on=20

    t(a,b);</STRONG></FONT></DIV>

    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3D"Courier New" size=3D2>Index created.</FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3D"Courier New" size=3D2>SQL&gt; select * from t =
order by=20

    a;</FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>     <DIV><FONT face=3D"Courier New" size=3D2>Execution=20     =

Plan<BR>----------------------------------------------------------<BR>&nb=
sp;&nbsp;=20

    0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT STATEMENT=20     Optimizer=3DCHOOSE<BR>&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; = SORT=20
    (ORDER BY)<BR>&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp; = 1&nbsp;&nbsp;&nbsp;&nbsp;=20

    TABLE ACCESS (FULL) OF 'T'</FONT></DIV>     <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>     <DIV><FONT face=3DArial size=3D2><EM>And set one of the columns not = null (now=20

    all entries will be indexed, since at least one field of index = entries will=20

    have a value)</EM></FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>     <DIV><FONT face=3D"Courier New" size=3D2>SQL&gt; <STRONG>alter table = t modify b=20

    not null;</STRONG></FONT></DIV>

    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3D"Courier New" size=3D2>Table altered.</FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3D"Courier New" size=3D2>SQL&gt; select * from t =
order by=20

    a;</FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>     <DIV><FONT face=3D"Courier New" size=3D2>Execution=20     =

Plan<BR>----------------------------------------------------------<BR>&nb=
sp;&nbsp;=20

    0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT STATEMENT=20     Optimizer=3DCHOOSE<BR>&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; = SORT=20
    (ORDER BY)<BR>&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp; = 1&nbsp;&nbsp;&nbsp;&nbsp;=20

    TABLE ACCESS (FULL) OF 'T'</FONT></DIV>     <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>     <DIV><FONT face=3DArial size=3D2><EM>Hm, no = change?</EM></FONT></DIV>

    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>     <DIV><FONT face=3D"Courier New" size=3D2>SQL&gt; <STRONG>analyze = table t compute=20

    statistics;</STRONG></FONT></DIV>

    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3D"Courier New" size=3D2>Table =
analyzed.</FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3D"Courier New" size=3D2>SQL&gt; select * from t =
order by=20

    a;</FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>     <DIV><FONT face=3D"Courier New" size=3D2>Execution=20     =

Plan<BR>----------------------------------------------------------<BR>&nb=
sp;&nbsp;=20

    0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT STATEMENT Optimizer=3DCHOOSE = (Cost=3D2=20

    Card=3D1 Bytes=3D26)<BR>&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp; = 0&nbsp;&nbsp; INDEX=20

    (FULL SCAN) OF 'I' (NON-UNIQUE) (Cost=3D1 Card=3D1=20     Bytes<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20     =3D26)</FONT></DIV>
    <DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</DIV>     <DIV><FONT face=3DArial size=3D2><EM>Rule based optimizer was too = dumb to take=20

    into account a&nbsp;NOT NULL&nbsp;constraint of another field of a = composite=20

    index. So, here's one reason for upgrading to CBO = :)</EM></FONT></DIV>

    <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3DArial size=3D2>Tanel.</FONT></DIV>
    <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3DArial size=3D2>----- Original Message ----- =
</FONT>

    <DIV><FONT face=3DArial size=3D2>From: "Goulet, Dick" &lt;</FONT><A=20     href=3D"mailto:DGoulet_at_vicr.com"><FONT face=3DArial=20     size=3D2>DGoulet_at_vicr.com</FONT></A><FONT face=3DArial = size=3D2>&gt;</FONT></DIV>

    <DIV><FONT face=3DArial size=3D2>To: "Multiple recipients of list = ORACLE-L"=20
    &lt;</FONT><A href=3D"mailto:ORACLE-L_at_fatcity.com"><FONT = face=3DArial=20

    size=3D2>ORACLE-L_at_fatcity.com</FONT></A><FONT face=3DArial=20     size=3D2>&gt;</FONT></DIV>
    <DIV><FONT face=3DArial size=3D2>Sent: Wednesday, January 07, 2004 = 4:04=20

    PM</FONT></DIV>
    <DIV><FONT face=3DArial size=3D2>Subject: RE: Problem with = understanding=20

    Optimization methods.</FONT></DIV></DIV>     <DIV><FONT face=3DArial><BR><FONT size=3D2></FONT></FONT></DIV><FONT = face=3DArial=20

    size=3D2>&gt; Actually from what is given I'd expect the optimizer = to select a=20

    full table scan in anycase, there's no where clause.<BR>&gt; = <BR>&gt; Dick=20

    Goulet<BR>&gt; Senior Oracle DBA<BR>&gt; Oracle Certified 8i = DBA<BR>&gt;=20

    <BR>&gt; -----Original Message-----<BR>&gt; Sent: Wednesday, January = 07,=20

    2004 1:09 AM<BR>&gt; To: Multiple recipients of list = ORACLE-L<BR>&gt;=20

    <BR>&gt; <BR>&gt; You can find out by employing&nbsp; the event = 10053, lev=20

    8.&nbsp; Looking from afar, however, it seems more<BR>&gt; likely = that you=20

    haven't configured your CBO properly. Here is something you can = try:<BR>&gt;=20

    <BR>&gt; Execute the following commands:<BR>&gt; <BR>&gt; alter = session set=20

    optimizer_index_caching=3D40;&nbsp; <BR>&gt; alter session set=20     optimizer_index_cost_adj=3D25;<BR>&gt; <BR>&gt; <BR>&gt; <BR>&gt; = After=20

    that,&nbsp; retry the query. If I'm correct, optimizer will now know = that=20

    index I/O is much cheaper<BR>&gt; then the table one and will be = much more=20

    likely to select full index scan over the full table scan.<BR>&gt; = When=20

    you're really, really bored, you can read Practical Oracle 8i - = Building=20

    Efficient Databases,<BR>&gt; it has a few pages about the parameters =

    above.&nbsp; Read the Gospel of Jonathan and enjoy.<BR>&gt; <BR>&gt; =

    <BR>&gt; <BR>&gt; On 2004.01.07 00:29, Denham Eva wrote:<BR>&gt; = &gt; Hello=20

    Listers,<BR>&gt; &gt; <BR>&gt; &gt; A normal sql query from a data = warehouse=20

    tool called Sagent. <BR>&gt; &gt; SELECT COL1, COL2, COL3<BR>&gt; = &gt; FROM=20

    TABLE<BR>&gt; &gt; ORDER BY 3;<BR>&gt; &gt; <BR>&gt; &gt; The table = has=20

    approximately 2 mil records.<BR>&gt; &gt; table has 22 = indexes.<BR>&gt; &gt;=20

    <BR>&gt; &gt; The database is set up optimizer CHOOSE.<BR>&gt; &gt; = I run=20

    DBMS_Stats.Gather_Schema_Stats('SchemaName') regularly.<BR>&gt; &gt; = OS is=20

    Win2k<BR>&gt; &gt; ORACLE 81741<BR>&gt; &gt; <BR>&gt; &gt; OK, when = doing a=20

    explain plan on the above sql, I get the following...<BR>&gt; &gt; = SELECT=20
    STATEMENT Optimizer Mode=3DCHOOSE<BR>&gt; =

&gt;&nbsp;&nbsp;&nbsp;&nbsp; SORT=20
    ORDER BY<BR>&gt; =
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20

    TABLE ACCESS=20
    =
FULL&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb= sp;&nbsp;=20

    TABLENAME&nbsp;&nbsp; -- Very slow and takes<BR>&gt; &gt; = hours!<BR>&gt;=20

    &gt; <BR>&gt; &gt; When adding the hint /*+RULE*/ for example I = get<BR>&gt;=20

    &gt; SELECT STATEMENT Optimizer Mode=3DHint:RULE<BR>&gt;=20     &gt;&nbsp;&nbsp;&nbsp; TABLE ACCESS BY INDEX=20     =
ROWID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n= bsp;&nbsp;=20

    TABLENAME<BR>&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = INDEX FULL=20
    =
SCAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb= sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20

    TABLE_INDEX&nbsp; --<BR>&gt; &gt; Much faster!!!<BR>&gt; &gt; = <BR>&gt; &gt;=20

    Have I given enough info that anyone can explain why the CHOOSE mode =

    insists<BR>&gt; &gt; on doing a TABLE ACCESS FULL?<BR>&gt; &gt; Is = there=20

    anything I can do to improve performance? Please remember that = this<BR>&gt;=20

    &gt; query comes from a Data Warehouse tool and hence does not = appear to=20

    accept<BR>&gt; &gt; hints.<BR>&gt; &gt; <BR>&gt; &gt; Any help will = be much=20

    appreciated!<BR>&gt; &gt; Denham<BR>&gt; &gt;&nbsp; <BR>&gt; &gt; -- =

    <BR>&gt; &gt; Please see the official ORACLE-L FAQ: </FONT><A=20     href=3D"http://www.orafaq.net"><FONT face=3DArial=20     size=3D2>http://www.orafaq.net</FONT></A><BR><FONT face=3DArial = size=3D2>&gt; &gt;=20

    </FONT><A href=3D"http://www.fatcity.com"><FONT face=3DArial=20     size=3D2>http://www.fatcity.com</FONT></A><BR><FONT face=3DArial = size=3D2>&gt; San=20

    Diego, California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- = Mailing list=20

    and web hosting services<BR>&gt;=20
    =

---------------------------------------------------------------------<BR>=
&gt;=20

    To REMOVE yourself from this mailing list, send an E-Mail = message<BR>&gt;=20

    to: </FONT><A href=3D"mailto:ListGuru_at_fatcity.com"><FONT = face=3DArial=20

    size=3D2>ListGuru_at_fatcity.com</FONT></A><FONT face=3DArial size=3D2> = (note EXACT=20

    spelling of 'ListGuru') and in<BR>&gt; the message BODY, include a = line=20

    containing: UNSUB ORACLE-L<BR>&gt; (or the name of mailing list you = want to=20

    be removed from).&nbsp; You may<BR>&gt; also send the HELP command = for other=20

    information (like subscribing).<BR>&gt; -- <BR>&gt; Please see the = official=20

    ORACLE-L FAQ: </FONT><A href=3D"http://www.orafaq.net"><FONT = face=3DArial=20

    size=3D2>http://www.orafaq.net</FONT></A><BR><FONT face=3DArial = size=3D2>&gt; --=20

    <BR>&gt; Author: Goulet, Dick<BR>&gt; &nbsp; INET: </FONT><A=20     href=3D"mailto:DGoulet_at_vicr.com"><FONT face=3DArial=20     size=3D2>DGoulet_at_vicr.com</FONT></A><BR><FONT face=3DArial = size=3D2>&gt; <BR>&gt;=20

    Fat City Network Services&nbsp;&nbsp;&nbsp; -- 858-538-5051 = </FONT><A=20

    href=3D"http://www.fatcity.com"><FONT face=3DArial=20     size=3D2>http://www.fatcity.com</FONT></A><BR><FONT face=3DArial = size=3D2>&gt; San=20

    Diego, California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- = Mailing list=20

    and web hosting services<BR>&gt;=20
    =

---------------------------------------------------------------------<BR>=
&gt;=20

    To REMOVE yourself from this mailing list, send an E-Mail = message<BR>&gt;=20

    to: </FONT><A href=3D"mailto:ListGuru_at_fatcity.com"><FONT = face=3DArial=20

    size=3D2>ListGuru_at_fatcity.com</FONT></A><FONT face=3DArial size=3D2> = (note EXACT=20

    spelling of 'ListGuru') and in<BR>&gt; the message BODY, include a = line=20

    containing: UNSUB ORACLE-L<BR>&gt; (or the name of mailing list you = want to=20

    be removed from).&nbsp; You may<BR>&gt; also send the HELP command = for other=20

    information (like subscribing).<BR>&gt;=20 </FONT></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_2405_01C3D5EF.2714A3F0--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Jan 08 2004 - 05:54:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US