Oracle compression with SWPM

[Former Member]

Hello All,

I would like to know the recommendations and any suggestions for enabling Tablespace compression during the installation of SAP using SWPM.

Details

Install ERP 6.0 SR3 with Kernel 7.21 on Linux x86_64 OS with Oracle 11.2.0.4

Compression can be enabled by selecting Advanced DB configuration during SAP installation.

I found the procedure of implementing compression after system build is quite tedious, so utilizing this option .

Compression can be enabled only for SAP Appln tablespaces like PSAPSR3/PSAPSR3700/PSAPSR3USR right?

I have already glanced thru the OSS notes for ACO/Compression for SAP Systems/FAQ on Compression, pre-requisites are met I believe:

Oracle 11.2.0.4 /SAP Kernel release 721 meet the pre-requisites.

Cannot be enabled for Oracle SYSTEM/SYSAUX tablespaces.

This procedure would implement index and oltp table compression by default right?

Anda are there any manual parameters need to be amended right after the installation?

Sanity checks/monitoring need to be performed after installation?

Are there any disadvantages by enabling compression during the installation.?

Please share your real time experiences and thoughts..

Thanks in advance.

[fidel_vales]

Hi all,

I think (cannot test it now or ask the developer) that the latest versions of SWPM are better, nevertheless, if during installation the compression is activated I would execute the script "Segments_Tables_CriticalCompressedTables_11g+.txt" and "Segments_Tables_CompressionExceptions.txt" from the SAP Note 1438410 to be sure

[ACE-SAP]

Hi Balaji,

One thing is not clear for me, does the 'migration project' means your are at first starting with a system copy (and not an installation) ?

In such scenario I will only work on compression when the whole process (copy & upgrade) is done.

Activating compression through SWPM might seem simpler... but it is less efficient as it sets the compression attribute at tablespace level. I've just checked on a 702 test system installed few years ago with the SWPM compression option. See here under how great the result is... (it was even worst as we did manually uncompress some tables).

A check with script Segments_Tables_CompressionExceptions from note 1438410 shows that more than 650 tables were wrongly compressed.

Maybe SWPM is now smarter, I did not have the opportunity/time to test it.

Regards

[Brindavan_M]

Hi Balaji,

The Advanced DB configuration during SAP installation is only if you are oracle database experts. If you not sure about doing then go as normal.

Thanks,

BM

[former_member182967]

Hello Balaji,

Only user tables in user tablespaces can be compressed. Oracle system tables (tables in tablespaces SYSTEM or SYSAUX) can not be compressed. (mentioned in note 1436352 – Oracle Database 11g Advanced Compression for SAP System)

Indexes can't be compressed during SWPM installation.

Set Parameter according to note 1171650 and 1431798.

I have installed several systems with Oracle ACO, I don't see lot of disadvantages (except for some bugs within Oracle, now have been fixed in new patches)

Regards,

Ning Tong

[Former Member]

Thank you Ning Tong So just straight away approach - enable OLTP compression for the SAP TS like PSAPSR3, PSAPSR3700 & PSAPSR3USR.

So Indexes must be compressed manually or can stay as such only with tables being compressed as a result of installation.

Can you provide some more detailed info on post installation/sanity check measures.

I am going to implement SAP11204P_1406-20010781 SBP, so this should be able to fix all the bugs I suppose. Right?.

[ACE-SAP]

Hi

Activating compression at tablespace level for all tables using that SWMP option does not sound like a good idea to me...

SAP recommends to only compress the largest tables.

Always apply that latest bundle patch at installation time.

Best regards

1847870 - Oracle 11g: Advanced Compression - Known issues and solution

For overall performance and throughput reasons NEVER compress all transparent tables.

Only compress the largest tables (less than 1000) to achieve good disk space reduction.

[former_member182967]

Hello Balaji,

Yes, you should compress index manually later. Refer to the following notes:

Note 1464156 - Support for index compression in BRSPACE 7.20

Note 1109743 - Use of Index Key Compression for Oracle Databases

Maybe there are still some unknown bugs, however, it is suggested to update the latest SBP.

In my view, there is no speciall post installation steps for Oracle ACO

Regards,

Ning

[former_member182967]

Hello Yves,

Not all table are compressed if I select compress for OLTP during SWPM installation.

( 1436352 - Oracle Database 11g Advanced Compression for SAP Systems)

SQL> select count(*) from dba_tables where owner='SAPDAT' and COMPRESSION='DISABLED';

  COUNT(*)

----------

      1066

Regards,

Ning Tong

[ACE-SAP]

Dear Ning

Note  1436352 only tells that  "New SAP installations from 7.00 on have an option in SAPINST to create oltp compressed tables during the installation of an SAP system."

1066 tables not compressed on a SAP system that has, depending on product / version between 30 000 to 70 000 tables is really far from SAP recommendation on compressing less than 1000 tables  !

Few years ago that option was so badly implemented that it even compress the tables that SAP explicitly recommend not to compress. I did not have the opportunity to test since then but I do consider this option as hazardous.

Best regards

[former_member182967]

Hello Yves,

Thanks a lot for your remind.

If this is the case, now I am not sure. I'd better open a message to SAP AGS support to get the news.

Regards,

Ning

[Former Member]

