Multiple Block Sizes

There are three sources of delay for disk IO.

• The time required for the read-write head to position itself under the appropriate cylinder or read-write head movement time.
• Rotational delay as the read-write head waits for the desired block
• The data transmission time from the disk back to the Oracle SGA.

For an OLTP database, there are many transactions happening constantly where small amount of data is read each time. So if the data is stored in tablespace with smaller block size then the amount of RAM memory required for caching the data and the disk IO will be less. If your rows are SMALL and you use a large blocksize,when you fetch a block you may get lots of rows that you are not interested in. For those databases that fetch small rows randomly from the disk, the Oracle DBA can segregate these types of tables into 2K Tablespaces.

In a data warehousing system the data is loaded on a periodic interval via a batch job. Most of the activity in an OLAP database comes from complex queries that aggregate large sets of data. Here there are no transactions dealing with small amount of data and hence larger block size will be beneficial. For those tables that contain raw, long raw, or in-line LOBs, moving the table rows to large block size will have an extremely beneficial effect on disk I/O since the entire row may fit in a single DB block. This reduces the overhead associated with chained rows. Larger oracle block sizes typically give fewer index levels and hence improved index access times to data . This is one of the major benefits of a larger block size.

Tables that experience large-table full-table scans will benefit from the larger block size. The initialization parameter db_file_multiblock_read_count specifies the maximum number of blocks read in one I/O operation during a full table scan. Data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is very high.

The DB_CACHE_SIZE parameter is used to define the size of the buffer cache for the standard block size. An individual buffer cache must be defined for each non-standard block size used.

DB_2K_CACHE_SIZE  = xM
DB_4K_CACHE_SIZE  = xM
DB_8K_CACHE_SIZE  = xM
DB_16K_CACHE_SIZE = xM
DB_32K_CACHE_SIZE = xM

CREATE TABLESPACE <tablespace-name> BLOCKSIZE 2048;

The KEEP and RECYCLE buffer caches can only be defined for the standard block size. In 9i these are defined using DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE, with their memory allocations being totally separate to the main buffer cache.

A number of rules apply to the use of variable block sizes:

• All partitions of a partitioned object must reside in tablespaces with the same block size.
• All temporary tablespaces must be of the standard block size.

• Consider an OLTP database that randomly reads single customer rows. Larger blocks means reading unwanted rows and hence a waste of data buffer resources. If we migrate this customer table into a 2k blocksize, we now only need to read-in 2k to get the row data. This results in several times more available space for random block fetches.
• Moving Oracle indexes into a fully-cached 32k buffer will ensure that Oracle favors index access, reducing unnecessary full-table scans and greatly reducing logical I/O because adjacent index nodes will reside within the larger, 32k block.
• For OLTP transactions with heavy DML activities, Small blocksize tables are beneficial in cleaning-out “direct blocks” that result from truncate operations and high activity DML.
• Tables with heavy write activities will benefit from smaller block sizes.
• Smaller blocksize can reduce conflict for data block resource where large number of users are updating the same table simultaneously
• For tables wih LOB's atored outside the table segment, each LOB row takes one block. If the average size of the LOB is around 2K then the LOB segment should stored in 2K tablespace

If the table contains several millions of rows and if there are lots of DML happening then index in a larger block size will be better. If the number of rows are less than a million then normal block size will be enough.

How to choose the correct block size

The following queries can be used to detemine the correct block size for a table
Some of the parameters are

1) Amount of reads via FULL TABLE SCANS
2) Amount of reads per transaction via TABLE ACCESS BY ROWID
3) Amount of writes per transaction
4) The average row length
5) Number of concurrent DML's

The following SQL's can be used to obtain some read/write statistics
The first query is used to get total amount of reads happening via FUll table Scans
The second query calculates the amount of reads per transaction for acces by rowid's


Calculate the cost of FULL Table Scans
--------------------------------------
select OBJECT_OWNER||'.'||OBJECT_NAME,
sum(CR_BUFFER_GETS+CU_BUFFER_GETS) 
from v$sql_plan_statistics_all 
where 
operation='TABLE ACCESS' and 
options='FULL' 
group by OBJECT_OWNER||'.'||OBJECT_NAME 
order by 2
/


Calculate the cost of Index by rowid
------------------------------------
select OBJECT_OWNER||'.'||OBJECT_NAME,
sum((CR_BUFFER_GETS+CU_BUFFER_GETS))/sum(EXECUTIONS),
from v$sql_plan_statistics_all
where 
EXECUTIONS>0 and 
operation='TABLE ACCESS' and 
options<>'FULL' 
group by OBJECT_OWNER||'.'||OBJECT_NAME 
order by 2
/


Write statistics
----------------
select OBJECT_OWNER||'.'||OBJECT_NAME,
sum(disk_writes)/sum(EXECUTIONS),
from v$sql_plan_statistics_all
where 
EXECUTIONS>0 and 
group by OBJECT_OWNER||'.'||OBJECT_NAME 
order by 2
/

Non-Standard Block Sizes