EBS 11i/R12/12.2 -- using a non-default block size for a tablespace

In this blog post, I will share you some recommendations and things to consider about using a non-default blocksize for a tablespace in Oracle EBS databases.

As you may already, the block size of an EBS database is 8K and it is a mandatory thing. However, as Oracle let us have different block size tablespace in a database, we can have bigger block sized tablespace in our EBS databases, even tough the EBS database block size is set 8K.

It is mandatory to  have 8k block sizes, in other words having the db_block_size init.ora parameter EBS databases , so this means, the standard tablespaces that comes with EBS must remain in 8K, but this also means, we can use non default tablespace block size for our custom application schemas,as having non-default block sized tablespaces is supported by Oracle.
In the early days, the ability to have non-default tablespaces may be invented for Transportable tablespaces, but as the new releases have released (11g and 12c), this ability have involved and now it is supported to be used in any case.

One use case may be when there is a need to have a new schema in this database and this new schema will be used by ETL processes / BI like reports, we can create a 32k block sized tablespace and put the db objects of this schema to this 32k tablespace to get the advantage of the following documented things;

Larger oracle block sizes (big tablespace block sizes or db_block_size) typically give fewer index levels and hence improved index access times to data. A single I/O will fetch many related rows and subsequent requests for the next rows will already be in the data buffer. This is one of the major benefits of a larger block size. Another benefit is that it will decrease the number of splits.

However, no one including Oracle can guarantee if having a 32k tablespace for a custom schema in an EBS database is a good thing or bad thing, as it depends on the load and profile of the environment.

For the standard EBS applications, it is guaranteed by Oracle , as they made 8k block size as a mandatory thing. On the hand, as for the custom applications/schemas, having a non default tablespace size may provide  3 results. That is, it may increase the performance, it may decrease the performance or the effect of it can not be felt (almost change in performance)

As for the rule of thumb, there are 3 points;

Use db_block_size = 8192 for most of the transactional processing systems.

Use db_block_size of larger than 8KB for OLTP systems in which your data structures are large. (avoiding change and migrated blocks)

Use db_block_size of larger values than 8KB for systems in which your undo generation is not a meaningful part of your workload. (Data warehouses)

So should we have non-default block sizes for custom tablespaces, which are used by reporting, BI , ETL applications then?

Well, the answer is it depends and if we can this problem as an issue, then the solution is "testing". So, it is supported, but in order to to decide whether it will be good or bad in terms of performance, the only thing that can be done is to test it.