SAP BASIS TUTORIALS: Advanced Compression with Oracle Database 11g

Oracle introduced Table Compression from version 9i, to compress data for bulk loaded. It’s been introduced from 11gR1 with OLTP Table Compression that allows data to be compressed for all DML’s(Inserts/Updates/Deletes),
OLTP Table Compression reduces the associated compression overhead of write operations making it suitable for transactional or OLTP environments as well.
OLTP Table Compression, therefore, extends the benefits of compression to all application workloads.

Basic compression comes with oracle 11g Enterprise Edition, To make table as OLTP compressed its again extra cost option with Enterprise Edition.

OLTP compression Benefits
1. Reduce space if Tables are Large Space usage reduction with OLTP Table Compression enabled gives the best results where the most duplicate data is stored (low cardinality).
2. To fasten the read performance. & so on…

So for operation which are CPU bound compression doesn’t fasten the performance, in fact it increases the CPU resource at database server.
There is lots of processing power used to decompress and compress that data.

The tables which are candidate for OLTP compression are generally, Tables with less frequent times Inserted/Updated & Tables which were used for Read-Only Operations. Eliminating duplicate values within a database block, even across multiple blocks and then replaced by a short reference to the appropriate entry in the symbol table.
Compressed data is self-contained within the database block as the metadata used to translate compressed data into its original state is stored in the block.
When compared with competing compression algorithms that maintain a global database symbol table,
Oracle’s unique approach offers significant performance benefits by not introducing additional I/O when accessing compressed data.

Compression Ratio Comparison (From Introduction article for Oracle 11gR2 & SAP)

Reorganization with Compress for OLTP

SAP R/3 – ECC 6.0
Oracle – 11gR2
Note:- If oracle license coming with SAP then NO need to buy anything from oracle if we purchased from oracle then need to ACO separately

Recently worked with one of customer whose application is of SAP R/3 on Oracle Database 11gR2. Before that what I understood from SAP notes, White papers & so on. I have gathered my findings above.

Now compare Table space level Free space for “PSAPSR3“:-

Above screenshot taken prior to this task, Noted Free_MB as 16,353.

Let’s run report for fragmented tables:-

Normally Table fragmentation is not a good practice only to gain space, but if we see above report table “BALDAT”, Actual MB is not even 5% in such cases we can consider to reorganize that table.

To Compress to OLTP & Move either you can follow any one of the below commands.

1. SQL> ALTER TABLE SAPR3.BALDAT MOVE COMRESS FOR OLTP;
(or)
2. SQL> ALTER TABLE SAPR3.BALDAT MOVE;
& SQL> ALTER TABLE SAPR3.BALDAT COMPRESS FOR OLTP;

Check Status of Compression:-