Reorganization

Summary

Symptom

1. 1. What is reorganization?

1. 2. Which objects can I reorganize?

1. 3. In which situations is reorganization useful?

1. 4. In which situations does reorganization not make sense?

1. 5. Which tools are used in reorganization?

1. 6. How does an online reorganization with BRSPACE work?

1. 7. What are the advantages and disadvantages of online reorganizations?

1. 8. Can the SAP application run during the reorganization?

1. 9. What runtimes should I expect for a reorganization?

1. 10. How can I optimize the performance of a reorganization?

1. 11. How can I determine how many rows of a table have already been exported or imported?

1. 12. To save time, can I release the R/3 system even if indexes are still being created during reorganization?

1. 13. What do I have to do if a restore is required?

1. 14. Can I use a target tablespace with the name of the source tablespaces during an online reorganization?

1. 15. What do I need to remember with regard to TABART changes?

1. 16. What error scenarios are possible in the context of reorganizations?

1. 17. Where can I find further information about reorganization?
   

Other terms

FAQ, frequently asked questions

Solution

1. 1. What is reorganization?

              The term reorganization refers to the reconstruction of objects in the database. A distinction is made between offline and online reorganization:

• Offline reorganization

           It is not always possible to access the objects during an offline reorganization. In general, it is therefore necessary to close the SAP system during the reorganization.

           During an offline reorganization, data is exported from the database into an export dump file. The objects are then set up again based on the export dump.

           As of BRSPACE Patch 7.00 (28) or 7. 10 (4), you can also perform an offline reorganization based on ALTER TABLE MOVE for tables without LONG and LONG RAW columns (Note 1080376). In this case, the table is restructured without an export or import being required.

• Online reorganization

           You can always access the affected segments during the reorganization. In general, the SAP system can therefore be run in parallel to the reorganization.

           By default, online reorganizations are carried out based on the Oracle package DBMS_REDEFINITION. This means that no access locks occur when segments are copied in the database.

           Non-SAP tools sometimes execute online reorganizations based on a different system (for example, the contents of the Redo log).

1. 2. Which objects can I reorganize?

              The following objects can be reorganized:

1. 1. a) Tables: Individual tables or groups of tables can be reorganized, and restructured in the same or a new tablespace. The corresponding indexes are also set up implicitly.

1. 1. b) Indexes: The reconstruction of indexes is not a reorganization in the actual sense. Instead, commands such as DROP / CREATE, REBUILD or COALESCE can be used to set up an index again. For more information, refer to Note 332677. For this reason, this note does not deal with the rebuilding of indexes.

1. 1. c) Tablespaces: If all segments are reorganized in a tablespace, the tablespace itself can also be modified at the same time (for example, it can be made smaller or its data file structure can be adjusted).

1. 3. In which situations is reorganization useful?

• Tablespace reorganizations can be useful in the following situations:

• • Activating Oracle features such as LMTS or ASSM: To activate features such as LMTS (Note 214995) and ASSM (Note 620803), it is necessary to completely restructure the tablespaces in a reorganization.

• • Corrupt block in the freespace area: If DBV reports a corrupt block in accordance with Note 354293 in the freespace area of a data file, you can remove this block with a tablespace reorganization (including data files).

• • Incorrectly created data file: If you created an unnecessary data file or a data file with an incorrect size by mistake, you cannot simply delete this file with Oracle 9i or lower (even if it does not contain yet any data). Apart from resizing it, your only other option is to reorganize the tablespace.

• • Recovering disk space: If a tablespace contains a lot of freespace that is never used, you can reduce the size of the tablespace with a tablespace reorganization so that space is recovered on the hard disk.

• • Changing the number of data files: Tablespace reorganizations may be useful in certain cases to increase the number of the data files to avoid inode lock-problems (see Note 793113), or to reduce the number of data files to avoid long BEGIN BACKUP runtimes (see Note 875477).

• • Conversion to the new tablespace layout: If you decide that it makes sense to convert to the new tablespace layout in a certain case (Note 355771), you can carry out this conversion by reorganizing the corresponding tablespaces.

• The reorganization of tables can be useful under the following circumstances:

• • Hot spots at disk level: If certain hard disk areas are accessed much more often than other areas as a result of poor data distribution, which then leads to performance problems due to the higher load, it can help to relocate certain tables using a reorganization. However, before doing this, you must carefully check the Oracle I/O configuration in accordance with Note 793113.

