| Lesson 3 | Optimizing space usage within blocks |
| Objective | Describe Oracle block space usage. |
Optimizing Oracle Block Space Usage
How Data is stored in an Oracle Data Block
High performance often depends on two factors: data block size and row storage. Let us now look at how data is stored within an Oracle data block. Oracle places some overhead in every data block. When a data file is added to an Oracle tablespace, Oracle allocates the data file and formats each data block, adding header and footer information within each block. We can add objects to the remaining space.
How to allocate space in Oracle data block
Oracle uses all of the row space in a data block, except for the space reserved by the PCTFREE storage management parameter.
To review, the PCTFREE table parameter sets the percentage of space to remain open for future updates of rows that already exist on that data block. PCTFREE can have a negative impact on performance if it is set too low.
PCTFREE and PCTUSED work together to optimize the use of space in the data blocks of the extents within a data segment. We will look at setting PCTFREE and PCTUSED later in this module.
To review, the PCTFREE table parameter sets the percentage of space to remain open for future updates of rows that already exist on that data block. PCTFREE can have a negative impact on performance if it is set too low.
Creating Tables for Good Performance
The PCTFREE table parameter determines when to perform a freelist un-link.
This happens when a block becomes full due to SQL INSERTS and the percent full exceeds the PCTFREE parameter. At this point the block is removed from the table's freelist. If PCTFREE is set to 20, this means that 20% of each data block in this table's data segment will be set aside and free for possible updates to the rows already existing within each block.
New rows can be added to the row data area, and corresponding transaction information can be logged in the variable portions of the block overhead area, until the row data and overhead are 80% of the total block size.
New rows can be added to the row data area, and corresponding transaction information can be logged in the variable portions of the block overhead area, until the row data and overhead are 80% of the total block size.
Creating Tables for Good Performance
When you create a segment, such as a table, Oracle allocates space in the database for the data. If subsequent database operations cause the data volume to increase and exceed the space allocated, then Oracle extends the segment. When installing applications, an initial step is to create all necessary tables and indexes. This operation is by itself relatively fast, and not much is gained by doing it in parallel. However, some things require attention:
- Using Automatic Segment-Space Management: Oracle automatically manages segment space for best performance.
- Setting Storage Options: Applications should carefully set storage options for the intended use of the table or index.
This includes setting the value for PCTFREE. (Using automatic segment-space management eliminates the need to specify PCTUSED).
Note: Use of free lists is no longer encouraged. To use automatic segment-space management, create locally managed tablespaces, with the segment space management clause set to AUTO. - Setting INITRANS: Each datablock has a number of transaction entries that are used for row locking purposes. Initially, this number is specified by the INITRANS parameter, and the default value (1 for tables, 2 for indexes) is generally sufficient. However, if a table or index is known to have many rows for each block with a high possibility of many concurrent updates, then it is beneficial to set a higher value. This must be done at the CREATE TABLE/CREATE INDEX time to ensure that it is set for all blocks of the object.
PCTFREE and PCTUSED work together to optimize the use of space in the data blocks of the extents within a data segment. We will look at setting PCTFREE and PCTUSED later in this module.
Only one object type may inhabit any given Oracle data block. Whenever Oracle allocates an extent for an object (table or index),
it is the physical data blocks that are allocated to the object. Therefore a tablespace can have different data blocks with different PCTFREE space, but each data block will contain only one type of object.
Let us take a look at how data blocks are reserved with different amounts of free space.
Let us take a look at how data blocks are reserved with different amounts of free space.
Data Block Size
This example shows that there are different amounts of reserved space within each of the data blocks.
Oracle Segments, Extents, and Blocks
In the previous module, an overview of tablespaces and the logical structures contained within them were discussed.
Also briefly presented were datafiles, allocated at the operating system level, as the building blocks for tablespaces. Being able to effectively manage disk space in the database requires an in-depth knowledge of tablespaces and datafiles,
as well as the components of the segments stored within the tablespaces, such as tables and indexes. At the lowest level, a tablespace segment consists of one or more extents, each extent comprising one or more data blocks. Figure 6-1 shows the relationship between segments, extents, and blocks in an Oracle database.
In the following sections are the details of data blocks, extents, and segments with the focus on space management.
Data Blocks
A data block is the smallest unit of storage in the database. Ideally, an Oracle block is a multiple of the operating system block to ensure efficient I/O operations. The default block size for the database is specified with the DB_BLOCK_SIZE initialization parameter.
This block size is used for the SYSTEM, TEMP, and SYSAUX tablespaces at database creation and cannot be changed without
re-creating the database.
Every data block contains a header that specifies what kind of data is in the block: table rows or index entries. The table directory section has information about the table with rows in the block; a block can have rows from only one table or entries from only one index, unless the table is a clustered table, in which case the table directory identifies all the tables with rows in this block. The row directory provides details of the specific rows of the table or index entries in the block. The space for the header, table directory, and row directory is a very small percentage of the space allocated for a block; our focus, then, is on the free space and row data within the block.
The next lesson examines how Oracle manages data rows using the Oracle segment header.
Every data block contains a header that specifies what kind of data is in the block: table rows or index entries. The table directory section has information about the table with rows in the block; a block can have rows from only one table or entries from only one index, unless the table is a clustered table, in which case the table directory identifies all the tables with rows in this block. The row directory provides details of the specific rows of the table or index entries in the block. The space for the header, table directory, and row directory is a very small percentage of the space allocated for a block; our focus, then, is on the free space and row data within the block.
The next lesson examines how Oracle manages data rows using the Oracle segment header.
Ad Cloud DBA Oracle
