Oracle Basics (2) – Data Storage

What is finally the primary objective of a database? Storing Data 😀
The question is, how Oracle makes it. For many beginners, a confusing topic is to get the picture between physical storage and logical one. So how does it work?

The storage in an Oracle database can be seen from 2 points of view:

• Database users
• System Administrators

On one hand you have users who see tables, indexes and so on. This could be summarized has objects or, so called in Oracle, Segments. On the other hand you have system adminitrators (Unix or Windows, doesn’t matter…), who see files and blocks.
As DBA you are the missing link between these 2 points of view 🙄
So, how do we go down from an Object, Segment, to the files and OS blocks? Take a look down there.

As we already said, data are stored in Segments (Tables, Indexes…). Segments are stored in Tablespaces and these are the bridge between logical storage and physical storage.

On one side, tablespaces are physical collection of data files, which are made of operating system blocks. On the other side, tablespaces are logical collection of Segments (Tables, indexes…). You could almost compare them to folders where you organize your data in.

Segments are composed of Extents, which are pieces of data. As Segments will most probably grow over time, Extents are added to follow the Segments’ size increase. Extents are made of Oracle Blocks.

Finally we reach the lowest level, where Oracle Blocks are groups of Operating System Blocks. The Oracle block size is defined on database level but can also be customized per tablespace. The Oracle block size must be greater or equal and a multiple of the OS blocks. This is basis while speaking about Oracle performances. Many best practices can be found on Internet about it.

Let’s take a small example:

1 Database with 8KB Oracle Blocks on File Systems of 4KB blocks.
==> 1 Oracle Block = 2 OS Blocks
Now we can have a look a little bit deeper in all these elements

Tablespaces and Data Files

As seen, Tablespaces are logical containers composed of one or more datafiles.

Oracle provides 2 types of tablespaces:

• Smallfile tablespaces – most commonly used
• Bigfile tablespaces

The Smallfile tablespaces are composed of several datafiles, while Bigfiles are composed of a single data file. One of the reasons why Oracle introduced big file tablespaces was the limitation on the number of data file composing a database (usually 64k). In Bigfile tablespaces, the data files max size limit is much higher than for small file tablespaces.

How does it work? You may have already heard about ROWID, which is a unique ID for each row in a database. In fact the ROWID is more than just an ID, it is also the pointer to the row location. Between Smallfiles and Bigfiles tablespaces, the ROWID is slightly different.

As by definition Bigfile tablespaces are made of a single file, there is no File Nb needed within the ROWID. Therefore the Block Nb is coded on 4GB instead of 4kB.

If we take our example of a 8kB database again, we have the following:
Smallfile max size: 4kB blocks X 8kB (size of each block) = 32 GB per file
Bigfile max size: 4GB blocks X 8kB (size of each block) = 32 TB per file

The tablespace management can be done either in the Oracle Data Dictionary or, since 10g, be Locally Managed. Nowadays almost all tablespaces are configured as Locally Managed TableSpace (LMTS). This means that the space management is done in a bitmap stored in the Segment Header of the tablespace. The LMTS has the advantage to reduce – but not avoid – fragmentation and improve DDL performances.

While creating data files, 2 solutions are possible. You can either create them with fix size which will be allocated at file creation, or you can create them in autoextend mode. Autoextend data files are created with an initial size and will then grow up automatically until the defined max size. If no max size is given at data file creation, then the limit defined by the Oracle Block Size (see above about ROWID and max data file size) will be applied.

Here are the 2 main views in oracle to get information on tablespaces and data files: dba_tablespaces, dba_data_files