Hello Yves/Ning,

Thanks for all your responses.

But in the OSS note 1436352 - Oracle Database 11g Advanced Compression for SAP Systems there are restrictions citing with regard to the OLTP table compression,that might be the reason that some tables are not compressed

Reference from the OSS Note

Tables with more than 255 columns are not compressed. Do not compress tables with more than 255 columns.

Compression is not applied to lob segments (lob segments can be compressed by using SecureFile compression which is described later in this note).

OLTP Table Compression of tables with LONG and LONG RAW columns is NOT supported. Please convert LONG and LONG RAW columns to SecureFile LOBs to use compression.

Source: Oracle White Paper 'Advanced Compression with Oracle Database 11g', January 2012 http://www.oracle.com/technetwork/database/storage/advanced-compression-whitepaper-130502.pdf

Only user tables in user tablespaces can be compressed. Oracle system tables (tables in tablespaces SYSTEM or SYSAUX) can not be compressed.

However what is the downside of some tables not getting compressed, they occupy more size but there wont be inconsitency/wrong data fetched??

Also initially I am going to install base version ERP 6.0 SR3 then do a technical upgrade to EHP7, in such a case how can I manage the compression for the new tables which will be created after the upgrade.

SUM tool has any provisions?

Or must be manged manually after the upgrade.

Please share your thoughts.

[ACE-SAP]

Hi Balaji,

Why are you installing an ECC 6 SR3 and upgrade, is it just for practicing ?

Do not worry about table not being compressed, SAP has run smoothly on Oracle for decades before advanced compression was available.

I think you should perform your upgrade and then compress the largest SAP tables excluding the ones from note 1431296

On database now days the target is more performance than disk saving... compression has positive impact on both but has some bad impacts on performance

Best regards

1431296 - LOB conversion and table compression with BRSPACE 7.20

1.4 Exception handling during table compression
We recommend that you do not compress the following tables in the Oracle system:
- Tables with more than 255 columns (Oracle requirement)
- SAP pool tables (for example, ATAB, UTAB)
- SAP cluster tables (for example, CDCLS, RFBLG)
- INDX-type tables (for example, BALDAT, SOC3)
- The ABAP source and ABAP load tables REPOSRC and REPOLOAD
- The update tables VBHDR, VBDATA, VBMOD, and VBERROR
- The RFC tables ARFCSSTATE, ARFCSDATA, ARFCRSTATE, TRFCQDATA, TRFCQIN, TRFCQOUT, TRFCQSTATE, QRFCTRACE, and QRFCLOG
- NRIV

1436352 - Oracle Database 11g Advanced Compression for SAP Systems

Performance
Overall system throughput is not negatively impacted and may improve.
Should you experience very long runtimes (i.e. 5-10 times slower) for certain operations (like mass inserts in BW PSA or ODS tables/partitions) then you should set the event 10447 level 50 in the spfile/init.ora. This will reduce the overhead for insertion into compressed tables/partitions.

- Amount of redo data generated can be up to 30% higher

- Up to 60% less physical reads from the database

- Up to  5% less physical writes to the database

- Up to 10% improvement in database buffer hit rate

- No impact on cpu utilization for regular transaction workload.
- Specific operations such as R3load imports (when not using the loadprocedure fast option) or client copies may run slower.

Oracle E-Business Suite Release 12.1 with Oracle Database 11g Advanced Compression

As of 11gR1, update operations incur some overhead as updated rows have to be decompressed before writes and therefore the update frequency is one factor to consider when choosing tables or partitions to be compressed. Read-only operations should benefit from a performance improvement while update-intensive operations will incur some overhead

[Former Member]

Hello Yves,

Yes my requirement is like that to install ECC and then upgrade, there are other background reasons for it.

Ok so I will have to compress the largest tables again after EHP7 upgrade.

I am not practising Its a migration project, so I have to follow the install then upgrade approach as the source is of the base release.

So can I enable compression during installation then again will perform compression after upgrade.

Is this ok?

Thanks for citing all the important points from OSS Notes

[manumohandas82]

Hi Balaji ,

Do you see any reason why you are looking into  compressing the tablespace . Are you in any  sort of compulsion  to reduce the disk usage ?

Compression  more specifically should fall into a maintenance cycle and should not ideally fall during the implementation process .

As Yves has clearly suggested in his previous post  , Compression do have benefits as well as it comes with a small percentage of performance degrade [ Performance may improve too ]  especially during inserts / updates .

So it will be better served for you to think of compressing specific tables [ Involves decision on what tables to compress and why ] after Go-live [ When you see certain tables are growing significantly when compared to others  and there is no CPU overhead in the systems ] .

In a nut shell ,  Do not compress if you are in no compulsion to reduce disk space [ Whish is the main benefit of compression ]

Compression let it fall during the maintenance cycle  [ House keeping / Post go-live tasks ]

Thanks ,

Manu

[former_member185954]

Hello Balaji,

Have you tried searching SAP Notes for these questions ?

The note below should help you.

1289494 - FAQ: Oracle compression

Regards,

Siddhesh

[Former Member]

Thanks Siddhesh.I have checked that note already and there were no reference on the steps detailing when used during installation.

I wanted to know more with respect to Oracle compresssion used during installation.