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 [highlightbold]extra cost[/highlightbold] 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:- [yellow]If oracle license coming with SAP then NO need to buy anything from oracle if we purchased from oracle then need to ACO separately[/yellow]
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 ---------------------- ------------------------------ -------------------- ---------- ---------- ------------ [yellow]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 [yellow]DISABLED[/yellow]
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) -------------------- [yellow]14984[/yellow] 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) -------------------- [yellow]772[/yellow] 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 [yellow]UNUSABLE[/yellow] 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 [blue]COMPRESS FOR OLTP[/blue]; 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 ------------------------------ -------- ------------ [yellow]BALDAT ENABLED OLTP[/yellow] 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 [yellow]VALID[/yellow] 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…. 🙂
