Partitioning methods used for SAP ERP systems

[Former Member]

Hi

We use Oracle for our SAP ERP systems. We have Oracle tables size ranging 500+ GB which are not partitioned. Now, we need to partition in order increase the manageability, availability and performance of SAP applications. I had gone thru couple of whitepapers from Oracle and SAP communities. Finally, I am thinking of using SAP Partitioning Engine. As I do not have access to SAP Support Service and unable to find more information on SAP Partition Engine.Could you please suggest any other better solution to implement Oracle table partitioning ?

Cheers

Sameer

[Reagan · Accepted solution]

Hello

There are few SAP notes for this.

Note 722188 - FAQ: Oracle partitioning

Note 1333328 - Partitioning Engine for Oracle

Note 742243 - General table partitioning as of SAP_BASIS 46C

As you don't have access to the service market place I believe you will need to contact the right person in the company for the S-User to access SMP.

There are certain prerequisites for using SAP Partitioning Engine.

They all are mentioned in the above SAP notes.

Regards

RB

[Former Member · Accepted solution]

Hi RB,

Thanks for your reply. Sure, I will contact appropriate team for access and get back if I have any queries.

Cheers

Sameer

[Former Member]

Hi Sameer

You should really investigate Data Archiving first to manage the future growth of your database so this doesnt get out of hand and stays within manageable levels.

As you are on Oracle 11g you should also look at Oracle compression.

Note 1289494 - FAQ: Oracle compression

Note 1436352 - Oracle 11g Advanced Compression for SAP Systems

You can get some good space saving by applying compression, especially if this is done as part of post archiving database reorganisations.

Regards

Mark

[ACE-SAP]

Hi

It would help to know what are your Oracle & SAP version.

What is the whole DB size ?

Which tables are getting that big (you cannot setup partitioning on all tables). Check note 706478 - Preventing Basis tables from increasing considerably).

Did you experience performance problem at DB level, globally & for these tables ?

Did you ever reorganize these tables ?

Compression could help (especially OLTP compression on 11g) to reduce the table &/or index size.

An other solution to you problem could be to perform some archiving operation, maybe with the help of the partition engine.

Regards

[Former Member]

I will reply soon with answers

[Former Member]

Environment : Oracle 11g, HPUX, SAP ERP applications ( MDM)

1. DB size --- >Approx 10TB and most of the tables are ranging from 100 to 500 GB.
2. I am in a initial stage to converting these tables into partitioning table in order to increase performance and manageability.
3. This is an effort to reduce the performance issues and ease the managing these tables.
4. I thought about this before asking this query. But considering the size of individual tables, thought getting expert opinion from Gurus.

I assume, you understood the context of partitioning the table in SAP ERP.  By the way, I had gone thru all the NOTES mentioned by RB. However, I am not that impressed using SAP Partitioning Engine considering the fact that pre-requisites.

Also, expect some pointers of choosing the partitioning keys.

[stefan_koehler]

Hi Sameer,

> Also, expect some pointers of choosing the partitioning keys.

How should we know and provide some suggestions here? No SQL and no corresponding structure is provided by you. Partitioning can only improve the performance, if you know what the query tries to achieve and how you can handle that the best way.

> This is an effort to reduce the performance issues and ease the managing these tables.

Manageability - Yes (in context of archiving and data handling). Solving performance issues - rather not. The SAP partitioning engine itself is focused on
manageability as well (SAPnote #1333328 - "We would like to mention explicitly the fact that the primary aim of this tool is not an improvement of query performance due to partitioning using the "SAP Partitioning Engine". The focus of this solution is to avoid negative side-effects during archiving."). The problem is, that SAP excessively uses VARCHAR2 column definitions for numeric values. In fact  range partitioning is also possible on VARCHAR2 columns, but you don't have any "automatic maintenance logic" for it (key word "Interval partitioning").

You can use partitioning for performance improvements in specific use cases / configurations, but not in general in a SAP environment. Otherwise you have to balance and maintain the partitions all the time manually. That's also one reason, why SAP developed a framework for that maintenance on their own.

Regards

Stefan

[Former Member]

Hi Stefan,

Thanks for your suggestions. I am still waiting for access to my work environment and unable to provide any more information. I need to study existing SQL queries and understand the plans etc. Definitely, I will review your suggestions and dig more.

Cheers

Sameer