Table Splitting question / system copy

[martin_juen2]

Hi,

we are migrating from Oracle DB on AIX to Exadata. The database is ~5 TB, the biggest table is CE10200 with 550 GB.

Report UMG_R3LOAD_RUNTIME_PREDICTION recommends a split factor 6 for this table.

I think this factor is too small. Does anyone have a reference for splitting tables? 90 GB for one package seems too large for me. I think 10 - 15 GB is a better value but i can't estimate the impact to the db performance / export performance when splitting the table to 50 packages.

I can't find a best practise for this topic. Maybe we can document a reference here

Thanks for your help.

Martin

[Former Member · Accepted solution]

Hi Martin,

I would suggest you to split the table into 5GB each per package. that means 550/5=110 Packages.

Please refer the below note for more information.

Note 952514 - Using the table splitting feature

http://scn.sap.com/message/13787569

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/b070e7b4-a5ca-2c10-1dab-a4b53fd1c...

http://scn.sap.com/thread/334951

Thanks and Regards,

Vimal

[martin_juen2 · Accepted solution]

Hi,

thanks for your input. In my first draft i was dealing with 7 GB per package. That means 78 Packages.

5 GB per package is nearly the same. so i think 5 - 10 GB per package should be a good reference value for a well performing export.

Thanks, Martin

[stefan_koehler]

Hi Martin,

i have chosen a table splitting size of round about 10 GB in most of my multi tera byte migrations.

However i used migration monitor with Oracle access path adjustments (like view definitions with parallel query) to get a good export performance. But all kind of definitions and performance optimizations depend on the environment and downtime requirements as Michael already mentioned.

For example it makes no sense to split a table into several hundred packages, if you are running 10 R3load processes only, because of your "export application server" has not enough CPU resources. Please consider the access path for all export processes and the waiting "post import" tasks (like index creation) on such objects as well.

Regards

Stefan

[Former Member]

I agree 6 is probably too small. There is no best practice, because one cannot give a general advice. The values are influenced by your hardware (disk/cpu/network), the table and data stored in it, how much downtime you get. For example if you can only run 4-8 parallel export processes, it is probably no use to split the table in 64 packages (though i like to have lots of small packages in general).

In your case it might even be advisable to use IMIG for those kind of objects.

You will have to run a bunch of tests to get runtime information.

It might also be a good time to double check, if you want to migrate all data to exadata or push the older data to an archiving system.

Cheers Michael