Advanced Compression with Oracle Database 11g for OLTP

Oracle introduced Table Compression from version 9i, to compress data for bulk loaded. Its 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 precessing 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 who’s 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 Tablespace level Free space for “PSAPSR3“:-

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

 

Let’s run report for fragmented tables:-

OWNER                  TABLE_NAME                     TABLESPACE_NAME       ACTUAL_MB OPTIMAL_MB CLAIMABLE_MB
---------------------- ------------------------------ -------------------- ---------- ---------- ------------
SAPSR3                 BALDAT                         PSAPSR3                   14984        710     14274[/yellow]
SAPSR3                 EDI40                          PSAPSR3                   17745      11964      5781
SAPSR3                 FAGLFLEXA                      PSAPSR3                   25652      21810      3842
SAPSR3                 CE11000                        PSAPSR3                   22182      18903      3279
SAPSR3                 ACCTIT                         PSAPSR3                   17737      14904      2833

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:-

SQL> SELECT table_name, compression, compress_for FROM dba_tables where table_name='BALDAT';
TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
BALDAT                         DISABLED

Compression is disabled as per the current status.

Check size of table before Reorganizing:-

SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='BALDAT';
 SUM(BYTES/1024/1024)
 --------------------
  14984
 SQL>

This above output noted because once we Re-Organize table the dependent indexes will become “UNUSABLE” , We need to rebuild them.

Now Move & Compress Table:-

A. Move Object “BALDAT”

SQL> alter table sapsr3.baldat move;
Table altered.
SQL>

B. Check size of table after Reorganizing

SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='BALDAT';
SUM(BYTES/1024/1024)
--------------------
                 772
SQL>

C. Check Status & Rebuild If status is “UNUSABLE”

SQL> select index_name,status from dba_indexes where table_name='BALDAT';
INDEX_NAME                     STATUS
------------------------------ --------
BALDAT~0                       UNUSABLE
SQL>
SQL> alter index sapsr3."BALDAT~0" rebuild online;
Index altered.
SQL>
SQL>select index_name,status from dba_indexes where table_name='BALDAT';
INDEX_NAME                     STATUS
------------------------------ --------
BALDAT~0                       VALID
SQL>

Compress For OLTP:-

This approach will enable OLTP Table Compression for all future DML’s

A. Compression to OLTP

SQL> ALTER TABLE SAPSR3.BALDAT COMPRESS FOR OLTP;
Table altered.
SQL>

B. Check Status Of Compression:-

SQL> SELECT table_name, compression, compress_for FROM dba_tables where table_name='BALDAT';
 TABLE_NAME                     COMPRESS COMPRESS_FOR
 ------------------------------ -------- ------------
 BALDAT                         ENABLED  OLTP
 SQL>

C. Check for Indexes & status on table “BALDAT”:-

SQL> select index_name,status from dba_indexes where table_name='BALDAT';
INDEX_NAME                     STATUS
------------------------------ --------
BALDAT~0                       VALID
SQL>

D. Perform Gather Stats:-

SQL> EXEC dbms_stats.gather_table_stats('SAPSR3','BALDAT',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>

E. Tablespace Free & Usage:-

Note:- Including 14000MB of “BALDAT“, I have performed for other tables also So output may be little fractional.

References:-

An Oracle White Paper - January 2012, Advanced Compression with Oracle Database 11g
An Oracle White Paper June 2011. Oracle Database: The Database of Choice for Deploying SAP Solutions
Oracle Database 11gR2 Functionality Ceritified by SAP - Oracle 11gR2 for SAP
SAP Note 1436352 - Oracle 11g Advanced Compression for SAP
SAP Note 1464156 - Support for index compression in BRSPACE 7.20

Happy Reading…. 🙂