Home » RDBMS Server » Server Administration » Oracle 10g Parameters (Performance related)
Oracle 10g Parameters (Performance related) [message #168512] Thu, 20 April 2006 16:41 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #169049 is a reply to message #168699] Mon, 24 April 2006 18:36 Go to previous messageGo to next message
SMISHRA
Messages: 5
Registered: April 2006
Location: Canada
Junior Member
Thanks all, who replied to my ques.

SM
Re: Oracle 10g Parameters (Performance related) [message #266859 is a reply to message #169049] Tue, 11 September 2007 17:18 Go to previous messageGo to next message
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 .
Re: Oracle 10g Parameters (Performance related) [message #266871 is a reply to message #168512] Tue, 11 September 2007 21:51 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

So bad formatted............
Previous Topic: very urgent please help me error (merged)
Next Topic: configure perfstat
Goto Forum:
  


Current Time: Sun Dec 04 14:37:44 CST 2016

Total time taken to generate the page: 0.10019 seconds