SQL> desc dba_tablespaces;
Name                       Null?         Type
-------------------------- ------------- ------------------------------------
TABLESPACE_NAME            NOT NULL      VARCHAR2(30)
BLOCK_SIZE                 NOT NULL      NUMBER
INITIAL_EXTENT                           NUMBER
NEXT_EXTENT                              NUMBER
MIN_EXTENTS                NOT NULL      NUMBER
MAX_EXTENTS                              NUMBER
MAX_SIZE                                 NUMBER
PCT_INCREASE                             NUMBER
MIN_EXTLEN                               NUMBER
STATUS                                   VARCHAR2(9)
CONTENTS                                 VARCHAR2(9)
LOGGING                                  VARCHAR2(9)
FORCE_LOGGING                            VARCHAR2(3)
EXTENT_MANAGEMENT                        VARCHAR2(10)
ALLOCATION_TYPE                          VARCHAR2(9)
PLUGGED_IN                               VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT                 VARCHAR2(6)
DEF_TAB_COMPRESSION                      VARCHAR2(8)
RETENTION                                VARCHAR2(11)
BIGFILE                                  VARCHAR2(3)
PREDICATE_EVALUATION                     VARCHAR2(7)
ENCRYPTED                                VARCHAR2(3)
COMPRESS_FOR                             VARCHAR2(12)
SQL> desc dba_data_files;
 Name                       Null?    Type
 -------------------------- -------- -------------------------------
 FILE_NAME                           VARCHAR2(513)
 FILE_ID                             NUMBER
 TABLESPACE_NAME                     VARCHAR2(30)
 BYTES                               NUMBER
 BLOCKS                              NUMBER
 STATUS                              VARCHAR2(9)
 RELATIVE_FNO                        NUMBER
 AUTOEXTENSIBLE                      VARCHAR2(3)
 MAXBYTES                            NUMBER
 MAXBLOCKS                           NUMBER
 INCREMENT_BY                        NUMBER
 USER_BYTES                          NUMBER
 USER_BLOCKS                         NUMBER
 ONLINE_STATUS                       VARCHAR2(7)

Segments and Extents

Segment is the generic name used in Oracle databases to represent objects like tables, indexes or partitions. These are stored in Data Files in pieces called Extents.

The Segments can be either in MANUAL mode or in AUTO mode – Automatic Segment Space Management (ASSM)
In earlier Oracle version, the MANUAL mode managed free blocks and free space in a Free List stored the Data Dictionary, which overloaded System tablespace. Since Oracle 10g and ASSM the free blocks and the free space are managed in a bitmap in the Segment Header of each Tablespace.

The 2 main views to find segments and extents information are: dba_segments and dba_extents

SQL> desc dba_segments
Name                       Null?           Type
-------------------------- --------------- -----------------------------------
OWNER                                      VARCHAR2(30)
SEGMENT_NAME                               VARCHAR2(81)
PARTITION_NAME                             VARCHAR2(30)
SEGMENT_TYPE                               VARCHAR2(18)
SEGMENT_SUBTYPE                            VARCHAR2(10)
TABLESPACE_NAME                            VARCHAR2(30)
HEADER_FILE                                NUMBER
HEADER_BLOCK                               NUMBER
BYTES                                      NUMBER
BLOCKS                                     NUMBER
EXTENTS                                    NUMBER
INITIAL_EXTENT                             NUMBER
NEXT_EXTENT                                NUMBER
MIN_EXTENTS                                NUMBER
MAX_EXTENTS                                NUMBER
MAX_SIZE                                   NUMBER
RETENTION                                  VARCHAR2(7)
MINRETENTION                               NUMBER
PCT_INCREASE                               NUMBER
FREELISTS                                  NUMBER
FREELIST_GROUPS                            NUMBER
RELATIVE_FNO                               NUMBER
BUFFER_POOL                                VARCHAR2(7)
FLASH_CACHE                                VARCHAR2(7)
CELL_FLASH_CACHE                           VARCHAR2(7)
SQL> desc dba_extents
Name                       Null?           Type
-------------------------- --------------- ------------------------------------
OWNER                                      VARCHAR2(30)
SEGMENT_NAME                               VARCHAR2(81)
PARTITION_NAME                             VARCHAR2(30)
SEGMENT_TYPE                               VARCHAR2(18)
TABLESPACE_NAME                            VARCHAR2(30)
EXTENT_ID                                  NUMBER
FILE_ID                                    NUMBER
BLOCK_ID                                   NUMBER
BYTES                                      NUMBER
BLOCKS                                     NUMBER
RELATIVE_FNO                               NUMBER

Another important concept to understand, in case of Table segments, is the “High Watermark” (HWM). It defines the position of the last formated block for the segment. It means that in case of a Full table scan (FTS – i.e. select * from table1;) Oracle will go through all the segment’s blocks up to the HWM position.

The key here is to keep in mind that deleting data, which means free blocks, does NOT move the HWM position. This can have a quite huge impact on requests performances.
The only ways to move down the HWM is either to truncate the table, but this also means losing all data, or to make a table reorganization. However this quite beyond our current topic 😀

So here we are with this introduction into Oracle data storage.
Have fun with Oracle 😀