A reorganization in the Oracle environment means a recreation of tables and their indexes from scratch based on the data in the existing table. You can achieve significant benefits with reorganizations of single tables or whole tablespaces. These benefits include reduced fragmentation, reduced space allocation, activation of features such as locally managed tablespaces (LMTS) or automatic segment space management (ASSM), and improved data clustering.

However, reorganizations often require major effort because you need to move large amounts of data. Usually a reorganization is much more than starting the reorganization tool and waiting until it is finished. Therefore, it is essential to analyze the current system situation in detail and to judge the benefits before performing the actual reorganization. You may need to spend significantly more time on the preparation than on the reorganization itself.

I describe typical scenarios that benefit from a reorganization so that you can combine several optimization approaches (e.g., reduction of fragmentation, activation of LMTS, and improvement of data clustering) in one reorganization. Furthermore, I also point out when you can convert table structures via a reorganization. This can be useful if you suffer from long-running SAP conversions or if you want to change the table data clustering (e.g., via sorting) to optimize the access performance.

The focus lies on the SAP standard reorganization tool BRSPACE and on the online reorganization functionality that this tool provides. This article is designed for Oracle database administrators in SAP environments that have experience in table or tablespace reorganizations. They should have a thorough understanding of the physical and logical database components such as tablespaces, datafiles, segments, extents, and blocks.

In the following sections I show how you can determine if parts of your Oracle database can take advantage of a reorganization. I focus on the reorganizations on table and tablespace level. Index reorganizations such as REBUILD or COALESCE operations are excluded on purpose because these mechanisms differ from BRSPACE online reorganizations.

Table Reorganizations

A table reorganization recreates the table and all its indexes. In the following sections, I describe the main reasons for reorganizations.

Reduction of Size and Fragmentation

In many cases, space and fragmentation considerations on the table level are the main reasons for performing reorganizations. Before looking at tables with a low space use or fragmentation, it is best to understand what an optimally filled table looks like. Figure 1 provides an example that meets the following criteria:

• The used space within the blocks in front of the table’s high water mark is high compared to the free space. The available free space within one block is so small that no additional records can be stored.
• The last block in front of the high water mark may be filled to a smaller extent
• Typically about 10% to 15% of a block is needed for the block header and the reserved PCTFREE space. This is normally not considered a space issue.
• The blocks after the high water mark are not filled with data at all. This is hardly ever a situation that requires a reorganization because this space can usually be reclaimed by ALTER TABLE … DEALLOCATE UNUSED. This should only be necessary in rare situations (e.g., if the blocks are larger than the data volume and therefore too much space is allocated after the high water mark).

Figure 1

Table with an optimal space use

Root causes for low space use are:

• Fragmentation: You can’t reuse the available free space within a table anymore because the free space fragments are too small for new records to be inserted (Figure 2). For example, this can happen after a table column is dropped.

Figure 2

Fragmented table

• Deletion and archiving: When many records are deleted from a table (e.g., during archiving) the used space in the blocks is significantly reduced. Depending on the data distribution, the deletion mainly concerns some or all blocks. Figure 3 shows the clustered deletion of data in some blocks, which is a typical situation when data is inserted and deleted on a time basis (the oldest records are typically deleted). The random deletion shown in Figure 4 deletes records from many table blocks and happens when the deletion criteria are not correlated to the time when the records were inserted.

Figure 3

Table after mass deletion: clustered deletion in some blocks

Figure 4

Table after mass deletion: random deletion in all blocks

Changing of Table Storage Parameters

Some table storage parameter changes can only take full effect when the table is recreated with the changed setting. Typical situations are:

• INITRANS changes: The physical attribute INITRANS defines the minimum number of Interested Transaction List (ITL) slots in each header of the table block. When more parallel Data Manipulation Language (DML) operations access the same block — and no freespace for dynamic ITL extension is available anymore in the block — processes are blocked with an Oracle enqueue (“enq: TX - allocate ITL entry”). In the worst case, this kind of lock can happen cross-wise with another enqueue wait and result in a deadlock (ORA-00060). By recreating the table with an increased INITRANS value, you reduce the risk of enqueues and deadlocks during parallel DML operations on the same segments and blocks.

• FREELIST GROUPS changes: The storage parameter FREELIST GROUPS defines the number of groups of freelists that concurrent INSERT operations can use, unless you use ASSM. In environments with highly concurrent INSERTs into the same table, you can reduce wait situations such as “buffer busy waits” by increasing the value for FREELIST GROUPS.

Moving Tables to a Different Tablespace

In some situations it is useful to move a table to a different tablespace. Also this operation requires a reorganization. Typical situations are:

• Manageability: By moving particularly large tables in their own tablespaces, you can keep the tablespace sizes below a critical limit. This makes future tablespace reorganizations easier.

