Logical storeage Hierarchy In Oracle

Oracle Database allocates logical space for all data in the database.The Oracle database is divided into increasingly smaller logical units to manage, store, and retrieve data efficiently and quickly. The logical storage management of the database’s data is independent of the physical storage of the database’s physical files on disk. This makes it possible for changes you make to the physical structures to be transparent to the database user or developer at the logical level.logical storage structures, including segments, extents, and data blocks.
 

Tablespaces
The data in an Oracle database are stored in tablespaces. 

-- An Oracle database can be logically grouped into smaller logical areas of space known as tablespaces. 

-- A tablespace can belong to only one database at a time.

-- Each tablespace consists of one or more operating system files, which are called data files.

-- A tablespace may consist of zero or more segments.

-- Tablespaces can be brought online while the database is running.

-- Except for the SYSTEM tablespace or a tablespace with an active undo segment, tablespaces can be taken offline, leaving the database running.

-- Tablespaces can be switched between read-write and read-only status.

Data Files 

-- Each tablespace in an Oracle database consists of one or more files called data files. These are physical structures that conform with the operating system on which the Oracle server is running.

-- A data file can belong to only one tablespace.

-- An Oracle server creates a data file for a tablespace by allocating the specified amount of disk space plus a small amount of overhead.

-- The database administrator can change the size of a data file after its creation or can specify that a data file should dynamically grow as objects in the tablespace grow.

Segments 

-- A segment is the space allocated for a specific logical storage structure within a tablespace. For example, all of the storage allocated to a table is a segment.

-- A tablespace may consist of one or more segments.

-- A segment cannot span tablespaces; however, a segment can span multiple data files that belong to the same tablespace.

-- Each segment is made up of one or more extents.
 

Extents
Space is allocated to a segment by extents.

-- One or more extents make up a segment.

• When a segment is created, it consists of at least one extent.
• As the segment grows, extents get added to the segment.
• The DBA can manually add extents to a segment. 

-- An extent is a set of contiguous Oracle blocks.

-- An extent cannot span a data file but must exist in one data file.

Data BlocksThe Oracle server manages the storage space in the data files in units called Oracle blocks or
data blocks.

-- At the finest level of granularity, the data in an Oracle database is stored in data blocks.

-- Oracle data blocks are the smallest units of storage that the Oracle server can allocate, read, or write.

-- One data block corresponds to one or more operating system blocks allocated from an existing data file.

-- The standard data block size for an Oracle database is specified by the DB_BLOCK_SIZE initialization parameter when the database is created.

-- The data block size should be a multiple of the operating system block size to avoid unnecessary I/O.
• The maximum data block size is dependent on the operating system.

Read More

Undo Space Allocation

Whenever a new transaction needs undo space:

• First , oracle tries to find a undo segment which has no active transaction and allocate the extent within the undo segment.
• If no such undo segment found then oracle tries to make online of and off-line undo segment and use it.
• If no such off-line undo segment found then create a new undo segment and use it.
• If there is no sufficient space to create a new undo segment then it try to reuse an expired extent from the existing undo segment.

Whenever a running transaction needs more undo space:

• If current extent has more free blocks? If it has then allocate the next free block within the extent.
• If current extent does not have free blocks then check the next extent within the segment. If the next extent is expired then wrap the next extent and allocate the first block of the next extent.
• If the next extent is not expired then get space from UNDO tablespace.If a free extent is available then allocate it to the undo segment and return the first block in the new extent.
• If there is no free space available then take space from offline undo segment. Deallocate the extent from the offline undo segment and add it to the current undo segment. Return the first free block of the extent.
• If there is no offline undo segment then deallocate the extent from the online undo segment and add it to the current undo segment. Return the first free block of the extent.
• If it fails then extend the datafile of the undo tablespace.If the file can be extended then add an extent to the current undo segment then return the block.
• If the extend datafile fails then try to reuse extents from its own undo segments. If it is not busy then warp into the next extent.
• If reuse extents from its own undo segment fails i.e they contain uncommitted transaction then take unexpired extents from it's own offline undo segments.
• If it fails then take unexpired extents from it's own online undo segments.

Read More

Extents Overview

An extent is a contiguous set (side-by-side) of Oracle data blocks allocated to a segment within a tablespace. The size of an extent is controlled by storage parameters used when you CREATE or ALTER the segment (INITIAL, NEXT and PCT_INCREASE) and tablespace parameters. 

When Extents Are Allocated

 

When you create a table or index then oracle automatically creates an initial extent with a number of specified data blocks under the table's segment.

If the data blocks of a segment's initial extent become full and more space is required to hold new data, Oracle automatically allocates an incremental extent for that segment. An incremental extent is a subsequent extent of the same or greater size than the previously allocated extent in that segment.

 Extent Allocation:-

