These are steps for checking and updating the oracle parameters against
SAP’s recommendations.
I. First, check the current parameters against these SAP Notes:
830576
601157
1171650
Generally, the parameter file is stored here:
/oracle/102_64/dbs/spfile.ora
II. Make a backup copy of the current spfile with these steps:
Log into the system as the adm account and run this command to enter
Oracle: sqlplus / as sysdba
Within SQLPlus, create the backup with this command:
create pfile=’/tmp/spfile_’ from spfile;
Type ‘exit’ to leave SQLPlus
III. Create a script to apply the needed updates.
Here is an example (note that yours will be different):
ALTER SYSTEM SET "_optim_peek_user_binds"=false SCOPE=SPFILE;
ALTER SYSTEM SET "_push_join_predicate"=false SCOPE=SPFILE;
ALTER SYSTEM SET "_table_lookup_prefetch_size" = 0 SCOPE=SPFILE;
ALTER SYSTEM SET "_bloom_filter_enabled" = false SCOPE=SPFILE;
ALTER SYSTEM SET "_fix_control" = '4728348:OFF' SCOPE=SPFILE;
ALTER SYSTEM SET "_in_memory_undo" = false SCOPE=SPFILE;
ALTER SYSTEM SET "_index_join_enabled" = false SCOPE=SPFILE;
ALTER SYSTEM SET "_optimizer_mjc_enabled" = false SCOPE=SPFILE;
ALTER SYSTEM SET "_sort_elimination_cost_ratio" = 10 SCOPE=SPFILE;
ALTER SYSTEM SET EVENT='10027 trace name context forever, level 1',
'10028 trace name context forever, level 1',
'10081 trace name context forever, level 1',
'10183 trace name context forever, level 1',
'10191 trace name context forever, level 1'
comment='Recommended by SAP NOTE 830576' SCOPE=SPFILE;
For expediency, I have generally just cut and paste this into the Oracle session.
This can also be directed into SQL plus.
IV. Verifying the Parameters Against SAP’s Recommendations
Open the text file in SAP Note 1171650
Log into the SAP system, run transaction SE38, and execute program RSORADJV
In the text box, enter the text from SAP Note 1171650 and execute the program.
Review the recommendations to ensure all parameters match the requirements.
[Note: blog copied from my self hosted site (it runs on FreeBSD at http://www.haynesworld.net/wurd]
