SAP Basis Tech Supports

Symptom

R3ta calculation of ranges for packages for large Oracle database tables to be splitted during UNICODE migrations may take very long.
Other terms

R3ta, migration, unicode, table splitter, system copy, package splitting
Reason and Prerequisites

R3ta does not use any Oracle specific optimizations to calculate the ranges for large tables to be split.
Table splitting can be used for ABAP systems with SAP kernel 6.40 and above.
======================================================================
Requirements and Limitations
======================================================================
Only certified DB/OS migration specialists are allowed to use these optimizationsThe script is currently only supported for 10.2 or higher Database Versions.Cluster Table Limitation. The column PAGENO cannot be used when splitting cluster tables.Specific ROWID table splitting limitations:ROWID table splitting MUST be performed during downtime of the SAP system. No table changes are allowed for ROWID splitted tables after ranges have been calculated and export was completed. Any table change before the export requires a recalculation of the rowid ranges.ROWID splitted tables MUST be imported with the loadprocedure fast option of R3loadROWID table splitting works only for transparent and non-partitioned tables.ROWID table splitting CANNOT be used if the target database is a non Oracle databaseThe script does generate multiple ranges into a single WHR file for the ROWID case and for partitioned tables if the number of ROWID ranges for a table or the number of table partitions exceed the value of the input parameter . This may cause problems for the MigMon utility. R3load is able to process more than one range per WHR file.A description on how to integrate the WHR files in the SAPINST procedure is not available yet, therefore the consultant performing the migration should be experienced with the integration.Solution

A PL/SQL Package “SPLITTER” (attached to this note) has been developed to make the calcution of the ranges more efficient using Oracle specific optimizations.
Depending on the type of table (cluster/pool, transparent, partitioned) different methods of calculation for the ranges will be performed to optimize the R3load export phase of the table.
There are currently two versions of the splitter package available with the following different filenames:
a) splitter_hint.txt – This version must be used for Oracle 10.2.x b) splitter_hint92.txt – This version must be used for Oracle 9.2.x
You have to use the following R3load versions for the above packages:
R3load 7.10 version built newer than August 19th, 2008R3load 7.00 version built newer than June 18th, 2008R3load 6.40 version built newer than June 18th, 2008

You can check your R3load version by running R3load -version.
There is additionally the file presplitter.txt. This file assist in the setup of the tablesplitter.
1. Installation
Step 1:
Create necessary privileges for the schema user which is the owner of the tables to be split such as sapr3 by connecting to the database as SYSDBA:
sqlplus
connect / as sysdba
grant select on dba_extents to ;
Step 2:
Select the Output Directory and create it in Oracle:
sqlplus
connect / as sysdba
create directory “~TABLE_SPLITTER_RANGES_DIR”
as ‘‘;
grant read, write on directory “~TABLE_SPLITTER_RANGES_DIR”
to ;
This step can be automated by renaming the file presplitter.txt to presplitter.sql and then executing the following commands:
sqlplus
connect / as sysdba
start presplitter
It defaults the output directory to the tmp directory of the OS.
Step 3:
Rename attached file from “splitter_hint.txt” to “splitter.sql”.
(Use the appropriate filename splitter_hint92.txt when using an Oracle 9.2 database).
Use sqlplus to install PL/SQL Package and login to database with the owner of the tables to be split such as the sapr3 user:
sqlplus
connect
start splitter

2. Usage:
Login to the database through sqlplus with the user under which the PL/SQL Package was installed:
sqlplus
exec table_splitter.ranges (’TNAME’, ‘CNAME’, no of packages)

TNAME is the name of the table to be splitted
CNAME is the name of the column for which the ranges will be calculated. If you do not know an appropriate column choose a column of the primary key index.
By specifying the special name ROWID for CNAME rowid splitting is activated.
For partitioned tables this parameter has no meaning so please choose an arbitrary name.
no of packages is the number of table packages in which the table should be split. The number of packages should not exceed four times the number of CPUs of the database server in which the table needs to be imported.
Examples:
exec table_splitter.ranges (’RFBLG’, ‘BELNR’, 10);
exec table_splitter.ranges (’GLPCA’, ‘ROWID’, 30);
exec table_splitter.ranges (’/BIC/ECUSTOMER001′, ‘FOO’, 20);

3. Output:
The result of the calculation is stored in ASCII files in the directory which was specified on the create durectory command. These files use the standard naming convention of “TNAME-N.WHR” where N ranges from 1 to .
These WHR files can be used as input for R3load.
In the database a table “~TABLE_SPLITTER_RANGES” is maintained which contains information about the calculated ranges and additional statistics used for the calculation.
Guidelines:
For transparent tables always use ROWID splitting. Be aware of the limitations for ROWID splitting described at the beginning of this note.For pool and cluster tables the most efficient ranges are calculated by the script itself.

Early customer experiences show that calculation for a 300 GB table can be achieved in less than 3 minutes when using ROWID splitting and less than 30 minutes for cluster tables. Please note that the runtime of ROWID splitting is completely independent from the size of the table to be splitted. So the above result of 3 minutes for a 300 GB table is also valid for a 900 GB table or even larger ones.
The script does not make any changes to the databases so you can use it is as often as you want. Because of the fast runtime you may also run it against the production database but not during peak times.
Please check OSS Note 855772 when using the Distribution Monitor together with this optimization as the WHR files generated by the splitter script in general will contain multiple ranges. Therefore
the skipR3ta and skipWhrChk option of DistMon need to be used.