Re: Optimizer related init parameters
> optimizer_index_cost_adj=10 > optimizer_index_caching=50 Use of these two depends on a number of things, all of which are relative to your application and users. When you say 'hybrid', it seems to indicate that you have reporting schema in the same database. How these will effect the querying of the reporting tables depends on the kind of reporting system you have setup. If you have a star schema, I don't know how these options might effect it. Someone else on the list undoubtedly knows. If more of a traditional relational layout is used, these parameters could serve to slow down the reporting queries, as nested loop operations mightbe favored by the CBO where a hash join might be more efficient. > optimizer_max_permutations=8000 If you have some very complex queries, this parameter can greatly decrease the parse time. You could probably go even lower - the default on 9i is 2000 IIRC. I've set to to 1000 on on 8i database that has some annoyingly complex views for use with INSTEAD OF triggers. > optimizer_dynamic_sampling=4 I don't have any experience with this one yet. To answer your own questions properly, you need to consider a couple things: Which usage has a higher performance priority - the reporting side or OLTP side? Have acceptable performance levels been established for both? Quite a few more items to consider, I;m sure, much of it site dependent. This is all I can think of before the first cup of coffee. Jared On Fri, 2003-10-10 at 05:54, VIVEK_SHARMA wrote: > How Good/advisable are the following 4 parameters' Values in a Hybrid > Application? > > Are there any know ill-effects of the same? > > > > Application - Banking (Hybrid) > > Solaris 9 > > Oracle 9.2 > > > > > > optimizer_max_permutations=8000 > optimizer_index_cost_adj=10 > optimizer_index_caching=50 > optimizer_dynamic_sampling=4 > > > > > > > > Some INFO :- > > Database has 6000 Concurrent Users accessing > > We do ONLY INDEX Scans with exceptional FTS . > > FTS if present occur only on SMALL Tables (a few Hundred Rows) > > FTS if unchecked greatly harm our performance > > Stripe Unit Size 64K > > Oracle Block Size 8K > > > > Will Give any info required > > > > Thanks > > > > > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Optimizer related init parameters
Vivek and list, I don't think any reasonable person will be able to say with a high-level of certainty whether the values that you have suggested, are optimal for your environment. The answer is a huge - IT DEPENDS. Having said that, here are some things you may want to take into consideration: 1) From a functionality perspective OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ were meant to do the same thing. It just happened to end up as 2 different parameters with 2 different code paths, which pretty much do the same(similar) thing. So usually, it is enought to set either one or the other, although setting both in my experience has generated no harm. So, if you want to optimizer to show bias towards index scans, then setting OPTIMIZER_INDEX_CACHING to a high value (90 or higher) will achieve that. Right now your value of 50, tells the optimizer that only 50% of the time, will it find index blocks in the DB buffer cache. This will affect the optimizer's decision making. Tim Gorman has a very simple formula to calculate the appropriate value on your system for OPTIMZER_INDEX_COST_ADJ, stated in his paper "Searching for intelligence in the Oracle Optimizer" (or something to that effect) on his site http://www.evdbt.com. It basically calculates a ratio of the average time for db file sequential read/db file scattered read from v$system_event, for your system. On a related topic, I think it is relevant to mention here that to carte-blanche curtail full-table-scans, may not work to the long-term benefit of your applications. However, I will assume here that you are aware of the core point - "amount of logical I/O" to be the most important (if not only) determinant when deciding whether FTS is better than index scans. 2) John Kanagaraj did some work and testing to determine that setting OPTIMIZER_MAX_PERMUTATIONS to a low value (2000 if I remember right), has a positive impact on the plans that is generated, especially in an Oracle Apps environment. You should check it out. 3) Julian Dyke and Steve Adams have performed some good tests and research on OPTIMIZER_DYNAMIC_SAMPLING. But, I think the jury is still out on what the optimal value for this might be. I guess 4 is good enough. But, realize that this parameter is relevant when you have "partial statistics" in your schema. Otherwise, I don't think there is any impact of this parameter. Final notes: 1) All of these parameters can be set at the session level. I would urge you to perform extensive tests before making global init.ora changes. 2) At the end of the day, you should ask yourself, why you are embarking on this effort of changing these values. If you have enough "trace data" to warrant these changes, then by all means. Otherwise, you may be setting yourself up for surprises in the future. Cheers, Gaja --- VIVEK_SHARMA <[EMAIL PROTECTED]> wrote: > How Good/advisable are the following 4 parameters' > Values in a Hybrid > Application? > > Are there any know ill-effects of the same? > > > > Application - Banking (Hybrid) > > Solaris 9 > > Oracle 9.2 > > > > > > optimizer_max_permutations=8000 > optimizer_index_cost_adj=10 > optimizer_index_caching=50 > optimizer_dynamic_sampling=4 > > > > > > > > Some INFO :- > > Database has 6000 Concurrent Users accessing > > We do ONLY INDEX Scans with exceptional FTS . > > FTS if present occur only on SMALL Tables (a few > Hundred Rows) > > FTS if unchecked greatly harm our performance > > Stripe Unit Size 64K > > Oracle Block Size 8K > > > > Will Give any info required > > > > Thanks > > > > > > > > = Gaja Krishna Vaidyanatha Principal Technical Product Manager, Application Performance Management, Veritas Corporation E-mail : [EMAIL PROTECTED] Phone: (650)-527-3180 Website: http://www.veritas.com __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Optimizer related init parameters
Gaja,List QUESTION IN CAPITALS BELOW :- Thanks indeed P.S. Welcome Back to the List . All have been missing you. -Original Message- Sent: Sunday, October 12, 2003 12:19 AM To: Multiple recipients of list ORACLE-L Vivek and list, I don't think any reasonable person will be able to say with a high-level of certainty whether the values that you have suggested, are optimal for your environment. The answer is a huge - IT DEPENDS. Having said that, here are some things you may want to take into consideration: 1) From a functionality perspective OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ were meant to do the same thing. It just happened to end up as 2 different parameters with 2 different code paths, which pretty much do the same(similar) thing. So usually, it is enought to set either one or the other, although setting both in my experience has generated no harm. So, if you want to optimizer to show bias towards index scans, then setting OPTIMIZER_INDEX_CACHING to a high value (90 or higher) will achieve that. Right now your value of 50, tells the optimizer that only 50% of the time, will it find index blocks in the DB buffer cache. This will affect the optimizer's decision making. Tim Gorman has a very simple formula to calculate the appropriate value on your system for OPTIMZER_INDEX_COST_ADJ, stated in his paper "Searching for intelligence in the Oracle Optimizer" (or something to that effect) on his site http://www.evdbt.com. It basically calculates a ratio of the average time for db file sequential read/db file scattered read from v$system_event, for your system. On a related topic, I think it is relevant to mention here that to carte-blanche curtail full-table-scans, may not work to the long-term benefit of your applications. However, I will assume here that you are aware of the core point - "amount of logical I/O" to be the most important (if not only) determinant when deciding whether FTS is better than index scans. Qs. COULD YOU GIVE SOME DETAIL ON THIS PLEASE (ABOVE PARA)? 2) John Kanagaraj did some work and testing to determine that setting OPTIMIZER_MAX_PERMUTATIONS to a low value (2000 if I remember right), has a positive impact on the plans that is generated, especially in an Oracle Apps environment. You should check it out. 3) Julian Dyke and Steve Adams have performed some good tests and research on OPTIMIZER_DYNAMIC_SAMPLING. But, I think the jury is still out on what the optimal value for this might be. I guess 4 is good enough. But, realize that this parameter is relevant when you have "partial statistics" in your schema. Otherwise, I don't think there is any impact of this parameter. Final notes: 1) All of these parameters can be set at the session level. I would urge you to perform extensive tests before making global init.ora changes. 2) At the end of the day, you should ask yourself, why you are embarking on this effort of changing these values. If you have enough "trace data" to warrant these changes, then by all means. Otherwise, you may be setting yourself up for surprises in the future. Cheers, Gaja --- VIVEK_SHARMA <[EMAIL PROTECTED]> wrote: > How Good/advisable are the following 4 parameters' > Values in a Hybrid > Application? > > Are there any know ill-effects of the same? > > > > Application - Banking (Hybrid) > > Solaris 9 > > Oracle 9.2 > > > > > > optimizer_max_permutations=8000 > optimizer_index_cost_adj=10 > optimizer_index_caching=50 > optimizer_dynamic_sampling=4 > > > > Some INFO :- > > Database has 6000 Concurrent Users accessing > > We do ONLY INDEX Scans with exceptional FTS . > > FTS if present occur only on SMALL Tables (a few > Hundred Rows) > > FTS if unchecked greatly harm our performance > > Stripe Unit Size 64K > > Oracle Block Size 8K > > > > Will Give any info required > > > > Thanks > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Optimizer related init parameters
Vivek, I wrote: >On a related topic, I think it is relevant to mention >here that to carte-blanche curtail full-table-scans, >may not work to the long-term benefit of your >applications. However, I will assume here that you >are aware of the core point - "amount of logical >I/O" to be the most important (if not only) >determinant when deciding whether FTS is better than >index scans. You asked: -- Qs. COULD YOU GIVE SOME DETAIL ON THIS PLEASE (ABOVE PARA)? My response to your question: - Logical I/O (db block gets + consistent gets) should be the primary determining factor on deciding whether a given SQL statement should use an index (or multiple indexes) or perform a full table scan. If an FTS on a table consumes 1000 blocks of logical I/O and the corresponding index scan consumes 1500 blocks of logical I/O, then, even though from response time perspective both execution plans may compare well, it is pretty evident that the 500 additional logical I/Os require 500 additional requests for the cache buffers chains latch. This is resource consumption (and potential for contention) that you should avoid. Thus, in this case, the FTS should be preferred over the index scan. So my point to you was : Do not deliberately PREVENT full-table scans, as logical I/O should drive the preferred execution plan for your queries. Hope that helps, Gaja --- VIVEK_SHARMA <[EMAIL PROTECTED]> wrote: > Gaja,List > > QUESTION IN CAPITALS BELOW :- > > Thanks indeed > > P.S. Welcome Back to the List . All have been > missing you. > > -Original Message- > Sent: Sunday, October 12, 2003 12:19 AM > To: Multiple recipients of list ORACLE-L > > Vivek and list, > > I don't think any reasonable person will be able to > say with a high-level of certainty whether the > values > that you have suggested, are optimal for your > environment. The answer is a huge - IT DEPENDS. > > Having said that, here are some things you may want > to > take into consideration: > > 1) From a functionality perspective > OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ > were meant to do the same thing. It just happened to > end up as 2 different parameters with 2 different > code > paths, which pretty much do the same(similar) thing. > So usually, it is enought to set either one or the > other, although setting both in my experience has > generated no harm. > > So, if you want to optimizer to show bias towards > index scans, then setting OPTIMIZER_INDEX_CACHING to > a > high value (90 or higher) will achieve that. Right > now > your value of 50, tells the optimizer that only 50% > of > the time, will it find index blocks in the DB buffer > cache. This will affect the optimizer's decision > making. > > Tim Gorman has a very simple formula to calculate > the > appropriate value on your system for > OPTIMZER_INDEX_COST_ADJ, stated in his paper > "Searching for intelligence in the Oracle Optimizer" > (or something to that effect) on his site > http://www.evdbt.com. It basically calculates a > ratio > of the average time for db file sequential read/db > file scattered read from v$system_event, for your > system. > > On a related topic, I think it is relevant to > mention > here that to carte-blanche curtail full-table-scans, > may not work to the long-term benefit of your > applications. However, I will assume here that you > are > aware of the core point - "amount of logical I/O" to > be the most important (if not only) determinant when > deciding whether FTS is better than index scans. > > Qs. COULD YOU GIVE SOME DETAIL ON THIS PLEASE (ABOVE > PARA)? > > > > 2) John Kanagaraj did some work and testing to > determine that setting OPTIMIZER_MAX_PERMUTATIONS to > a > low value (2000 if I remember right), has a positive > impact on the plans that is generated, especially in > an Oracle Apps environment. You should check it out. > > 3) Julian Dyke and Steve Adams have performed some > good tests and research on > OPTIMIZER_DYNAMIC_SAMPLING. > But, I think the jury is still out on what the > optimal > value for this might be. I guess 4 is good enough. > But, realize that this parameter is relevant when > you > have "partial statistics" in your schema. Otherwise, > I > don't think there is any impact of this parameter. > > Final notes: > > 1) All of these parameters can be set at the session > level. I would urge you to perform extensive tests > before making global init.ora changes. > > 2) At the end of the day, you should ask yourself, > why > you are embarking on this effort of changing these > values. If you have enough "trace data" to warrant > these changes, then by all means. Otherwise, you may > be setting yourself up for surprises in the future. > > > Cheers, > > Gaja > --- VIVEK_SHARMA <[EMAIL PROTECTED]> wrote: > > How Good/advisable are the following 4 parameters' > > Values in a Hybrid > > Application? > > > > Are there any know ill-effects of the same? > > > >
