Setting Oracle 8.0.6 Parameters

[Naveen Shetty]

Hi All ,

Would it possible for somebody to guide me with the correct values for below oracle parameters.

Setup:
---------

Server Type : HP 9000 N4000
CPU : 4 Nos.
Memory : 4GB
Database : SAP 4.0B / Oracle 8.0.6
Storage Box : Net Appliance F740 Model .
OS Type : HP- Unix 11.0

Kindly let me know the values for the below oracle parameters :

1. db_file_multiblock_read_count :
Now it is set to 4 .

2. disk_asynch_io :
Now it is set to False

3. spin_count :
Now set to 0.


Kindly suggest me the best values for these parameters as I am facing some performance prob with this config .


Waiting for replies

Thanks & Regards ,


Naveen Shetty.

[CHRIS ANORUO]

1. db_file_multiblock_read_count : 8 (Initial value).
2. disk_asynch_io : False is okay depending on your kernel configuration setting of fs_async being zero
3. spin_count : Now set to 0.

[CHRIS ANORUO]

1. db_file_multiblock_read_count : 32 (This is the recommended value i.e for a LARGE database)
You should not uncheck the parameter for _db_block_cache_protect=true, it has a severe effect on performance as it is memory buffer related.

[John Palmer]

Hi,

db_file_multiblock_read_count is only used for full table and index fast full scans. It should be set to 64K divided by your database block size. ie 8 for an 8k block size, 32 for a 2k block size.

disk_asynch_io should be true if asynchronous I/O to datafiles, controlfiles and logfiles is supported - that is you are using raw volumes rather than normal filesystem files. Oracle state that if this parameter is false then DBWR_IO_SLAVES should be set appropriately.

spin_count needs more investigation, you need to consult Oracle's documentation. I would question your value of zero however as Oracle state that the valid range is 1 - 1000000. Try running svrmgrl and type 'show parameter spin'. This will give you the actual value in use. On several of our databases, it seems to default to 2000.

Regards,
John

[CHRIS ANORUO]

1. db_file_multiblock_read_count : 32 (for 32 bit application) and 64 (for 64 bit application).

2. disk_asynch_io :(TYPE=boolean; VALUE IS SET TO TRUE) If asynchronous I/O to datafiles, controlfiles and logfiles is supported.

3. spin_count : (Type=Integer and value set to 2000)

[Naveen Shetty]

Hi ,

Thanks for all your replies .

Mr.John , thanks for the descp. on each values , but I was not able to find parameter db_block_cache_protect as you were indicating me to check .

Kindly revert back ..

Thanks in advance ...

[Steve Slade]

Naveen,

John is dead on with regards to the parms. The spin parameter is used to deal with latch conflicts for internal resources. The spin count indicates how long a process waits if the latch it wants is already in use. Oracle do not recommend that you change this from its default (of around 2000). Setting this to zero would have a detrimental performance effect.

These parms aside, have you changed any other parameters? If you are concerned with regards to performance a good starting point is the utlbstat reports. You may have run these already but ...

When the system is in 'normal' use, login as sysdba in server manager.
1. Turn on timed statistics ->
alter system set_timed_statistics=true;
This in itself has a slight performance hit, but is a necessary evil to make the stats worthwhile.

2. run the script @ ?/rdbms/rdbms/utlbstat

3. Wait for around 30mins to 1 hour. (It is not good to wait too long as the stats become too averaged, with different activity)

4. run the script @ ?/rdbms/rdbms/utlestat; This produce a report file called report.txt

5. Turn off timed statistics. alter system set_timed_statistics=false;

The report file is anoated (slightly), things to look out for are: ratios less than around 1; long wait times for non internal processes; latch/lock conflicts.

[CHRIS ANORUO]

Hi Naveen,

The db_block_cache_protect parameter can be found in $ORACLE_HOME/dbs/init***.ora file.