Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Setting Oracle 8.0.6 Parameters

Naveen Shetty
Frequent Advisor

Setting Oracle 8.0.6 Parameters

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.

Queries
7 REPLIES
CHRIS ANORUO
Honored Contributor

Re: Setting Oracle 8.0.6 Parameters

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.
When We Seek To Discover The Best In Others, We Somehow Bring Out The Best In Ourselves.
CHRIS ANORUO
Honored Contributor

Re: Setting Oracle 8.0.6 Parameters

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.
When We Seek To Discover The Best In Others, We Somehow Bring Out The Best In Ourselves.
John Palmer
Honored Contributor

Re: Setting Oracle 8.0.6 Parameters

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
Honored Contributor

Re: Setting Oracle 8.0.6 Parameters

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)


When We Seek To Discover The Best In Others, We Somehow Bring Out The Best In Ourselves.
Naveen Shetty
Frequent Advisor

Re: Setting Oracle 8.0.6 Parameters

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 ...
Queries
Steve Slade
Frequent Advisor

Re: Setting Oracle 8.0.6 Parameters

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.
If at first you do not succeed. Destroy all evidence that you even attempted.
CHRIS ANORUO
Honored Contributor

Re: Setting Oracle 8.0.6 Parameters

Hi Naveen,

The db_block_cache_protect parameter can be found in $ORACLE_HOME/dbs/init***.ora file.
When We Seek To Discover The Best In Others, We Somehow Bring Out The Best In Ourselves.