• • Low table fill level: If blocks of tables are largely empty (for example, because data was archived and deleted earlier), this means that an unnecessary number of blocks are read to the Oracle buffer for a small amount of data. In addition, the table occupies more space on the hard disk than necessary. In this case, reorganizing the table can regain free space in the tablespace, and reduce the number of blocks that need to be read to the buffer. For more information, see Note 821687.

• • Changing the storage parameters of blocks that were already allocated: If you want to change memory parameters such as INITRANS (Note 84348) or FREELIST_GROUPS (Note 619188), you can only do so within a reorganization process.

• • Moving segments to another tablespace: You can only move segments to another tablespace by carrying out a reorganization. This is useful, for example, if you want to store large tables in a separate tablespace.

• • Reducing chained or migrated rows: If a table contains an unnecessary number of chained or migrated rows, these can be reduced by reorganization. Note, however, that many tables with long records (for example, tables based on LONG and LONG RAW columns) generally contain chained rows. In these cases, a reorganization does not cause a reduction.

• • Reducing extents: If tables were created in DMTS with a large number of extents (more than 1000), you should create these tables again with a lower number of extents to avoid unforeseen ST enqueue problems (see Note 787533).

• • Converting from LONG columns to LOB columns (Oracle 10 g or higher): As of Oracle 10g, you can use a reorganization to convert LONG columns to the LOB data type.

• • Change to the table entries: In certain cases, it may make sense to rebuild a table with a particular sorting, for example, to improve the clustering factor of an index (Note 832343). Note that you cannot resort the table with the regular reorganization tool. Rather, you have to carry out individual reorganization steps (Note 85558).

1. 4. In which situations does reorganization not make sense?

              In most cases, other than those mentioned above, reorganization does not make sense.

1. 5. Which tools are used in reorganization?

              We recommend that you carry out reorganizations in the SAP environment using the BRSPACE tool (Note 647697). This tooll has a user-friendly interface, its parameters can be set in various ways and it warns users of any potential risks that may result from their actions. It is primarily intended for online reorganization using DBMS_REDEFINITION, but if required can also be used for offline reorganizations using EXP and IMP or, as of Oracle 10g, using Data Pump (Note 1013049).

              As of BRSPACE Patch 7. 00 (28), you can also perform an offline reorganization based on ALTER TABLE MOVE for tables without LONG and LONG RAW columns (Note 1080376). This is preferable to the other offline methods, because the data has to be moved only once, and parallelization is possible.

              Note 646681 contains detailed information about reorganizing with BRSPACE.

              If the reorganization is used mainly to correct table fragmentation and to achieve more space in a tablespace, you can use Segment Shrinking as of Oracle 10g (Note 910389).

              As of SAP 4. 6x, you can carry out a reorganization of tables with LONG and LONG RAW fields while the system is running using transaction ICNV. This transaction is usually used to convert tables incrementally as part of an upgrade. The procedure described in Note 96515 is also suitable for Oracle databases. In addition, refer to Note 806554 for ICNV-related possibilities for optimization. The following tables cannot be converted with ICNV, since they are needed to execute ICNV: VBDATA, TST03, TBATG, TICNV and TCNV.

              In some cases, a table conversion with transaction SE14 can be an alternative to a BRSPACE offline reorganization. It has the advantage that you can continue using the SAP system while the conversion is running. However, you cannot access the table that is being converted. In addition you must ensure that no unexpected situations occur (for example, an error in the structure of the primary index) that lead to further problems (for example, duplicate keys).

              In addition, SAP also provided the SAPDBA tool for carrying out reorganizations. With regard to reorganization, this tool has been replaced by BRSPACE. For this reason, this note does not deal with SAPDBA.

              In some cases, it may be helpful to use Oracle functions such as ALTER TABLE MOVE, EXP/IMP or Data Pump directly. SAP permits you to use these Oracle commands, but we do not offer support if problems or errors occur as a result.

1. 6. How does an online reorganization with BRSPACE work?

              You start an online reorganization of tables or entire tablespaces using the BRSPACE function TBREORG as described in Note 646681. During reorganzation, BRSPACE carries out the following steps:

• BRSPACE uses DBMS_REDEFINITION.CAN_REDEF_TABLES to check whether the selected tables can be reorganized online.

