One of our Production database has a rather peculiar block size of 16KB. This database was created about 2 years ago and at that time the size of the db was approx 1TB only.
However over the past 2 years the db size has grown to approx 5TB.

The workload is a mix of OLTP (transaction) as well as OLAP (reporting).

This db block size is causing our DB Buffer Pool to be constantly filled up (95%~100% full).
The db uses ASM on Raw Devices.

Is the 16KB db block size sub-optimal?
Can this large block size be adversely impacting our storage sub-system performance?
If so what can be done to remedy the situation?

Noteworthy here is that we do in fact perform huge full table scan for reporting as well as for our application (business logic).
The OLTP nature stems from the fact that our application also has transactional (financial transaction) module.


This db block size is causing our DB Buffer Pool to be constantly filled up (95%~100% full)

Your DB Buffer Pool should always be full unless you have recently restarted your server - Oracle will not purge blocks from the cache unless it has to in order to make room for other blocks.

This in itself is not an indication that changing the block size will improve anything. You need to know more about the cache misses.

If the OLTP side of your workload can query any part of your 5TB data, optimizing the buffer pool a little for it will probably not make much difference (but may unnecessarily slow down your big sequential scans that it was presumably optimized for)

On the other hand, if there is a small subset of the data hit regularly, and cache misses for the OLTP workloads can be substantially reduced by reducing the block size (which will depend on whether the reads are really scattered across blocks or not), then perhaps the benefits would be significant.

In the end you will have to test and find out :)

| improve this answer | |
  • You are correct. My mistake, i did not put forth my point more clearly. Our Buffer cache is constantly full but what is important to note here is that our average row-length varies between 60B to 220B. Yes monitoring the cache miss will give us a good idea the usefulness of the 16KB block size – Jack Aug 25 '11 at 20:55
  • So are there lots of single row queries scattered over the entire data set? A low cache hit ratio on it's own does not necessarily mean a problem – Jack Douglas Aug 25 '11 at 20:59
  • The workload can be summarized as- Large Reads (Indexed and FTS) then Table/view joins and aggregations based on certain filters, this gives us the target data set for the next stage of the application. This next stage is purely oltp in nature with single row reads and writes happening. – Jack Aug 25 '11 at 21:28
  • "single row reads and writes" from what percentage of the 5TB - all of it or just a subset? – Jack Douglas Aug 25 '11 at 21:48
  • The single reads and writes constitute a significantly small portion of our db. most of these tables are iot. However the queries executing the single row reads/writes are pretty complex and perform heavy reads from other tables for checking the business logic conditions before carrying out update/insert on these tables. – Jack Aug 25 '11 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.