SAP Basis Tech Supports

Symptom
1. What does the term “I/O-intensive database operations” refer to in this note?2. What problems can occur as a result of these database operations?3. How can I minimize the effects of these operations?4. When do I actually need to use I/O-intensive database operations?5. Which online options are available?6. Which aspects do I have to bear in mind to optimize performance?7. How can I best execute I/O-intensive database operations with Oracle tools?8. How can I best execute I/O-intensive database operations with SAP tools?
Other terms

FAQ
Solution
1. What does the term “I/O-intensive database operations” refer to in this note?
The term refers to the following tasks, which are necessary during the database administration or the structuring of the system, and which affect large data volumes:Creating indexesRebuilding indexesCoalescing indexesGenerating statisticsStructure validation of objectsDetermination of index fragmentationReorganization of tablesSystem copy / Unicode conversion with R3LOADMedia recoveryClient deletionTable conversionIncremental conversion
The term does not refer to database-intensive transactions that are executed by the application in the production system.2. What problems can occur as a result of these database operations?
Various problems can occur that affect the production operation directly:The operations may place locks on important objects so that a production operation that runs in parallel is now only possible with restrictions.The processing of large datasets can use up large amounts of system resources so that a production operation is now only possible with restrictions.Certain objects may be temporarily inaccessible.Due to long runtimes, the operation may not be completed within the planned downtime.3. How can I minimize the effects of these operations?
To minimize the extent, runtime and effects of I/O-intensive database operations, consider the following questions:Do I actually need the database operation in question?Can I execute the operation online without locks?Can I speed up the operation by setting parameters or using parallel processing?
These questions are answered below.4. When do I actually need to use I/O-intensive database operations?
Before you think about optimizing an I/O-intensive operation, you should ask yourself if the operation is required. Take into account the following information:Creating indexes
It makes sense to create an index if the application requires a new index (see Note 766349 for more information). It is a good idea to delete and recreate an index if the index structure has changed (for example, if a column was added) or if you want to clean up a corrupt index or adjust storage parameters such as INITRANS. In other cases, it may be better to rebuild or coalesce the index.Coalescing indexes
It is a good idea to coalesce an index if an index has become fragmented (see Note 771929) and the fragmentation impairs database performance.Rebuilding indexes
Rebuilding an index is another way of eliminating index fragmentation. Unlike coalescing, the index is completely rebuilt, which leads to more extensive defragmentation. In addition, you can use a rebuild to move an index to another tablespace or to reduce the space requirements of an index.
Note 771929 describes in detail the differences between rebuilding and coalescing indexes.Generating statistics
Statistics are generated by BRCONNECT by default. New statistics are generated if the number of table entries has changed by more than 50% since the last time statistics were generated. New statistics must also be generated manually if new objects (tables, indexes, columns) were created. For more information, see Note 588668.Structure validation of objects
An object validation using VALIDATE STRUCTURE is the most comprehensive consistency check. Possible alternatives with a lesser degree of accuracy are DBVerify and Export. Refer to Note 540463 for further details.Determination of index fragmentation
As described in Note 771929, check whether fragmented indexes can also be determined with methods that do not cause any high system load (BRCONNECT messages, index size, index blocks in the buffer pool, buffer gets/execution).Reorganization of tables
Note 541538 describes the circumstances under which a reorganization can be useful.System copy / Unicode conversion with R3LOAD
System copies are necessary when you want to change the hardware platform or database, or when you want to rebuild or update a system of the system group based on an existing system. Frequently, you have to use R3LOAD to perform the system copy since other mechanisms such as R3COPY or EXP/IMP are not supported.
A Unicode conversion is a specific instance of a system copy to convert the system to Unicode.Media recovery
Notes 605062 and 491160 describe the situations in which a recovery may be required.Client deletion
It is a good idea to delete a client if it is no longer in use and you want to release the space that the client uses in the database and possibly use it for other purposes.Table conversion
A table conversion is required if (for example, as part of a transport or an upgrade) changes must be made to the table structure (for example, adding additional columns or changing the column types).Incremental conversion
The incremental conversion (Transaction ICNV) is used to convert large online tables within the framework of SAP upgrades. For example, this is a good idea if a large cluster table is to be converted into a transparent table.5. Which online options are available?
Coalescing an index is always executed without locks.
As of Oracle 8i, you can rebuild indexes online. However, note that temporary locks are still set so the online function is restricted (refer to Note 682926, point 2).
As of Oracle 9i, you can create indexes, reorganize tables without RAW and LONG RAW fields and validate structures online. You can use ICNV (Note 541538) to restructure tables with RAW and LONG RAW fields during production operation.6. Which aspects do I have to bear in mind to optimize performance?
To avoid unnecessarily long runtimes, bear in mind the following points before you execute critical I/O-intensive operations:Oracle parameter DB_FILE_MULTIBLOCK_READ_COUNT
This parameter specifies how many blocks the hard disk reads at once during a full table scan or a fast full scan of an index. The higher you set the parameter, the better these operations perform (up to the operating system limit). The Oracle default for this parameter is 32, lower values may be used in the SAP environment (see Note 124361). However, to optimize performance, you can set much higher values – such as 128 – for I/O-intensive operations.
Caution: The parameter value affects the cost calculation of CBOs (see Note 750631). Therefore, you must ensure that the parameter is only adjusted for the I/O-intensive database operation. You can set the parameter for individual sessions by using:
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT =
Oracle parameters SORT_AREA_SIZE / PGA_AGGREGATE_TARGET
With parameter SORT_AREA_SIZE (traditional PGA) or parameter PGA_AGGREGATE_TARGET (dynamic PGA), you can define the size of sorting areas in the memory (see Notes 619876 and 789011). The larger the sorting area, the faster operations – such as building the index, GROUP BY or ORDER BY – are processed. You can also use ALTER SESSION to adjust these parameters dynamically.Oracle parameters DB_CACHE_SIZE / DB_BLOCK_BUFFERS
Under certain circumstances, enlarging the Oracle buffer pool can significantly improve the performance of the I/O-intensive operation. Enough free physical memory must be available to be able to do this. For more information, see Note 789011.
Sometimes, a big buffer pool can have a negative effect on performance. For Oracle Release 10.2.03 or lower see Note 1028099.Other Oracle parameters
Make sure not to set any Oracle parameters deviating from the SAP Standard, which could cause an additional load to the system. For example, you should not set DB_BLOCK_CHECKING to TRUE, in order to avoid unnecessary block checks.
It may also be useful to set the parameter DB_BLOCK_CHECKSUM to FALSE in the critical time period.NOLOGGING option
You can use the NOLOGGING option to prevent redo log information from being created at certain points (for example, when the index is constructed). The advantage of this is a significant improvement in performance. The disadvantage is that a recovery of the operation is not possible (see Note 547464, for example).Redo log configuration
To avoid unnecessary checkpoint load and to reduce bottlenecks to the online redo logs, you should optimize the redo log layout. See Note 793113 for information, and make sure that no more than one log switch per minute occurs and that sufficient redo logs are available.NOARCHIVELOG option
If the system is not used live in parallel to the I/O-intensive operation, and if you can avoid recovering the operation in the case of an error, the ARCHIVELOG mode of the database can be temporarily deactivated so that no Offline Redo logs are written. However, after the ARCHIVELOG mode is reactivated, a consistent backup must be made immediately so that, if a problem occurs, there is a backup to the restore/recovery.Parallel Query
Many database operations such as the building of an index or full table scans can be executed in parallel at database level. Note 651060 describes the prerequisites and provides other details. Parallel Query is only useful if sufficient system resources (especially CPU resources) are available. In general, the number of CPU resources should be more than twice the level of parallel processing. Under certain circumstances, Parallel Query may cause performance to deteriorate (for example, if insufficient CPU resources are available or if the Parallel Query processes generate hot spots at hard-disk level). In order to optimize the parallel query performance, you must set PARALLEL_MAX_SERVERS to a sufficiently high value (that is, at least to the required number of parallel query slaves) and set PARALLEL_EXECUTION_MESSAGE_SIZE to at least 16384.Parallel processing on the client
Many client tools such as BRSPACE, BRCONNECT and R3LOAD provide their own parallel processing options, which can reduce the overall runtime significantly. The following also applies here: There must be sufficient resources to allow a parallel execution.7. How can I best execute I/O-intensive database operations with Oracle tools?
All operations profit to a greater or lesser extent from an increased DB_FILE_MULTIBLOCK_READ_COUNT and an increased sorting area. Therefore, you should increase the relevant parameters for the operations as described above.Creating indexes
CREATE INDEX … [ONLINE] [PARALLEL ]
[NOLOGGING]
Choose ONLINE to create the index ONLINE without locks on tables.
Choose PARALLEL to activate Parallel Query with a < degree> level of parallel processing.
Choose NOLOGGING if you do not want redo log information to be generated.
The individual options are independent of each other and can be used as required.
See also Note 334224.Rebuilding indexes
ALTER INDEX … REBUILD [ONLINE] [PARALLEL ]
[NOLOGGING]
Choose ONLINE to rebuild indexes without locks on tables. However, bear in mind the restrictions described in Note 682926 (2).
Choose PARALLEL to activate Parallel Query with a < degree> level of parallel processing.
Choose NOLOGGING if you do not want redo log information to be generated.
The individual options are independent of each other and can be used as required.Coalescing indexes
ALTER INDEX COALESCE [PARALLEL ];
Choose PARALLEL to activate Parallel Query with a < degree> level of parallel processing.Generating statistics
ANALYZE TABLE … STATISTICS …
DBMS_STATS.GATHER_TABLE_STATS(… [, degree => ]);
The generation of statistics by means of ANALYZE TABLE cannot be processed in parallel internally. If you use DBMS_STATS, you can specify a level of parallel processing with “degree => “. For more detailed information on generating statistics with ANALYZE TABLE and DBMS_STATS, see Note 588668.
Also refer to Notes 632336, 684545, 711047 and 906139 for information on optimizing DBMS_STATS runtimes on Oracle 9i.Structure validation of objects
ANALYZE TABLE
VALIDATE STRUCTURE CASCADE [ONLINE];
Choose ONLINE to perform the validation ONLINE without locks on tables.
For Oracle 10g, you should also refer to Note 1022097.Determination of index fragmentation
ANALYZE INDEX VALIDATE STRUCTURE;
You cannot use the ONLINE option since significant analysis results are no longer determined with this option (see Note 444287).Reorganization of tables
You can use EXP and IMP to reorganize tables at Oracle level offline, or you can use DBMS_REDEFINITION to reorganize tables at Oracle level online. As of Oracle 10g, Data Pump is also available for offline reorganization (see Note 1013049). However, we recommend that you use the BRSPACE tool, which calls the Oracle functions.Media recovery
RECOVER DATABASE … [PARALLEL ]
You can use the PARALLEL option to divide the recovery among processes.Client deletion
Note 857973 contains expert options for speeding up the deletion of a client on Oracle level.Table conversion
You can avoid long-running table conversions when adding columns if a default value is not required and if the application tolerates NULL values (which is not always the case).
In certain cases, ASSM problems are responsible for long runtimes (see Note 1124639).Incremental conversion
If a table is converted without CBO statistics, generating statistics can improve performance in certain cases, since the related use of CBO also enables access paths such as fast full scans of indexes.8. How can I best execute I/O-intensive database operations with SAP tools?
Bear in mind the following: To improve performance, you should set parameters DB_FILE_MULTIBLOCK_READ_COUNT and PGA_AGGREGATE_TARGET or SORT_AREA_SIZE as described above.
To perform I/O-intensive administration tasks, we recommend that you use the BRSPACE (see Note 647697) and BRCONNECT (see Note 403704) tools.Creating indexes
Indexes are created in the R/3 environment by default with transaction SE11 or SE14 in the context of transports. These mechanisms do not support any of the methods for optimizing performance described in this note. Therefore, if necessary, you must use other tools to create indexes (Note 334224).Rebuilding indexes
You can use BRSPACE to rebuild indexes as follows:
brspace -f idreorg -i [-p ] [-e ]
You can use the “-p” option to activate parallel processing on the client – BRSPACE can then rebuild multiple indexes at the same time.
You can use the “-e” option to activate Parallel Query.Coalescing indexes
BRSPACE provides the following command for coalescing indexes:
brspace -f idalter -a coalesce
Parallel processing is not supported by BRSPACE.Generating statistics
You can use the following BRCONNECT commands to generate statistics for one table or all tables:
brconnect -f stats -t

