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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).