Oracle Parameter Recommedation

[former_member182034]

Dear Oracle Experts,

I have upgraded Oracle 10204 to 10205 and now i want to alter the oracle parameter while system Specs is:

Processors: x3690 X5, Xeon 8C E7-2820 105W 2.00GHz with 2 Processor

RAM: 32 GB

current initSID.ora

prd.__db_cache_size=738197504
*._OPTIM_PEEK_USER_BINDS=FALSE
*.compatible='10.2.0'
*.control_file_record_keep_time=30
*.db_block_size=8192
*.db_cache_size=4294967296
*.db_files=254
*.db_name='PRD'
*.dml_locks=4000
*.event='10191 trace name context forever, level 1'
*.FILESYSTEMIO_OPTIONS='setall'
*.job_queue_processes=1
*.log_buffer=1048576
*.log_checkpoint_interval=0
*.log_checkpoints_to_alert=true
*.open_cursors=800
*.optimizer_features_enable='10.2.0.1'
*.parallel_max_servers=160
*.pga_aggregate_target=629145600
*.processes=280
*.sessions=560
*.sga_max_size=5368709120
*.shared_pool_reserved_size=72461844
*.shared_pool_size=724618444
*.sort_area_retained_size=0
*.sort_area_size=2097152
*.statistics_level='typical'
*.undo_management='AUTO'
*.undo_retention=43200
*.undo_tablespace='PSAPUNDO'
*.user_dump_dest='G:\oracle\PRD\saptrace\usertrace'
*.workarea_size_policy='AUTO'

there are may parameters are not configure with respect to SAP Note 830576 - Parameter recommendations for Oracle 10g.

while my system is OLTP(SAP ECC 6.0) and please give me your expert suggestion with respect to 10.2.0.5 about following parameters.

STAR_TRANSFORMATION_ENABLED
 _FIX_CONTROL
EVENT
 _INDEX_JOIN_ENABLED
DB_FILE_MULTIBLOCK_READ_COUNT
OPEN_CURSORS
OPTIMIZER_INDEX_COST_ADJ 
PARALLEL_THREADS_PER_CPU
PGA_AGGREGATE_TARGET
SHARED_POOL_SIZE
_FIRST_SPARE_PARAMETER 

Regards,

[former_member188883]

Hi,

Please run the script attached to SAP Note 1171650. Check what value it proposes in your system for below mentioned parameters. You may need to adjust the parameters accordingly.

Regards,

Deepak Kori

[former_member182034]

hi Deepkorri,

please confirm me...

the result of that script is:

• INFORMATION 1 *** Parametercheck for Oracle 10.2. based on Note/Version: 830576/221

• INFORMATION 2 *** Parametercheck last changed: 2011-12-22

• INFORMATION 3 *** Parametercheck Execution: 2012-02-06 14:49:10

• INFORMATION 4 *** DB Startup: 2012-02-04 18:00:16

• INFORMATION 5 *** DB SID: PRD

• INFORMATION 6 *** DB Environment: OLTP, not RAC

• INFORMATION 7 *** DB Platform: Microsoft Windows x86 64-bit

• INFORMATION 8 *** DB Patchset: 10.2.0.5.0

• INFORMATION 9 *** Last detectable DB Mergefix: from SAP Bundle Patch (released 2011-06-10)

• INFORMATION 10 *** Reliability checks: events passed, fixcontrols passed

• INFORMATION 11 *** WARNING: Extended support for 10.2.0.5 only available with a special extended support contract (note 1431752)

there are main four columns(Name, Recommendation, IS_VALUE, SHOULD_BE_VALUE) in Result of the SELECT statement

now I have to change the parameter value according Recommendation.

Recommendation Columns consists value with

OK mean I have no need to change it

Automatic Check OK mean ?

IS_VALUE mean current value?

SHOULD_BE_VALUE mean must this value?

