Oracle Advanced Compression on SAP systems running oracle 11g. Thoughts?

[Former Member]

Experts,

I'm certainly not a DBA (I'm a basis guy with limited DBA skills), but I was wondering if anyone out there has used oracle advanced compression on any oracle DBs that are running SAP. We have ECC6.0/CRM7.0/BW7.02/PI7.11 systems all running oracle 11g.

Our DBA team is considering buying the oracle advanced compression product. We've been told it could save us 60% in disk space. I wanted to know your thoughts on this product and its impact on an SAP system,

thanks

NICK

[stefan_koehler · Accepted solution]

Hello Nick,

the ACO is awesome .. we have implemented it in our ECC 6.0 (4 TB) and BW 7 (2.5 TB) systems.

The average compression factor was round about 0.7

There were no negative impacts .. we improved the average response time by less pyhsical reads and better cache usage.

Regards

Stefan

[Former Member · Accepted solution]

Stefan,

Thanks for the quick reply. One further question, did you guys choose to compress everything or did you pick/choose the objects that you turn compression on for?

--NICK

[stefan_koehler · Accepted solution]

Hey Nick,

the "strategy" we used depend on the kind of system (OLTP = ECC 6.0 / OLAP = BW 7.01).

We compressed all BW tables and set the default compression attribute for the corresponding tablespaces. This is necessary to keep the compression after specific BW activities where database objects are dropped and recreated.

In ECC 6.0 i choose a selective compression for the biggest tables (> 10 GB) and the cache relevant ones (view x$bh).

Please do not forget the LOBs - depending on your system "load" profile these objects can be a very large part of your database.

Regards

Stefan

[volker_borowski2 · Accepted solution]

> the ACO is awesome .. we have implemented it in our ECC 6.0 (4 TB) and BW 7 (2.5 TB) systems.
> The average compression factor was round about 0.7

Hi Stefan,

how did you measure that and what was your procedure to get there?

I am currently working ACO on a Bank Analyzer BW prodcopy. The target is to move the system to compression online,

so I am not able to do a fair check (that would be R3load export and two installations comp/no-comp) with applying index compression on the "comp"ed system afterwards, then compare). I need to go the online - reorg way.

In this case, I am not doing reorg first and compress afterwards (to get the comp-only values) but just doing it in a single run. This includes the freespace fragmentation return to my win-counters, so my values a quite impressing.

We are around 50% gain yet, but I guess the system needs to see some processing now to re-establish the "normal" Indexsituation (with a couple of 50:50 splits blowing up the index again).

So I expect a fair "operating" value to be around 60% win of the source size.

Did you move your system to new disks, or did you reorg inplace?

Volker

OK, question already answerd.... sorry for being a slow typer

Edited by: Volker Borowski on Sep 19, 2011 9:57 PM

[PH · Accepted solution]

Hi Stefan,

although your post is long ago, I still have a question on your compression strategy in the BW system:

With "[...] we compressed all BW tables [..]" did you mean, that you only compressed those "/BI0/*" and "/BIC/*" tables? Or did you also compress standard SAP system tables and SAP BW system tables like the RS* tables?

We are currently doing an Oracle Advanced Compression project for our BW system 7.4 on Oracle 11.2.0.4.

Would you see issues, if we compress ALL tables of the SAP BW system? Well, we currently have the system tables and the custom BW data tables in one tablesspace and the current strategy is to compress ALL tables...

Best regards,

Philipp Hinnah

[Former Member]

Hi Nick,

Check the note 1436352 - Oracle 11g Advanced Compression for SAP Systems

Best regards,

Orkun Gedik