The data block is the smallest unit of Oracle storage. All Oracle Input and Output (I/O) operates on the basis of one or more data blocks.
The data block is based on the underlying blocks in the operating system, as shown in the following diagram.
Communication between Data Blocks and OS Blocks in Oracle DBMS
In Oracle Database Management System (DBMS), communication between data blocks and operating system blocks occurs indirectly through a series of well-defined processes and memory structures. The primary goal of this interaction is to facilitate efficient reading and writing of data between the Oracle database and the underlying operating system's storage subsystem.
The following elements play a vital role in this communication:
Buffer Cache: The buffer cache, located in the System Global Area (SGA), serves as an intermediary between data blocks and operating system blocks. It is a memory area that temporarily stores copies of data blocks read from disk, allowing the database to quickly access frequently used data without incurring the overhead of disk I/O operations.
Database Writer (DBWn) Process: The Database Writer (DBWn) process is responsible for writing modified data blocks from the buffer cache to the operating system blocks on disk. DBWn writes data blocks to disk based on several criteria, such as when a checkpoint occurs, when the buffer cache runs out of free buffers, or when a block is no longer needed in the cache. By asynchronously writing data to disk, the DBWn process ensures that the database can continue processing transactions without waiting for disk I/O operations to complete.
Direct Path I/O: In some cases, such as bulk data loading operations, Oracle bypasses the buffer cache and writes data blocks directly to operating system blocks on disk. This direct path I/O mechanism improves performance by reducing the overhead of managing the buffer cache for large-scale data operations.
Log Writer (LGWR) Process:
While not directly involved in the communication between data blocks and operating system blocks, the Log Writer (LGWR) process plays a crucial role in ensuring data consistency and durability. LGWR writes transactional redo data to the online redo log files, which are subsequently used to recover the database in case of a crash or other failures. This process ensures that all committed transactions are recorded on disk, even if the corresponding data blocks have not yet been written by the DBWn process.
Read Consistency: Oracle DBMS maintains read consistency by using undo data to present a consistent view of the database to each transaction, regardless of concurrent modifications by other transactions. This mechanism ensures that data block changes made by one transaction are not visible to other transactions until the changes are committed.
The communication between data blocks and operating system blocks in Oracle DBMS is facilitated through a series of processes and memory structures, such as the buffer cache, DBWn, direct path I/O, LGWR, and read consistency mechanisms. These elements work together to ensure efficient data access, consistency, and durability in the Oracle database environment.
You can set the size of the Oracle data block for the entire database when you create the database.
A data block is made up of one more operating system blocks.
The size of the operating system block depends on the particular operating system on which you are running Oracle.
Configuring data blocks
The default data block size for an Oracle database is 2 KB, but Oracle can support data blocks up to 32 KB with standard hardware, and more with some hardware. Most databases use data blocks that are at least 4 KB.
The size of a data block affects the overall performance of your Oracle system.
The larger the size of the data block, the more information is read, at a minimum, and the more operating system I/O operations may be necessary. If your database is used with mostly smaller rows, reduce the number of I/O operations by setting the data block size smaller.
If your database is used with mostly larger pieces of data, such as BLOBs[1], set the data block size larger to avoid unnecessary additional I/O.
The next lesson discusses Oracle segments.
[1]BLOB: A binary large object (BLOB) is a large piece of data.