How to set DB_BLOCK_size parameter

[稀里哗啦]

I have a FC60 as storage, using RAID 0+1, stripe size is 8k,How to set DB_BLOCK_size parameter?

[稀里哗啦]

sorry, it is DB_SIZE .

[Barbara Spadaro_1]

Can I assume we're talking about an Oracle database? If so, which version are you running?
The initialisation parameters are set in the init.ora parameter file. And if you're using 8i, that would be DB_BLOCK_SIZE. This is set when you create a database and cannot be changed later.

[Andreas D. Skjervold]

Hi

Set the following in your init.ora file before createing the database:

DB_BLOCK_SIZE = 8192

This sets the Oracle block size to 8K, which is the most used size for most kinds of database types.

Andreas

[稀里哗啦]

Maybe I confused you in early message.
my problem is which DB_BLOCK_SIZE value can get the max performance?
I think because the stripe of FC60 is 8k, may be (8k*stripe number) is good for performance.
But I can't sure is it right.

[Barbara Spadaro_1 · Accepted solution]

The DB_BLOCK_SIZE parameter should be equal to or a multiple of your OS block size - but not smaller.
Then it's a case of what data are you storing? If your rows are small, then a larger block size means you're caching more blocks for the same number of DB_BLOCK_BUFFERS and memory usage increases. But if your rows are large, a larger block size means you're less likely to incur overhead from row chaining. It should also improve index access as there's less index levels.
Ultimately it's all a trade-off and you need to decide what you can live with/sacrifice.
Don't forget their are other parameters that can impact on your performance, eg shared_pool_size,sort_area_size,etc.
It's all good fun....

[Yogeeraj_1]

hi,

I think this document will also help you in your task. It is about Configuring storage subsystems for the Oracle databases.

Hope this Helps!

Best Regards
Yogeeraj

[T G Manikandan]

DB_BLOCK_SIZE parameter is a very crucial one for the database.
Once the database is created the block size parameter cannot be altered.

There are a lot of calls which are unanswered with this parameter sizing reagarding the performance.

I just searched metalink.oracle.com to get some info

//*


Related:

Oracle recommends that this parameter be set to a numeric power of 2
when the database is created. eg: 2048, 4096 , 8192 or 16384

The best setting of the parameter DB_BLOCK_SIZE depends on the type of data
you are looking at, the application and implementaion.
This parameter affects the maximum value of the FREELISTS storage
parameter for tables and indexes. DSS (data warehouse) database
environments tend to benefit from larger block size values.

The main considerations are as follows:

1. Balance 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.

2. Balance with application data
If your rows are SMALL and you use a large blocksize, when you
fetch a block you may get lots of rows that you are (probably)
not interested in.

Larger blocks mean more rows cached for the same number
of DB_BLOCK_BUFFERS (taking up more memory).

If your application has LONG row lengths choosing a larger
blocksize may allow the entire row to fit in a single DB
block. This reduces the overhead associated with chained
rows. Be careful here because INSERTS will always try to
fit a row entirely into a block rather than chaining it across
blocks so the extra space from a larger block size may be
wasted.

3. Index Branches
Larger oracle block sizes typically give fewer index levels
and hence improved index access times to data. This is one
of the major benefits of a larger block size.

4. Locking/Block Contention
With larger blocks processes are more likely to want access to
the same block hence potentially increasing block contention.
Use larger values for INITRANS / MAXTRANS to help safeguard
against this.


It is recommended that the stripe size should never be smaller than the maximum size of an Oracle I/O request. The maximum size of the Oracle I/O request is in turn determined by the Oracle database block size (DB_BLOCK_SIZE) and the Oracle multi_block read count initialization parameter (DB_FILE_MULTIBLOCK_READ_COUNT).
For example, if DB_BLOCK_SIZE is 8k and the DB_FILE_MULTIBLOCK_READ_COUNT is 4, the maximum size of an Oracle I/O request will be 32k.
But always, there is an OS limit on this maximum size of I/O request. Most OSs restrict it to 64K.
Again, to determine the DB_BLOCK_SIZE, the type of application is to be considered. An OLTP system which has many random reads/writes would best use a small DB_BLOCK_SIZE and the stripe size should be a multiple of it. A DSS system would benefit more from a large DB_BLOCK_SIZE.
*//

check this site if you have access.