Skip to Content

Oracle ACO, a compression methodology launched by Oracle with 11g.

Most of the customers got benefited with this in terms of storage reductions, performance improvements etc.. We have this enabled last year in a different way to get more benefited!

Details: Oracle ACO is an compression methodology which is a pack of:

OLTP table compression

Index key compression (Oracle 10G on)

Securefile compression

RMAN compression

Data guard network compression

Activity type: It’s available as an online activity, end to end activity can be done ONLINE (Yes, no downtime is required for normal compression!)

Methodologies available: In place compression and tablespace migration (Out place)

Methodology that we selected: Tablespace migration (Involved storage migration) with compression

Benefits that we got: Huge storage reduction from existing system, Well managed data files, New TS structure (LMTS etc.)

Steps: Earlier we have our entire database stored in sapdata1 – sapdata40 file systems (Each file system was having different sizes).  We created equally sized data files from sapdata50 to sapdata60 -> Created *_new tablespaces –> Migrated data from old tablespace to new tablespace with compression option (-C) ON –> Deleted the old tablespace once the entire data was moved –> Renamed *_New tablespace to original tablespace

Finally we got all our database in sapdata50-sapdata60, so removed all the old file systems and LUNs which saved some additional storage as well.

This method need a post compression downtime because it has storage migration involved, downtime is needed to move system,sysaux tablespaces to new storage (sapdata50-60 in above scenario).

Results: We ended up with 3x compression ratio (ECC database has become 1TB from it’s original size 3TB, Storage has reduced from 5.5TB to 2TB).

Learnings: Execution is quite simple, managing system load during compression is a key. There are some restrictions, mentioned in 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

Hope it helps!

To report this post you need to login first.

10 Comments

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

  1. Fidel Vales

    Hi,

    3 comments, that I think they are important.

    1st.- ACO is available for “free”, it has a monetary cost. You have to find out if youhave the licence to use it.

    2nd.- Index key compression has nothing to do with ACO. you can compress indexes without the ACO licence (it is a “free” feature”)

    3rd.- Be sure to implement the latest SBP as there are very important bugs related to table compression. Refer to the following Hotnews note:

    1847870

    Oracle 11g: Advanced Compression – Known issues and solution

    There are a lot of other things, but usually they are addressed in the “compression proyect” but I have found that the first 3 are not really “known”

    (0) 
    1. Harish Karra Post author

      Thanks Fidel, yes these 3 are initial things which are there in above mentioned notes.

      I just added “known issues and solution” note 🙂

      Regards,

      Nick Loy

      (0) 
  2. Yves KERVADEC

    Hi,

    From my experience I would not recommend compressing all the tables. This is also what SAP recommends in the OSS note quoted by Fidel.

    I’ve seen a customer getting into trouble by mass compressing tables, they did select the compression option in installer and some tables that should not be compressed (list in note 1431296) were compressed and they quickly get into performance issue with RFC and update.

    Another drawback is the redo volume increase up to 30% (source : SAP on Oracle Development Update (June 2013)) especially if you did compresse tables that undergoes massive changes.

    11G already save space with deferred segment creation, compressing the biggest tables and index is a good way to save space and even (perhaps) increase performance.


    Regards

    1431296 – LOB conversion and table compression with BRSPACE 7.20

    1.4 Exception handling during table compression

    We recommend not to compress the following tables in Oracle (if these tables have SecureFile LOBs, you should also not compress them):

    – Tables with more than 255 columns (Oracle requirement)

    – SAP pool tables (for example, ATAB, UTAB)

    – SAP cluster tables (for example, CDCLS, RFBLG)

    – INDX-type tables (for example, BALDAT, SOC3)

    – The ABAP source and ABAP load tables REPOSRC and REPOLOAD

    – update tables VBHDR, VBDATA, VBMOD, and VBERROR

    – RFC tables ARFCSSTATE, ARFCSDATA, ARFCRSTATE, TRFCQDATA, TRFCQIN, TRFCQOUT, TRFCQSTATE, QRFCTRACE, & QRFCLOG

    – NRIV

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

    For overall performance and throughput reasons NEVER compress all transparent tables. Only compress the largest tables (less than 1000) to achieve good disk space reduction.

    (0) 
    1. Harish Karra Post author

      Hi,

      Infact standard brspace command excludes the above recommendations by default now a days 🙂

      So no need to worry, it will not allow you to compress clustered, pool, >255 columns etc..

      Anytime you can decompress (Again an online activity)

      Regards,

      Nick Loy

      (0) 
      1. Yves KERVADEC

        Hi Nick

        It’s not exactly what is said in the note, it is not excluded by default you need extra options.

        I did not test this, so maybe you’re true and the note is outdated…..

        Regards

        1431296 – LOB conversion and table compression with BRSPACE 7.20

        These recommendations are accommodated in BRSPACE as follows:

        – Tables with more than 255 columns are automatically excluded from table compressions.

        – Pool and cluster tables, INDX-type tables, ABAP source and ABAP load tables, and update tables are excluded using the special option “-SCT” (skip compressed table).

        – RFC tables are excluded using the special parameter “_reorg_excl_tab”, in which you can specify the tables explicitly.

        (0) 
        1. Harish Karra Post author

          Yes my friend,

          That is what I mentioned. I am no where mentioned the sequence of commands that we used OR end to end approach, which might have created some confusion! I am sorry about that.

          We enabled the compression for all of my database tables (doesn’t mean for all cluster, pool etc. tables). It means we followed big-bang approach for this ACO enablement by following the recommendations (We used -SCT, reorg_excl_tab).

          Regards,

          Nick Loy

          (0) 
          1. Yves KERVADEC

            Ok Nick,

            I was just giving these remarks to help.

            I’m sure that the people reading this blog do not have a knowledge in that area as sharp as yours…

            It was just my 2 cents remarks, and the feedback from my personal experience on that subject…

            Excuse me I’m a perfectionist, and I did like details.

            Thank you for sharing your experience.

            Cheers

            Yves

            (0) 

Leave a Reply