SQL> SELECT tablespace_name, extent_id, blocks, bytes
  2    FROM user_extents
  3   WHERE segment_name = 'EMP';

TABLESPACE_NAME                 EXTENT_ID     BLOCKS      BYTES
------------------------------ ---------- ---------- -------------- ----------
USERS                                                  0                     8               65536
USERS                                                  1                     8               65536


When Extents Are Deallocated

In general, the extents of a segment do not return to the tablespace until you drop the schema object whose data is stored in the segment. Exceptions to this include the following:
 

• The owner of a table or cluster, or a user with the DELETE ANY privilege, can truncate the table or cluster with a TRUNCATE...DROP STORAGE statement.
• A database administrator (DBA) can deallocate unused extents using the following SQL syntax:  ALTER TABLE table_name DEALLOCATE UNUSED; 
• Periodically, Oracle deallocates one or more extents of a rollback segment if it has the OPTIMAL size specified.

When extents are freed, Oracle modifies the bitmap in the datafile (for locally managed tablespaces) or updates the data dictionary (for dictionary managed tablespaces) to reflect the regained extents as available space. Any data in the blocks of freed extents becomes inaccessible.

Read More

Row Chaining, Row Migrating, PCTFREE, PCTUSED

Row Chaining  

The row is too large to fit into an EMPTY data block that is known as chained row. Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. If  you have a 2k blocksize but your row size is 4k, we will use 3 blocks to store that row in pieces.  Any table with a long/long raw will have chained rows.Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining in these cases isnot avoided. Any table whose rowsize exceeds the  blocksize will have chained rows.  Any table with more then 255 columns will have chained rows.You can see if you "have it" by looking at the system wide statistic:

select * from v$sysstat where name like 'table pop cont%';

Row Migrating

A row that was moved to another block due to an update making it too large to fit on its original block with the other rows there is known as migrated row.Oracle tries to find another Blockwith enough free space to hold the entire row.If such block is available Oracle moves the entire ROW to the NEW BLOCK.Oracle keeps the original Row piece of a Migrated row row to POINT to the NEW BLOCK containing the actual row.The ROWID of the MIGRATED rows does not change. INDEXES are not updated and they point to the ORIGINAL row LOCATION.

Migration and Chaining have negative effects on performance. INSERT and UPDATE statements that cause migration and chaining perform very poorly since due to additional PROCESSING. Queries that use an Index to select migrated or chained rows must perform additional I/O's.

 

PCTFREE Parameter

The PCTFREE parameter specifies the minimum percentage of data block to be reserved as free space. If you used PCTFREE 20 then at least 20% size of data block will be reserved as free space. For example if you use data block size 8K (DB_BLOCK_SIZE=8192) then PCTFREE 20 will reserved 1638 bytes as free space in a data block. This parameter is used to update to the existing rows already within each block.You can specify this parameter which issuing CREATE TABLE statement.

   

 

PCTUSED Parameter

This parameter specifies the minimum percentage of a block that can be used for row data plus overhead(data block header, table directory, and row directory) before new rows are added to the block.

After a data block is filled to the limit determined by PCTFREE, Oracle considers the block unavailable for the insertion of new rows until the percentage of that block falls below the parameter PCTUSED.

If we set PCTUSED 40 in the CREATE TABLE statement then data block used for this table's data segment is considered unavailable for the insertion of any new rows until the amount of used space in the block falls to 39% or less (Here the block's used space has previously reached PCTFREE).

Related Post:- Row Format

Read More

Row Format In DB

Oracle stores each row of a database table containing data for less than 256 columns as one or more row pieces. If an entire row can be inserted into a single data block, then Oracle stores the row as one row piece.If all of a row's data cannot be inserted into a single data block or an update to an existing row causes the row to outgrow its data block, Oracle stores the row using multiple row pieces. A data block usually contains only one row piece for each row. When Oracle must store a row in more than one row piece, it is chained across multiple blocks.

 

When a table has more than 255 columns, rows that have data after the 255th column are likely to be chained within the same block. This is called intra-block chaining. A chained row's pieces are chained together using the rowids of the pieces.Each row piece, chained or unchained, contains a row header and data for all or some of the row's columns.
The row header contains information about:

•     Row pieces
•     Chained row pieces only)
•     Columns in the row piece
•     Cluster keys

A row fully contained in one block has at least 3 bytes of row header. After the row header information, each row contains column length and data. The column length requires 1 byte for columns that store 250 bytes or less, or 3 bytes for columns that store more than 250 bytes, and precedes the column data. Space required for column data depends on the datatype. If the datatype of a column is variable length, then the space required to hold a value can grow and shrink with updates to the data.

Related Post:- Oracle Data Block Format

Read More

Oracle Data Block Format

The Format of Oracle Data Block is as follows:

