Logical Storage Structures

Introduction to Logical Storage Structures

        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 (see Chapter 11, "Physical Storage Structures"). The data in the data files is stored in operating system blocks.
        Figure 12–1 is an entity-relationship diagram for physical and logical storage. The crow's foot notation represents a one-to-many relationship.

Logical Storage Hierarchy

        Figure 12–2 shows the relationships among data blocks, extents, and segments within a tablespace. In this example, a segment has two extents stored in different data files.

        At the finest level of granularity, Oracle Database stores data in data blocks. One logical data block corresponds to a specific number of bytes of physical disk space, for example, 2 KB. Data blocks are the smallest units of storage that Oracle Database can use or allocate.
        An extent is a set of logically contiguous data blocks allocated for storing a specific type of information. In Figure 12–2, the 24 KB extent has 12 data blocks, while the 72 KB extent has 36 data blocks.
        A segment is a set of extents allocated for a specific database object, such as a table. For example, the data for the employees table is stored in its own data segment, whereas each index for employees is stored in its own index segment. Every database object that consumes storage consists of a single segment.
        Each segment belongs to one and only one tablespace. Thus, all extents for a segment are stored in the same tablespace. Within a tablespace, a segment can include extents from multiple data files, as shown in Figure 12–2. For example, one extent for a segment may be stored in users01.dbf, while another is stored in users02.dbf. A single extent can never span data files.

Logical Space Management

        Oracle Database must use logical space management to track and allocate the extents in a tablespace. When a database object requires an extent, the database must have a method of finding and providing it. Similarly, when an object no longer requires an extent, the database must have a method of making the free extent available.
        Oracle Database manages space within a tablespace based on the type that you create. You can create either of the following types of tablespaces:

        ■Locally managed tablespaces (default)
        The database uses bitmaps in the tablespaces themselves to manage extents. Thus, locally managed tablespaces have a part of the tablespace set aside for a bitmap. Within a tablespace, the database can manage segments with automatic segment space management (ASSM) or manual segment space management (MSSM).
        ■Dictionary-managed tablespaces
        The database uses the data dictionary to manage extents (see "Overview of the Data Dictionary" on page 6-1).

Locally Managed Tablespaces
        A locally managed tablespace maintains a bitmap in the data file header to track free and used space in the data file body. Each bit corresponds to a group of blocks. When space is allocated or freed, Oracle Database changes the bitmap values to reflect the new status of the blocks.
        The following graphic is a conceptual representation of bitmap-managed storage. A 1 in the header refers to used space, whereas a 0 refers to free space.

        ■Avoids using the data dictionary to manage extents
        Recursive operations can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a data dictionary table or undo segment.
        ■Tracks adjacent free space automatically
        In this way, the database eliminates the need to coalesce free extents.
        ■Determines the size of locally managed extents automatically
        Alternatively, all extents can have the same size in a locally managed tablespace and override object storage options.

        Note:
        Oracle strongly recommends the use of locally managed tablespaces with Automatic Segment Space Management.

        Segment space management is an attribute inherited from the tablespace that contains the segment. Within a locally managed tablespace, the database can manage segments automatically or manually. For example, segments in tablespace users can be managed automatically while segments in tablespace tools are managed manually.

Automatic Segment Space Management
        The ASSM method uses bitmaps to manage space. Bitmaps provide the following advantages:
        ■Simplified administration
        ASSM avoids the need to manually determine correct settings for many storage parameters. Only one crucial SQL parameter controls space allocation: PCTFREE. This parameter specifies the percentage of space to be reserved in a block for future updates (see "Percentage of Free Space in Data Blocks" on page 12-12).
        ■Increased concurrency
Multiple transactions can search separate lists of free data blocks, thereby reducing contention and waits. For many standard workloads, application performance with ASSM is better than the performance of a well-tuned application that uses MSSM.
        ■Dynamic affinity of space to instances in an Oracle Real Application Clusters (Oracle RAC) environment

ASSM is more efficient and is the default for permanent, locally managed tablespaces

        Note:
        This chapter assumes the use of ASSM in all of its discussions of logical storage space.

Dictionary-Managed Tablespaces
        A dictionary-managed tablespace uses the data dictionary to manage its extents. Oracle Database updates tables in the data dictionary whenever an extent is allocated or freed for reuse. For example, when a table needs an extent, the database queries the data dictionary tables, and searches for free extents. If the database finds space, then it modifies one data dictionary table and inserts a row into another. In this way, the database manages space by modifying and moving data.
        The SQL that the database executes in the background to obtain space for database objects is recursive SQL. Frequent use of recursive SQL can have a negative impact on performance because updates to the data dictionary must be serialized. Locally managed tablespaces, which are the default, avoid this performance problem.