• Independent reorganizations: By moving one table in its own tablespace, you can reorganize this table independent of other tables in the future (e.g., for reducing the tablespace space)

Reduction of Chained and Migrated Rows

Chained and migrated rows are records that spawn across at least two Oracle blocks. A migrated row has a pointer that only points from the first block to the next, while a chained row already has data in the first block. Both situations are often responsible for performance degradations and a space overhead. When I discuss chained rows in the following sections, I am also including migrated rows.

You can’t resolve chained rows that are larger than the available free space in a block with a reorganization (unless you increase the table block size). You can remove chained rows that are smaller than the available free space in a block with a reorganization.

Reduction of Allocated Extents

In dictionary managed tablespaces (DMTS), having many allocated extents is a big performance risk because space transactions (extent allocations or deallocations) can't be done concurrently and they serialize on the space transaction enqueue ("enq: ST - contention"). In a real example, the cleanup of 900,000 extents of one table took about four days to complete and prevented other extent allocations (e.g., during an INSERT operation) and deallocations to succeed. Therefore, it can be useful to reorganize the table in a controlled way into a locally managed tablespace. This also requires significant space transaction time, but it is possible to do it during a planned timeframe.

Transition from LONG to LOB Columns

You can use reorganizations to migrate from LONG and LONG RAW columns to LOB columns. This feature is available as of Oracle 10g and supported as of SAP kernel 6.40. This step is required if you want to perform an online reorganization because online reorganizations are not supported with target columns of type LONG or LONG RAW.

Activation of Table Compression

To compress a table, it has to be reorganized. Due to technical restrictions, table compression with Oracle 10g is supported in SAP environments only in a few situations (see SAP Note 1289494 for more information). As of Oracle 11g, a more sophisticated compression algorithm is available.

Activation of Transparent Data Encryption (TDE)

To activate TDE on columns of a table, it is necessary to reorganize the table. See SAP Note 974876 for more information regarding TDE.

Table Structure and Data Clustering Changes

Typical structure and data clustering changes are:

• Partitioning
• Adding, removing, or modifying columns
• Sorting table records by an index
• Switching to an index-organized table (IOT)

When tables become larger and larger it can be difficult to change the structure or data clustering of a table with traditional tools such as Data Dictionary (DDIC) functionality, SAP incremental conversion, or offline reorganizations. A BRSPACE online reorganization is a good option for performing these tasks online or in a parallel manner. These kinds of advanced reorganizations require manual interaction and deeper skills, which I discuss in the next section.

Tablespace Reorganizations

Tablespace reorganizations include the reorganization of all segments within the tablespace and the recreation of the tablespace with new datafiles. In addition to the reasons for table reorganizations described in the previous section, the following reasons may apply for tablespace reorganizations.

Activation of LMTS and ASSM

LMTS provide significant advantages compared to dictionary managed tablespaces (DMTS) in the area of extent allocation and deallocation, because space is administered within the tablespace rather than in the central Oracle dictionary. Therefore, you should move to LMTS whenever possible. You can only achieve this switch by performing a tablespace reorganization. For BRSPACE it is mandatory that the target tablespace of a tablespace reorganization is an LMTS.

ASSM is the new way of managing free and used space within segments. It replaces the former freelist-based approach and can only be activated by recreating a tablespace. ASSM is a prerequisite for several new Oracle features such as segment shrinking. However, plenty of critical problems existed since it was introduced with Oracle 9i. Meanwhile (i.e., with Oracle release 10.2.0.4 and higher) it runs rather stable and should usually be considered when doing a tablespace reorganization.

Cleanup of a Freespace Block Corruption

If the logical structure of an Oracle block is corrupted and the block belongs to the freespace of a datafile, this is not a problem for the database. Whenever a segment allocates the block and fills it with data the corruption is corrected. Nevertheless, consistency checks based on DBVERIFY may report the block as corrupted, which is confusing because you always have to check if the reported corruption is critical or related to a block in the freespace. You can repair a freespace block corruption by creating a dummy segment that overwrites the corruption or by reorganizing the tablespace.

Reduction of Size and Fragmentation

The reduction of size and fragmentation is often a central goal during a tablespace reorganization — not only on the table level, but also on the tablespace level. For several reasons, you can have a significant amount of unused space within one tablespace:

• Data has been deleted from segments within the tablespace. This reclaimed space may either be available within the segments or already as freespace within the tablespace (if the segments have been reorganized individually).
• Some tables and indexes have already been moved to a different tablespace
• The tablespace was created too large initially

Only in rare cases is it possible to decrease the size of a tablespace significantly by resizing datafiles to smaller sizes. Normally this is possible only to a minor extent because a resize can only be successful as long as there is still freespace in the last part of the datafile. The only reliable chance to reduce a tablespace size significantly is its reorganization.

