Basis and others: 806554 - FAQ: I/O-intensive database operations

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

Reason and Prerequisites

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 indexes

Rebuilding indexes

Coalescing indexes

Generating statistics

Structure validation of objects

Determination of index fragmentation

Reorganization of tables

System copy / Unicode conversion with R3LOAD

Media recovery

Client deletion

Table conversion

Incremental 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 LONG and LONG RAW fields, and validate structures online. You can use ICNV (Note 541538) to restructure tables with LONG 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 = <new_value>

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 <index_name> ... [ONLINE] [PARALLEL <degree>]
[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 <index_name> ... REBUILD [ONLINE] [PARALLEL <degree>]
[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 <index_name> COALESCE [PARALLEL <degree>];

Choose PARALLEL to activate Parallel Query with a < degree> level of parallel processing.

Generating statistics

ANALYZE TABLE ... STATISTICS ...
DBMS_STATS.GATHER_TABLE_STATS(... [, degree => <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 => <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 <table_name> 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 <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 <degree>]

You can use the PARALLEL option to divide the recovery among <degree> processes.
After each recovered archive log, a checkpoint must be performed. Therefore, checkpoint tuning measures (such as increasing the number of DBWR processes or using larger redo logs) can also help.

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).

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 <indexes> [-p <degree>] [-e <degree>]
           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 <table> [-p <degree>] [-g <degree>]
brconnect -f stats -t all [-p <degree>] [-g <degree>]
           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 <table> -v [-p <degree>]
brconnect -f stats -t all -v [-p <degree>]
           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 <index> -v index_store [-p <degree>]

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 <tables> [-p <degree>] [-e <degree>]
           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 <tables>
brspace -f tbimport -t <tables> [-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 <degree>".
           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 <table> PARALLEL <degree>;
           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.
           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 <degree>]

When you specify the " -e <degree> " option, a parallel recovery is carried out with <degree> 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.

Header Data

Released On

11.01.2012 21:50:03

Release Status

Released for Customer

Component

BC-DB-ORA Oracle

Other Components

SV-BO Backoffice Service Delivery

Priority

Recommendations / Additional Info

1013049		FAQ: Oracle Data Pump
1022097		Analyze table validate structure cascade online is slow
1028099		Long import runtimes for large db_cache_size
1043380		Efficient Table Splitting for Oracle Databases
1045847		ORACLE DIRECT PATH LOAD SUPPORT IN R3LOAD
1047369		Faster DB accesses for R3szchk using Oracle DBSL
124361		Oracle parameterization (R/3 >= 4.x, Oracle 8.x/9.x)
1287382		Extending BW objects on Oracle with large tables
1319517		Unicode Collection Note
334224		Important notes for creating indexes
365304		CC-ADMIN: Reports for deleting tables
403704		BRCONNECT - Enhanced function for Oracle DBA
408527		Checking the statistics using DBA_TAB_MODIFICATIONS
424239		New BRCONNECT parameter: stats_dbms_stats
444287		Checking the index storage quality
491160		Restore scenarios for lost files of oracle databases
541311		CC-INFO: Parallel processes FAQ
541538		FAQ: Reorganization
547464		Nologging Option when creating indexes
548016		Conversion to Unicode
588668		FAQ: Database statistics
592393		FAQ: Oracle
605062		FAQ: Restore and recovery
618868		FAQ: Oracle performance
619188		FAQ: Oracle wait events
619876		Oracle9i: Automatic PGA Memory Management
628590		Activating table monitoring using "-f monit" option
632336		Long runtimes when compiling statistics on Oracle 9.2.0.x
646681		Reorganizing tables with BRSPACE
647697		BRSPACE - New tool for Oracle database administration
651060		FAQ: Oracle Parallel Execution
682926		Composite SAP note: Problems with "create/rebuild index"
684545		Statistics generation: DBMS_STATS runs longer than ANALYZE
711047		Performance problem on DBMS_STATS in 9.2
750631		Approximations for cost calculation of the CBO
766349		FAQ: Oracle SQL optimization
771929		FAQ: Index fragmentation
789011		FAQ: Oracle memory areas
793113		FAQ: Oracle I/O configuration
842240		FAQ: Backup strategy of large and highly-available databases
85558		Tables with data sorted by index
857081		Unicode conversion: downtime estimate
857973		Deleting clients efficiently using Oracle
906139		Histogram creation takes longer with 9.2 than with 8.1.7
912620		FAQ: Oracle indexes
936441		Oracle settings for R3load based system copy
952514		Using the table splitting feature
954268		Optimization of export: Unsorted unloading

806554 - FAQ: I/O-intensive database operations

No comments: