Combine unicode conversion and database compression

[Former Member]

Hi Experts

We have Oracle 11 database non unicode and we don't use table compression.

We will do unicode conversion and we think that it is a good opportunity to compress the database.  Please let me know if we can to combine and perform unicode conversion and oracle database compression as one single project.

Thanks in advance.

Osmany

[Former Member]

Hello,

We have also performed the combination Unicode conversion + Oracle OLTP table compression.

Like already said before, after this operation you will end up with a reorganized database in Unicode with OLTP table compression active.

We reduced a 21 Tb database (non Unicode !) to 6,8 Tb (Unicode UTF-8 + OLTP table compression + reorganized).

We have also performed a test without compression, we went from 21 Tb then to 16 Tb.

We are now live for 3 months with OLTP talble compression and the overall DB performance is even better as before (much less disk reads as before).

We did NOT include Oracle index compression for the moment !

During your Unicode import you can select on which table spaces you want OLTP table compression.

Afterwards sapinst will make sure that during the Unicode import all tables are OLTP compressed (index compression is not included here, this is a totally different process).

The coming month we will have a look at Oracle index compression also.

This can be done by online rebuilding the indexes but there is a potential performance issue afterwards, so good testing is necessary.

During Unicode import sapinst also excludes some tables from compression (tables that are already compressed by SAP such as REPOLOAD, ARFCSDATA, EDI40, ...).

Note 1436352 - Oracle 11g Advanced Compression for SAP Systems

Note 1289494 - FAQ: Oracle compression

Also have a detailed look on Oracle licensing because some of the Advanced Oracle compressions require supplementary licensing.

Hope this was useful to you.

Regards.

Wim

[Former Member]

Hello,

Thanks everyone for your comments, it information is very useful to us.

The DB size is 8 TB, we do not have MDMP system.  The downtime will be scheduled on a weekend.

Is The time during unicode import affected by compression process?

The excluded tables to compression will be stored in different tablespace without compression?

Thanks a lot for your help.

Regards.

Osmany

[Former Member]

Hello,

The tables that will not be compressed by sapinst will NOT be put in a separate tablespace.

This is not a requirement, you can store compressed and uncompressed tables in a compressed tablespace.

You can easily query the tables that are compressed as follows:

SQL> select owner, table_name, compression, compress_for from dba_tables;

The actual Unicode import with compression is a little bit more CPU intensive (to calculate the compression).

Of course you need to test this multiple times to see what the optimal parallellism is for both the export and import and for rebuilding the indexes after import.

We exported a 21Tb database on 10 hours and ended up with 1,6 Tb of export files.

The Unicode import + OLTP compression + index rebuilds took 14 hours to end up with a 6,8 Tb database.

We have used 75 parallel processes and table splitting for the biggest tables during the export.

We have used 50 parallel processes for the import/compression + index rebuilds.

Hardware : IBM Power7 with 22 physical CPUs and 1Tb memory.

PS : Unicode conversion itself is done during "export" of the database.

Regards.

Wim

[former_member188883]

Hi Osmany,

As described by Jim during Unicode conversion, you shall be performing export of source database ( non-Unicode) and import into target database (unicode). During this export/import process, entire database gets reorged. So you can see savings in form of  reduced used database space.

Additionally as you are on Oracle 11g, you may activate table compression ( Oracle 11g feature ) on target database so that any new piece of information stored is compressed. This configuration can be activated before you import the unicode database into target system.

Hope this helps.

Regards,

Deepak Kori

[JimSpath]

We did our Unicode conversions several years ago. We have looked at compression, though archiving, index rebuilds and online table reorganizations work pretty well.

Do you have an MDMP system? Not likely, but it complicates the Unicode conversion greatly. I have blogs about our projects on SCN (google "unicode spath site:sap.com").

How big are the databases? How much downtime can you sustain?

Understand that if your system is old, has done archiving, and you have not done much reorgs, the Unicode conversion will shrink your database size...