Reducing the size of a tablespace can provide further benefits such as reduced database size, reduced backup size, or reduced filesystem size.

Datafile Layout Change

You can use a tablespace reorganization to perform changes to the datafile layout when you can:

• Create larger datafiles, such as to avoid reaching the limit of 1023 datafiles per tablespace in case of very large tablespaces
• Create smaller datafiles such as to reduce inode contention (a serialization of writes on a datafile if no concurrent input/output [I/O] is used)

• Get rid of datafiles that you created in the wrong locations or with wrong sizes (although this would also be possible with other operations such as RESIZE, RENAME, or DROP).

Tablespace Layout Change

There are several reasons why it can be useful to change the tablespace layout, such as:

• You have a very large tablespace that is hard to handle and you want to break it up into several smaller tablespaces that can be administered and reorganized in an acceptable time frame
• You want to move some tables (particularly large ones) to their own tablespaces

• You want to change to the new SAP tablespace layout (with one main tablespace PSAP<sid>)

Performing a BRSPACE Online Reorganization

In the following sections, I describe how a typical BRSPACE reorganization is started and which main steps are implicitly executed. Furthermore, I will explain the best procedure in case of error situations.

Reorganization Commands

Reorganizing a table online with BRSPACE looks simple, but in reality a complex procedure is executed that can result in many errors if you are not prepared. Call BRSPACE with the following parameters and it performs all the necessary reorganization steps automatically:

brspace -c force -f tbreorg -t <table_name>

The same applies to a tablespace reorganization from tablespace <source_tsp> to tablespace <target_tsp> that you can start with a command such as:

brspace -c force -f tbreorg -s <source_tsp> -t "*" -n <target_tsp>

Experience shows that using the above commands without some preparation checks can cause trouble. Typical problem situations are:

• Overflow of temporary tablespace
• Overflow of default tablespace
• Overflow of target tablespace
• Terminations due to invalid triggers
• Inconsistent data class (TABART) settings on the SAP side
• Target tables increase in size
• Available system resources are not utilized properly

Reorganization Steps

During an online reorganization, BRSPACE performs many steps implicitly. To understand its possibilities and restrictions, you should make sure that you understand the main actions:

Step 1. CREATE TABLE and CREATE INDEX commands for the target table are generated using the Oracle procedure DBMS_METADATA.GET_DDL and written to a file ddl.sql in a reorganization specific subdirectory of the directory $SAPDATA_HOME/sapreorg.

Step 2. The CREATE TABLE command creates the target table with the name of the source table followed by the suffix #$.

Step 3. The cost-based optimizer (CBO) statistics of the source table (and its indexes and columns) are exported to a statistics table using DBMS_STATS.EXPORT_TABLE_ STATS.

Step 4. The first central and long running reorganization step DBMS_REDEFINITION. START _REDEF_TABLE is started. In this phase, all data is copied from the source table to the target table. Concurrent changes to the table are recorded in a materialized view log (MLOG$_<table_name>).

Step 5. The indexes on the target table are created with the name of the source indexes and the suffix #$.

Step 6. The exported CBO statistics are imported into the target table based on DBMS_ STATS.IMPORT_TABLE_STATS.

Step 7. The online reorganization is finalized using DBMS_REDEFINITION.FINISH_ REDEF_TABLE. In this step, all changes are copied from the materialized view log to the target table and the name of the source table and the target table is exchanged.

Step 8. The source table is dropped.

Step 9. The #$ suffix of the target indexes is removed.

After, it is often useful to gather new CBO statistics on the target table because they can significantly differ from the CBO statistics of the source table (e.g., reduced fragmentation and changed table structure).

Procedure in Case of Errors

If the online reorganization terminates due to an error, it normally does not result in critical problems or inconsistencies because the source table and source indexes remain untouched. An error only concerns the currently reorganized table. All other tables that were already reorganized successfully during the same reorganization run are not affected. To ensure that you can cleanly restart the reorganization of the faulty table, you should perform a cleanup using:

brspace -c force -f tbreorg -t <table_name> -a cleanup

This command resets the parallelism degree or performs DBMS_REDEFINITION. ABORT_REDEF_TABLE to drop the materialized view log.

Advanced BRSPACE Online Reorganizations

Typically with BRSPACE, the goal of online reorganizations is to reduce fragmentation or change the tablespace design. However, many don’t realize that this feature can help you perform changes to the table structure (e.g., adding columns) or data clustering (e.g., via table sorting) online and significantly parallelized. I describe the most important possibilities in the following sections.

Sorting of Table Data