_fix_control (5099019)	add with value "5099019:ON"	5099019:ON
_fix_control (5705630)	add with value "5705630:ON"	5705630:ON
_fix_control (5765456)	add with value "5765456:3"	5765456:3
_fix_control (6055658)	add with value "6055658:OFF"	6055658:OFF
_fix_control (6399597)	add with value "6399597:ON"	6399597:ON
_fix_control (6430500)	add with value "6430500:ON"	6430500:ON
_fix_control (6440977)	add with value "6440977:ON"	6440977:ON
_fix_control (6626018)	add with value "6626018:ON"	6626018:ON
_fix_control (6670551)	add with value "6670551:ON"	6670551:ON
_fix_control (6972291)	add with value "6972291:ON"	6972291:ON
_fix_control (7692248)	add with value "7692248:ON"	7692248:ON
_fix_control (7891471)	add with value "7891471:ON"	7891471:ON
_fix_control (9196440)	add with value "9196440:ON"	9196440:ON
_fix_control (9495669)	add with value "9495669:ON"	9495669:ON
_fix_control (4728348)	check if default value " " is suitable (set to 4728348:OFF if Winbundle<=5)	set to 4728348:OFF if Winbundle<=5
_fix_control (6120483)	ok (is not set; mentioned with other prerequisites/not mentioned in note)	
_fix_control (6221403)	ok (is not set; mentioned with other prerequisites/not mentioned in note)	
_fix_control (6329318)	ok (is not set; mentioned with other prerequisites/not mentioned in note)	
_fix_control (6660162)	ok (is not set; mentioned with other prerequisites/not mentioned in note)	
_fix_control (7325597)	ok (is not set; mentioned with other prerequisites/not mentioned in note)	

event

event (38087)	add with value "38087 trace name context forever, level 1"	38087 trace name context forever, level 1
event (10183)	add with value "10183 trace name context forever, level 1"	10183 trace name context forever, level 1
event (10027)	add with value "10027 trace name context forever, level 1"	10027 trace name context forever, level 1
event (10028)	add with value "10028 trace name context forever, level 1"	10028 trace name context forever, level 1
event (10629)	add with value "10629 trace name context forever, level 32"	10629 trace name context forever, level 32
event (14532)	add with value "14532 trace name context forever, level 1"	14532 trace name context forever, level 1
event (10142)	add with value "10142 trace name context forever, level 1"	10142 trace name context forever, level 1
event (38068)	add with value "38068 trace name context forever, level 100"	38068 trace name context forever, level 100
event (38085)	add with value "38085 trace name context forever, level 1"	38085 trace name context forever, level 1
event (44951)	add with value "44951 trace name context forever, level 1024"	44951 trace name context forever, level 1024
event (10411)	check if default value " " is suitable (set with level 1 if Winbundle>=3)	set with level 1 if Winbundle>=3
event (10191)	ok (is set correctly =)	10191 trace name context forever, level 1
event (10049)	ok (is not set; mentioned with other prerequisites/not mentioned in note)	
event (10091)	ok (is not set; mentioned with other prerequisites/not mentioned in note)	
event (10162)	ok (is not set; mentioned with other prerequisites/not mentioned in note)	
event (10753)	ok (is not set; mentioned with other prerequisites/not mentioned in note)	
event (10891)	ok (is not set; mentioned with other prerequisites/not mentioned in note)	

how and where(initSID.ora) i can set the value of above event and _fix parameters?

Regards,

[Former Member]

Easiest way to set this parameter;

Stop the database

SQL> create pfile from spfile;

add the entries into the init<DBSID>.ora, as below;

fixcontrol='4728348:OFF','5099019:ON','5705630:ON',...

event='10027 trace name context forever, level 1','10028 trace name context forever, level 1',...

Then execute the command, below;

SQL> create spfile from pfile;

Start the database

Best regards,

Orkun Gedik

[former_member182034]

thx Orkun,

please have look on remaining parameters

NAME	RECOMMENDATION	IS_VALUE
optimizer_index_cost_adj	add with value "20"	100
star_transformation_enabled	add with value "TRUE"	FALSE
_optimizer_mjc_enabled	add with value "FALSE"	
_sort_elimination_cost_ratio	add with value "10"	
parallel_execution_message_size	add with value "16384"	2152
parallel_threads_per_cpu	add with value "1"	2
query_rewrite_enabled	add with value "FALSE"	TRUE
_disable_objstat_del_broadcast	add with value "FALSE"	
log_archive_dest_1	add with value "LOCATION=[drive]:\oracle\PRD\oraarch\PRDarch"	
log_archive_format	add with value "%t_%s_%r.dbf"	ARC%S_%R.%T
max_dump_file_size	add with value "20000"	UNLIMITED
optimizer_features_enable	delete (is set; not to be set as explicitly mentioned in note)	10.2.0.1
log_archive_dest	delete (is set; not to be set as explicitly mentioned in note)	F:\oracle\PRD\oraarch\PRDarch
_fix_control (4728348)	check if default value " " is suitable (set to 4728348:OFF if Winbundle<=5)	
_b_tree_bitmap_plans	check if default value " " is suitable (set to FALSE if Winbundle<=2)	
_first_spare_parameter	check if default value " " is suitable (set to 1 if Winbundle>=3)	
db_writer_processes	check if default value "4" is suitable (change default in case of dbwr problems only)	4
control_files	check if value "G:\ORACLE\PRD\SAPDATA1\CNTRL\CNTRLPRD.DBF, ..." is suitable (three copies on different disk areas)	G:\ORACLE\PRD\SAPDATA1\CNTRL\CNTRLPRD.DBF, ...
db_cache_size	check if value "4294967296" is suitable (appropriately set)	4294967296
parallel_max_servers	check if value "80" is suitable (Number of DB machine CPU CORES*10)	80
pga_aggregate_target	check if value "629145600" is suitable (appropriately set)	629145600
remote_os_authent	check if value "TRUE" is suitable (set to TRUE on ABAP stack systems with a Unix App. Server without SSFS (note 1622837))	TRUE
dml_locks	check why set but mentioned with other prerequisites/not mentioned in note	4000
job_queue_processes	check why set but mentioned with other prerequisites/not mentioned in note	1
log_checkpoint_interval	check why set but mentioned with other prerequisites/not mentioned in note	0
remote_login_passwordfile	check why set but mentioned with other prerequisites/not mentioned in note	EXCLUSIVE
sga_max_size	check why set but mentioned with other prerequisites/not mentioned in note	5368709120
shared_pool_reserved_size	check why set but mentioned with other prerequisites/not mentioned in note	72461844
sort_area_retained_size	check why set but mentioned with other prerequisites/not mentioned in note	0
sort_area_size	check why set but mentioned with other prerequisites/not mentioned in note	2097152
statistics_level	check why set but mentioned with other prerequisites/not mentioned in note	typical
workarea_size_policy	check why set but mentioned with other prerequisites/not mentioned in note	AUTO
shared_pool_size	automatic check ok; doublecheck if value "738197504" is suitable (appropriate value between 400MB and 4GB)	738197504
undo_retention	automatic check ok; doublecheck if value "43200" is suitable (appropriately set)	43200

please mention the value of above parameter and if you think that i have to remove some parameters then please mention those one. 😛

Regards,

[former_member188883]

Hi,

Following parameters needs to be added

optimizer_index_cost_adj	add with value "20"	100
star_transformation_enabled	add with value "TRUE"	FALSE
optimizermjc_enabled	add with value "FALSE"	
sortelimination_cost_ratio	add with value "10"	
parallel_execution_message_size	add with value "16384"	2152
parallel_threads_per_cpu	add with value "1"	2
query_rewrite_enabled	add with value "FALSE"	TRUE
disableobjstat_del_broadcast	add with value "FALSE"	
log_archive_dest_1	add with value "LOCATION=[drive]:\oracle\PRD\oraarch\PRDarch"	
log_archive_format	add with value "%t_%s_%r.dbf"	ARC%S_%R.%T
max_dump_file_size	add with value "20000"	UNLIMITED

Following parameters needs to be removed

optimizer_features_enable	delete (is set; not to be set as explicitly mentioned in note)	10.2.0.1
log_archive_dest	delete (is set; not to be set as explicitly mentioned in note)

Perform the changes in pfile.

Then create spfile from pfile as mentioned by Orkun.

Regards,

Deepak Kori

[former_member182034]

thx deepkorri nd orkun for your expert suggestion.

please suggest the value of below parameters with respect to Processor and RAM

Processors: x3690 X5, Xeon 8C E7-2820 105W 2.00GHz with 2 Processor

RAM: 32 GB

parallel_max_servers = 160

Process = 280

Session= 560

db_cache_size = 4294967296

sga_max_size = 5368709120

shared_pool_reserved_size = 72461844

shared_pool_size =738197504

pga_aggregate_target = 629145600

sort_area_size =2097152

Thanks and Best Regards,

[former_member188883]

Hi,

The script from the SAP note has projected the values based on your current available resources and database version.

No further changes required on the proposed values.

Regards,

Deepak Kori

[former_member182034]

hi dear,

would you please suggest me about following parameters because these parameter is not set accordingly to

32GB RAM.

specially:

db_cache_size = 4294967296
sga_max_size = 5368709120

and

sga_target 
shared_pool_reserved_size = 72461844
shared_pool_size =738197504
pga_aggregate_target = 629145600
sort_area_size =2097152

how can i distribute this RAM(32GB) within Oracle, Window OS and SAP?

Regards,

[former_member182034]

hi Dear,

SQL> create spfile from pfile;

File created.

SQL> startup
ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed

now i have to remove above parameter because getting error.

please have a look about initSID.ora and if you find any issue/correction then please suggest me?

prd.__db_cache_size=738197504
*._OPTIM_PEEK_USER_BINDS=FALSE
*._optimizer_mjc_enabled=FALSE
*._disable_objstat_del_broadcast=FALSE
*._sort_elimination_cost_ratio=10
*._fix_control='4728348:OFF','5099019:ON','5705630:ON','5765456:3','6221403:ON','6329318:ON','6399597:ON','6430500:ON','6440977:ON','6626018:ON','6670551:ON','6972291:ON','7325597:ON','7692248:ON','7891471:ON','9495669:ON'
*.background_dump_dest='G:\oracle\PRD\saptrace\background'
*.compatible='10.2.0'
*.control_file_record_keep_time=30
*.control_files='G:\oracle\PRD\origlogA\cntrl\cntlrPRD.dbf','G:\oracle\PRD\origlogB\cntrl\cntrlPRD.dbf','G:\oracle\PRD\sapdata1\cntrl\cntrlPRD.dbf'
*.core_dump_dest='G:\oracle\PRD\saptrace\background'
*.db_block_size=8192
*.db_cache_size=4294967296
*.db_files=254
*.db_name='PRD'
*.dml_locks=4000
*.event='10027 trace name context forever, level 1','10028 trace name context forever, level 1','10183 trace name context forever,level 1','10191 trace name context forever, level 1','10629 trace name context forever,level 32','14532 trace name context forever, level 1','38068 trace name context forever, level 100','38087 trace name context forever, level 1','10753 trace name context forever, level 2','10411 trace name context forever, level 1','10142 trace name context forever, level 1','38085 trace name context forever, level 1','44951 trace name context forever, level 1024','10049 trace name context forever, level 2'
*.FILESYSTEMIO_OPTIONS='setall'
*.job_queue_processes=1
*.log_archive_dest='F:\oracle\PRD\oraarch\PRDarch'
*.log_buffer=1048576
*.log_archive_format=%t_%s_%r.dbf
*.max_dump_file_size=20000
*.log_checkpoint_interval=0
*.log_checkpoints_to_alert=true
*.open_cursors=800
*.optimizer_index_cost_adj=20
*.parallel_max_servers=160
*.parallel_execution_message_size=16384
*.parallel_threads_per_cpu=1
*.pga_aggregate_target=629145600
*.query_rewrite_enabled=FALSE
*.processes=280
*.recyclebin='off'
*.remote_login_passwordfile='exclusive'
*.remote_os_authent=true
*.star_transformation_enabled=TRUE
*.sessions=560
*.sga_max_size=5368709120
*.shared_pool_reserved_size=72461844
*.shared_pool_size=724618444
*.sort_area_retained_size=0
*.sort_area_size=2097152
*.statistics_level='typical'
*.undo_management='AUTO'
*.undo_retention=43200
*.undo_tablespace='PSAPUNDO'
*.user_dump_dest='G:\oracle\PRD\saptrace\usertrace'
*.workarea_size_policy='AUTO'

Regards,

[Former Member]

Hi Jamil,

>> would you please suggest me about following parameters because these parameter is not set accordingly to

32GB RAM.

I recommend you configure the values, below;

Oracle=9 GB

SAP=19 GB

OS=4 GB

You can configure these values, regarding you needs, by monitoring statistical values in the further months

Best regards,

Orkun Gedik

[former_member182034]

hi,

last question of this post :)...

as Orkun said that 9 GB for Oracle

please suggest the value of below parameters with respect to 9GB (Total 32GB) RAM mean how much memory i have to assign these critical parameters :

db_cache_size =

sga_max_size =

and

shared_pool_reserved_size =

shared_pool_size =

pga_aggregate_target =

sort_area_size =

may i have to assign the value in sequence of these parameter fixcontrol and event if yes then which sequence i have to follow mean number wise or data wise , please confirm below parameter setting. are these OK or not?

*._fix_control='4728348:OFF','5099019:ON','5705630:ON','5765456:3','6055658:OFF','6399597:ON','6430500:ON','6440977:ON','6626018:ON','6670551:ON','6972291:ON','7692248:ON','7891471:ON','9196440:ON','9495669:ON'

and

*.event='10027 trace name context forever, level 1','10028 trace name context forever, level 1','10142 trace name context forever, level 1','10183 trace name context forever, level 1','10191 trace name context forever, level 1','10629 trace name context forever, level 32','14532 trace name context forever, level 1','38085 trace name context forever, level 1','38068 trace name context forever, level 100','38087 trace name context forever, level 1','44951 trace name context forever, level 1024'

please confirm above sequence is OK or not.

Thanks and Best Regards,,

[former_member204746]

last part of the question is duplicated at http://forums.sdn.sap.com/thread.jspa?threadID=2133036&tstart=0

I am closing that one here.