PL/SQL Table Splitting for Oracle Databases

[milanjajoo]

In this blog, you will learn how to perform table splitting using PL/SQL table splitter for Oracle Database. This helps to optimized the migration downtime. 

Table Splitting: Data of single table can be exported and imported in parallel with multiple R3load process. This process is called Table splitting. Large table can take too much time during export and import. With Table splitting we can reduce export/import time.

Advantage:

• Large tables are processed in smaller packages. If the export or import of a table abort with an error, only the processing of the package where the error occurred has to be repeated and not for the complete table.
• The export and import of one table can be performed in parallel by starting several R3load processes to work on same packages in parallel.
• Creates WHERE conditions to allow multiple R3LOADs to run on a single table during export and import.

The following three table splitters are available in SAP:-

- SAPUPTOOL is a database independent table splitter that runs on all SAP supported databases (since 7.40).

 - R3TA is an earlier database independent table splitter that runs on all SAP supported databases.

 - The PL/SQL table splitter runs only on Oracle.

-> In this example I do the table splitting using SWPM tool for oracle specific PL/SQL splitting method on SAP NW 7.4

Requirements and Limitations

• The script is currently only supported for Oracle 10.2 or higher Database Versions (source and target database).

• Cluster Table Limitation. The column PAGENO cannot be used when splitting cluster tables. Also delustering of clustered tables is not supported.

• 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 R3load

• ROWID table splitting works only for transparent and non-partitioned tables.

• ROWID table splitting CANNOT be used if the target database is a non Oracle database
• input parameter <no of packages>.  Should be more than one.

1> Extract top sized tables using DB02 or with SQL query:

     Go to  DB02 -> Space -> Segments -> Overview

SQL query:

set pagesize 100

set linesize 100

column segment_name format A30

column segment_type format A30

SELECT * FROM (select SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024/1024 GB, TABLESPACE_NAME from dba_segments order by 3 desc ) WHERE  SEGMENT_TYPE='TABLE' and  ROWNUM <= 50;

With output of above query, we can prepare below table:

Owner	Name	Type	Tablespace	Size(MB)	GB
SAPR3	AQLDB	TABLE	SAP<SID>	598,879.00	585
SAPR3	VBFS	TABLE	SAP<SID>	455,158.44	444
SAPR3	GLPCA	TABLE	SAP<SID>	132,489.31	129
SAPR3	EDI40	TABLE	SAP<SID>	102,300.00	100
SAPR3	ACCTIT	TABLE	SAP<SID>	98,770.25	96

2> Find tables with high row count

SQL Query:

set pagesize 900

set linesize 900

column owner format A25

column table_name format A25

select owner, table_name, nvl(num_rows,-1) from all_tables order by nvl(num_rows,-1) desc;

extract the output in excel table format as shown in below for example:

OWNER	TABLE_NAME	NVL(NUM_ROWS,-1)
SAPR3	VBFS	5855945500
SAPR3	S505	644862333

Above table size you can find from DB02 -> Space -> Segments

3> Find large LOB Segment tables:

Go to DB02 -> Space -> Segments -> Overview

or SQL query:

SELECT * FROM (select SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024/1024 GB, TABLESPACE_NAME from dba_segments order by 3 desc ) WHERE  SEGMENT_TYPE='LOBSEGMENT' and  ROWNUM <= 50;

Extract the output and create table. below is example:

Owner	Name	Type	Tablespace	Size(MB)	GB	Table
SAP<SID>	SYS_LOB0000009852C00007$$	LOBSEGMENT	PSAP<SID>	107,409.00	105	SOFFCONT1
SAP<SID>	SYS_LOB0000009936C00007$$	LOBSEGMENT	PSAP<SID>	93,851.00	92	BDS_CONT1

4> Prepare a consolidated excel with columns: Size (GB), Package(no), Package size in GB(Size/No of packages) and primary key (for cluster tables).