1. Header 
2. Table Directory 
3. Row Directory 
4. Row Data
5.  Free Space 

 

Header  

The header contains general block information, such as the block address and the type of segment (for example, data or index).
 

Table Directory

This portion of the data block contains information about the table having rows in this block.
 

Row Directory

This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area).

After the space has been allocated in the row directory of a data block's overhead, this space is not reclaimed when the row is deleted. Therefore, a block that is currently empty but had up to 50 rows at one time continues to' have 100 bytes allocated in the header for the row directory. Oracle reuses this space only when new rows are inserted in the block. 

Row Data

This portion of the data block contains table or index data. Rows can span blocks.

Related Post:- Row Format
 

Free Space

- Free space is allocated for insertion of new rows and for updates to rows that require additional space.
- In data blocks allocated for the data segment of a table or cluster, or for the index segment of an index, free space can also hold transaction entries.

- A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT...FOR UPDATE statement accessing one or more rows in the block. 

- The space required for transaction entries in most operating systems require approximately 23 bytes.

Read More

Oracle Data Block Overview

• Oracle manages the storage space in the datafiles of a database in units called data blocks.  
• Data block is the smallest unit of data used by a database.  
• At the physical, operating system level, all data is stored in bytes. Each operating system has a block size. Oracle requests data in multiples of Oracle data blocks, not operating system blocks.  
• The standard block size is specified by the initialization parameter DB_BLOCK_SIZE. 
• You can specify of up to five nonstandard block sizes. 
• The data block sizes should be a multiple of the operating system's block size within the maximum limit to avoid unnecessary I/O. 
• Oracle data blocks are the smallest units of storage that Oracle can use or allocate.

Related Topic: Data Block Format

Read More

Logical Storage Structure

Oracle Database allocates logical space for all data in the database. The logical units of database space allocation are data blocks, extents, segments, and tablespaces. At a physical level, the data is stored in data files on disk. The data in the data files is stored in operating system blocks.

The logical parts of the Oracle database are those structures within Oracle that determine where in the physical structures your table and index data reside. Since at the core of every operating system are mechanisms to access the physical data block defined on disk, Oracle must also have a logical structure that will relate down to that data block as your tables are created within the data files and as they grow over time. This is done by three logical structures that are kept for every object created in Oracle. These three structures are data blocks, extents, and segments.


Related Video:- Oracle DBA - Logical Database Structure

Read More

Physical Storage Structure

The physical structure of an Oracle database is determined by the operating system files that provide the actual physical storage for database information.One characteristic of an RDBMS is the independence of logical data structures such as tables, views, and indexes from physical storage structures. Because physical and logical structures are separate, you can manage physical storage of data without affecting access to logical structures. For example, renaming a database file does not rename the tables stored in it. An Oracle database is a set of files that store Oracle data in persistent disk storage.The physical structure of an Oracle database includes only three types of files: control files, data files, and redo log files

Data files

A data file is a physical file on disk that was created by Oracle Database and contains data structures such as tables and indexes. A temp file is a data file that belongs to a temporary tablespace. The data is written to these files in an Oracle proprietary format that cannot be read by other programs.

Control files

A control file is a root file that tracks the physical components of the database.

Online redo log files

The online redo log is a set of files containing records of changes made to data.

Read More

Database Storage Structure

An Oracle database is made up of physical and logical structures. Physical structures are those that can be seen and operated on from the operating system, such as the physical files that store data on a disk. Logical structures are created and recognized by Oracle Database and are not known to the operating system. The primary logical structure in a database, a tablespace, contains physical files. The applications developer or user may be aware of the logical structure, but is not usually aware of this physical structure. The DBA must understand the relationship between the physical and logical structures of a database.

Read More

Oracle Database

Oracle introduced the first commercial Relational Database Management System (RDBMS) almost 30 years ago. Oracle database consists of operating system files, also known as database files, that provide the actual physical storage for database information. The database files are used to ensure that the data is kept consistent and can be recovered in the event of a failure of the instance.

The general purpose of a database is to store and retrieve related information. An Oracle database has a logical and a physical structure. The physical structure of the database is the set of operating system files in the database. An Oracle database consists of three file types.

• Data files containing the actual data in the database
• Redo logs containing a record of changes made to the database to enable recovery of     the data in case of failures
• Control files containing information necessary to maintain and verify database integrity Other Key File Structures

     The Oracle server also uses other files that are not part of the database:  

• The parameter file defines the characteristics of an Oracle instance. For example, it contains parameters that size some of the memory structures in the SGA.
• The password file authenticates users privileged to start up and shut down an Oracle instance.
• Archived redo log files are offline copies of the redo log files that may be necessary to recover from media failures.

Related video:- Introduction to Oracle Database Administration - Oracle DBA

Read More