Logical Storage Structures

Storage structures take care of the data/information storage. There are two types of storage structures in Oracle; logical and physical. In this article, I will familiarize you with how data are stored and retrieved from the database (logical) perspective.

Logical storage structures

Logical storage structures are managed and handled only by Oracle Database. An operating system cannot see them. All logical structures are stored in a database logical space (data blocks, extents, segments and tablespaces).

As you can see in the picture above, the smallest structure in the Oracle Database is the data block. They are usually very tiny (a few KB) and they are part of extents. Data blocks in extents are contiguous.

The set of data blocks composes a segment. Segments are allocated for database objects (i.e. tables). Each object has its own segment (i.e. table and indexes, … have their own segments – data segment and index segment respectively).

Lastly, it is the tablespace that consists of multiple segments. Segments can belong to one tablespace only.

As I said, all these structures are managed on the database level (not operating system level). The database has to have a way how to allocate new extents or delete those not needed anymore in a tablespace. There are two types of tablespace management:

• Locally managed
  • ASSM – Automatic Segment Space Management
  • MSSM – Manual Segment Space Management
• Dictionary managed

I am not gonna talk about the latter one, for it’s not common and I am not familiar with this one; should you need more details – google it pls 🙂

In logically managed tablespaces, data files consist of datafile header and data file block; I will talk about them in the section below (physical storage structures). In the data file header, there is bitmap information about free and used space in a data file. With every change (new space allocated or freed), it updates the bitmap information (1 = used, 0 = available).

Each tablespace can have different management in place. Some might have ASSM the others can have MSSM. There is no problem with that and many times it’s very handy 🙂

ASSM – Automatic segment space management

This is a default management setup by Oracle for permanent and locally managed tablespaces. ASSM is more efficient compared to MSSM due to concurrent transaction access to data. This reduces the wait time and improves performance. Another important advantage of using ASSM is automatic settings of storage parameters. The only one that can be set is PCTFREE which defines and extra space allocated for future updates.

MSSM – Manual segment space management

This older method uses a free list to manage free space in the segment. Every database object managed by MSSM keeps the free space in High Water Mark. On top of PCTFREE, it uses parameters such as FREELISTS, PCTUSED, and FREELIST GROUPS. As you can see, it can get pretty tricky to manage a segment with MSSM for you have to properly configure PCTFREE and PCTUSED to utilize the space optimally and avoid wasting and therefore avoid performance issues. This is all you have to know about MSSM and I am not gonna tell you more intentionally for ASSM is the correct approach.

Data blocks

Data blocks sometimes referred to as pages, are the smallest bits of database I/O operations. Whenever a query performs I/O it transfers data blocks. At the same level, there are operating system blocks. Oracle data blocks contain Operating system blocks (see the picture below).

Whenever you query a table and trying to get data from a table (requesting data block) operating system translates this into a request for operating system block retrieval from permanent storage (harddisk).

The Oracle data block size is set during the database creation. It cannot be changed unless you re-create the database. You can check the block size by executing this query below:

SELECT block_size FROM v$controlfile;

The Oracle data block format is defined in a way to enable track data and free space in the block. This format uses the same structure for all object types (table, index, …).

Block header, table directory, and row directory form a structure called Data Block Overhead. Its purpose is to manage the block itself (not data!). To do so, it is divided into those three sections mentioned above.

Block header stores segment type and disk address. Transaction-managed blocks, on top of that, contain information about active and historical transactions. For every transaction, the block has to store transaction entry details. If there is not enough space in the header, transactional data might be stored in the free space area.

Table directory stores meta-data for heap-organized tables whose rows are in the block.

Row directory carries a piece of information about the row location of the data portion for that block.

Row data contains the actual data (number of rows, index entries, column values, …). Similarly to the data block, row blocks have their own structure as well (see the picture below); basically row header and column data. This structure is called “row piece” and a row can be contained in one or multiple row pieces.

Row header stores columns in the row piece, cluster keys, and pieces of the row located in other data blocks. That is because row data are not always inserted at the same data block (usually due to insufficient space) and therefore they have to be stored in multiple row pieces.

Column data stores the actual data in the row. They are usually listed in the same order as they are created when CREATE TABLE is performed (except a few cases). As you can see in the picture, column data stores column length and data individually. Different data types have different sizes and that is why it is so important to choose data type wisely and not waste space (and therefore decrease the performance). That is why VARCHAR2 is way better CHAR (VARCHAR2 stands for VARiable size CHARacter and can shrink or extend according to the actual value whereas CHAR will always allocate the maximum defined size no matter what is stored inside).

ROWID is a unique Oracle identifier to locate and access a row. It composes of four sections and can be easily retrieved from the database using ROWID keyword in the select section:

SELECT rowid 
FROM user
WHERE id_user = 10001;