One major component in the area of SQL statement tuning is the optimization of the sort order of records within tables. Optimizing the record order can sometimes speed up central SQL statements by factor 20. BRSPACE provides an easy-to-use option for this task, the -r option:

brspace -c force -f tbreorg -t <table_name> -r <index_name>

With this command, the table <table_name> is implicitly sorted by the index <index_name>.

For an example, take sorting table RESB by index RESB~M:

brspace -c force -f tbreorg -t RESB -r RESB~M

Make sure that the temporary tablespace PSAPTEMP is sized sufficiently to hold the whole table data because all the records may need to be sorted in PSAPTEMP (dependent on the execution plan used for reading the source table). Be aware that sorting results in an increased runtime compared to a reorganization without sorting. Also take into account that based on the table change rate, the sort order quality reduces over time, so it can be necessary to repeat sorting (e.g., on a yearly basis).

Activation of an IOT

Similar to sorting the table data, you can use an IOT to guarantee a certain sort order of the table records. An IOT can be particularly useful if a table has only a primary SAP index and sorting by this index is useful. See SAP Note 641435 for a detailed discussion of IOTs.

As of BRSPACE 7.10 (22) a functionality is available that allows the transition from a normal heap organized table to an IOT and vice versa. See SAP Note 1325304 for more details.

Activation of Partitioning

In times of growing tables, highly concurrent accesses, and critical archiving activities it becomes more important to consider partitioning of certain tables. When you have identified an optimal partitioning layout for a table, you can use BRSPACE to activate it during an online reorganization:

Step 1. Start a BRSPACE online reorganization with the -d first option, which generates the DDL commands for the target table and indexes, and then waits for a user interaction:

brspace -f tbreorg -t <table_name> -d first

Step 2. Open the file <sapdata_home>/sapreorg/<reorg_subdir>/ddl.sql, which contains the CREATE commands for the target table and indexes.

Step 3. Include the necessary PARTITION clauses in the CREATE TABLE command, such as:

PARTITION BY HASH (<column_name>) PARTITIONS 16

for hash partitioning on <column_name> with 16 partitions.

Step 4. If you also want to partition indexes, include the relevant commands in the CREATE INDEX clause(s), such as LOCAL for a locally partitioned index. (A locally partitioned index is an index that is partitioned in the same way as a table.)

Step 5. Continue the BRSPACE online reorganization.

Appending Columns

Appending columns with NOT NULL values to an existing table can cause significant problems when the table contains a high number of rows, such as:

• Long runtime because each record has to be modified individually
• ASSM problems can be responsible for an even longer runtime
• Possibility of chained rows when there is not enough free space in an Oracle block to hold the additional column values
• Access performance can be significantly affected by the existence of chained rows
• During the conversion no changes to the table are possible

All these often critical disadvantages are avoided by BRSPACE, for example:

• The table is always accessible because no locks are set
• No unnecessary chained rows
• Significantly reduced runtime because of the parallelization option and recreation of the table from scratch

You can add new columns based on a BRSPACE online reorganization in the following way:

Step 1. Make sure that at least BRSPACE 7.10 (24) is in place.

Step 2. Maintain the parameter _reorg_new_col in the BRSPACE profile to define the new columns, such as:

_reorg_new_col =

          ('"ZNEW1" NUMBER    DEFAULT 0   NOT NULL',

           '"ZNEW2" VARCHAR2(20) DEFAULT ' ' NOT NULL')

Step 3. Start a BRSPACE online reorganization with the -ANC (add new column) option:

brspace -c force -f tbreorg -t <table_name> -ANC

Step 4. Remove the _reorg_new_col parameter from the BRSPACE profile.

Activation of Compression

Index and table compression can be useful under certain conditions to reduce the allocated space and optimize the performance (see SAP Note 1289494 for more information). As of BRSPACE 7.10 (24) compression can be activated (and deactivated) on a table and index level using the following options:

• -CBD: Activation of table compression
• -DBD: Deactivation of table compression
• -CBB: Activation of default index compression
• -CBB <column_count>: Activation of index compression of <column_count> columns
• CBB 100: Activation of index compression based on VALIDATE STRUCTURE results (note that this can cause increased runtime and locks)
• -DBB: Deactivation of index compression

Particularly in cases with manual intervention there is the risk of inconsistencies and undesired effects in case of mistakes. Therefore you should use these possibilities with care and test them carefully before applying them in production environments.

Martin Frauendorfer

Martin Frauendorfer studied computer science at the University of Erlangen, Germany. He joined SAP Support in 1999 and has focused on Oracle-related problems since then. Today he works as a service architect in SAP Solution Operation Support. His main tasks involve supporting large, critical SAP customers who use Oracle databases. Key focus areas are Oracle performance tuning and Oracle administration.

You may contact the author at martin.frauendorfer@sap.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.