DB_BLOCK_SIZE - netware 5- Oracle Workgroup server 7.3.2.0.0

[oradba]

Sir,

I am new to Oracle 7 Workgroup Server. I am using version 7.3.2.0.0 on a Novell Netware 5.x. I am using a Netinfinity server with on RAID 3 (15 GB) with duel processor. The server got 512M SDRAM and 100 MBPS Enet Card.

I have just created my INITSID.ORA. with the following settings.


DB_NAME=SID
Db_files=40
Control_files= path

Compatible=7.3.2.0.0
Db_file_multiblock_read_count=32
Db_block_buffers=4800
Db_block_size=8192
Shared_pool_size=201326592
Log_buffers=31457280
Processes=200
Dml_locks=500
Sequence_cache_entries=100
Sequence_cache_hash_buckets=89
Remote_login_passwordfile=shared
Os_authent_prefix=””
]checkpoint_process=true
log_checkpoint_timeout=0
log_checkpoint_interval=10000

Note: the OS Block size is 64k. I am unable to set the DB_BLOCK_SIZE to 64K (131072). It give the error ora-00374 and it is allowing us to set it to .5K to 8K. Is there any option to change the setting to 64 K. Is there any problem if we create the Oracle Block Size less then the OS Block Size. As per Oracle Manual. Oracle Block size should be multiples or OS Block Size. Pls guide. Awaiting your reply.


Also I would like you comment on my INIT parameter settings. Do you wan me to change any thing to this existing settings. Pls help.

Rgds
Sunil Varma
R.Sunil@hlcl.com

 

[karluk]

My understanding of db_block_size is that this parameter is fixed at database creation time and cannot be modified simply by changing the initsid.ora file. That means you need to find out what block size was used to create your database and then put that value in the db_block_size initialization parameter.

If you decide you really want to modify db_block_size you will need to drop and recreate your database.

 

[oradba]

Karluk,

I do agree with your statement. But FYI I have recreated the database with this init parameter file using
starup pfile=initsid.ora nomount. my os block size is 64k, which i can not change without a reinstalling Netware. But my Oracle block size can be change if i recreate the database which will not take much time. As per oracle manuals Oracle block size should be multipls of OS Block size. But Oracle is not allowing me to create an os block size of 64k. It allows me to create a block size in the range of .5K to 8K. I have two questions.
One. If i create a Oracle block size of 8K and OS block size of 64K will it degrade the performance?
Is it possible to make to Oracle block size to 64K? Is there any altranative methods avbl??

Pls help

Rgds
Sunil Varma
R.Sunil Varma

 

[karluk]

>One. If i create a Oracle block size of 8K and OS block size of 64K will it degrade the performance?

You very possibly might see a performance hit. Each physical read will retrieve 64K from the disk, but only 8K of that will be Oracle data. It looks as if the OS will have to do a lot of extra work because of this. An even bigger problem might be running out of disk space. 56K out of every 64K allocated to Oracle data files will be completely wasted, because Oracle can't use the entire block.

>Is it possible to make to Oracle block size to 64K? Is there any altranative methods avbl??

Unfortunately the answer to this is "no". Oracle 7 doesn't support 64K block sizes. That's why you are getting the ORA-00374 error. In fact I believe even Oracle 8 only supports a maximum of 32K block sizes, and not on every OS.

 

[MikeLacey]

well - if you have an oracle block size of 32k and and os block size of 64k -- when the database wants to read two (or more) blocks at once, which it does a lot, it will find that the block it wants is probably already in memory, assuming contiguous data files I suppose

 

[oradba]

MikeLacey & Karluk, thanks a lot for your reply. I got an idea now. I feel I must now reinstall netware 5 and configure it for 8K as the block size, so that both os& oracle got the same block size. do you recomment this settings. pls guide me if i am wrong..

Rgds
Sunil Varma
r.sunil@hlcl.com

 

[karluk]

If the only issue was your db_block_size I would say go ahead and reconfigure Netware 5 with an 8k block size. However another of your posts addresses the issue of Netware 5 compatibility with Oracle 7.3.2. Since our best information is that they aren't compatible I think you need to work with Oracle to identify a configuration that they support. For example you might end up with Netware 5 and Oracle 8. Then you could consider going to a 16k or 32k block size.

 

[karluk]

Hi Mike,
Your post indicates that you think Oracle will be able to pack multiple Oracle blocks into a larger OS block. You may be right, but I'm extremely skeptical. I think it's a lot more likely that Oracle will write a single Oracle block per OS block with resulting overhead and wasted disk space. I suspect that's why Oracle recommends that the Oracle blocksize be a multiple of the OS blocksize.

No doubt Sunil could test this if he has time.

 

[MikeLacey]

Hi Karl,

Yes, that's what I think, don't see why it shouldn't really, can you elaborate a bit?

I'm aware of space being wasted if you create a file not exactly the same size as the OS blocksize, and that sounds reasonable.

-- I've been wrong before mind you...

 

[karluk]

Hi Mike,

I think it's fairly clear that if db_block_size = 8k and OS block size = 64k that a single disk read will fetch 64k from disk, but only place 8k in Oracle's db buffer cache. However the OS undoubtedly has its own disk cache so the entire 64k will be placed in the OS cache. The question is whether the entire 64k contains Oracle blocks that can be passed to Oracle without doing another physical disk read.

Unfortunately the Oracle documentation is unclear on this point. The following is a relevant excerpt from the documentation.

"For good performance Oracle Block size should be made equal to or a multiple of the O/S blocksize. It is not sensible to have the size SMALLER than the OS blocksize as a single read will actually read in 'OS block size bytes' even if only part of this is passed on to Oracle."