Reorganization

Use

This section helps you develop an approach to reorganization, which improves the structure of the database, and can result in improved performance. BRSPACE performs the reorganization using the new Oracle feature, online table redefinition. The following graphic shows some of the reasons for reorganization:

For more information on how reorganization with the Oracle DBMS_REDEFINITION package works, see Reorganization with the Redefinition Package.

Prerequisites

Is reorganization really necessary?

You need to reorganize less often than in the past due to the following:

·        With locally managed tablespaces, space allocation inside a tablespace is now more efficient. The parameters MAXEXTENTS and NEXT no longer exist. Previously, incorrect use of these parameters often caused Oracle to create too many or too large extents, so wasting space.

·        Automatic segment space allocation reduces internal fragmentation within Oracle blocks and improves the performance of parallel queries.

·        Large disks and RAID systems with large and secure memory buffers reduce I/O hotspots. It is less important to distribute data files manually to different disks during reorganization because the setup itself can improve performance.

However, you might still need to reorganize if the following factors apply:

·        You want to transform dictionary managed into locally managed tablespaces.

·        You want to move certain large and heavily used tables into separate tablespaces.

·        There are fragmented tables or indexes in dictionary managed tablespaces that you are still using. You can identify these by using the database system check. Check the parameters TOO_MANY_EXTENTS, CRITICAL_SEGMENT, and PCTINCREASE_NOT_ZERO. For more information, see BRCONNECT Default Conditions for Database Administration.

To see how you can use reorganization for your system, see Reorganization Case Study.

Effects of a Reorganization

A reorganization can have the following positive effects on the database:

·        The data from one object is merged into a single extent or into fewer extents.

·        The data from a tablespace with many small files is merged into one or more larger data files.

·        Freespace fragments in an object are merged into larger freespace segments. This process is called "defragmentation."

·        The fill level in the individual blocks is evened out, so reducing internal fragmentation.

·        Data chains are resolved in most cases.

Features

BRSPACE performs the reorganization using the new Oracle feature, online table redefinition, with the following advantages:

·        Online reorganization improves availability since the SAP system does not need to be stopped for the reorganization. To avoid a performance impact on the SAP system, make sure that you perform the reorganization when the system load is low.

You cannot perform online reorganization for tables with LONG or LONG RAW fields but you can convert them to CLOB or BLOB online. After this conversion, you can reorganize all tables online. For more information, see SAP Note 646681.

·        Parallel reorganization to improve performance. You can also reorganize without parallelism if you want to minimize the impact on the production database.

·        Less risky because Oracle creates a copy of the table and transfers the entire table contents before deleting the original table.

·        Consistency is guaranteed because all changes to tables currently being reorganized are preserved.

·        Easy restart for an aborted reorganization. The restarted reorganization only processes tables that have not yet been reorganized.

BRSPACE supports reorganization of partitioned tables and indexes. The reorganization does not change the partitions and their parameters, unless you actually change the Data Definition Language (DDL) statements.

If a partition of a partitioned table or index is in a tablespace that you want to reorganize, BRSPACE reorganizes all other partitions of the object in other tablespaces too, even if you do not specify that you want to reorganize the other tablespaces. In other words, BRSPACE reorganizes all partitions of a partitioned object.

BRSPACE also supports the reorganization of tables with all types of large object (LOB) columns. Large objects are recreated with the same physical characteristics as before the reorganization.

Activities

...

You can use table reorganization to:

·        Transform data dictionary managed tablespaces into locally managed tablespaces

·        Transform tablespaces in an old layout – that is, different tablespaces for data and indexes – into tablespaces in the new layout required for Multiple Components in One Database (MCOD), or vice versa

·        Move large tables to a separate tablespace

·        Reorganize tables due to internal or external fragmentation

The reorganization case study shows how you can transform data dictionary managed to locally managed tablespaces in the new SAP layout, that is, with a single large tablespace.

For more information on how to perform a table reorganization, see Reorganizing Tables with BR*Tools.

You can enter first for Create DDL statements in the BRSPACE menu or the command option –d|-ddl so that you can alter the Data Definition Language (DDL) statements for the reorganized tables. BRSPACE pauses and you can change the attributes of the following objects:

·         Table

·         Storage

·         Field

·         Index

For more information see Reorganizing Tables with BR*Tools or -f tbreorg.

If you change attributes, make sure that they:

·         Are syntactically correct

·         Do not contain any new fields

·         Are compatible with the SAP dictionary