[-p ] [-g ]
brconnect -f stats -t all [-p ] [-g ]
You can use the “-p” option to activate parallel processing on the client (see Note 403713).
You can use the “-g” option to activate Oracle parallel execution for DBMS_STATS. See also Note 424239.
When using DBMS_STATS you can switch from Row Sampling to Block Sampling in order to reduce the number of block accesses needed (see Note 424239).
Do not set the BRCONNECT parameter STATS_CHANGE_THRESHOLD lower than necessary. Otherwise, statistics are created again for an unnecessary number of tables.
With Oracle 9i and lower, you can activate table monitoring (see Notes 408527, 628590) to shorten the time-intensive check phase of generating statistics. As of Oracle 10g, this feature is active by default.
Refer also to the other notes listed above for optimizing DBMS_STATS performance on Oracle.Structure validation of objects
You can use the following BRCONNECT commands to validate the structure of one table or all tables (including their indexes):
brconnect -f stats -t
-v [-p ]
brconnect -f stats -t all -v [-p ]
You can use the ” -p” option to activate parallel processing on the client – BRCONNECT can then process several tables at the same time.
As of Oracle 9i, the operation is automatically executed online.Determination of index fragmentation
brconnect -f stats -t -v index_store [-p ]
You can use the ” -p” option to activate parallel processing on the client – BRSPACE can then process several indexes at the same time.
Caution: The operation always locks the relevant table, since the necessary statistical information can only be determined when the table is locked (see Note 444287).Reorganization of tables
You can use the following BRSPACE command to reorganize tables online:
brspace -f tbreorg -t
[-p ] [-e ]
You can use the “-p” option to activate parallel processing on the client – BRSPACE can then reorganize several tables at the same time.
You can use the “-e” option to activate Parallel Query.
You can execute an offline reorganization (which is necessary for tables with LONG and LONG RAW fields and with Oracle 8.1.7 or lower) as follows:
brspace -f tbexport -t
brspace -f tbimport -t
[-m no]
When you specify “-m no”, commits are suppressed during the import, and this can lead to significant improvements in performance. However, you should note that PSAPROLL or PSAPUNDO must be larger than the largest table to be imported.
You can execute an offline reorganization on the basis of Data Pump as of Oracle 10g and BRSPACE 7.00 (17) as follows: You can parallelize the export phase and the import phase by specifying “-p “.
Refer to Note 646681 for more information about reorganization with BRSPACE.System copy / Unicode conversion with R3LOAD
For the parallel processing of R3LOAD on the client, refer to the information on system copy in the relevant guides.
In addition, you can activate Parallel Query to parallel process full table scans that are executed during an export:
ALTER TABLE
PARALLEL ;
To ensure that the system does not use an index access instead of a more suitable full table scan, you should set parameter OPTIMIZER_INDEX_COST_ADJ to 100.
As of R3load 6. 40, you have the option of using a direct path import, which avoids the Oracle buffer pool and therefore performs better. For more information, see Note 1045847.
See also Note 936441, which contains extensive information on tuning R3LOAD activities on Oracle.
For information about how to considerably reduce the time required by R3szchk to calculate the storage requirement, see Note 1047369.
You can split larger tables using Notes 1043380 and 952514.
SAP internal Note 960280 is also available, which contains optimizations for using R3TA on Oracle.
Note 954268 describes how performance gains can be made using unsorted unloading.
For information about the Unicode conversion, see also Note 857081.Media recovery
brrecover … [-e ]
When you specify the ” -e ” option, a parallel recovery is carried out with as the level of parallel processing.Client deletion
See Note 365304. The option for parallel processing the client deletion on SAP is described in Note 541311.Table conversion
Note 1287382 describes how you can convert tables in the BW environment.
In addition, parallel and unlocked conversions are also often possible on the basis of BRSPACE online reorganizations. However, this requires additional manual actions and can lead to unwanted results if the procedure is performed incorrectly.Incremental conversion
The incremental conversion with ICNV uses a cursor internally, which uses the entries of the source table for the loop. Every entry is transferred accordingly and then deleted from the source table. This approach is optimal.
However, to avoid ORA-01555 errors, the cursor is closed after an hour and then reopened. As a result, all cleared areas of the table (or the index used) have to be read again before the first data record that still exists is found. In the case of very large tables, it may take more than an hour to scan all blocks that have already been cleared, which has a very negative effect on the overall performance. To solve this problem, manually modify the timeout. To do this, adjust the following row of report RADIV050:
IF RUN_TIME > max_table_time.
Max_table_time (Default: 3600) can be replaced with a different number of seconds until the timeout occurs. Since max_table_time is also used in other reports, you should not adjust the value of the constants. Instead, you should replace this constant with the corresponding numeric constant. Therefore, the following entry would be for a timeout of one day:
IF RUN_TIME > 86400.
Since RADIV050 is a template, you cannot use the Modification Assistant to carry out the change.
After you adjust the value, you must stop and restart the data transfer in ICNV in order to activate the new setting.

Permalink

Leave a Comment

Name (required)

E-mail required)

URI

• Related

  • Support for overloaded operations in web service
  • FAQ: Database structure check (VERIFY)
  • Monitoring operations on MDM Server
  • OpenSQL database access for the configuration engine

• Links

  • SAP BW / BI Supports
  • SAP Financial Consulting
  • SAP Netweaver Techs
  • SAP Netweaver FI / CO supports
  • SAP CRM / CS Solutions
  • SAP BASIS troubleshoot
  • SAP Netweaver Portal ( Enterprise Portal )
  • SAP netweaver MDM Master Data Management
  • ORACLE PL/SQL Development

• ADSM; ALE allocator ALV analysis asp.net oracle backint; backup backup and replace Cache_Size; capital intensive CCMS CHECK classification cserv.p daemon tool data database db2 restore DB13 DB13C DB59 deamon tool demon tool drive information external FAQ frequ Frequently Asked Questions heap; imogen heap information information schema intensive intensive care intensive mask intensive premium maxdb parameter Q&A statistics verify verify v1 VERITAS;

SAP and the SAP logo are registered trademarks of SAP AG. No warranties on the information provided are offered or implied.
SAP Basis Tech Supports · Entries (RSS) · XHTML · CSS