Note: Package number can differ environment to environment. There is no thumb rule to decide package size and package no. We can decide this after doing test run (POC).After doing test run you can use Migtime tool to find which table took how much time. In our environment after doing 3 POC we have decided to keep average package size 4 GB for normal tables and 3 GB for cluster tables.

Note: <no of packages>.  Should be more than one. With value 1 only table structure will be export, no table data will be export with value 1.

5> The check whether a table is transparent or cluster, use table DD02L and DD06L. The table DD02L contains list of all Normal (transparent) tables and DD06L contains list of all cluster tables.

DD02L – NORMAL tables

DD06L – Cluster tables

To do this, go to SE16 in SAP

Using table (DD02L and DD06L), we can see which tables are cluster tables, for those cluster tables we need to specify primary key during table splitting preparation.in our case EDI40 is cluster table.

To identify primary key for cluster tables, open each cluster table in SE11 and check for the unique key ID (If you do not know an appropriate column choose a column of the primary key index) as shown below:

For Normal tables, we will give the Primary key as ‘ROWID’

Populate the table with details as below

6> Create table_split.txt file

We need to create table_split.txt file and keep this file under export directory, it is required in SWPM. So we need to create text file in below format:

<Table Name>%<no of packages>;<Primary Key>

<Table Name> is the name of the table to be splitted

<no of packages> is how many number of packages for table

<Primary Key> is for normal table is “ROWID” and is for cluster table is unique key ID.

Example:

AQLDB%140;ROWID

VBFS%120;ROWID

GLPCA%35;ROWID

EDI40%40;DOCNUM

ACCTIT%25;ROWID

S505%20;ROWID

SOFFCONT1%20;ROWID

BDS_CONT1%25;ROWID

Note: export directory will create by SWPM in preparation phase.

7> Start SWPM tool and run table splitting

Call sapinst and choose Oracle -> System Copy -> Source System -> Based on AS ABAP -> Table splitting Preparation:

8> On next screen enter below details:

Export Location: Directory where you want to put splitting package and where table_split.txt file is Kept.

Target Database Type: select DB type

Table Input File: File which we created in table splitting preparation “table_split.txt”

Number of Parallel SAPuptool Jobs: Parallel process during table splitting

9> Enter schema user/password and select oracle specific PL/SQL spliter

10> After you have entered all requested input parameters, the software provisioning manager displays the Parameter Summary screen. This screen shows both the parameters that you entered and those that the software provisioning manager set by default. If required, you can revise the parameters before starting the table split.

Note: SAP Application should be stopped before start table splitting execution to avoid inconsistencies.

11> To start the table split, choose Start.

12> Once execution is completed the *.WHR files and the whr.txt file are created in the <Export_Dump_Directory>/ABAP/DATA subdirectories of the export directory.

Example:

13> Check in the export directory <Export_Dump_Directory>/ABAP/DATA if *.WHR files and a whr.txt file (contains the name of the split tables) have been created for all tables that are to be split..

• If no *.WHR files and no whr.txt file could be produced for some of these tables, create fewer packages for these tables:
1. Create a new, empty installation directory.
2. Define a new, empty export dump directory <Temporary_Dump_Directory>.
3. Run the Prepare Table Splitting service again and provide an input file that contains only the missing tables with a lower number of packages for each defined table.

• If the *.WHR files and an entry in the whr.txt file have been created for the missing tables, merge these results with the results from the first Prepare Table Splitting run:
1. Copy the *.WHR files from <Temporary_Dump_Directory>/ABAP/DATA to <Export_Dump_Directory>/ABAP/DATA.
2. Add the lines from <Temporary_Dump_Directory>/ABAP/DATA/whr.txt to <Export_Dump_Directory>/ABAP/DATA/whr.txt.

Reference:

1043380 - Efficient Table Splitting for Oracle Databases

[Ankit_Sh]

Very Informative and useful.