• It increases the DB_FILE_MULTIBLOCK_READ_COUNT parameter to 128, to ensure an optimal Full Table Scan performance (see Note 806554).

• It determines the CREATE TABLE statement and the CREATE INDEX statement for creating target segments with DBMS_METADATA.GET_DDL.

• It determines dependent objects such as grants, constraints, triggers and comments using DBMS_METADATA.GET_DEPENDENT_DDL.

• It creates the target table with the naming convention <source_table>#$.

• It exports statistics of the source table using DMBS_STATS.EXPORT_TABLE_STATS.

• It calls DBMS_REDEFINITION.START_REDEF_TABLE (the central function of the DBMS_REDEFINITION-package).

• • It waits until all open changes are closed on the table to be reorganized.

• • It copies the source table data into the target table.

• • It logs all changes to the source table until the later DBMS_REDEFINITION.FINISH_REDEF_TABLE in a Materialized View Log (MLOG$_<source_table>), which is created in the default-tablespace of the user.

• It creates indexes on the target table in accordance with the naming convention <source_index>#$.

• It imports the previously exported statistics for the target table using DBMS_STATS.IMPORT_TABLE_STATS. (with BRSPACE 6.40 (below 49), 7.00 (below 34) and 7.10 (below 10), this step was performed at the end after renaming the indexes.)

• It finishes the online reorganization using DBMS_REDEFINITION.FINISH_REDEF_TABLE:

• • It copies the changes to the source table that occurred during the online-reorganization.

• • It swaps the names of the source table and the target table.

• It drops the source table.

• It changes index names from <index_name>#$ to <index_name>.

1. 7. What are the advantages and disadvantages of online reorganizations?

              Compared with an offline reorganization, an online reorganization has the following advantages and disadvantages:

• Advantages:

• • The reorganization can be carried out in parallel with the current operation. Lock situations or access errors do not occur.

• • All data must only be copied once. When you carry out an offline reorganization (based on import or export), two copy processes are always required (source object -> export-dump; export-dump -> target object). This often results in an improved performance.

• Restrictions:

• • With Oracle 9i or lower, an online reorganization of tables with LONG columns or LONG RAW columns is not possible. As of 10g, you can avoid the problem if you carry out a LONG2LOB conversion, as described in Note 646681.

• • Changes to the structure of a table (for example by adding columns or partitions) cause problems if they are implementes while the online reorganization is running. This is particularly the case for BW-based systems in which a large number of these types of DDL operations is carried out.

• • Twice the space is needed in the database because the source table or the source tablespace is only deleted once the target objects have been successfully created.

• • Up until Oracle 9i, the tablespace name always changes if a tablespace reorganization is carried out. As of 10g, it is possible to carry out a tablespace reorganization without having to change the tablespace name due to the RENAME TABLESPACE feature.

1. 8. Can the SAP application run during the reorganization?

              You can carry out an online reorganization with BRSPACE and use the SAP system at the same time. However, bear in mind that DDL operations (such as CREATE or DROP, which are used on a large scale when loading data in BW) may cause errors for reorganized objects. For this reason, we recommend that you carry out online reorganizations when no DDL operations are running.

              For offline reorganizations, the SAP system always needs to be closed.

1. 9. What runtimes should I expect for a reorganization?

              We cannot make any general statements regarding the runtime of a reorganization in relation to the volume of data, since the runtime is determined by many external factors (in particular, by the hardware used).

              Therefore, we can only provide runtime estimates based on experiences with previous reorganizations on the same system.

              Note that during offline reorganizations based on import or export, the data import can take three times longer than the data export.

              In addition, the final creation of the indexes is relatively time-consuming.

1. 10. How can I optimize the performance of a reorganization?

              Note the following points when analyzing and optimizing the performance of a reorganization:

• It is not always required to try to achieve the maximum reorganization performance. For example, if you carry out an online reorganization while the system is running, the duration of the reorganization is less important. Instead, make sure that there are still enough system resources available to the productive operation.

• See Note 806554, which describes the options available for optimizing the reorganization performance (for example, parameter setting, parallel processing).

• During runtime, carry out a wait event analysis as described in Note 619188.  This analysis allows you to make a precise prediction about the potential for optimization. It also makes it possible to determine the typical causes for performance problems based on a wait event analysis.

• A higher export runtime during the offline backup can be triggered by a poor I/O performance of the file system in which the dump file is created. This is mainly the case if a wait event analysis in accordance with Note 619188  shows that the main processing time of the export disappears outside of the database.