--> ABm3OPADDAAAAZ8AAB

• Data object number
  • first six characters (=> ABm3OP)
  • identifies the segment
  • objects in the same segment have the same data object number
• Relative file number
  • next three characters (=> PAD)
  • identifies the data file with the row
• Block number
  • following six characters (=> DAAAAZ8)
  • identifies the block with the row
  • two rows with the same block number can reside in the different data file for the same tablespace  (they are not related to tablespace but data file)
• File number
  • last three characters (=> AAB)
  • identifies the row in the block

ROWID can be updated in some special cases. For example, when ROW MOVEMENT is enabled on partitions (while updating partitioned key). It is the same case for SHRINK TABLE operator and a few others as well.

To speed up some queries and save space in a database, data blocks can be compressed. You can read about how to compress them in another article called “How to save some space in Oracle“. Contrary to that article, here, I will explain what is happening behind the scenes.

When you apply data block compression (advanced or basic), Oracle replaces duplicate values with a simple symbol reference. This reference is stored in a “symbol table”. Let’s check the example below to give you a better understanding.

Here is a non-compressed table with orders:

 id_order |  id_item  | price |   date_order   | quantity
----------------------------------------------------------
    1001  |    1200   |  50   |  Nov 12, 2019  |    3
    1250  |    1200   |  50   |  Nov 13, 2019  |    4
    1333  |    3499   |  50   |  Dec  2, 2019  |    7
    1451  |     100   |  29   |  Dec  2, 2019  |    5
    1453  |    1635   |  45   |  Dec 14, 2019  |   12

After compression, the table will look like:

 id_order |  id_item  | price |   date_order   | quantity
----------------------------------------------------------
    1001  |       %   |   $   |  Nov 12, 2019  |    3
    1250  |       %   |   $   |  Nov 13, 2019  |    4
    1333  |    3499   |   $   |       &        |    7
    1451  |     100   |  29   |       &        |    5
    1453  |    1635   |  45   |  Dec 14, 2019  |   12

As you noticed, id_item duplicates were replaced by %, price duplicates by $, and date_ order duplicates by  &.

Simultaneously with the replacement, there was a symbol table created with the references to data:

 Symbol |    Value    | Column |   Rows
------------------------------------------
   %    |        1200 |      2 | 152-153
   $    |          50 |      3 | 152-154
   &    | Dec 2, 2019 |      4 | 154-155

As applications or users keep on inserting or updating data to the database (bottom-up approach) the free space is getting smaller. To properly manage the free space and avoid wasting Oracle provides us with the PCTFREE parameter. This crucial parameter plays a big role in performance optimization and the overall performance of the database. First of all, it prevents row migration (when performing update) and thus avoids wasting space.

PCTFREE enforces the database to not go lower than the specified percentage of allocated free space. However, the block can have more than the percentage says. In other words, if we (for example) set PCTFREE to 10% it cannot happen that the total amount of free space will drop to 8 or 5 % of that block. On the other side, nothing can prevent the free space to grow up to (for example) 50 % (or in some rare cases – I saw those too 🙂 – up to 90-100%. This is a terrible waste and a sign of lack of performance activity). These commands can increase free space and cause you (as a DBA) a headache 🙂

• DELETE
• UPDATE – updating to a smaller value or triggering row movement
• INSERT – into a compressed table

For INSERT statements, the free space can be claimed again if it is within the same transaction or by another transaction but only when the previous one is committed and space is needed. For direct loads, it will not try to re-use the free space and only cause fragmentation.

That is why DELETE and INSERT /*+ APPEND */ is the worst combination ever!

Such fragmentation can only coalesce when INSERT or UPDATE tries to use the block with sufficient space for the new row piece. This is the only scenario of how Oracle “self-manages” its free space and prevents wasting. Why? It’s simple 🙂 Otherwise, it would be a big overhead and it would cause a lot of performance issues when trying to coalesce the free space every time.

A similar situation happens when reusing the index space. When you insert and delete a certain indexed value, the database can reuse this slot if needed. Contrary to the table block, index blocks become free only when empty. Database tags this empty block and makes it available for reuse. This is not happening automatically and therefore rebuilding the index is required.

ALTER INDEX my_schema.idx_name REBUILD;
-- or
ALTER INDEX my_schema.idx_name COALESCE; -- no lock required

The idea is, that when you have (let’s say) 5 index leaf blocks half empty, after rebuilding you can end up with two fully utilized leaf blocks. That not only saves space but also improves performance.

To check whether the index needs to be rebuilt, you have to consider two main things:

• High fragmentation – Plenty of DML statements causing lots of deleted leaf blocks
• High index scan access plans – Plenty of index scans in the execution plan

Index rebuilding has its pros and cons and you have to always weigh whether to do that or not. Generally, if you don’t do many updates or deletes – you don’t have to rebuild.

