Skip to Content

This blog post is a follow up to :

http://scn.sap.com/community/oracle/blog/2012/05/17/oracle-advanced-compression-for-sap-my-onfield-feedback-part-1

About Oracle advanced compression advantages.

Here we have a look at the runtime part.

Here are a few figures about the impact of the oracle compression on the overall runtime  :

1. Background runtime chart

For the background processes, the overall average runtime went down from an average of 7.1s  to 3.5s , which is around 50% less.

The db runtime was divided by 3.6 coming down from an average of 3,778s to 1,046s.

2. Dialog ( interactive )  runtime chart

For the dialog processes, the overall average runtime went down from an average of 916ms  to 563ms, which is around 38% less.

The db runtime was divided by around 2.6 coming down from an average of 597ms to 225ms.

3. Overall  runtime chart

For the overall processes, the average runtime went down from an average of 1.8s  to 800ms which is around 55% less.

The db runtime was divided by around 3,3 coming down from an average of 1s to 300ms.

4.Focus on some of the main transactions for this customer

S_ALR_87013611

VA88

MB51

VA01

VL10

FBL5N

MMBE

KE30

MD04

My conclusion : the Oracle Advanced Compression feature is really useful in terms of space saving and on the improvement of these transactions runtime mainly driven by the database runtime.

Part 1 of this feedback :

http://scn.sap.com/community/oracle/blog/2012/05/17/oracle-advanced-compression-for-sap-my-onfield-feedback-part-1



                  



To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

    1. Steve Soumah Post author

      Hello,

      I was able to compress the database by performing an export / import of the whole database using sapinst ( you should be able to do the same using swpm ) .

      You perform the export, and then on the import phase, you select the COMPRESS FOR OLTP option when you specify the Oracle DB layout.

      This is the option we chose. This means database unavailability during the operation.

      You can also perform this kind of tasks online by performing reorganizations. Still the performance will be degraded during the reorgs.

      Best regards,

      S.SOUMAH.

      (0) 
  1. Peter Scharr

    Very interesting – thanks. Without any experiences in Post-Installation compression I always assumed runtime will increase due to decompression load, but finally this doesn’t seem to be true.

    Will check this for the customer test environment as well (here: IBM DB/2).

    (0) 
    1. Stefan Koehler

      Hi Peter,

      the compression is performed in an asynchronous manner (nearly PCTFREE) on block level and is internally based on a de-duplication method although the feature is called compression. The Oracle (OLTP) compression works differently than the DB2 compression (Compression dictionaries) as the maintenance is done on block level and “on-the-fly” at round about PCTFREE.


      However the compression is also not triggered by all DMLs (like Oracle states), which can lead to some unexpected data growth / explosion over the time depending on the DML pattern. The performance gain itself is mostly based on doing less logical and physical I/O.

      Regards

      Stefan

      (0) 
  2. Sandeep Alapati

    Hi,

    Can we do Oracle Database Compression online i.e  without effecting the regular operations of the system ?

    If yes kindly provide the steps to proceed ..

    Thanks & Regards,

    Sandeep Alapati

    (0) 
    1. Harish Karra

      Yes Sandeep,

      Online reorganization is possible from Oracle 10G, so compression can be done online using reorg+compression method (by exporting a TS/Table from old to new).

      Refer below notes:

      1289494 – FAQ: Oracle compression

      1109743-Use of Index Key Compression for Oracle Databases

      1436352 – Oracle 11g Advanced Compression for SAP Systems

      1431296 – LOB conversion and table compression with BRSPACE

      1847870 – Oracle 11g: Advanced Compression – Known issues and solution

      Regards,

      Nick Loy

      (0) 

Leave a Reply