• Long-running exports and imports may also be linked to the activated concurrent I/O for the file system of the dump file. You must therefore ensure that no CIO is used for the dump file system.

• If the data import hangs, this may be due to an archiver being stuck (Note 391).

• If the index creation is very slow, the reason may be incorrect parameterization of the temporary tablespace (for example, if the default size of the extent is too small) or the PGA.  In this case, refer to Note 659946.

• If the extent is too small for the index itself, this may also result in an unnecessarily long runtime (due to a large number of space transactions) if the index is not created in an LMTS. You should therefore check whether the dimensions of the NEXT value are large enough.

• If performance problems occur because the system reorganizes many small tables and DBMS_METADATA.GET_DDL requires a lot of time, you can change to BRSPACE 7.00 (20) or higher. As of this release, BRSPACE only calls DBMS_METADATA.GET_DDL when it is truly necessary. For example, if a table has no dependent objects, the corresponding call is no longer executed.

• If the performance is negatively affected when you create NOT NULL constraints in Oracle 10g, you must ensure that you are using at least BRSPACE 7.00 (20).

1. 11. How can I determine how many rows of a table have already been exported or imported?

              To determine how many rows of a table have already been exported or imported during a lengthy export or import, you can manually extend the "FEEDBACK=<rows>" parameter in the generated EXP or IMP command before you start the reorganization. As a result, EXP or IMP always issues a new point when a <rows> row is exported or imported.

              If an import with commits is carried out, you can also use the

SELECT COUNT(*) FROM <table>;

              command to determine how many rows of the table have already been imported and committed.

1. 12. To save time, can I release the R/3 system even if indexes are still being created during reorganization?

              No, you must not release the R/3 system unless all indexes have been created. There are two reasons for this:

• If unique indexes are missing, the R/3 application may use existing key field combinations to write entries in tables. This situation is usually prevented by the existence of unique indexes, which do not allow these kinds of duplicate entries. As a consequence of entries that are not unique , the unique indexes can no longer be created later on and serious application inconsistencies may occur.

• If some indexes are missing, database access is very slow in certain circumstances, as the system must either carry out full table scans or use suboptimum indexes. In extreme cases, these kinds of long-running programs can shut down an entire system.

1. 13. What do I have to do if a restore is required?

              It should never be necessary to carry out a restore during an online reorganization. In the case of an offline-reorganization, however, various reasons can make a restore necessary:

• The time window for completing the reorganization is not sufficient.

• Critical errors such as corrupt export dumps occur (for example, Note 535675).

• Problems occur that cannot be corrected during the import process.

              A restore is usually only necessary if objects were already deleted from the database, and if this data cannot be restructured in a different way.

              If you have decided to carry out a restore to reset the database to the time before the reorganization, you must restore (and if necessary, incompletely recover) the whole database. It is not sufficient to restore only the tablespaces concerned, as their time stamps (SCN) would no longer match the other tablespaces.

1. 14. Can I use a target tablespace with the name of the source tablespaces during an online reorganization?

              If you want to create a tablespace with a changed data file layout ("Tablespace reorganization with data files"), you can do this easily with the offline reorganization. If you use the online reorganization, source and target tablespaces exist at the same time. This means that the target tablespace cannot have the same name as the source tablespace. Up until Oracle 9i, this that the target tablespace always has a different name to the previous tablespace. As of Oracle 10g, you can rename tablespaces. Therefore the final name of the target tablespace can correspond to the name of the source tablespace. See the section on tablespace reorganization under Oracle 10g in Note 646681.

1. 15. What do I need to remember with regard to TABART changes?

              TABARTs are defined in the TAORA and IAORA tables. Each TABART refers to exactly one tablespace. If you now create a new tablespace, BRSPACE automatically carries out the corresponding changes based on the "-1" parameter to TAORA and IAORA.

              BRSPACE also changes the TABART belonging to a segment in the DD09L table if there are any differences after a reorganization. From a SAP-DDIC point of view, this change is not advisable because the technical settings in DD09L are supposed to reflect the default settings and not the current status. In order to avoid problems with system copies with R3LOAD for which the DD09L entries are evaluated, this adjustment may still be advisable.

              For more details on TABARTs, see Note 666061.

              The following points must be considered with regard to these settings:

