Oracle Block Size - Important Tuning

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.

Hiring senior Oracle DBA process is time consuming and costly. After hiring senior remote dba experts, there is no guarantee of stability of him or her. Due to this reason, nowadays company prefers remote DBA services. Company would get professional expert remote DBA support in lowest cost and without risk of stability. Dbametrix is worldwide leader remote DBA experts team and offering uninterrupted stable professional remote dba support to worldwide clients. You can trust on our remote dba work. Contact our sales department for more query without technical specification and requirement SLA.