Customer Portal

Oracle Block Size - Important Tuning


3D Oracle Block and OS Block

Most Advisable
Tips and technical articles of Oracle DBA
November, 2012   |  Oracle DBA Tips  |  Author: Mark Carter

Sizing concept of Oracle Block

I frequently go on diminutive projects to client sites, mostly to tune a database that is given that dreadful performance. While investigative the Oracle database side, I come across various database block sizes ranging from 2K to 32K. Then inquired about the matching OS block size, the DBA reacts with a gesticulation and a mournful "Do we concern?" look.

Many important parameters in the Oracle initialization parameter file depend on the database block size, which in revolve absolutely depends on the operating system block size. DBAs who recognize the Oracle Universal installer’s default setting 8k or unambiguously set the DB_BLOCK_SIZE exclusive of look upon for the OS block size risk a considerable performance drawback and wastage of resource utilization. Always be aware of your Operating System block size as DBA and it is best practice. If you didn’t have this detail while creating the new database, there is a possibility that your database block size is not set optimally.

During creation of the new database, the Oracle block size (off course it is logical) is one of the most important setting, since this parameter is virtually consistent during the lifetime of the system, sysaux, and undotbs tablespaces in database. Make sure that you are armed with the OS block size before you go on board on this one-way trip. Make the database block size at least correspondent to the OS block size, if not some multiple thereof. If your Oracle server uses raw devices, think the logical OS block size, which can diverge at the carefulness of the system administrator. By default, mist logical volume managers LVMs set the logical OS block size to 8K. So if your site utilizes an LVM, possibilities are that it is place to the default size of 8k. In such scenarios, it is desirable to set the database block size to at least 8k.

If you supervise an OLTP application and desire to set a more granular database block size, like 4k, you should reconstruct your file system with the new OS block size of 4k, physically via the mkfs command execution or using a menu-driven system administration tool. This is alike to having to reconstruct the database in order to modify database block size. All this might appear like a lot of administrative burden. Nevertheless, for ensuring database performance improvement and availability, there is no sacrifice too great.

Also, as was mentioned before, setting the DB_BLOCK_SIZE to a multiple of the OS block size many induce pre-reading, since reading even a single database block causes multiple OS blocks to be read. In OLTP environments where read-ahead is not desired, set the DB_BLOCK_SIZE to be equivalent to the OS block size not a multiple of the OS block size.

We are offering high quality remote database services to maintain Oracle databases for improving performance and high availability. We offer guaranteed response time during our database support and remote dba activity. Our Oracle experts team provides 24/7 database monitoring with solid troubleshooting Oracle support. For more details, contact our sales department using contact us form.

Mark Carter
- Oracle DBA
Back to DBA Tips Articles
Copyright Dbametrix Solutions © 2005. All Rights Reserved.
Back to page top