Database growth following index key compression in Oracle 11g

[Former Member]

Hi,

We have recently implemented index key compression in our sap R3 environments, but unexpectedly this has not resulted in any reduction of index growth rates.

What I mean by this is that while the indexes have compressed on average 3 fold (over the entire DB), we are not seeing this with the DB growth going forward.

ie We were experiencing ~15GB/month growth in our database prior to compression, but this figure doesnt seem to have changed much in the 2-3months that we have implemented in our production environments.

Our trial with ACO compression seemed to yield reduction of table growth rates that corresponded to the compression ratio (ie table data growth rates dropped to a third after compression), but we havent seen this with index compression.

Does anyone know if a rebuild with index key compression will it compress any future records inserted into the tables once compression is enabled (as I assumed) or does it only compress whats there already?

Cheers

Theo

[stefan_koehler]

Hello Theo,

Does anyone know if a rebuild with index key compression will it compress any future records inserted into the tables once compression is enabled (as I assumed) or does it only compress whats there already?

I wrote a blog about index key compression internals long time ago (), but now i noticed that one important statement is missing. Yes future entries are compressed too - index key compression is a "live compression" feature.

We were experiencing ~15GB/month growth in our database prior to compression, but this figure doesnt seem to have changed much in the 2-3months that we have implemented in our production environments.

Do you mean that your DB size still increases ~15GB per month overall or just the index segments? Depending on the segment type growth - maybe indexes are only a small part of your system at all.

If you have enabled compression and perform a reorg of them, you can run into one-time effects like 50/50 block splits due to fully packed blocks, etc. It also depends on the way the data is inserted/updated and which indexes are compressed.

Regards

Stefan

[Former Member]

Prior to compression, we were seeing growth rate of 30-33GB/month, 15GB of index growth and 15GB or so for tables.

Index sizes in SAP tablespace (PSAPDAT) have continued to grow at 15GB/month following large scale compression (index compression of top 200 or so tables), and Im trying to understand why if this is a live compression technique.

Edited by: Theo Magiris on Jan 17, 2012 5:50 AM

[stefan_koehler]

Hi Theo,

as already mentioned - after you have reorganized your indexes you can run into effects like 50/50 block splits where "half of the space is wasted".

You can verify this with an index tree dump.

SQL> SELECT object_id FROM dba_objects WHERE object_name = '<INDEX_NAME>';
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level <OBJECT_ID>';

Regards

Stefan

[former_member188883]

Hi,

Does anyone know if a rebuild with index key compression will it compress any future records inserted into the tables once compression is enabled (as I assumed) or does it only compress whats there already?

Rebuild the index will compress whats there already. New entries will get compressed with index key compression.

Regards,

Deepak Kori

[Former Member]

Further info, we implemented index compression as per scripts provided in

Note 1109743 - Use of Index Key Compression for Oracle Databases

Cheers

Theo