| Oracle 10g Parameters (Performance related) [message #168512] |
Thu, 20 April 2006 16:41  |
SMISHRA
Messages: 5
Registered: April 2006
Location: Canada
|
Junior Member |
|
|
I would like to ask some Oracle/Siebel experts about few Oracle 10g Parameter settings.We are running Siebel7.7 on Oracle 10g Rel 1(10.1.0.4).We didn't find any document from Siebel, about Oracle Parameters setting.I mean there are few words here and there and they are for Oracle 9i not 10g specific.
The following are the parameters I would like to take opinions.
Note: We have gathered the stat for 'all index columns' and didn't define any bucketsize so it will be default bucket-size.
granularity => 'ALL',
cascade => TRUE,
degree => DBMS_STATS.DEFAULT_DEGREE
1. Optimizer_Mode: Should it be set to Choose or All_Rows (they said should be set to All_Rows). At the moment we have set to CHOOSE.
2.OPTIMIZER_FEATURES_ENABLE - Its related to MVG's performance.
3.OPTIMIZER_MAX_PERMUTATIONS = 100 (default is 2000), for Oracle 9i Siebel said, it should be set to 100 but in 10g this parameter is Obsolete. Any input on this parameter?
4.OPTIMIZER_INDEX_COST_ADJ - 1 (default is 100)
5.DB_FILE_MULTIBLOCK_READ_COUNT
We are running into issues like some screens sucks into CBO but not in RBO.I would like to ask about the ideal Oracle Parameters settings, before looking into bad performing SQL's
Any input on above or additional parameter would be highly appreciated.
Thanks,
SM
|
|
|
|
| Re: Oracle 10g Parameters (Performance related) [message #168699 is a reply to message #168512] |
Fri, 21 April 2006 12:37  |
markmal
Messages: 113
Registered: April 2006
Location: Toronto, Canada
|
Senior Member |
|
|
| SMISHRA wrote on Thu, 20 April 2006 17:41 |
I would like to ask about the ideal Oracle Parameters settings, before looking into bad performing SQL's
|
1. There are no "ideal Oracle Parameters settings".
2. Looking for values for parameters before looking for problematic SQLs is wrong approach.
Find your most problematic SQL, look why it runs wrong, fix it, if it is impossible, add/remove indexes, try to use Query Substitution option, then change global parameters if needed.
1. Optimizer_Mode: Should it be set to Choose or All_Rows (they said should be set to All_Rows). At the moment we have set to CHOOSE.
Looks quite OK. But whay you do not rust vendor's advise?
2.OPTIMIZER_FEATURES_ENABLE - Its related to MVG's performance.
3.OPTIMIZER_MAX_PERMUTATIONS = 100 (default is 2000), for Oracle 9i Siebel said, it should be set to 100 but in 10g this parameter is Obsolete. Any input on this parameter?
This limits a number of different variants that optimizer tries when it is searching the best plan. Too low value can cause sub-efficient plans for complex SQLs. Too high value can cause "slow thinking" effect, too slow plan creation. Although quality of plan will be better.
I think 100 is toooo low.
4.OPTIMIZER_INDEX_COST_ADJ - 1 (default is 100)
mmmm. I think too low.
5.DB_FILE_MULTIBLOCK_READ_COUNT
value?
do not make it too big for OLTP application (which your Siebel product is). Make it moderate.
|
|
|
|
| Re: Oracle 10g Parameters (Performance related) [message #168836 is a reply to message #168512] |
Sun, 23 April 2006 13:24 |
madhusunkara
Messages: 59
Registered: March 2006
Location: hyderabad,india
|
Member |
|
|
optimizer_index_cost_adj is one usefull parameter setting this low will cause CBO to prefer Index scans over full-table scans a value between 10 to 30 will give good results, frequent changes to this parameter is not suggestable in production environment , if you have non deafult blocksize tablespaces it might be good idea to relocate some of the indexes to appropriate tablespaces, check cache_buffer_chains problem before doing.
>> We are running into issues like some screens sucks into CBO
check out what those sessions are actually doing, OWI can help you,enable trace for that session may be level 12, you can get better idea
Thanks
Madhu
|
|
|
|
|
|
| Re: Oracle 10g Parameters (Performance related) [message #266859 is a reply to message #169049] |
Tue, 11 September 2007 17:18 |
vivsiv
Messages: 1
Registered: September 2007
|
Junior Member |
|
|
Hi ,
were you able to get the parameters right for 10g settings on siebel environment. we are still having difficulties in doing so. for some reason the cache is not getting used properly meaning first time if i hit a screen in siebel its slow but the next time its fast . after say about couple of hours its again slow (I guess its getting cached ) after which its fast again..the DBA seem to be confused ..can u throw some of your parameters that you tweaked on 10g .
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
