How to improve the performance of Oracle 11G in SAP system

[zhang_jiwen]

Hi experts,

    We are going to upgrade Oracle database from 10g to 11g,then need to learn how to improve the performance of Oracle.Could anyone give me some advice or material on performance tuning?

    Thanks in advance!

Best regards,

Sciano

[Former Member · Accepted solution]

Hello Sciano,

the best practices and therefore the best course of action are:

• apply the latest patchset (11.2.0.3)
• apply the latest SAP Bundle Patches
• set the Oracle parameters as recommended by SAP (see note 1171650)
• update Oracle database statistics and don't forget the Oracle system statistics (note 927295)

Then check the overall performance of your system. When upgrading from Oracle 10g to 11g it can happen that individual SQL statements show a bad performance, e.g. due to a bad execution plan. Then it is sufficient to analyze and fix them. If you have the SQL Tuning Advisor available, this is an easy task.

If you are interessted in the topic Oracle performance with SAP systems, then have a look at SAP note 618868: FAQ Oracle Performance.

Regards,

Mark

[zhang_jiwen · Accepted solution]

Hi Mark,

    Thanks for your quick response,and you do offer me a big help.

Best regards,

Sciano

[Former Member · Accepted solution]

Hi Sciano,

consider also to reorganize tables and to activate compression.

You can compress indexes during reorg freely, while you can compress table too but customer needs to buy "Advanced Compression Option".

Take a look at these notes for more info:

1431800 - Oracle 11.2.0: Central Technical Note

1436352 - Oracle 11g Advanced Compression for SAP Systems

1109743 - Use of Index Key Compression for Oracle Databases

Index Compression allows you to save up to 20% of space, and to improve data buffer quality and database troughput.

Consider that Oracle 11g has a feature (Deferred Segment Creation) that will allow you to save the space occupied by the empty tables.

Regards

Leo

[zhang_jiwen · Accepted solution]

Hi Leopoldo,

    I'm very grateful for your offer,I will read the notes first of all.

Best regards,

Sciano

[Former Member · Accepted solution]

Hello Sciano,

I like Oracle Compression, it is a good idea to use it, as long as it is included in your license. However compression isn't really part of tuning the database. Typically the performance improvement by compressing the data is too small to be detectable by the dialog users. Some batchjobs might benefit significantly. So don't expect too much from compressing your data. It might happen that you only see temporary effects from the reorganization.

Regards,

Mark

[Former Member · Accepted solution]

Hi,

please consider these improvement made by using Advanced Compression (as mentioned on "SAP on Oracle Development Update - November 2011):

Results

Considerable improvements

Factor 2.2 space savings

60% less physical reads

5% less physical writes

10% better database cache hit rate

30% higher redo rate

No overhead in CPU consumption

Regards

Leo

[zhang_jiwen · Accepted solution]

Hi Leo,

    So many thanks.I got some information from a presenation like below:

   In those ways,what ways do you prefer in your oracle server?Please give me some addvice,thanks.

Regards,

Sciano

[Former Member · Accepted solution]

Hi Sciano,

start to tune hardware resources (CPU, paging and I/O).

Then configure properly SGA and PGA.

After chec other recommendations as mentioned in your attachment.

Regards

Leo