Re: Problem with understanding Optimization methods.

Yes.
On 2004.01.08 01:14, Denham Eva wrote:
> Mladen
>
> Thank You for this suggestion, works in that the CBO now see it this way....
>
> SELECT STATEMENT Optimizer Mode=CHOOSE
> TABLE ACCESS BY INDEX ROWID TABLENAME
> INDEX FULL SCAN TABLENAME_NDX
>
> Can these parameters be set in the init.ora?
>
> Many Thanks Once Again!
> Denham
>
> -----Original Message-----
> Sent: Wednesday, January 07, 2004 8:09 AM
> To: Multiple recipients of list ORACLE-L
>
>
> 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:
>
> Execute the following commands:
>
> alter session set optimizer_index_caching=40;
> alter session set optimizer_index_cost_adj=25;
>
>
>
> 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.
>
>
>
> On 2004.01.07 00:29, Denham Eva wrote:
> > Hello Listers,
> >
> > A normal sql query from a data warehouse tool called Sagent.
> > SELECT COL1, COL2, COL3
> > FROM TABLE
> > ORDER BY 3;
> >
> > The table has approximately 2 mil records.
> > table has 22 indexes.
> >
> > The database is set up optimizer CHOOSE.
> > I run DBMS_Stats.Gather_Schema_Stats('SchemaName') regularly.
> > OS is Win2k
> > ORACLE 81741
> >
> > OK, when doing a explain plan on the above sql, I get the following...
> > SELECT STATEMENT Optimizer Mode=CHOOSE
> > SORT ORDER BY
> > TABLE ACCESS FULL TABLENAME -- Very slow and takes
> > hours!
> >
> > When adding the hint /*+RULE*/ for example I get
> > SELECT STATEMENT Optimizer Mode=Hint:RULE
> > TABLE ACCESS BY INDEX ROWID TABLENAME
> > INDEX FULL SCAN TABLE_INDEX --
> > Much faster!!!
> >
> > 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.
> >
> > Any help will be much appreciated!
> > Denham
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Denham Eva
> > INET: EVAD_at_TFMC.co.za
> >
> > 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).
> >
>
> --
> Mladen Gogala
> Oracle DBA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
> INET: mgogala_at_adelphia.net
>
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Denham Eva
> INET: EVAD_at_TFMC.co.za
>
> 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).
>

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

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).