Managing blocks in Buffer Cache

The blocks in the buffer cache are basically managed in a single place with two different lists pointing at them:
• The dirty list of blocks that need to be written by the database block writer
(DBWn; we’ll take a look at that process a little later)
• A list of nondirty blocks

The X$BH table shows information about the blocks in the block buffer cache. Here, we can see the touch count get incremented as we hit blocks. Oracle will attempt to increment the TCH once every three seconds (there is a TIM column that shows the last update time to the TCH column), but it is not considered important that the number be 100 percent accurate, as it is close. Also, Oracle will intentionally “cool” blocks and decrement the TCH count over time.


Query to find out top five hot blocks:

select tch, file#, dbablk,
case when obj = 4294967295
then 'rbs/compat segment'
else (select max( '('||object_type||') ' ||
owner || '.' || object_name ) ||
decode( count(*), 1, '', ' maybe!' )
from dba_objects
where data_object_id = X.OBJ )
end what
from (
select tch, file#, dbablk, obj
from x$bh
where state <> 0
order by tch desc
) x
where rownum <= 5
/

(or)

select tch, file#, dbablk, DUMMY
from x$bh, (select dummy from dual)
where obj = (select data_object_id
from dba_objects
where object_name = 'DUAL'
and data_object_id is not null)

Creating a tablespace with a different block size other than the default:

SQL>create tablespace ts_16k
datafile size 5m
blocksize 16k;
create tablespace ts_16k

*
ERROR at line 1:
ORA-29339: tablespace blocksize 16384 does not match configured blocksizes

SQL> show parameter 16k
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0

Here the tablespace does not get created since I have configured db_16k_cache_size parameter. Inorder to create a tablespace with different cache size other than the default cache that is for the SYSTEM and all TEMPORARY tablespaces ( you can have upto four other blocksize) you need to shrink your db_cache_size if it set to a large value:


Ex:
SQL> show parameter db_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 1G

SQL> alter system set db_cache_size = 768m;

System altered.

SQL> alter system set db_16k_cache_size = 256m;

System altered.

SQL> create tablespace ts_16k
datafile size 5m
blocksize 16k;

Tablespace created.

Here the two cache sizes db_cache_size and db_16k_cache_size are exclusive; if one “fills up,” it cannot use space in the other. These multiple blocksizes were not intended as a performance or tuning feature, but rather came about in support of transportable tablespaces—the ability to take formatted data files from one database and transport or attach them to another database. They were implemented in order to take data files from a transactional system that was using an 8KB blocksize and transport that information to a data warehouse using a 16KB or 32KB blocksize.The transactional data would benefit from the smaller blocksizes due to less contention on the blocks (less data/rows per block means less people in general would go after the same block at the same time) as well as better buffer cache utilization (users read into the cache only the data they are interested in—the single row or small set of rows). The reporting/warehouse data, which might be based on the transactional data, would benefit from the larger blocksizes due in part to less block overhead (it takes less storage overall), and larger logical I/O sizes perhaps. And since reporting/warehouse data does not have the same update contention issues, the fact that there are more rows per block is not a concern, but a benefit.

Note: For example, if after startup your SGA size was 128MB and you wanted to add an additional 64MB to the buffer cache, you would have had to set the SGA_MAX_SIZE to 192MB or larger to allow for the growth.