Also, if the depth of the index is higher than 4, you might consider rebuilding.

To check the index depth:

SELECT owner
, index_name
, blevel
, leaf_blocks
FROM dba_indexes
WHERE 1=1
AND owner = 'MY_SCHEMA'
AND index_name = 'IDX_NAME';

Chained and migrated rows

Some rows are too big to fit into a single row and Oracle, of course, thought about these too 🙂 There are three types of scenarios:

• A row has more than 255 columns
• A row is bigger than the data block (cannot fit inside one only)
• A row fitted well previously but was updated and there is not enough free space to hold the whole row

One thing to remember about chained or migrated rows is, whenever the row is chained or migrated the I/O operation increases because Oracle has to scan multiple blocks to retrieve the row. To find such segments with free space available to be claimed, you can use the Segment Advisor.

Extents

As it was mentioned at the beginning of this article, extents are composed of contiguous data blocks. Extents are then forming a segment. When the segment is created, Oracle allocates an initial extent, although no data are stored there (data blocks are exclusively allocated for this segment).

When this initial extent becomes full and more space is needed, Oracle automatically allocates an incremental extent. The database (in locally managed tablespace) will for the bitmap of a data file for another free space. If there is no free space in that data file, Oracle will look into another data file. Though, extents always reside within the same tablespace they might be stored in different data files.

Extents cannot be emptied/deallocated unless:

• the object is DROPped
• you SHRINK, MOVE, or TRUNCATE the object
• for indexes, you can REBUILD/COALESCE

Segments

A segment composes of multiple extents and it stores all the data within a tablespace. There are several types of segments:

• User segments – tables, table partitions, clustered table, indexes, index partitions, …
  • each nonpartitioned objects and object partition is stored in its segment
• Temporary segments – global temporary tables, CTAS, …
• UNDO segments – rollback, transaction data integrity (consistency), flashback, …

User segments

Oracle uses deferred segment creation by default to update only metadata when creating database objects (tables/indexes). Whenever you try to insert data into an object (table or partition), Oracle will allocate a segment for each part; table/partition, LOB, or/and index.

Let’s have a look at this example to give you a better understanding of how segments are created when creating an object.

CREATE TABLE employee (
  id_emp INT PRIMARY KEY,
  emp_name VARCHAR2(255),
  dtime_inserted DATE
);

This will create 2 schema objects and therefore 2 corresponding segments.

1. Table “employee”
2. Index on ID_EMP (because primary key constraint automatically creates and index)

Temporary segments

These are allocated usually during query execution; when your query needs (for example) sorting or when you are creating indexes, Oracle first stores the index segments into a temporary segment and then stores it in a permanent segment. The only exception is “in memory” processing, where Oracle skips temporary segments creation and process all data in memory. This “in memory” approach is, generally, way faster and recommended. Although in some cases it might be better to “preload” (store in temporary segments) data.

Whenever the query is finished all temporary segments related to that query are released (=dropped). One important thing to remember about operations performed in temporary segments is they are not recorded in the online redo log!

As you could notice in the table DBA_USERS there are two types of tablespaces.

SELECT username
, default_tablespace
, temporary_tablespace
FROM dba_users;

Yes; The latter one is where temporary segments are stored 🙂 It is usually a general practice that there is only one temporary_tablespace for all users to minimize I/O and avoid messing other tablespaces with temporary segments.

UNDO segments

All UNDO data are stored inside the database rather than in logs. Undo data has its own UNDO tablespace. You can get more details about the UNDO tablespace by executing the following queries:

-- statistics for UNDO activity
SELECT * FROM v$undostat;

-- activity of current transactions
SELECT * FROM v$transaction;

-- historical statistics for UNDO activity
SELECT * FROM dba_hist_undostat;

UNDO tablespace is fully managed by a mode called “automatic undo management mode”. This tablespace is used for keeping all transactional changes for rollback purposes before the commit is executed to provide read consistency. To find out more about how data are processed while DML (during transactions) please read DML Processing article.

Tablespaces

The final level of logical storage structures are tablespaces that contain segments. In Oracle, we divide tablespaces into two main categories:

1. Permanent tablespaces
   1. SYSTEM – data dictionaries, administrative views, and tables, …
   2. SYSAUX – centralized storage for database metadata outside SYSTEM
   3. UNDO
   4. user tablespaces
2. Temporary tablespaces
   1. TEMP – temporary objects (session duration limit); no permanent objects can be stored here

Rule of thumb says, that user objects should have their dedicated tablespace and application data should have another dedicated tablespace, aside from SYSTEM and SYSAUX tablespace which are mandatory. With separate tablespaces, you can control the disk space allocation, assign a quota to limit the space-wasting, manage data inside the tablespace without affecting the other tablespaces (turn off/on or encrypt), back up individual tablespaces and many more operations.