Role of Oracle Data Blocks in Oracle 19c

1. Fundamental Unit of Storage
   Oracle Data Blocks (or database blocks) are the smallest unit of storage in an Oracle database. They store table data, indexes, undo information, and other database objects. Each data block corresponds to a specific amount of physical storage on disk.
2. Data Block Structure
   An Oracle data block consists of several components:
   • Header (Block Metadata) – Contains information like transaction details, row directory, and block type.
   • Table Directory – Stores information about the tables that own the rows in the block (useful in clustered tables).
   • Row Directory – Maintains pointers to row data within the block.
   • Free Space – Unused space in the block for future inserts or updates.
   • Data Space – Contains actual row data.
3. Block Size and Performance Considerations
   • The default block size varies but is typically 8 KB in Oracle 19c.
   • A database can have multiple block sizes (e.g., 2 KB, 4 KB, 8 KB, 16 KB, 32 KB), which is useful for performance tuning.
   • Larger blocks (16 KB, 32 KB) are efficient for full-table scans, while smaller blocks (2 KB, 4 KB) reduce contention in OLTP systems.
4. Oracle Data Blocks and Transactions
   • Each block stores row-level data and manages concurrency through ITL (Interested Transaction List) entries.
   • Data blocks maintain Undo Information to allow rollback and provide read consistency.
   • Oracle’s Automatic Undo Management (AUM) helps optimize undo block allocation.
5. Data Block Recovery in Oracle 19c
   • Media Recovery: If a data block becomes corrupted, it can be recovered using RMAN Block Media Recovery (RECOVER BLOCK).
   • Redo and Undo Logs: Oracle tracks changes at the block level using redo logs to ensure crash recovery.
   • DBMS_REPAIR: This package helps diagnose and repair corrupted blocks in non-critical situations.

Conclusion Oracle "data blocks are critical" for efficient storage, transaction management, and recovery operations. Understanding their structure helps in performance tuning, troubleshooting corruption issues, and optimizing database recovery strategies in Oracle 19c.
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.

1. Oracle data block: Can be based on one or more operating system blocks, but is the same for the entire database.
2. Operating system blocks: The operating system block is the smallest unit of operating system I/O.

In Oracle 19c, communication between data blocks (at the database level) and operating system blocks (at the storage level) occurs through buffered I/O and direct I/O mechanisms managed by the Oracle Database Storage Architecture. This interaction primarily happens through the Database Buffer Cache, the Redo Log Buffer, and the Storage Management layer.

1. Key Communication Layers
   • Oracle Data Blocks (Logical Storage)
     • The smallest unit of storage in the database.
     • Typically 2 KB, 4 KB, 8 KB, 16 KB, or 32 KB, depending on the block size setting.
     • Managed by tablespaces, segments, and extents.
   • Operating System Blocks (Physical Storage)
     • Defined by the underlying OS and filesystem.
     • Often 512 bytes, 4 KB, or more, depending on the OS.
     • Data must be aligned properly for efficient reads/writes.
2. Types of Communication Between Data Blocks and OS Blocks
   • a. Buffer Cache Interaction (Logical Reads & Writes)
     • Oracle first checks the Database Buffer Cache in the SGA (System Global Area).
     • If a data block is already cached, it is read directly from memory (logical read).
     • If not, Oracle fetches it from disk (physical read) and loads it into the buffer cache.
   • b. Direct Path Reads and Writes (Bypassing Buffer Cache)
     • Used in parallel query execution, large table scans, and direct-path inserts.
     • Bypasses the buffer cache and directly interacts with the OS blocks.
   • c. Checkpointing and Dirty Block Writes
     • When a transaction modifies a block, it is marked as "dirty" in the Database Buffer Cache.
     • Oracle periodically writes dirty blocks back to the data files using the DBWR (Database Writer) process.
     • The DBWR process interacts with the OS storage layer to write data blocks to OS blocks.
   • d. Redo Log and Write-Ahead Logging
     • Before a data block modification is written to disk, a corresponding redo entry is written to the Redo Log Buffer.
     • The LGWR (Log Writer) process flushes the redo logs to the redo log files.
     • This ensures data recoverability and crash recovery.
   • e. ASM (Automatic Storage Management) Layer
     • If using ASM (Automatic Storage Management), Oracle interacts with the OS storage through ASM Disks.
     • ASM manages striping and mirroring at the block level.
3. Optimization Techniques for OS Block Interaction
   • DB_BLOCK_SIZE: Configured based on workload (e.g., OLTP vs. Data Warehousing).
   • DB_WRITER_PROCESSES: Can be tuned to optimize block writes.
   • DIRECT_PATH_READ: Used for bulk data operations to avoid cache overhead.
   • ASYNC I/O: Improves database performance by allowing non-blocking I/O.

Conclusion The communication between Oracle Data Blocks and OS Blocks is managed through multiple layers, including buffer cache management, direct path operations, redo logging, and storage architecture. The efficiency of this interaction is crucial for database performance, consistency, and recoverability.

In Oracle 19c, there is a distinction between data blocks and system blocks:

1. Data Blocks
   • Also known as Oracle Blocks or Logical Blocks.
   • These are the smallest units of I/O storage in an Oracle database.
   • They store table rows, index entries, and other user data.
   • The size of a data block is determined by the DB_BLOCK_SIZE parameter (e.g., 8KB, 16KB, 32KB).
   • Each data block consists of:
     • Header: Metadata like block type, SCN, transaction information.
     • Table Directory: Tracks table structures if multiple tables share the block.
     • Row Directory: Stores row pointers.
     • Free Space: Space available for new data.
     • Data Area: Stores actual row data.
2. System Blocks
   • Typically refer to blocks within the SYSTEM and SYSAUX tablespaces that store Oracle metadata.
   • Contain data dictionary information, undo segments, and PL/SQL objects.
   • Managed by Oracle internally and not directly used by user data.
   • Critical for database operation and should be protected against corruption.

Key Differences

Feature	Data Blocks	System Blocks
Purpose	Stores user table and index data	Stores Oracle metadata and dictionary information
Tablespace	Any user-defined tablespace (e.g., USERS, DATA)	SYSTEM and SYSAUX tablespaces
Management	Managed based on segment allocation	Managed internally by Oracle
Modification	Modified when user data changes	Modified when database structures or metadata change

Conclusion While both types of blocks exist within an Oracle database, data blocks are for storing actual user data, whereas system blocks hold crucial database metadata and system information. Proper management of system blocks ensures database integrity, while data block tuning impacts performance and storage efficiency.

Index segments are objects designed to increase the speed of data accessed in a table. Using a library analogy, think about how a card catalog works.
Suppose we want to find where the book, "Professional Oracle" is located in the library. We can wander around the library and hope to stumble upon this book or we can go to the card catalog to find its location. The card catalog is an index of all the books available in the library including their locations and we can jump to a listing of library books starting with the letter "O" to quickly find our book. Once our book has been located with the card catalog, we can look up the unique decimal number of the book that provides a path to the physical location of the book we want.
Back to index segments, an index contains the value for one or more columns in a table. Furthermore, a ROWID is used to identify values located in any corresponding columns. What this means is that Oracle can access data directly from a table by first looking up the ROWID in an index. Indexes are not always the most efficient way for retrieving data and this particularly holds true for smaller tables that require less than 100 data blocks. Oracle 8i provides many types of index, of which the most commonly used are:

1. B*-Tree,
2. reverse key,
3. bitmapped, and
4. function-based.

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.

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.