Symptom
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
Note that R3trans should only be used for copying clients up to Release 2.1.
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.
