Copying, transporting or deleting clients with the help of R3trans takes too long.
This note gives you a few options for speeding up these actions.
In addition, it also contains information about the 'normal' values for the runtimes and which performance benefits can be expected for the different options.
A performance problem exists, if the throughput of R3trans is significantly below the following values:
- Client copy: 120 MB/hour
- Client deletion: 80 MB/hour
- Export: 200 MB/hour
- Import: 80 MB/hour
During a client export/import R3trans is implicitly called via the transport program tp and is therefore active up to Release 4.0.
Reason and Prerequisites
Several causes for the performance problems are possible:
1. Cluster tables
Cluster tables are an SAP mechanism that allows you to store several logically connected entries, also from different "logical" tables, in one "physical" table entry. In addition to other advantages (such as the definition of tables with a lot of key fields), these clusters also allow quick access to this data on the condition that the table entries that logically belong together are always read together.
For actions such as copying, transporting or deleting clients, these entries are not treated as a logical unit, but are processed as a simple table. This results in poorer performance than for transparently stored tables.
This performance deterioration is strengthened by the fact that the cluster records are compressed when they are stored. For actions such as CLIENTREMOVE, the cluster records must therefore first be read and expanded. The entries that belong to a certain logical table are then deleted. The remaining entries of the other logical tables are then recompressed and the changed cluster record is written back to the database. This is naturally costlier than a simple DELETE statement in the database.
The same applies to transporting and copying clients.
These problems occur particularly frequently with the following tables: CDCLS: CDHDR CDPOS PCDHDR PCDPOS (in Releases < 4.0A)
RFBLG: BSEC BSED BSEG BSES BSET
The end of this note describes how to determine the size of your cluster tables.
2. Transparent tables
For transparent tables (tables that have a corresponding table with the same structure at database level), there may be performance problems when copying with the option "client copy".
3. Main memory bottlenecks
When you transport clients, main memory bottlenecks can occur during the import. This can adversely affect paging or swapping at operating system level, depending on the system configuration.
4. Database problems
A further possible source of performance problems is:
a) Missing indices
b) Un-released locks
c) Lock escalation (ADABAS)
d) Rollback segments too small (Oracle)
e) Strong fragmentation of database indices or LONG RAW fields (Oracle)
f) (Wrong) statistics on internal tables (Oracle)
Solution
Depending on the cause, different solutions:
1. Cluster tables
a) Deletion of a client (clientremove)
If a client is deleted with R3trans, this is generally done with an R3trans control file that has approximately the following contents:
clientremove
client = ...
select *
R3trans now works through the physical cluster tables in the logical table sequence, causing the performance losses described above.
In Releases <= 3. 0F, a solution to this is to delete the largest clusters manually before starting R3trans:
Determine which clusters are particularly large.
For each of these clusters, delete the entries of the client to be deleted by manually executing the following SQL command, either with your own ABAP program or with database tools (sqlplus, dbaccess, xquery):
DELETE FROM <cluster_name> WHERE MANDT = '<client>'
Thus, for example
DELETE FROM RFBLG WHERE MANDT = '002'
You can then delete the remaining client specific table entries with R3trans and the control file described above.
Since the R/3 System carries out row-locking on the database level, during this process very large rollback segments or an extremely large number of locks are created on the database. Please refer to note 102034 for further details.
The names of all client-specific cluster tables can be found with the following SELECT command: SELECT TABNAME FROM DDNTT
WHERE TABTYPE = 'C' AND CLIENTPOS > 0
The performance benefit that is to be expected when you delete the physical clusters instead of the logical tables is approx. a factor of 3.
As of R3trans Release 3. 1G, this workaround is no longer required. For control files of the type
clientremove client = ...
select *
R3trans deletes at the physical level.
b) Copying a client (client copy)
Copying a client in a system is not generally handled by R3trans but by client administration tools within the SAP System.
If, for some reason, you want to copy individual tables directly with R3trans, this can be done with a control file of the following type:
clientcopy
source client = ...
target client = ...
select * from <tabelle>
select * from <tabelle>
For cluster tables, the same effects can be achieved as during client deletion. As a solution, it is recommended to delete the target clients first, as described under 1a) or to first delete the contents of the table to be copied.
If you want to copy all logical tables of a cluster, the name of the cluster table can also be entered directly in the R3trans control file. Example:
clientcopy source client = ...
target client = ...
select * from rfblg
The performance benefit that can be expected when you copy physical clusters instead of logical tables can be estimated at approximately a factor of 5 (if entries already exist in the target client) or 3 (if the target client is empty).
A client copy can also be carried out by export/import, see 1c). In this case, you can expect a performance gain in the region of a factor of 1.5 in comparison to a direct copy.
As of Release 3. 1G, R3trans copies the physical cluster tables directly with "select *" in a client copy.
c) Transport (Export/Import)
Transporting a client between systems is generally done with the client administration tools within the SAP System. These generate a transport request that is implicitly transferred to the transport tools tp and R3trans for the client transport. Depending on the chosen client copy profile, cluster tables can also be contained in such a client transport (especially if a copy profile was chosen "with application data").
The problems described above can also occur on cluster tables causing performance problems here.
A possible solution is to delete the larger cluster tables in the target system before the import, as described under 1a).
Reason: When importing, R3trans attempts to merge the table entries transported with the existing ones in memory and to only write the changed table entries. To do this, all entries from the logical tables must be read and expanded, requiring considerable resources. The strategy of only writing back changed records, in order to avoid database accesses is less effective for cluster tables, as a change to the logical table entry suffices to force the physical table entry to be written back to the database. A previous deletion of the physical clusters can significantly improve the speed of this.
A further way to speed this up is to exclude the cluster tables during the normal client transport and deal with them separately. The solution however, requires that the source and target systems are either both ASCII or both EBCDIC systems.
For this, proceed as follows:
For the purpose of the export, a transport is created by the client administration tools that can also contain the name of the logical cluster tables. The logical table names must be removed from this transport request by executing the following SQL command either with your own ABAP program or with the database tools (sqlplus, dbaccess, xquery):
UPDATE E071 SET PGMID = '*' WHERE TRKORR = '<transport_request>'
AND OBJ_NAME IN ('<tab1>','<tab2>',...,'<tabn>');
Then carry out the transport as usual:
tp export <transport_request> client=...
The missing cluster tables are then transported, so that the physical cluster tables rather than the logical ones are exported and imported when using R/3trans directly.
Exporting the physical cluster tables:
Log on as < sid>adm to an application server on the source system and change to the directory /usr/sap/trans/tmp.
Create an R3trans control file exp.ctl with the following contents: export
client = ...
file = '/usr/sap/trans/data/cluster.dat'
select * from <cluster_name>
select * from <cluster_name>
...Then list all physical clusters whose logical tables were previously removed from the transport request.
Start the export afterwards with the command R3trans -w exp.log exp.ctlA log is then written in the file exp.log, the data is exported to the file cluster.dat.
If you do not have a central transport directory, you must copy the file /usr/sap/trans/data/cluster.dat into the transport directory of the target system.
Importing the physical cluster tables:
Log on as < sid>adm to an application server on the target system and change to the directory /usr/sap/trans/tmp.
Create an R3trans control file imp.ctl with the following contents: import
file = '/usr/sap/trans/data/cluster.dat'
client = ...Then start the import with the following command: R3trans -w imp.log imp.ctlA log is written to the file imp.log.
If you only encounter performance problems during the import and can at least re-export the cluster records, it is not necessary to modify the previously generated transport request and export it a second time. In this case, it is sufficient to prevent the import of the logical cluster tables and then re-transport the physical cluster tables as described above.
You can prevent the import of the logical cluster tables as follows:
Terminate the import.
Mark the logical cluster tables as successfully imported with the following SQL command:
If R3trans release <= 3.0F:
UPDATE E071 SET LOCKFLAG = ':'
WHERE TRKORR = '<transport request>'
AND OBJ_NAME IN (SELECT TABNAME FROM DDNTT WHERE REFNAME =
'<clustername>')
If R3trans release >= 3.1G:
UPDATE E071 SET LOCKFLAG = '2'
WHERE TRKORR = '<transport request>'
AND OBJ_NAME IN (SELECT TABNAME FROM DDNTT WHERE REFNAME =
'<clustername>')
Afterwards, restart the terminated import and make sure that this runs without unconditional mode 1.
The performance benefit that can be expected when transporting the physical clusters instead of the logical tables is approx. a factor of 3.
Client transports with application tables are often required because a test system should be supplied with "actual" data from a productive system either on a regular basis, or just once. In these cases, it would also be possible to set up the test system as a database copy.
We are currently working on a solution whereby R3trans directly transports the physical clusters.
2. Transparent tables
a) When you delete or transport clients, no particular performance problems are known.
b) A performance loss can result when copying a client for transparent tables, so R3trans currently avoids array-inserts. Instead, the table entries are individually inserted into the target clients with INSERT/UPDATE sequences.
A possible solution here is to delete the target client (see 1a). As a result, all table entries can be written with INSERTs and need not be written on the second attempt with UPDATE.
A second possible solution is to carry out the client copy as an export and subsequent import in the target clients. In this case, the performance gain is achieved by merging the table entries in the main memory (described under 1c)), and by the fact that database accesses are saved and internal array-inserts are used.
We are currently working on a solution whereby array-inserts can also be used internally during the clientcopy.
3. Main memory bottlenecks
a) When you delete or copy clients, no memory bottlenecks are known to cause performance losses.
b) When you transport clients, it is possible that memory bottlenecks occur during the import that may have an effect on the performance. The memory requirement results from merging the table entries in memory, as described under 1c).
Note 6588 describes how to reduce the main memory requirement for an R3trans import.
4. Database problems
A further source of performance problems may be:
a) Missing indices
Missing indices always cause performance losses.
Problems of this type can be analyzed with Transaction ST04. For further details, see Note 15374.
b) Unreleased database locks from other processes (exclusive lockwaits)
Database locks from other processes cause the R3trans process to stop.
Problems of this type can be analyzed with Transaction ST04. For further details, see Note 15374.
c) Lock escalation (ADABAS)
For processes that block too many individual records of a table, the database system carries out lock escalation, that is, an attempt is made to set an exclusive lock at table level. If other processes work on the same table, the escalation process hangs. For further details, see Note 65946.
d) Rollback segments too small (ORACLE)
Rollback segments that are too small or a tablespace PSAPROLL that has been defined too small can cause R3trans to terminate and therefore also cause a time loss. Detailed information on this is contained in Notes 10146, 60233, and 102034.
e) Fragmentation of database indices or LONG RAW fields (Oracle)
If a client is created several times (for example, when you delete and copy clients periodically), it is possible that the increasing fragmentation of indices and LONG RAW fields causes a dramatical loss of performance. A typical feature of this problem is that the performance losses increase each time.
There has been very little research of these problems, and the solutions presented here should only be carried out after consulting the SAP Hotline.
Fragmentation of indices:
Potentially, all tables can be affected.
Solution: Delete the database indices for the tables in question and recreate them.
Fragmentation of LONG RAW fields:
This particularly affects the cluster tables.
The problem may occur under
UNIX with oracle release < 7.1.6
NT with oracle release < 7.2.3
Solution: Install an oracle patch (see Note 28293) or reorganize the concerned tables. Useful information to accelerate table reorganizations can be found in Note 12621.
f) (Wrong) statistics on internal tables (Oracle)
Very rarely, performance problems can be caused by statistics on Oracle internal tables which lead to the use of the cost based optimizer (CBO). For further information refer to Note 138639.
Determining the cluster tables and their current size To determine which cluster tables are particularly large in your system, proceed as follows:
- Determining all cluster tables with the DD information system:
Development -> Dictionary -> Environment -> Information system, or Transaction SE85.
Other objects -> Select Pooled/cluster tables.
Function "All selections", choose table type "CLUSTER" and execute.
You now get a list of all known clusters in the system.
- Open a second session and navigate to the database analysis tools:
Administration -> System administration -> Monitor -> Performance -> Database -> Tables/indexes.
- For each of the cluster tables in the list, determine the current memory requirements in the database as follows:
Select the function "Detailed analysis" and enter the name of the cluster.
On the next screen, position the cursor on the name of the cluster and select the function "Analysis".
The memory requirements for the table are now displayed.
No comments:
Post a Comment