Re: RULE versus CHOOSE
> Qs.1 How does optimizer_mode=CHOOSE Compare with RULE ? There's a world of difference between them. Using the Cost-Based Optimizer (CBO) versus Rule-Based is like using a GPS system to navigate a city you've not been to in a while versus your own vague memories of it. In the latter case, you may only remember a few ways around and things may have changed dramatically. With the former, the current or near-current situation is always known if used properly. > Qs.2 Is there ANY Benefit of keeping optimizer_mode=CHOOSE WITHOUT > having Any Statistics Existent on the Application Tables , Indexes ? Using CHOOSE without analyzing the relevant schemas is the same as using RULE because Oracle has no current "map of the terrain" to utilize. So, it falls back on the standard rules. > Qs.3 Are there any Disadvantages with Using RULE in 8.1.7.0 ? Tons IMHO. Not in 8.1.7 in particular, but in general. Most databases are constantly changing with the addition, changing and removing of data. What the CBO offers is a roadmap to that changing data. Remember with the CBO that it is only as good as your statistics. In sites I work at, I recommend that relevant schemas are analyzed nightly to keep things current (btw, never analyze SYS). In conjunction with that, we create and update histograms on relevant columns in the application schemas to handle skewed data distributions. > Qs.4 In Choose mode are there any Commonly known Standard Important > Statistics' Fields/Values which can be Looked at to understand why > optimizer took a particular path ? > What Causes a Path to be Chosen in CHOOSE , we are largely ignorant > about . This is difficult to explain and quite complex. There surely is a better authority than I to answer this here and MetaLink could probably enlighten you as well. > NOTE - At a Customer's Database , Our Development Section Head wants to > set optmizer_mode=RULE & keep it so . His Reasons :- > - The path of the optimizer is more predictable when set to RULE > - Any under-performance Issues would be Handled by Giving HINTS etc > rather than Allowing the Optimizer to Choose / Compute it's own Path > which may be a BAD One . > - A Correct Path being Taken Today may in Time get Automatically Changed > to a Worse Path somewhere in future (with the Stats getting OLD etc.) . > - Lastly his Team will Take Responsibility for Any Performance Issues > arising out of a Code underperforming . See what I said above about the changing face of data in a database. And what happens when his team changes? Are they always going to be there to handle the performance issues and keep on top of them? "A correct path being taken today may in time get automatically changed" is exactly the reason as far as I'm concerned to use the CBO. It takes the guesswork out of most query optimization if used properly. > Qs Are there Any Best practices Documents / Links on RULE vs. CHOOSE ? I'm sure there are many other, but the biggies I work with are (1) analyze relevant schemas regularly, (2) create/maintain histograms on columns that have skewed data distributions and (3) don't analyze SYS. > Qs. What do you Folks Advice ? Should I Given in to the 80-20 Rule Or > Still Endeavour to persist for optimizer_mode=CHOOSE ? I would say persist in your argument for CHOOSE. The biggest reason to do it now IMHO is that, according to Oracle, RULE is going to be desupported in the near future. CHOOSE will soon be the default. Anyone with more experience than I here, please chime in. -- Jon Walthour, OCDBA Oracle DBA Computer Horizons Cincinnati, Ohio -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jon Walthour 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: RULE versus CHOOSE
Jon, Great write up. Several times you mentioned creating Histograms for skewed data distributions. I am just curious if you have ever studied the performance impact with and without them? We used to maintain histograms but when we studied the performance impact (with and without them) we determined that there was very little benefit with histograms. The down side with them is that they drastically increased the amount of time it took to analyze the tables at night. We decided to do without them. Have you ever studied their benefits verse drawbacks? Tom Tom Terrian Oracle DBA WPAFB - DAASC [EMAIL PROTECTED] 937-656-3844 -Original Message- Sent: Friday, July 06, 2001 8:11 AM To: Multiple recipients of list ORACLE-L > Qs.1 How does optimizer_mode=CHOOSE Compare with RULE ? There's a world of difference between them. Using the Cost-Based Optimizer (CBO) versus Rule-Based is like using a GPS system to navigate a city you've not been to in a while versus your own vague memories of it. In the latter case, you may only remember a few ways around and things may have changed dramatically. With the former, the current or near-current situation is always known if used properly. > Qs.2 Is there ANY Benefit of keeping optimizer_mode=CHOOSE WITHOUT > having Any Statistics Existent on the Application Tables , Indexes ? Using CHOOSE without analyzing the relevant schemas is the same as using RULE because Oracle has no current "map of the terrain" to utilize. So, it falls back on the standard rules. > Qs.3 Are there any Disadvantages with Using RULE in 8.1.7.0 ? Tons IMHO. Not in 8.1.7 in particular, but in general. Most databases are constantly changing with the addition, changing and removing of data. What the CBO offers is a roadmap to that changing data. Remember with the CBO that it is only as good as your statistics. In sites I work at, I recommend that relevant schemas are analyzed nightly to keep things current (btw, never analyze SYS). In conjunction with that, we create and update histograms on relevant columns in the application schemas to handle skewed data distributions. > Qs.4 In Choose mode are there any Commonly known Standard Important > Statistics' Fields/Values which can be Looked at to understand why > optimizer took a particular path ? > What Causes a Path to be Chosen in CHOOSE , we are largely ignorant > about . This is difficult to explain and quite complex. There surely is a better authority than I to answer this here and MetaLink could probably enlighten you as well. > NOTE - At a Customer's Database , Our Development Section Head wants to > set optmizer_mode=RULE & keep it so . His Reasons :- > - The path of the optimizer is more predictable when set to RULE > - Any under-performance Issues would be Handled by Giving HINTS etc > rather than Allowing the Optimizer to Choose / Compute it's own Path > which may be a BAD One . > - A Correct Path being Taken Today may in Time get Automatically Changed > to a Worse Path somewhere in future (with the Stats getting OLD etc.) . > - Lastly his Team will Take Responsibility for Any Performance Issues > arising out of a Code underperforming . See what I said above about the changing face of data in a database. And what happens when his team changes? Are they always going to be there to handle the performance issues and keep on top of them? "A correct path being taken today may in time get automatically changed" is exactly the reason as far as I'm concerned to use the CBO. It takes the guesswork out of most query optimization if used properly. > Qs Are there Any Best practices Documents / Links on RULE vs. CHOOSE ? I'm sure there are many other, but the biggies I work with are (1) analyze relevant schemas regularly, (2) create/maintain histograms on columns that have skewed data distributions and (3) don't analyze SYS. > Qs. What do you Folks Advice ? Should I Given in to the 80-20 Rule Or > Still Endeavour to persist for optimizer_mode=CHOOSE ? I would say persist in your argument for CHOOSE. The biggest reason to do it now IMHO is that, according to Oracle, RULE is going to be desupported in the near future. CHOOSE will soon be the default. Anyone with more experience than I here, please chime in. -- Jon Walthour, OCDBA Oracle DBA Computer Horizons Cincinnati, Ohio -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jon Walthour 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 s
RE: RULE versus CHOOSE
See http://www.sucs.swan.ac.uk/~arthur/jargon/html/The-Jargon-Lexicon.html At 09:20 AM 7/6/01 -0800, you wrote: >Jon > >Great Answers Indeed . > >What does IMHO stand for though ? > >Thanks Again so much . > >Vivek > > >> -Original Message- >> Sent: Friday, July 06, 2001 8:11 AM >> To: Multiple recipients of list ORACLE-L >> >> >> > Qs.1 How does optimizer_mode=CHOOSE Compare with RULE ? >> >> There's a world of difference between them. Using the Cost-Based >> Optimizer >> (CBO) versus Rule-Based is like using a GPS system to navigate a city >> you've >> not been to in a while versus your own vague memories of it. In the >> latter >> case, you may only remember a few ways around and things may have >> changed >> dramatically. With the former, the current or near-current situation >> is >> always known if used properly. >> >> > Qs.2 Is there ANY Benefit of keeping optimizer_mode=CHOOSE WITHOUT >> > having Any Statistics Existent on the Application Tables , Indexes ? >> >> Using CHOOSE without analyzing the relevant schemas is the same as >> using >> RULE because Oracle has no current "map of the terrain" to utilize. >> So, it >> falls back on the standard rules. >> >> > Qs.3 Are there any Disadvantages with Using RULE in 8.1.7.0 ? >> >> Tons IMHO. Not in 8.1.7 in particular, but in general. Most databases >> are >> constantly changing with the addition, changing and removing of data. >> What >> the CBO offers is a roadmap to that changing data. Remember with the >> CBO >> that it is only as good as your statistics. In sites I work at, I >> recommend >> that relevant schemas are analyzed nightly to keep things current >> (btw, >> never analyze SYS). In conjunction with that, we create and update >> histograms on relevant columns in the application schemas to handle >> skewed >> data distributions. >> >> > Qs.4 In Choose mode are there any Commonly known Standard Important >> > Statistics' Fields/Values which can be Looked at to understand why >> > optimizer took a particular path ? >> > What Causes a Path to be Chosen in CHOOSE , we are largely ignorant >> > about . >> >> This is difficult to explain and quite complex. There surely is a >> better >> authority than I to answer this here and MetaLink could probably >> enlighten >> you as well. >> >> > NOTE - At a Customer's Database , Our Development Section Head wants >> to >> > set optmizer_mode=RULE & keep it so . His Reasons :- >> > - The path of the optimizer is more predictable when set to RULE >> > - Any under-performance Issues would be Handled by Giving HINTS etc >> > rather than Allowing the Optimizer to Choose / Compute it's own Path >> > which may be a BAD One . >> > - A Correct Path being Taken Today may in Time get Automatically >> Changed >> > to a Worse Path somewhere in future (with the Stats getting OLD >> etc.) . >> > - Lastly his Team will Take Responsibility for Any Performance >> Issues >> > arising out of a Code underperforming . >> >> See what I said above about the changing face of data in a database. >> And >> what happens when his team changes? Are they always going to be there >> to >> handle the performance issues and keep on top of them? "A correct path >> being >> taken today may in time get automatically changed" is exactly the >> reason as >> far as I'm concerned to use the CBO. It takes the guesswork out of >> most >> query optimization if used properly. >> >> > Qs Are there Any Best practices Documents / Links on RULE vs. CHOOSE >> ? >> >> I'm sure there are many other, but the biggies I work with are (1) >> analyze >> relevant schemas regularly, (2) create/maintain histograms on columns >> that >> have skewed data distributions and (3) don't analyze SYS. >> >> > Qs. What do you Folks Advice ? Should I Given in to the 80-20 Rule >> Or >> > Still Endeavour to persist for optimizer_mode=CHOOSE ? >> >> I would say persist in your argument for CHOOSE. The biggest reason to >> do it >> now IMHO is that, according to Oracle, RULE is going to be desupported >> in >> the near future. CHOOSE will soon be the default. >> >> Anyone with more experience than I here, please chime in. >> >> -- >> >> Jon Walthour, OCDBA >> Oracle DBA >> Computer Horizons >> Cincinnati, Ohio >> >> >-- >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). > > Dennis Taylor A contented man is one w
RE: RULE versus CHOOSE
Vivek, In addition to Jon's excellent note, may I add that IOTs and Partitioned tables *will* need statistics. Missing stats will result in a guesstimate based on some parameters and queries involving such tables will take the wind out of the Development head's sail >> NOTE - At a Customer's Database , Our Development Section >Head wants to >> set optmizer_mode=RULE & keep it so . His Reasons :- >> - The path of the optimizer is more predictable when set to RULE >> - Any under-performance Issues would be Handled by Giving HINTS etc >> rather than Allowing the Optimizer to Choose / Compute it's own Path >> which may be a BAD One . >> - A Correct Path being Taken Today may in Time get >Automatically Changed >> to a Worse Path somewhere in future (with the Stats getting >OLD etc.) . >> - Lastly his Team will Take Responsibility for Any Performance Issues >> arising out of a Code underperforming . Incorrect understanding and implementation of the CBO in preceeding years kept many away from it (including myself). This is probably the reason for the reluctance with this Team lead. Since he is willing to take responsibility, I would suggest that you set 'RULE', but collect stats anyway. Use these with badly performing queries and use these to 'convert' him.. John Kanagaraj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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: RE: RULE versus CHOOSE
Tom: I never did any official benchmark studies, per se. I studied the CBO/histograms about 4 months ago when we were converting an app over from Oracle 7 to 8i. The new version of the app had queries that were very different from its earlier version and so performance on 8i as compared to 7 was dramatically worse. Of course, the app owners blamed the database. In the course of my defense of the db, I discovered that the data (being primarily composed of case studies) was severely skewed to the more recent dates (i.e., the further back you went, the sparser the number of cases). I discovered that the optimizer was doing a lot of range scans for queries when it should have been doing full table scans according to the CBO thresholds. Histograms on certain date fields throughout the schema dropped times on certain large report queries from 30 minutes to under 2. That's all I know. I can't give you an hard empirical evidence, just anecdotal evidence that, when properly used, histograms do seem to have dramatic impacts. -- Jon Walthour, OCDBA Oracle DBA Computer Horizons Cincinnati, Ohio >--- Original Message --- >From: "Terrian, Tom" <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Date: 7/6/01 9:20:24 AM > >Jon, > >Great write up. > >Several times you mentioned creating Histograms for skewed data distributions. >I am just curious if you have ever studied the performance impact with and >without them? We used to maintain histograms but when we studied the >performance impact (with and without them) we determined that there was very >little benefit with histograms. The down side with them is that they >drastically increased the amount of time it took to analyze the tables at night. >We decided to do without them. Have you ever studied their benefits verse >drawbacks? > >Tom > >Tom Terrian >Oracle DBA >WPAFB - DAASC >[EMAIL PROTECTED] >937-656-3844 > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jon Walthour 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: RE: RULE versus CHOOSE
Histogramms make sence only for columns with skewed data and only if literals used in where clause - not bind variables. To improve optimizer you need to change parameters OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ. Get an article The Search for Intelligent Life in the Cost-Based Optimizer from www.evdbt.com - it ex-plains use of these parameters. Also check your parameter DB_FILE_MULTIBLOCK_READ_COUNT - maybe you need to increase it depending on your platform and your block size and your max IO size (Solaris 5.5 and up it is configurable) and your extent sizes. Alex Hillman -Original Message- Sent: Friday, July 06, 2001 10:55 AM To: Multiple recipients of list ORACLE-L Tom: I never did any official benchmark studies, per se. I studied the CBO/histograms about 4 months ago when we were converting an app over from Oracle 7 to 8i. The new version of the app had queries that were very different from its earlier version and so performance on 8i as compared to 7 was dramatically worse. Of course, the app owners blamed the database. In the course of my defense of the db, I discovered that the data (being primarily composed of case studies) was severely skewed to the more recent dates (i.e., the further back you went, the sparser the number of cases). I discovered that the optimizer was doing a lot of range scans for queries when it should have been doing full table scans according to the CBO thresholds. Histograms on certain date fields throughout the schema dropped times on certain large report queries from 30 minutes to under 2. That's all I know. I can't give you an hard empirical evidence, just anecdotal evidence that, when properly used, histograms do seem to have dramatic impacts. -- Jon Walthour, OCDBA Oracle DBA Computer Horizons Cincinnati, Ohio >--- Original Message --- >From: "Terrian, Tom" <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Date: 7/6/01 9:20:24 AM > >Jon, > >Great write up. > >Several times you mentioned creating Histograms for skewed data distributions. >I am just curious if you have ever studied the performance impact with and >without them? We used to maintain histograms but when we studied the >performance impact (with and without them) we determined that there was very >little benefit with histograms. The down side with them is that they >drastically increased the amount of time it took to analyze the tables at night. >We decided to do without them. Have you ever studied their benefits verse >drawbacks? > >Tom > >Tom Terrian >Oracle DBA >WPAFB - DAASC >[EMAIL PROTECTED] >937-656-3844 > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jon Walthour 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: RE: RULE versus CHOOSE
On Friday 06 July 2001 10:28, VIVEK_SHARMA wrote: > Can you possibly Detail what you mean by Skewed Data ? Real life example. 145,000 rows. ( large rows ). the column most used by SQL queries has 4 distinct values. 144,800 of them are the same value. Using histograms on this worked quite well. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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: RE: RULE versus CHOOSE
Vivek: Skewed data is data that is not evenly distributed in a column. Let me try to explain. Let's say you have a shipping company that does business throughout the US (in all 50 states) and you have a shipping table with address to customer that has a STATE column with the 2 letter state abbreviation as part of their address. You have done business in all 50 states, but most of your business comes from the 6 states that are in your region of the country. Therefore, statistically speaking, those 6 state abbreviations are represented an abnormally high number of times; they may represent as much as 80% of all the shipping orders. Without evidence to the contrary, the CBO assumes evenly distributed data when it works. This would mean, for the purposes of our example, that the CBO would assume that each state is represented 2% of the time and that these 6 states taken together would represent 12% of the total, not 60%. So, let's say I run a query selecting all the ordered shipped to one of those states. Something like "select * from orders where state='NY'. Now, the CBO is going to assume that the state 'NY' represents 2% of the total orders on average and do use the index on the "state" column. In fact, however, much of our business comes from NY and it represents 35% of all the orders. So, the CBO should go for a full table scan. But it doesn't know that--it can only assume an even distribution of the data from the normal statistics collected. A histogram on the "state" column would provide the CBO with the additional information it needs to know that, in this case, the expected number of rows to return from the query would represent a large percentage of the total number of rows and that it should do a full table scan. So, as has been said earlier, you want to look for data in indexed columns that are used in where clauses as literal predicates where the data is not evenly distributed in the column. These generally make good candidates for histograms. Hope this makes sense. -- Jon Walthour, OCDBA Oracle DBA Cincinnati, Ohio - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, July 06, 2001 1:28 PM Can you possibly Detail what you mean by Skewed Data ? > -Original Message- > From: Jon Walthour [SMTP:[EMAIL PROTECTED]] > Sent: Friday, July 06, 2001 8:25 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: RE: RULE versus CHOOSE > > > > Tom: > > I never did any official benchmark studies, per se. I studied > the CBO/histograms about 4 months ago when we were converting > an app over from Oracle 7 to 8i. The new version of the app had > queries that were very different from its earlier version and > so performance on 8i as compared to 7 was dramatically worse. > Of course, the app owners blamed the database. In the course > of my defense of the db, I discovered that the data (being primarily > composed of case studies) was severely skewed to the more recent > dates (i.e., the further back you went, the sparser the number > of cases). I discovered that the optimizer was doing a lot of > range scans for queries when it should have been doing full table > scans according to the CBO thresholds. Histograms on certain > date fields throughout the schema dropped times on certain large > report queries from 30 minutes to under 2. > > That's all I know. I can't give you an hard empirical evidence, > just anecdotal evidence that, when properly used, histograms > do seem to have dramatic impacts. > > -- > > Jon Walthour, OCDBA > Oracle DBA > Computer Horizons > Cincinnati, Ohio > > > >--- Original Message --- > >From: "Terrian, Tom" <[EMAIL PROTECTED]> > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > >Date: 7/6/01 9:20:24 AM > > > > >Jon, > > > >Great write up. > > > >Several times you mentioned creating Histograms for skewed data > distributions. > >I am just curious if you have ever studied the performance impact > with and > >without them? We used to maintain histograms but when we studied > the > >performance impact (with and without them) we determined that > there was very > >little benefit with histograms. The down side with them is > that they > >drastically increased the amount of time it took to analyze > the tables at night. > >We decided to do without them. Have you ever studied their > benefits verse > >drawbacks? > > > >Tom > > > >Tom Terrian > >Oracle DBA > >WPAFB - DAASC > >[EMAIL PROTECTED] > >937-656-3844 > > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA IN
RE: RULE versus CHOOSE - sorry it's long
Title: RE: RULE versus CHOOSE - sorry it's long Hi Vivek, comments inline. List, please correct me if I am wrong. -Original Message- From: VIVEK_SHARMA [SMTP:[EMAIL PROTECTED]] Sent: Friday, July 06, 2001 1:56 AM To: Multiple recipients of list ORACLE-L Subject: RULE versus CHOOSE Database = Oracle 8.1.7.0.0 on SunOS 5.6 . Current Database Size = 20 GB This is only a Test One & the Live Production will be a BIGGER (100 GB) One . OPTIMIZER_MODE = RULE & NO Statistics Exist Currently Qs.1 How does optimizer_mode=CHOOSE Compare with RULE ? [Lisa Koivu] Choose invokes the cost based optimizer (CBO). If the optimizer_goal = RULE, it is rule, period (RBO). Qs.2 Is there ANY Benefit of keeping optimizer_mode=CHOOSE WITHOUT having Any Statistics Existent on the Application Tables , Indexes ? [Lisa Koivu] Well, that equates to rule, I believe. However, if anyone does something like put statistics on an index somewhere and forget to delete them, and that index is used in a query, your optimizer will change to CBO and you may end up with unexpected query plans. I also believe that degree > 1 will invoke CBO. (not exactly sure?) If Statistics are DELETED on ALL Objects , yet with optmizer_mode = CHOOSE , does it behave in Exactly the Same manner as having optmizer_mode set to RULE Or are there Still Some Advantages which can be Reaped ? [Lisa Koivu] Same as answer above. Qs.3 Are there any Disadvantages with Using RULE in 8.1.7.0 ? [Lisa Koivu] Can't comment specifically on that, I haven't had the opportunity to play with 8.1.7 Qs.4 In Choose mode are there any Commonly known Standard Important Statistics' Fields/Values which can be Looked at to understand why optimizer took a particular path ? What Causes a Path to be Chosen in CHOOSE , we are largely ignorant about . [Lisa Koivu] Read up on histograms and exactly what the statistics mean (DBA_TABLES, etc). With CBO it isn't always exactly clear why it did what it did - for example, I have tried in the past to eliminate all FTS's from a query. CBO did not like that, it wanted to FTS at least one table. That's one thing you will find in CBO - it will favor FTS's more so than RBO. NOTE - At a Customer's Database , Our Development Section Head wants to set optmizer_mode=RULE & keep it so . His Reasons :- - The path of the optimizer is more predictable when set to RULE [Lisa Koivu] Well, yes. There is a published list of steps RBO will take to try to determine the query plan. - Any under-performance Issues would be Handled by Giving HINTS etc rather than Allowing the Optimizer to Choose / Compute it's own Path which may be a BAD One . [Lisa Koivu] Well, have you tried it? Some view hints as hard-coding. However, in some cases it is warranted. - A Correct Path being Taken Today may in Time get Automatically Changed to a Worse Path somewhere in future (with the Stats getting OLD etc.) . [Lisa Koivu] You avoid this by keeping your statistics fresh at all times. You may have to mess with the statistics for any skewed columns (again, this is histograms) but the bottom line is stale statistics mean sub-optimal query plans. I believe there's a package called DBMS_STATS that will monitor your objects for stale statistics. I don't know what the threshold is for determining if statistics are stale, I haven't investigated this package. However, there's also a school of thought that says ANY change to your data renders the statistics invalid, period. Also, deleting statistics from an object and following this step with analyzing the object renders much better behavior. Don't ask why... it's just another quirk (list, correct me if I am wrong) - Lastly his Team will Take Responsibility for Any Performance Issues arising out of a Code underperforming . [Lisa Koivu] Well, that's a challenge. Tuning your top ten bad statements should be an ongoing task... Are they qualified to do this? are there any SQL tuning experts on his team? Qs Are there Any Best practices Documents / Links on RULE vs. CHOOSE ? [Lisa Koivu] CBO is best suited for DSS environment, where FTS is common and is not viewed as evil. CBO has several features built into it that exploit a proper star schema design and deliver plans more suited to the volume of data (index combining, etc.) I have yet to see an OLTP or hybrid-type system running successfully on CBO, but then again I have only been looking at this closely over the last three years. Maybe the list can shed mroe light on this question. Qs. What do you Folks Advice ? Should I Given in to the 80-20 Rule Or Still Endeavour to persist for optimizer_mode=CHOOSE ? [Lisa Koivu] I can tell you that just analyzing everything, setting optimizer_goal = CHOOSE and restarting the app most likely will not work. Moving to CBO will involve a certain amount of
Re: RULE versus CHOOSE - sorry it's long
On Friday 06 July 2001 06:41, Koivu, Lisa wrote: > Choose invokes the cost based optimizer (CBO). If the > optimizer_goal = RULE, it is rule, period (RBO). Not true Lisa. Try setting parallel degree > 1 on a table, do an explain plan on a SELECT and see what happens. Certain operations require CBO, and CBO will be invoked regardless of the CHOOSE setting. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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: RULE versus CHOOSE - sorry it's long
Title: RE: RULE versus CHOOSE - sorry it's long One more thing to consider, if you're using RBO, you may not be able to use lot of features added to oracle 8i. features like function based indexes, bitmap indexes, IOTs are applicable for CBO environment only. rgds amar -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Friday, July 06, 2001 5:41 PMTo: Multiple recipients of list ORACLE-LSubject: RE: RULE versus CHOOSE - sorry it's long Hi Vivek, comments inline. List, please correct me if I am wrong. -Original Message- From: VIVEK_SHARMA [SMTP:[EMAIL PROTECTED]] Sent: Friday, July 06, 2001 1:56 AM To: Multiple recipients of list ORACLE-L Subject: RULE versus CHOOSE Database = Oracle 8.1.7.0.0 on SunOS 5.6 . Current Database Size = 20 GB This is only a Test One & the Live Production will be a BIGGER (100 GB) One . OPTIMIZER_MODE = RULE & NO Statistics Exist Currently Qs.1 How does optimizer_mode=CHOOSE Compare with RULE ? [Lisa Koivu] Choose invokes the cost based optimizer (CBO). If the optimizer_goal = RULE, it is rule, period (RBO). Qs.2 Is there ANY Benefit of keeping optimizer_mode=CHOOSE WITHOUT having Any Statistics Existent on the Application Tables , Indexes ? [Lisa Koivu] Well, that equates to rule, I believe. However, if anyone does something like put statistics on an index somewhere and forget to delete them, and that index is used in a query, your optimizer will change to CBO and you may end up with unexpected query plans. I also believe that degree > 1 will invoke CBO. (not exactly sure?) If Statistics are DELETED on ALL Objects , yet with optmizer_mode = CHOOSE , does it behave in Exactly the Same manner as having optmizer_mode set to RULE Or are there Still Some Advantages which can be Reaped ? [Lisa Koivu] Same as answer above. Qs.3 Are there any Disadvantages with Using RULE in 8.1.7.0 ? [Lisa Koivu] Can't comment specifically on that, I haven't had the opportunity to play with 8.1.7 Qs.4 In Choose mode are there any Commonly known Standard Important Statistics' Fields/Values which can be Looked at to understand why optimizer took a particular path ? What Causes a Path to be Chosen in CHOOSE , we are largely ignorant about . [Lisa Koivu] Read up on histograms and exactly what the statistics mean (DBA_TABLES, etc). With CBO it isn't always exactly clear why it did what it did - for example, I have tried in the past to eliminate all FTS's from a query. CBO did not like that, it wanted to FTS at least one table. That's one thing you will find in CBO - it will favor FTS's more so than RBO. NOTE - At a Customer's Database , Our Development Section Head wants to set optmizer_mode=RULE & keep it so . His Reasons :- - The path of the optimizer is more predictable when set to RULE [Lisa Koivu] Well, yes. There is a published list of steps RBO will take to try to determine the query plan. - Any under-performance Issues would be Handled by Giving HINTS etc rather than Allowing the Optimizer to Choose / Compute it's own Path which may be a BAD One . [Lisa Koivu] Well, have you tried it? Some view hints as hard-coding. However, in some cases it is warranted. - A Correct Path being Taken Today may in Time get Automatically Changed to a Worse Path somewhere in future (with the Stats getting OLD etc.) . [Lisa Koivu] You avoid this by keeping your statistics fresh at all times. You may have to mess with the statistics for any skewed columns (again, this is histograms) but the bottom line is stale statistics mean sub-optimal query plans. I believe there's a package called DBMS_STATS that will monitor your objects for stale statistics. I don't know what the threshold is for determining if statistics are stale, I haven't investigated this package. However, there's also a school of thought that says ANY change to your data renders the statistics invalid, period. Also, deleting statistics from an object and following this step with analyzing the object renders much better behavior. Don't ask why... it's just another quirk (list, correct me if I am wrong) - Lastly his Team will Take Responsibility for Any Performance Issues arising out of a Code underperforming . [Lisa Koivu] Well, that's a challenge. Tuning your top ten bad statements should be an ongoing task... Are they qualified to do this? are there any SQL tuning experts on his team? Qs Are there Any Best practices Documents / Link
RE: RULE versus CHOOSE - sorry it's long
Just some comment on following lines > Another comment: I believe the 9i doc states that RBO is > desupported. Gosh, I highly doubt it, I think Oracle Apps run RBO. > But Oracle has been saying for a long time that RBO is "going away". Oracle themselves have shifted from RBO to CBO beginning with Oracle Applications release 11i . So there shouldn't be major hassle dessuporting RBO from 9i onwards. HTH, Rajesh OCDBA 8&8i > -Original Message- > From: Koivu, Lisa [SMTP:[EMAIL PROTECTED]] > Sent: Friday, July 06, 2001 6:14 PM > To: '[EMAIL PROTECTED]'; VIVEK_SHARMA > Subject: RE: RULE versus CHOOSE - sorry it's long > > Hi Vivek, comments inline. List, please correct me if I am wrong. > > -Original Message- > From: VIVEK_SHARMA [SMTP:[EMAIL PROTECTED]] > Sent: Friday, July 06, 2001 1:56 AM > To: Multiple recipients of list ORACLE-L > Subject:RULE versus CHOOSE > > Database = Oracle 8.1.7.0.0 on SunOS 5.6 . > Current Database Size = 20 GB > This is only a Test One & the Live Production will be a BIGGER (100 > GB) > One . > OPTIMIZER_MODE = RULE & NO Statistics Exist Currently > > Qs.1 How does optimizer_mode=CHOOSE Compare with RULE ? > [Lisa Koivu] > Choose invokes the cost based optimizer (CBO). If the optimizer_goal > = RULE, it is rule, period (RBO). > > Qs.2 Is there ANY Benefit of keeping optimizer_mode=CHOOSE > WITHOUT > having Any Statistics Existent on the Application Tables , Indexes ? > [Lisa Koivu] > Well, that equates to rule, I believe. However, if anyone does > something like put statistics on an index somewhere and forget to > delete them, and that index is used in a query, your optimizer will > change to CBO and you may end up with unexpected query plans. I also > believe that degree > 1 will invoke CBO. (not exactly sure?) > > > If Statistics are DELETED on ALL Objects , yet with optmizer_mode = > CHOOSE , does it behave in Exactly the Same manner as having > optmizer_mode set to RULE Or are there Still Some Advantages which can > > be Reaped ? > [Lisa Koivu] > Same as answer above. > > Qs.3 Are there any Disadvantages with Using RULE in 8.1.7.0 ? > [Lisa Koivu] > Can't comment specifically on that, I haven't had the opportunity to > play with 8.1.7 > > Qs.4 In Choose mode are there any Commonly known Standard > Important > Statistics' Fields/Values which can be Looked at to understand why > optimizer took a particular path ? > What Causes a Path to be Chosen in CHOOSE , we are largely ignorant > about . > [Lisa Koivu] > Read up on histograms and exactly what the statistics mean > (DBA_TABLES, etc). With CBO it isn't always exactly clear why it did > what it did - for example, I have tried in the past to eliminate all > FTS's from a query. CBO did not like that, it wanted to FTS at least > one table. That's one thing you will find in CBO - it will favor > FTS's more so than RBO. > > NOTE - At a Customer's Database , Our Development Section Head > wants to > set optmizer_mode=RULE & keep it so . His Reasons :- > - The path of the optimizer is more predictable when set to RULE > [Lisa Koivu] > Well, yes. There is a published list of steps RBO will take to try to > determine the query plan. > - Any under-performance Issues would be Handled by Giving HINTS etc > rather than Allowing the Optimizer to Choose / Compute it's own Path > which may be a BAD One . > [Lisa Koivu] > Well, have you tried it? Some view hints as hard-coding. However, in > some cases it is warranted. > > - A Correct Path being Taken Today may in Time get Automatically > Changed > to a Worse Path somewhere in future (with the Stats getting OLD etc.) > . > [Lisa Koivu] > You avoid this by keeping your statistics fresh at all times. You may > have to mess with the statistics for any skewed columns (again, this > is histograms) but the bottom line is stale statistics mean > sub-optimal query plans. I believe there's a package called DBMS_STATS > that will monitor your objects for stale statistics. I don't know > what the threshold is for determining if statistics are stale, I > haven't investigated this package. However, there's also a school of > thought that says ANY change to your data renders the statistics > invalid, period. Also, deleting statistics from an object and > following this step with analyzing the object renders much better > behavior. Don't ask why... it's just another quirk (list, correc
RE: RULE versus CHOOSE - sorry it's long
Title: RE: RULE versus CHOOSE - sorry it's long Thank you for correcting me Jared. Lisa -Original Message- From: Jared Still [SMTP:[EMAIL PROTECTED]] Sent: Friday, July 06, 2001 11:36 PM To: [EMAIL PROTECTED]; Koivu, Lisa Subject: Re: RULE versus CHOOSE - sorry it's long On Friday 06 July 2001 06:41, Koivu, Lisa wrote: > Choose invokes the cost based optimizer (CBO). If the > optimizer_goal = RULE, it is rule, period (RBO). Not true Lisa. Try setting parallel degree > 1 on a table, do an explain plan on a SELECT and see what happens. Certain operations require CBO, and CBO will be invoked regardless of the CHOOSE setting. Jared
Re: RULE versus CHOOSE - sorry it's long
Having recently read the note on this on MetaLink, it was fresh in my memory. :) Jared On Monday 09 July 2001 05:50, Koivu, Lisa wrote: > Thank you for correcting me Jared. > > Lisa > > > -Original Message- > > From: Jared Still [SMTP:[EMAIL PROTECTED]] > > Sent: Friday, July 06, 2001 11:36 PM > > To: [EMAIL PROTECTED]; Koivu, Lisa > > Subject:Re: RULE versus CHOOSE - sorry it's long > > > > On Friday 06 July 2001 06:41, Koivu, Lisa wrote: > > > Choose invokes the cost based optimizer (CBO). If the > > > optimizer_goal = RULE, it is rule, period (RBO). > > > > Not true Lisa. > > > > Try setting parallel degree > 1 on a table, do an explain > > plan on a SELECT and see what happens. > > > > Certain operations require CBO, and CBO will be invoked > > regardless of the CHOOSE setting. > > > > Jared Content-Type: text/html; name="Attachment: 1" Content-Transfer-Encoding: quoted-printable Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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).
