M3 - OLTP Compression and Performance

Tests with OLTP compression have been made with an M3 BE program called RPS999. This batch program was running for approximately one hour. Using OLTP compression, the performance for RPS999 was 6-8 % better than without. The compress ratio for the tables was approximately 4.

Activate OLTP compression on existing tables

  1. Activate compression by running the command:

    ALTER TABLE <schema>.<table_name> COMPRESS FOR ALL OPERATIONS

  2. Compress existing table data by running the command:

    ALTER TABLE <schema>.<table_name> MOVE;

  3. Rebuild the indexes by running the commands:

    ALTER INDEX <schema>.<index_name_1> REBUILD;  
ALTER INDEX <schema>.<index_name_2> REBUILD;  
...

  4. Gather statistics for the table and indexes by running the command:

    exec DBMS_STATS.GATHER_TABLE_STATS('<schema>'.'<table_name>',estimate_percent=>NULL, cascade=>TRUE);

Activate OLTP compression on tablespace level

  1. Activate compression by running the command:

    ALTER TABLE <tablespace_name> DEFAULT COMPRESS FOR ALL OPERATIONS

  2. All new tables in this tablespace will be OLTP compressed.

    The tablespaces you should alter are:

    TMVXSD and TMVXLD