Symptom
- 1. What is reorganization?
- 2. Which objects can I reorganize?
- 3. In which situations is reorganization useful?
- 4. In which situations does reorganization not make sense?
- 5. Which tools are used in reorganization?
- 6. How does an online reorganization with BRSPACE work?
- 7. What are the advantages and disadvantages of online reorganizations?
- 8. Can the SAP application run during the reorganization?
- 9. What runtimes should I expect for a reorganization?
- 10. How can I optimize the performance of a reorganization?
- 11. How can I determine how many rows of a table have already been exported or imported?
- 12. To save time, can I release the R/3 system even if indexes are still being created during reorganization?
- 13. What do I have to do if a restore is required?
- 14. Can I use a target tablespace with the name of the source tablespaces during an online reorganization?
- 15. What do I need to remember with regard to TABART changes?
- 16. What error scenarios are possible in the context of reorganizations?
- 17. Is it possible that performance deteriorates after an online reorganization?
- 18. Where can I find further information about reorganization?
Other Terms
FAQ, frequently asked questions
Reason and Prerequisites
Solution
- 1. What is reorganization?
- Offline 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
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).
- 2. Which objects can I reorganize?
- 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.
- 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.
- 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).
- 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 SAP Note 354293 in the freespace area of a data file, you can remove this block using 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). You can do this during BRSPACE online reorganizations as described in SAP Note 1016172. This is generally based on an SQL analysis, as the one provided by SAP as part of "Technical Performance Optimization - Oracle" services.
- 4. In which situations does reorganization not make sense?
- 5. Which tools are used in reorganization?
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.
- 6. How does an online reorganization with BRSPACE work?
- 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>.
- 7. What are the advantages and disadvantages of online reorganizations?
- 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 implemented 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.
- If you reorganize tables without a unique index (for example, BW fact tables), the system creates a ROWID column in the target table, which in individual cases can lead to an increase in the target table of up to 30% compared to the source table.
- 8. Can the SAP application run during the reorganization?
For offline reorganizations, the SAP system always needs to be closed.
- 9. What runtimes should I expect for a reorganization?
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.
- 10. How can I optimize 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).
- As of Oracle 11g, setting event 10995 to level 2 can prevent frequent reparsing during online reorganization; this particularly speeds up the reorganization of a number of small tables (see SAP Note 1565421).
- 11. How can I determine how many rows of a table have already been 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.
- 12. To save time, can I release the R/3 system even if indexes are still being created during reorganization?
- 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.
- 13. What do I have to do if a restore is required?
- 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.
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.
- 14. Can I use a target tablespace with the name of the source tablespaces during an online reorganization?
- 15. What do I need to remember with regard to TABART changes?
BRSPACE also changes the TABART belonging to a segment in the DD09L table if there are any differences after a reorganization. From an 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.
- 16. What error scenarios are possible in the context of reorganizations?
- Materialized view-related errors
- Tablespace overflow (Note 3155)
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)
- ORA-00018: maximum number of sessions exceeded
- BR0602E No valid SAP license found - please contact SAP
- ORA-00069: cannot acquire lock -- table locks disabled for <table>
ORA-08116: can not acquire dml enough lock(S mode) for online index
build
- ORA-00955: name is already used by an existing object
brspace -f tbreorg -t "*" -a cleanup
- ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
- BR1110W Table <owner>.<table> has a LONG column
BR1113E All tables have been skipped for reorganization
- ORA-04098: trigger '<user>./BI0/05000...#$' is invalid and failed
re-validation
- ORA-01792: maximum number of columns in a table or view is 1000
- ORA-14404: partitioned table contains partitions in a different tablespace
ALTER TABLE "<table_name>" MOVE PARTITION "<partition_name>"
TABLESPACE "<correct_tablespace_name>";
See Notes 666061 and 722188 and be aware that the MOVE command sets enqueues and that global indexes will become unusable (therefore, a rebuild is required).
- ORA-00600 [kdlm_merge_lobs:1]
- Error due to HIDDEN/UNUSED columns
- Incorrect results or ORA-12096/ORA-00942 errors with RAC
- 17. Is it possible that performance deteriorates after an online reorganization?
The deterioration in performance may also be caused by the use of Oracle Parallel Data Manipulation Language (DML) due to which the sequence of the data records may be mixed. This has negative effects on the clustering factors of indexes. If you use Oracle Parallel Execution (BRSPACE parameter "-e <degree>"), you can deactivate Parallel DML if required by using the additional BRSPACE parameter "-SPM". As a result, the system uses only the more harmless parallel processing variants Parallel Query and Parallel DDL.
- 18. Where can I find further information about reorganization?
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.
Header Data
| Released On | 12.03.2012 10:22:19 | ||
| Release Status | Released for Customer | ||
| Component | BC-DB-ORA-DBA Database Administration with Oracle | ||
| Other Components |
| ||
| Priority | Recommendations / Additional Info | ||
| Category | FAQ |
Validity
This document is not restricted to a software component or software component version
References
This document refers to:
SAP Notes
