Re: Changing DB_BLOCK_SIZE

dbsgrm_at_Arco.COM (Gary R Meyers(214)754-4191) writes:

>Greetings, folks. We are running Oracle on a SUN 6/670, and are involved in
>doing some application and platform performance testing.
>We would like to tune Oracle up as best we can before beginning tests.
 

>My question has to do with DB_BLOCK_SIZE. Currently, it is set at the default
>for SUN, 2K. In speaking with the application vendor about optimizing our site,
>I have been advised to, among other things, increase the block size to 8192.
>Also, to increase DB_BLOCK_BUFFERS to 8192.
 

>The main reason for considering the block size change is that previous tests
>seem to indicate that a 12 user load is about the limit for this particular
>application on this platform.
 

>I have also been told before (not by this vendor) not to fool with the block
>size. Sooooooo, I would appreciate any words of wisdom the sages of netland
>might have--- Pros and cons.
 

>You can email me --- dbsgrm_at_Arco.COM, or post to the net.
 

>Thanks in advance for the help.
 

>Gary Meyers (214)754-4191
>dbsgrm_at_Arco.Com
>--
>* Gary Meyers gary.meyers_at_merlin.edm.arco.com
>* ARCO Exploration &
>* Production Technology

The argument I heard from an Oracle consultant last week goes as follows:

Increasing the size of db_block_size should help a system that is I/O bound. The reason is that you will be moving larger blocks (ie. 8K) of data to/from disk to memory. The theory with larger blocks, is that there is some probability that a block needed is already in memory. When we asked if we should alter db_block_buffers (the number of blocks in memory), the consultant responded no if we had enough memory to support it. In your case, going from a 2K to 8K block size, would quadruple the size of your buffer cache (same number of blocks, only 4 times bigger). You certainly could increase the number of blocks, db_block_buffers, if you have enough memory to support it without affecting application performance. (For example, on a system with virtual memory, you would not want to increase the size of the buffer cache if that made the application swap).

Your best bet is to figure out what the bottleneck is with 12 users on the system. Is it CPU, memory, or I/O. How well tuned is the application? The biggest performance gains are often with tuning an application. If you haven't already, take a look at the ORACLE RDBMS Performance Tuning Guide for some tips.

Good luck. Tuning is a challenge.

---
  /_ _   /		sequent computer system, inc.	+-----+
 / / /  /__/ __		15450 sw koll parkway		|   # |
/ / /  / \__(_/_/_/	beaverton, or  97006-6063	|     |
mary kay petersen/	mkay_at_sequent.com 		+-----+
(503)578-5817 __/