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

Appropriate size of Hp-ux block while creating Oracle database ( OLTP or DSS)

Mr. Sandeep Kapare
Occasional Visitor

Appropriate size of Hp-ux block while creating Oracle database ( OLTP or DSS)

hi all,

Is there any hard or fast rule for configuring ur HP-UX 11i box for creating oracle datawarehouse with a block size of 32k ?

thanks in advance.
4 REPLIES
twang
Honored Contributor

Re: Appropriate size of Hp-ux block while creating Oracle database ( OLTP or DSS)

Usually for OLTP systems is advisable to have smaller block size assuming that data requirement for information tend to be small and granular (Not every process is using the same information of the same block). Contrary, for DSS were reads tend to be large, a bigger database block size will help in caching more information in memory with less I/O accesses.
As large table scans are common ( both indexed and full ) on DSS, if your OS BS is 8k then your database blocksize should be a factor of this , say 16k. Yes it will cause more I/O at the OS level but if your data is well maintained and not fragmented then most of the blocks would be contiguous and the OS would not have to perform a lot more work. Also what will help is if your Disks have a lot of read cache. If you have Full table scans going on make sure you have a good Muti block read count setting as this will help speed up those scans.

I have found the max for block size on an VXFS file system is 8k. In your case, if you use 32K DB_BLOCK_SIZE, there is relatively less overhead, thus more room to store useful data, good for sequential access, or very large rows, therefore it is ok for a DSS.
Indira Aramandla
Honored Contributor

Re: Appropriate size of Hp-ux block while creating Oracle database ( OLTP or DSS)

Hi,

There is no hard / fast rule for configuring your HP-UX 11i box of that block size should it be for oracle database.

For good performance Oracle Block size should be made equal to or a multiple of the O/S blocksize. Normally Oracle DB_BLOCK_SIZE will be in 4K, 8K, 16K....etc

In an OLTP system you generally pick out only a few records to work on in any particular transaction/query. The plan normally used would only pull in one or two blocks in order to pick out 3 or 4 records from them. The I/O time is greater to pull in a large block, merely to get a few rows. Block size 8K is more appropriate for an OLTP application.

Balance DB_block_size with Operating System Block Size. 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.

Matching the Oracle and OS block sizes results in one Oracle IO block request being one OS IO request. For a good argument for db_block_size = fs_block_size.


IA
Never give up, Keep Trying
Khalid A. Al-Tayaran
Valued Contributor

Re: Appropriate size of Hp-ux block while creating Oracle database ( OLTP or DSS)


Hi,

We have it as 8K. As recommended by SAP.
Hari Kumar
Trusted Contributor

Re: Appropriate size of Hp-ux block while creating Oracle database ( OLTP or DSS)

I agree with the very good info above
and a few line here
Oracle recommends 8k block size if u are uncertain and that for Large Transaction Processing Environments
Smaller Block size:
When rows are small and random acces alot.
Good for OLTP
Larger Block Size :
When rows are large and sequential access.
Good for DSS
the scale for Block size is as

8K 16K 32K 64K
<---OLTP DSS --->
Information is Wealth ; Knowledge is Power