RE: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING
Does Implementing this Parameter Need the Indexes to be in ANALYZED State ? -Original Message- From: Gaja Krishna Vaidyanatha [SMTP:[EMAIL PROTECTED]] Sent: Thursday, June 07, 2001 11:31 AM To: Multiple recipients of list ORACLE-L Subject: Re: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING Dear Oracle-L Listers, After 7 months of hibernation, it feels good to be back. Thanks to many of you for your continued support and encouragement over the past months. Some of the interesting threads were forwarded to me in the past few months, just so that I don't miss out on the fun. Talk about good timing and a fun topic, this post was one of the first messages to pop into my Inbox, after I rejoined the list today. Although, I don't have the whole thread, here are some comments to the previous posts. --- Greg Moore [EMAIL PROTECTED] wrote: My goal was to strongly favor indexes and make CBO behave like RBO. Mladen, I would have considered setting optimizer_goal=first_rows and would appreciate hearing your ideas on why you decided to set OPTIMIZER_INDEX_COST_ADJ to 30 instead. Thank you. - Greg Sorry if this is a duplicate ... the mail server bounced the original. -- I am not so sure that having the CBO work like RBO is always the best method across the board for all applications. Depending on the nature of your applications, it can produce mixed results. If the application is transactional in nature, then index scans will be a more preferred method versus applications that are more data mart in nature, where full table scans or fast-full index scans may be preferred. In today's world, there are very few true OLTP systems, most of them act like OLTP during the day, and like DataMarts during the batch window. The primary rationale behind not overdoing the use of indexes is that indexes are not always optimal for a SQL's execution plan. The yardstick you need to use is how many block visitations does my SQL have to perform with indexes versus without indexes?. If the block visitations are lower for a full table scan, then that is the better plan. Setting OPTIMIZER_MODE to FIRST_ROWS does twist the optimizer's arm to use indexes over a full table scan and this may wreak havoc during your application's batch window. OPTIMIZER_INDEX_COST_ADJ directly adjusts the cost of using an index. The default value of 100 makes the optimizer evaluate the cost of the index as normal, and a value of 50 makes the optimizer evaluate the cost to be half as expensive as normal. This parameter encourages the use of all indexes, regardless of their selectivity. It applies to index use in general. While OPTMIZER_MODE=FIRST_ROWS, will almost always force the use of an index, the parameter OPTIMIZER_INDEX_COST_ADJ encourages the optimizer to use indexes. When set to a value of 30, you are telling the optimizer, that the use of indexes is actually approximately 1/3rd of the normal cost. I'd rather use the latter over the former, as in my experience with ERP applications, I have found the latter is less forceful than the former, especially during the batch window. Hope that helps, Gaja = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING
PMFJI. If you set OPTIMIZER_GOAL=FIRST_ROWS for the entire database you'll have a *big* problem in your hands with most of the dictionary views, including catexp, becoming exceedingly slow. It's better (if you can) to stay with CHOOSE and analyze just what you need. Then you fiddle with OPTIMIZER_COST_ADJ and OPTIMIZER_INDEX_CACHE to get the right balance of index vs table scan. Works for me. Except I had to stay with FIRST_ROWS like it or not (due to a bug) and it hasn't been good for dict views at all... Cheers Nuno Souto [EMAIL PROTECTED] http://www.users.bigpond.net.au/the_Den - Original Message - I would have considered setting optimizer_goal=first_rows and would appreciate hearing your ideas on why you decided to set OPTIMIZER_INDEX_COST_ADJ to 30 instead. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING
The reason is that the 'FIRST_ROWS' parameter didn't give the expected results. We wanted index access strongly favored and setting OPTIMIZER_INDEX_COST_ADJ to 30 (we tried with 75 and 50, too) gave us the plans that we were expecting. The problem with cost based optimizer are the bind variables, when you're doing a range scan (COL1 between :a and :b). CBO assumes, even with the histograms that you're searching through the 25% of the table and generates a full scan. OPTIMIZER_INDEX_COST_ADJ set to 30 made all of our PRO*C and Oracle*Forms modules behave as expected. To tell the truth, I do not have any conclusions that I can generalize, because my task was only to make sure that we do not suffer a huge performance hit when migrating to Oracle8i (from 7.3.4.4). There is also another way of doing things, namely, setting CURSOR_SHARING to FORCE, in which case all of the bind variables will be forcibly replaced by constants. This, I'm told, does not sit very well with Oracle's own CASE products. I still have CURSOR_SHARING set to EXACT, which is the default. -Original Message- Sent: Wednesday, June 06, 2001 9:36 PM To: Multiple recipients of list ORACLE-L My goal was to strongly favor indexes and make CBO behave like RBO. Mladen, I would have considered setting optimizer_goal=first_rows and would appreciate hearing your ideas on why you decided to set OPTIMIZER_INDEX_COST_ADJ to 30 instead. Thank you. - Greg Sorry if this is a duplicate ... the mail server bounced the original. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING
About CURSOR_SHARING - EXACT means that SQL statements are not changed, and FORCE means that constants are changed to bind variables. Alex Hillman -Original Message- Sent: Thursday, June 07, 2001 11:52 AM To: Multiple recipients of list ORACLE-L The reason is that the 'FIRST_ROWS' parameter didn't give the expected results. We wanted index access strongly favored and setting OPTIMIZER_INDEX_COST_ADJ to 30 (we tried with 75 and 50, too) gave us the plans that we were expecting. The problem with cost based optimizer are the bind variables, when you're doing a range scan (COL1 between :a and :b). CBO assumes, even with the histograms that you're searching through the 25% of the table and generates a full scan. OPTIMIZER_INDEX_COST_ADJ set to 30 made all of our PRO*C and Oracle*Forms modules behave as expected. To tell the truth, I do not have any conclusions that I can generalize, because my task was only to make sure that we do not suffer a huge performance hit when migrating to Oracle8i (from 7.3.4.4). There is also another way of doing things, namely, setting CURSOR_SHARING to FORCE, in which case all of the bind variables will be forcibly replaced by constants. This, I'm told, does not sit very well with Oracle's own CASE products. I still have CURSOR_SHARING set to EXACT, which is the default. -Original Message- Sent: Wednesday, June 06, 2001 9:36 PM To: Multiple recipients of list ORACLE-L My goal was to strongly favor indexes and make CBO behave like RBO. Mladen, I would have considered setting optimizer_goal=first_rows and would appreciate hearing your ideas on why you decided to set OPTIMIZER_INDEX_COST_ADJ to 30 instead. Thank you. - Greg Sorry if this is a duplicate ... the mail server bounced the original. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING
I have heard alot of people complain that CURSOR_SHARING force causes 600 errors, I haven't seen it but I stay away from that setting. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/7/01 1:06 PM I have cursor_sharing set to FORCE (but we don't run Case) we found that the programmers had coded statements with constants and we were hitting shared pool problems. So I set it to FORCE and suddenly things are nice and quiet It does sometimes change queries that are non-repeating (which causes a slight performance hit on those) but since those are ad-hoc queries as opposed to the ones that run most of the time, I can live with a (barely noticeable) performance hit. Rachel From: Gogala, Mladen [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING Date: Thu, 07 Jun 2001 07:51:31 -0800 The reason is that the 'FIRST_ROWS' parameter didn't give the expected results. We wanted index access strongly favored and setting OPTIMIZER_INDEX_COST_ADJ to 30 (we tried with 75 and 50, too) gave us the plans that we were expecting. The problem with cost based optimizer are the bind variables, when you're doing a range scan (COL1 between :a and :b). CBO assumes, even with the histograms that you're searching through the 25% of the table and generates a full scan. OPTIMIZER_INDEX_COST_ADJ set to 30 made all of our PRO*C and Oracle*Forms modules behave as expected. To tell the truth, I do not have any conclusions that I can generalize, because my task was only to make sure that we do not suffer a huge performance hit when migrating to Oracle8i (from 7.3.4.4). There is also another way of doing things, namely, setting CURSOR_SHARING to FORCE, in which case all of the bind variables will be forcibly replaced by constants. This, I'm told, does not sit very well with Oracle's own CASE products. I still have CURSOR_SHARING set to EXACT, which is the default. -Original Message- Sent: Wednesday, June 06, 2001 9:36 PM To: Multiple recipients of list ORACLE-L My goal was to strongly favor indexes and make CBO behave like RBO. Mladen, I would have considered setting optimizer_goal=first_rows and would appreciate hearing your ideas on why you decided to set OPTIMIZER_INDEX_COST_ADJ to 30 instead. Thank you. - Greg Sorry if this is a duplicate ... the mail server bounced the original. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT
RE: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING
Vivek, My guess is probably yes, but I should be honest that I really have not worked with an environment with these parameters turned on and not having statistics on the objects (both tables and indexes), as if I recall correctly, these parameters came into being in 8.0.4/8.0.5. I have used the CBO in every system that I have worked on since version 7.3.4 and have had no need to use the RBO in the past 4 years. It may be an interesting test though. Cheers, Gaja --- VIVEK_SHARMA [EMAIL PROTECTED] wrote: Does Implementing this Parameter Need the Indexes to be in ANALYZED State ? -Original Message- From: Gaja Krishna Vaidyanatha [SMTP:[EMAIL PROTECTED]] Sent: Thursday, June 07, 2001 11:31 AM To: Multiple recipients of list ORACLE-L Subject:Re: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING Dear Oracle-L Listers, After 7 months of hibernation, it feels good to be back. Thanks to many of you for your continued support and encouragement over the past months. Some of the interesting threads were forwarded to me in the past few months, just so that I don't miss out on the fun. Talk about good timing and a fun topic, this post was one of the first messages to pop into my Inbox, after I rejoined the list today. Although, I don't have the whole thread, here are some comments to the previous posts. --- Greg Moore [EMAIL PROTECTED] wrote: My goal was to strongly favor indexes and make CBO behave like RBO. Mladen, I would have considered setting optimizer_goal=first_rows and would appreciate hearing your ideas on why you decided to set OPTIMIZER_INDEX_COST_ADJ to 30 instead. Thank you. - Greg Sorry if this is a duplicate ... the mail server bounced the original. -- I am not so sure that having the CBO work like RBO is always the best method across the board for all applications. Depending on the nature of your applications, it can produce mixed results. If the application is transactional in nature, then index scans will be a more preferred method versus applications that are more data mart in nature, where full table scans or fast-full index scans may be preferred. In today's world, there are very few true OLTP systems, most of them act like OLTP during the day, and like DataMarts during the batch window. The primary rationale behind not overdoing the use of indexes is that indexes are not always optimal for a SQL's execution plan. The yardstick you need to use is how many block visitations does my SQL have to perform with indexes versus without indexes?. If the block visitations are lower for a full table scan, then that is the better plan. Setting OPTIMIZER_MODE to FIRST_ROWS does twist the optimizer's arm to use indexes over a full table scan and this may wreak havoc during your application's batch window. OPTIMIZER_INDEX_COST_ADJ directly adjusts the cost of using an index. The default value of 100 makes the optimizer evaluate the cost of the index as normal, and a value of 50 makes the optimizer evaluate the cost to be half as expensive as normal. This parameter encourages the use of all indexes, regardless of their selectivity. It applies to index use in general. While OPTMIZER_MODE=FIRST_ROWS, will almost always force the use of an index, the parameter OPTIMIZER_INDEX_COST_ADJ encourages the optimizer to use indexes. When set to a value of 30, you are telling the optimizer, that the use of indexes is actually approximately 1/3rd of the normal cost. I'd rather use the latter over the former, as in my experience with ERP applications, I have found the latter is less forceful than the former, especially during the batch window. Hope that helps, Gaja = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author
RE: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING
we hit a ora-600 related to cursor sharing=force and wws advised us an unusual fix -- setting sort area = sort retained .. funny thing is that this fix solved our problem .. Deepak -Original Message- Sent: Thursday, June 07, 2001 11:20 AM To: Multiple recipients of list ORACLE-L I have heard alot of people complain that CURSOR_SHARING force causes 600 errors, I haven't seen it but I stay away from that setting. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/7/01 1:06 PM I have cursor_sharing set to FORCE (but we don't run Case) we found that the programmers had coded statements with constants and we were hitting shared pool problems. So I set it to FORCE and suddenly things are nice and quiet It does sometimes change queries that are non-repeating (which causes a slight performance hit on those) but since those are ad-hoc queries as opposed to the ones that run most of the time, I can live with a (barely noticeable) performance hit. Rachel From: Gogala, Mladen [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING Date: Thu, 07 Jun 2001 07:51:31 -0800 The reason is that the 'FIRST_ROWS' parameter didn't give the expected results. We wanted index access strongly favored and setting OPTIMIZER_INDEX_COST_ADJ to 30 (we tried with 75 and 50, too) gave us the plans that we were expecting. The problem with cost based optimizer are the bind variables, when you're doing a range scan (COL1 between :a and :b). CBO assumes, even with the histograms that you're searching through the 25% of the table and generates a full scan. OPTIMIZER_INDEX_COST_ADJ set to 30 made all of our PRO*C and Oracle*Forms modules behave as expected. To tell the truth, I do not have any conclusions that I can generalize, because my task was only to make sure that we do not suffer a huge performance hit when migrating to Oracle8i (from 7.3.4.4). There is also another way of doing things, namely, setting CURSOR_SHARING to FORCE, in which case all of the bind variables will be forcibly replaced by constants. This, I'm told, does not sit very well with Oracle's own CASE products. I still have CURSOR_SHARING set to EXACT, which is the default. -Original Message- Sent: Wednesday, June 06, 2001 9:36 PM To: Multiple recipients of list ORACLE-L My goal was to strongly favor indexes and make CBO behave like RBO. Mladen, I would have considered setting optimizer_goal=first_rows and would appreciate hearing your ideas on why you decided to set OPTIMIZER_INDEX_COST_ADJ to 30 instead. Thank you. - Greg Sorry if this is a duplicate ... the mail server bounced the original. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network
RE: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING
I haven't heard any such complaints yet, but if anybody has them, I'd me more then glad to hear about them -Original Message- Sent: Thursday, June 07, 2001 2:20 PM To: Multiple recipients of list ORACLE-L I have heard alot of people complain that CURSOR_SHARING force causes 600 errors, I haven't seen it but I stay away from that setting. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/7/01 1:06 PM I have cursor_sharing set to FORCE (but we don't run Case) we found that the programmers had coded statements with constants and we were hitting shared pool problems. So I set it to FORCE and suddenly things are nice and quiet It does sometimes change queries that are non-repeating (which causes a slight performance hit on those) but since those are ad-hoc queries as opposed to the ones that run most of the time, I can live with a (barely noticeable) performance hit. Rachel From: Gogala, Mladen [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING Date: Thu, 07 Jun 2001 07:51:31 -0800 The reason is that the 'FIRST_ROWS' parameter didn't give the expected results. We wanted index access strongly favored and setting OPTIMIZER_INDEX_COST_ADJ to 30 (we tried with 75 and 50, too) gave us the plans that we were expecting. The problem with cost based optimizer are the bind variables, when you're doing a range scan (COL1 between :a and :b). CBO assumes, even with the histograms that you're searching through the 25% of the table and generates a full scan. OPTIMIZER_INDEX_COST_ADJ set to 30 made all of our PRO*C and Oracle*Forms modules behave as expected. To tell the truth, I do not have any conclusions that I can generalize, because my task was only to make sure that we do not suffer a huge performance hit when migrating to Oracle8i (from 7.3.4.4). There is also another way of doing things, namely, setting CURSOR_SHARING to FORCE, in which case all of the bind variables will be forcibly replaced by constants. This, I'm told, does not sit very well with Oracle's own CASE products. I still have CURSOR_SHARING set to EXACT, which is the default. -Original Message- Sent: Wednesday, June 06, 2001 9:36 PM To: Multiple recipients of list ORACLE-L My goal was to strongly favor indexes and make CBO behave like RBO. Mladen, I would have considered setting optimizer_goal=first_rows and would appreciate hearing your ideas on why you decided to set OPTIMIZER_INDEX_COST_ADJ to 30 instead. Thank you. - Greg Sorry if this is a duplicate ... the mail server bounced the original. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California
RE: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING
Which version of the database and which platform? -Original Message- Sent: Thursday, June 07, 2001 3:47 PM To: Multiple recipients of list ORACLE-L we hit a ora-600 related to cursor sharing=force and wws advised us an unusual fix -- setting sort area = sort retained .. funny thing is that this fix solved our problem .. Deepak -Original Message- Sent: Thursday, June 07, 2001 11:20 AM To: Multiple recipients of list ORACLE-L I have heard alot of people complain that CURSOR_SHARING force causes 600 errors, I haven't seen it but I stay away from that setting. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/7/01 1:06 PM I have cursor_sharing set to FORCE (but we don't run Case) we found that the programmers had coded statements with constants and we were hitting shared pool problems. So I set it to FORCE and suddenly things are nice and quiet It does sometimes change queries that are non-repeating (which causes a slight performance hit on those) but since those are ad-hoc queries as opposed to the ones that run most of the time, I can live with a (barely noticeable) performance hit. Rachel From: Gogala, Mladen [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING Date: Thu, 07 Jun 2001 07:51:31 -0800 The reason is that the 'FIRST_ROWS' parameter didn't give the expected results. We wanted index access strongly favored and setting OPTIMIZER_INDEX_COST_ADJ to 30 (we tried with 75 and 50, too) gave us the plans that we were expecting. The problem with cost based optimizer are the bind variables, when you're doing a range scan (COL1 between :a and :b). CBO assumes, even with the histograms that you're searching through the 25% of the table and generates a full scan. OPTIMIZER_INDEX_COST_ADJ set to 30 made all of our PRO*C and Oracle*Forms modules behave as expected. To tell the truth, I do not have any conclusions that I can generalize, because my task was only to make sure that we do not suffer a huge performance hit when migrating to Oracle8i (from 7.3.4.4). There is also another way of doing things, namely, setting CURSOR_SHARING to FORCE, in which case all of the bind variables will be forcibly replaced by constants. This, I'm told, does not sit very well with Oracle's own CASE products. I still have CURSOR_SHARING set to EXACT, which is the default. -Original Message- Sent: Wednesday, June 06, 2001 9:36 PM To: Multiple recipients of list ORACLE-L My goal was to strongly favor indexes and make CBO behave like RBO. Mladen, I would have considered setting optimizer_goal=first_rows and would appreciate hearing your ideas on why you decided to set OPTIMIZER_INDEX_COST_ADJ to 30 instead. Thank you. - Greg Sorry if this is a duplicate ... the mail server bounced the original. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
RE: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING
this is 816 running on solaris 2.6 .. -Original Message- Sent: Thursday, June 07, 2001 5:14 PM To: Multiple recipients of list ORACLE-L Which version of the database and which platform? -Original Message- Sent: Thursday, June 07, 2001 3:47 PM To: Multiple recipients of list ORACLE-L we hit a ora-600 related to cursor sharing=force and wws advised us an unusual fix -- setting sort area = sort retained .. funny thing is that this fix solved our problem .. Deepak -Original Message- Sent: Thursday, June 07, 2001 11:20 AM To: Multiple recipients of list ORACLE-L I have heard alot of people complain that CURSOR_SHARING force causes 600 errors, I haven't seen it but I stay away from that setting. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/7/01 1:06 PM I have cursor_sharing set to FORCE (but we don't run Case) we found that the programmers had coded statements with constants and we were hitting shared pool problems. So I set it to FORCE and suddenly things are nice and quiet It does sometimes change queries that are non-repeating (which causes a slight performance hit on those) but since those are ad-hoc queries as opposed to the ones that run most of the time, I can live with a (barely noticeable) performance hit. Rachel From: Gogala, Mladen [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING Date: Thu, 07 Jun 2001 07:51:31 -0800 The reason is that the 'FIRST_ROWS' parameter didn't give the expected results. We wanted index access strongly favored and setting OPTIMIZER_INDEX_COST_ADJ to 30 (we tried with 75 and 50, too) gave us the plans that we were expecting. The problem with cost based optimizer are the bind variables, when you're doing a range scan (COL1 between :a and :b). CBO assumes, even with the histograms that you're searching through the 25% of the table and generates a full scan. OPTIMIZER_INDEX_COST_ADJ set to 30 made all of our PRO*C and Oracle*Forms modules behave as expected. To tell the truth, I do not have any conclusions that I can generalize, because my task was only to make sure that we do not suffer a huge performance hit when migrating to Oracle8i (from 7.3.4.4). There is also another way of doing things, namely, setting CURSOR_SHARING to FORCE, in which case all of the bind variables will be forcibly replaced by constants. This, I'm told, does not sit very well with Oracle's own CASE products. I still have CURSOR_SHARING set to EXACT, which is the default. -Original Message- Sent: Wednesday, June 06, 2001 9:36 PM To: Multiple recipients of list ORACLE-L My goal was to strongly favor indexes and make CBO behave like RBO. Mladen, I would have considered setting optimizer_goal=first_rows and would appreciate hearing your ideas on why you decided to set OPTIMIZER_INDEX_COST_ADJ to 30 instead. Thank you. - Greg Sorry if this is a duplicate ... the mail server bounced the original. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
RE: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING
There are certain bugs filed for this - specifically for interMedia. We did have problem while we used 'score' function in ConText query. We had no option but to revert back to CURSOR_SHARING=EXACT. - Praful --- Gogala, Mladen [EMAIL PROTECTED] wrote: I haven't heard any such complaints yet, but if anybody has them, I'd me more then glad to hear about them -Original Message- Sent: Thursday, June 07, 2001 2:20 PM To: Multiple recipients of list ORACLE-L I have heard alot of people complain that CURSOR_SHARING force causes 600 errors, I haven't seen it but I stay away from that setting. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/7/01 1:06 PM I have cursor_sharing set to FORCE (but we don't run Case) we found that the programmers had coded statements with constants and we were hitting shared pool problems. So I set it to FORCE and suddenly things are nice and quiet It does sometimes change queries that are non-repeating (which causes a slight performance hit on those) but since those are ad-hoc queries as opposed to the ones that run most of the time, I can live with a (barely noticeable) performance hit. Rachel From: Gogala, Mladen [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING Date: Thu, 07 Jun 2001 07:51:31 -0800 The reason is that the 'FIRST_ROWS' parameter didn't give the expected results. We wanted index access strongly favored and setting OPTIMIZER_INDEX_COST_ADJ to 30 (we tried with 75 and 50, too) gave us the plans that we were expecting. The problem with cost based optimizer are the bind variables, when you're doing a range scan (COL1 between :a and :b). CBO assumes, even with the histograms that you're searching through the 25% of the table and generates a full scan. OPTIMIZER_INDEX_COST_ADJ set to 30 made all of our PRO*C and Oracle*Forms modules behave as expected. To tell the truth, I do not have any conclusions that I can generalize, because my task was only to make sure that we do not suffer a huge performance hit when migrating to Oracle8i (from 7.3.4.4). There is also another way of doing things, namely, setting CURSOR_SHARING to FORCE, in which case all of the bind variables will be forcibly replaced by constants. This, I'm told, does not sit very well with Oracle's own CASE products. I still have CURSOR_SHARING set to EXACT, which is the default. -Original Message- Sent: Wednesday, June 06, 2001 9:36 PM To: Multiple recipients of list ORACLE-L My goal was to strongly favor indexes and make CBO behave like RBO. Mladen, I would have considered setting optimizer_goal=first_rows and would appreciate hearing your ideas on why you decided to set OPTIMIZER_INDEX_COST_ADJ to 30 instead. Thank you. - Greg Sorry if this is a duplicate ... the mail server bounced the original. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling
Re: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING
Given that OPTIMIZER_INDEX_CACHING is presumably meant to the indicative of how much of an index is cached, then setting it to your buffer hit ratio would seem like a reasonable starting point. Cheers Connor --- [EMAIL PROTECTED] wrote: Oracle 8.0.5.2.1 -- yeah, I know I know... we are going to 8.1.7 eventually AIX 4.3.3 Hey allLooking at our init parameters today after reading Tim Gorman's White Paper The Search for Intelligent Life in the Cost Based Optimizer and these 2 stood out like a sore thumb. Both are at the default values: OPTIMIZER_INDEX_COST_ADJ = 100 OPTIMIZER_INDEX_CACHING = 0 We have an OLTP and a DSS database and I am looking at changing the OPTIMIZER_INDEX_CACHING = 90 for both and setting the OPTIMIZER_INDEX_COST_ADJ to a reasonable value based on db sequential reads vs. db scattered reads as a guideline any suggestions/recommendations/words of warning/flogging?There is some scattered info on this on Metalink but I'm looking for some real-world improvements or headaches. Thanks to all in advance and hopefully I won't have to type HELP so someone can ask me if I'm an idiot :-) John D. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING
I was fiddling with those parameters but on an OLTP configuration. My goal was to strongly favor indexes and make CBO behave like RBO. I succeeded in doing so, mainly by setting OPTIMIZER_INDEX_COST_ADJ to 30 (making a cost of index read 30% of the ost of a table read). -Original Message- Sent: Wednesday, June 06, 2001 9:01 AM To: Multiple recipients of list ORACLE-L I was reading that paper yesterday as well. We have a 120 Gig data warehouse that uses partitions and a large fact table with many smaller dimension tables ( a modified star schema design). This database is currently on version 8.0.4 but will soon be 8.1.7 on Sun Solaris 2.6. Can anyone tell me if they have a similar design that has benefitted from changing these two parameters? Thanks in advance, Cherie Machler Oracle DBA Gelco Information Network John.Dailey@ing- fsi-na.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by:cc: [EMAIL PROTECTED]Subject: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING 06/05/01 04:30 PM Please respond to ORACLE-L Oracle 8.0.5.2.1 -- yeah, I know I know... we are going to 8.1.7 eventually AIX 4.3.3 Hey allLooking at our init parameters today after reading Tim Gorman's White Paper The Search for Intelligent Life in the Cost Based Optimizer and these 2 stood out like a sore thumb. Both are at the default values: OPTIMIZER_INDEX_COST_ADJ = 100 OPTIMIZER_INDEX_CACHING = 0 We have an OLTP and a DSS database and I am looking at changing the OPTIMIZER_INDEX_CACHING = 90 for both and setting the OPTIMIZER_INDEX_COST_ADJ to a reasonable value based on db sequential reads vs. db scattered reads as a guideline any suggestions/recommendations/words of warning/flogging?There is some scattered info on this on Metalink but I'm looking for some real-world improvements or headaches. Thanks to all in advance and hopefully I won't have to type HELP so someone can ask me if I'm an idiot :-) John D. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING
My goal was to strongly favor indexes and make CBO behave like RBO. Mladen, I would have considered setting optimizer_goal=first_rows and would appreciate hearing your ideas on why you decided to set OPTIMIZER_INDEX_COST_ADJ to 30 instead. Thank you. - Greg Sorry if this is a duplicate ... the mail server bounced the original. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING
Dear Oracle-L Listers, After 7 months of hibernation, it feels good to be back. Thanks to many of you for your continued support and encouragement over the past months. Some of the interesting threads were forwarded to me in the past few months, just so that I don't miss out on the fun. Talk about good timing and a fun topic, this post was one of the first messages to pop into my Inbox, after I rejoined the list today. Although, I don't have the whole thread, here are some comments to the previous posts. --- Greg Moore [EMAIL PROTECTED] wrote: My goal was to strongly favor indexes and make CBO behave like RBO. Mladen, I would have considered setting optimizer_goal=first_rows and would appreciate hearing your ideas on why you decided to set OPTIMIZER_INDEX_COST_ADJ to 30 instead. Thank you. - Greg Sorry if this is a duplicate ... the mail server bounced the original. -- I am not so sure that having the CBO work like RBO is always the best method across the board for all applications. Depending on the nature of your applications, it can produce mixed results. If the application is transactional in nature, then index scans will be a more preferred method versus applications that are more data mart in nature, where full table scans or fast-full index scans may be preferred. In today's world, there are very few true OLTP systems, most of them act like OLTP during the day, and like DataMarts during the batch window. The primary rationale behind not overdoing the use of indexes is that indexes are not always optimal for a SQL's execution plan. The yardstick you need to use is how many block visitations does my SQL have to perform with indexes versus without indexes?. If the block visitations are lower for a full table scan, then that is the better plan. Setting OPTIMIZER_MODE to FIRST_ROWS does twist the optimizer's arm to use indexes over a full table scan and this may wreak havoc during your application's batch window. OPTIMIZER_INDEX_COST_ADJ directly adjusts the cost of using an index. The default value of 100 makes the optimizer evaluate the cost of the index as normal, and a value of 50 makes the optimizer evaluate the cost to be half as expensive as normal. This parameter encourages the use of all indexes, regardless of their selectivity. It applies to index use in general. While OPTMIZER_MODE=FIRST_ROWS, will almost always force the use of an index, the parameter OPTIMIZER_INDEX_COST_ADJ encourages the optimizer to use indexes. When set to a value of 30, you are telling the optimizer, that the use of indexes is actually approximately 1/3rd of the normal cost. I'd rather use the latter over the former, as in my experience with ERP applications, I have found the latter is less forceful than the former, especially during the batch window. Hope that helps, Gaja = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
