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

                                 Compression is disabled as per the current status.

Check size of table before Reorganizing:-

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”

B. Check size of table after Reorganizing

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

 Compress for OLTP:-

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

A. Compression to OLTP

B. Check Status of Compression

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

D. Perform Gather Stats

                           

E. Tables pace Free & Usage

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

Difference between PFILE and SPFILE? What are their advantages?

Difference between PFILE and SPFILE? What are their advantages?

 PFILE

Until Oracle 8i, we were using a text file called the PFILE (parameter file) for setting the database initialization parameters. This PFILE is read at instance startup time to get specific instance characteristics. The PFILE is text based, and can be edited in an editor like vi on UNIX or Notepad on Windows. Any changes that were made in PFILE would only take effect when the database is restarted,

Since SAP/Oracle recommends using SPFILE over PFILE, I will provide some details on SPFILE to answer your question in more detail.

SPFILE

In Oracle9i, a new feature called SPFILE (server parameter file) was introduced. SPFILE is a binary file that contains the same information as the old PFILE. SPFILE permits dynamic changes without requiring you to restart that instance.

By default, if you do not specify PFILE in your STARTUP command, Oracle will use server parameter file (SPFILE). If you choose to use the traditional text initialization parameter file (PFILE), you must specify the PFILE clause when issuing the STARTUP command.

The SPFILE is different from the PFILE in that it cannot be directly edited. This is because it has a header and footer that contain binary values. Since you cannot change a SPFILE directly, Oracle allows you to manage the SPFILE via the ALTER SYSTEM command.

When you execute the ALTER SYSTEM command, the parameter change is validated immediately, which helps avoid errors associated with entering an incorrect parameter name or an invalid value. In addition, the ALTER SYSTEM allows you to specify whether you want to update the parameter immediately, update just the SPFILE, or both. All these features help avoid human error associated with manually updating a PFILE.

The following example shows how to change the current value of SESSIONS and store the value in the SPFILE.

SQL> ALTER SYSTEM SET SESSIONS=200 SCOPE=SPFILE;

The new part in this command is the parameter SCOPE. You have the following options for this.

SCOPE = SPFILE

(For both static and dynamic parameters, changes are recorded in the spfile, and will take effect in the next restart.)

SCOPE = MEMORY

(For dynamic parameters, changes are applied in memory only. No static parameter change is allowed.)

SCOPE = BOTH

For dynamic parameters, the change is applied in both the server parameter file (SPFILE) and memory. No static parameter change is allowed.)

For dynamic parameters, we can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.

Now for the first part of your question. Since you are using Oracle 10G, you have to use the ALTER SYSTEM command to change the Oracle parameters as recommended by the SAP Early Watch Alert.