• Since upgrades and transports may undo the BRSPACE changes without being noticed, it is important to refer to the BRSPACE changes according to Note 778784 before a system copy with R3LOAD.

• In BW systems, TABARTs are also defined in other tables: RSDCUBE for infocubes, aggregates, and dimension tables; RSTS for PSA tables. When TABARTs are changed, the mappings must also be adjusted in these tables in accordance with Note 771191.

• When you use partitioned tables with globally partitioned indexes, the default tablespace must be adjusted on Oracle level as well using "ALTER TABLE ...  MODIFY DEFAULT ATTRIBUTES TABLESPACE <tablespace>" (Note 666061). This action is carried out automatically by BRSPACE.

• If the tablespace layout is only changed for one system of the system infrastructure (development, testing, production), you do not need to carry out this change in all involved systems in the same way. Instead, you only have to define the same TABARTs in TAORA and IAORA, but these do not have to refer to the same tablespaces in all systems.

           From a transport point of view, you do not have to change the technical settings of already existing tables (DD09L-updates), since the DD09L settings are only relevant when new objects are created.

1. 16. What error scenarios are possible in the context of reorganizations?

              Characteristic errors, causes and solutions are:

• Materialized view-related errors

           Refer to Note 741478, which contains the characteristic errors in the materialized views environment.

• Tablespace overflow (Note 3155)

           If the SYSTEM tablespace is affected, make sure that PSAPTEMP rather than SYSTEM is assigned to the user SYS as a temporary tablespace.

           If the default tablespace of the table owner is affected, make sure that as few data changes as possible are made to the tables to be reorganized, avoid reorganizing tables without a primary index, increase the default tablespace sufficiently, or temporarily select another default tablespace.

           If the target tablespace for reorganization is affected, make sure that no INITIAL extents that are too large have been allocated (Option " -l 2"), and configure the target tablespace with sufficient size.

• MAXEXTENTS error (Note 533455)

           Where possible, always use Locally Managed Tablespaces (LMTS). For DMTS, the solutions described above under "Tablespace overflow" are relevant. Instead of increasing the affected tablespaces, however, you must increase MAXEXTENTS or the extent size.

• ORA-00018: maximum number of sessions exceeded

           With large-scale parallel processing, the SESSIONS limit may be reached and the error described above may occur. In this case, increase the SESSIONS parameter sufficiently.

• BR0602E No valid SAP license found - please contact SAP

           If this error occurs when you carry out an import during an offline reorganization with BRSPACE, this may be connected to the drop of the license table MLICHECK after the export. The reason for this is that MLICHECK was not moved into a help tablespace in accordance with Note 646681 (14). Apart from an individual manual procedure for reconstructing MLICHECK, the only thing you can do in this case is to restore a backup.

• ORA-00955: name is already used by an existing object

           If this error occurs when you are creating the target table (with the suffix "#$"), it is probably because a preceding reorganization was canceled without the target table being deleted. In this situation, perform a cleanup:

brspace -f tbreorg -t "*" -a cleanup

• ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

           If ORA-01452 occurs when you create indexes after a BRSPACE offline reorganization, it is likely that the same data was importes several times. This may be the case if the first import experiences an error such as a tablespace overflow and if you then try to restart the import without taking further action (the BRSPACE import cannot be restarted). Instead, you should first drop all tables that have already been imported. Afterwards, the offline reorganization process can be restarted when the tables are created using ddl.sql .

• BR1110W Table <owner>.<table> has a LONG column
  BR1113E All tables have been skipped for reorganization

           These messages are issued when you try to reorganise one or several tables with LONG or LONG RAW colums online with Oracle 9i or lower. Instead, reorganise these tables offline.

• ORA-04098: trigger '<user>./BI0/05000...#$' is invalid and failed
             re-validation

           Triggers that follow the naming convention /BI0/05 are left over from BW compressions. Delete these triggers at Oracle level or on the basis of Notes 982120 (BW 7.00) and 1061807 (BW 3.x) and repeat the reorganization process.

• Error due to HIDDEN/UNUSED columns

           See Note 1129347, which describes how to handle problems in relation to HIDDEN columns or UNUSED columns (for example, ORA-00947).

1. 17. Where can I find further information about reorganization?

              Note 646681 contains information about reorganizing objects with BRSPACE.

              Note 741478 contains information about how materialized views can be used in online reorganizations and what problems can occur when you do this.

              Also refer to the relevant SAP online documentation