Re: CBO not using the index even though it is faster when hinted

set optimizer_index_cost_adj to value that is less than (cost without hint * 100 / cost with hint).

Waleed

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, January 24, 2003 7:34 PM

> Lower the settings for optimizer_index_cost_adj.
>
> set optimizer_index_cost_adj to value that is less than (cost without hint
/
> cost with hint).
>
> This should help.
>
>
> Regards,
>
> Waleed
>
> -----Original Message-----
> Sent: Friday, January 24, 2003 3:39 PM
> To: Multiple recipients of list ORACLE-L
> hinted
>
>
> All the columns in the table are not null ...
>
> here is an interesting test I ran ...
>
> several Direct_Path_Write and Read waits with large elasped times
> are showing up in the 10046 trace data on this query due to the
> full table scan.
>
> Just deleted statistics on this and fwiw ...
>
> With Statistics ....
> =====================
>
> SELECT Statement Optimizer=Choose (Cost=225 ...)
> SORT (Order By) (Cost=225 ...)
> Table Access (Full) of 'table1'
>
> Physical Reads = 433
>
> Without Statistics ..
> ======================
>
> SELECT Statement Optimizer=Choose
> Table Access (By Index Row) of 'table1
> Index ( Full Scan ) of 'concatenated index' (Non-Unique)
>
> Physical Reads = 0
>
> Mike
>
> -----Original Message-----
> Sent: Thursday, January 23, 2003 6:29 PM
> To: Multiple recipients of list ORACLE-L
> ....
>
>
> Anyone have any problems with the CBO not using
> a index when you know it is faster by forcing a
> hint ?
>
> I have set the following ...
>
> Solaris
> Oracle Version 8.1.7.4
>
> block size = 8
> DB_FILE_MULTIBLOCK_READ_COUNT = 8
> mode = Choose
>
> also using Tim Gormans 90 and 50 values for the other optimizer parms.
>
> Select col1, col2, col3, blah1, blah2 from table order by col1, col2,
col3;
>
>
> Concatenate index on col1, col2, col3.
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Johnson, Michael
> INET: Michael.Johnson_at_oln-afmc.af.mil
>
> 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: Johnson, Michael
> INET: Michael.Johnson_at_oln-afmc.af.mil
>
> 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: Khedr, Waleed
> INET: Waleed.Khedr_at_FMR.COM
>
> 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: Waleed Khedr
  INET: wkhedr_at_attbi.com

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