Parameters maintenance for Oracle 10.2.0.4 on Solaris 10 zone

[Former Member]

Hi!

We use SAP Solution Manager 7.0 on Solaris 10 Zone with Oracle.

We have updated Oracle from 10.2.0.2 to 10.2.0.4.

What are the parameter recommendation for Oracle 10.2.4?

What is the approach to do this?

Thank you very much!

regards

Thom

[Former Member]

SAP note 1171650 contains a really useful parameter check that can assist pretty well in finding parameters that are set different from the SAP standard recommendation.

OPTIMIZER_DYNAMIC_SAMPLING = 6 can be very useful for OLAP systems where many statements are executed with literals rather than bind variables. This dynamic sampling level is used to determine column dependencies.

Whenever you come to the conclusion that you have to set a parameter different from the SAP standard, it is good if you open a SAP customer message in order to clarify the necessity.

Kind regards

Martin

[former_member204746]

refer top SAP note 830576.

problems I had with this note:

when parameter OPTIMIZER_DYNAMIC_SAMPLING was set to "6", I had major performance problems on big tables. unsetting this to the default value of 2 fixed the problem.

on a BW system, I had to set OPTIMIZER_INDEX_COST_ADJ to 20, even if SAP tells you no to do on an OLAP system.

This is my experience on my systems and results may vary on your system.

[Former Member]

Hi Eric,

can you describe in more technical details how to apply?

[stefan_koehler]

Hello Eric,

> when parameter OPTIMIZER_DYNAMIC_SAMPLING was set to "6", I had major performance problems on big tables. unsetting this to the default value of 2 fixed the problem.

That is really interesting - for the Dynamic Sampling Levels itself take a look at here:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#i43032

For the block sampling rules - take a look at here:

http://www.dbazine.com/oracle/or-articles/hotka3

Regards

Stefan

[former_member204746]

how to apply? do this:

sqlplus / as sysdba

show parameter pfile

if parameter is empty, this means that you are using file init[SID].ora. so, simply edit it with "vi"

if parameter is not empty, check Markus' previous answer to learn how to apply using sqlplus.

SAP note 830576 is very long to understand because it contains links to another list of SAP notes, read all of them, prepare yourself and try the settings.

[former_member204746]

Thanks Stefan, this info was really interesting.

[markus_doehr2]

Parameters don't differ between a zoned or a not-zoned installation.

Make sure you have an /etc/project set up and assigned the users using /etc/user_attr to that project so Oracle can run.

Markus

[Former Member]

Hello,

Thank you very much.

Does the SAP installation usually include the setup of oracle parameters?

Is there some automatic report that check and set up the valeus to the rigtht values, e.g. according to SAP note 830576?

What are the crucial/important parameters?

If not, what are the technical steps to do this manually?

Thank you!

Thom

[markus_doehr2]

> Does the SAP installation usually include the setup of oracle parameters?

partly.

> Is there some automatic report that check and set up the valeus to the rigtht values, e.g. according to SAP note 830576?

No. Many of those parameters (the events mainly) depend on specific patches that need to be installed to activate them. Did you apply all the interim patches for 10.2.0.4 or just that patch?

> What are the crucial/important parameters?

all of them are more or less important. Oracle is a complex beast.

> If not, what are the technical steps to do this manually?

You can use brtools to change them one by one - or write a script like the following. However, this implies that all single/interim/cumulative patches are installed, not only 10.2.0.4.

Check note 1137346 - Oracle Database 10g: Patches for Release 10.2.0.4 for the single patches and read all the linked notes to the patches so you can built your own script to set the parameters.

This is an example script for HP-UX but you could use a likewise one for Solaris:

alter system set
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',
	'10891 trace name context forever, level 1',
      '14532 trace name context forever, level 1',
      '38068 trace name context forever, level 100',
	'38085 trace name context forever, level 1',
	'38087 trace name context forever, level 1',
	'38095 trace name context forever, level 1',
	'10411 trace name context forever, level 1',
	'44951 trace name context forever, level 1014',

comment='M. Doehr 16.09.2008'
scope=spfile

alter system set HP_UX_SCHED_NOAGE = 178

alter system set open_cursors = 1500 scope=spfile;

alter system set optimizer_index_caching = 50 scope=spfile;

alter system set optimizer_index_cost_adj = 20 scope=spfile;

alter system set parallel_max_servers = 40 scope=spfile;

alter system set query_rewrite_enabled = 'false' scope=spfile;

alter system set star_transformation_enabled = 'true' scope=spfile;

alter system set "_b_tree_bitmap_plans" = false scope=spfile;

alter system set "_bloom_filter_enabled" = false scope=spfile;

alter system set "_fix_control" =	'5705630:ON',
						'5765456:7',
						'6221403:ON',
						'6440977:ON',
						'6626018:ON',
						'6972291:ON'		
						 scope=spfile;

alter system set "_index_join_enabled" = false scope=spfile;

alter system set "_optim_peek_user_binds" = false scope=spfile;

alter system set "_optimizer_mjc_enabled" = false scope=spfile;

alter system set "_sort_elemination_cost_ratio" = 10 scope=spfile;

alter system set "_table_lookup_prefetch_size" = 0 scope=spfile;

alter system set "_in_memory_undo" = false scope=spfile;

[Former Member]

Hi Markus,

I applyed all the necessary patches for 10.2.4.

[Former Member]

follow Note 1166258 - Parameter recommendations for Oracle 10.2.0.4

cheers,

-Sunil