Advanced Index Compression 12c - _advanced_index_compression_options

[fschoen]

Dear All

We are in the process to upgrade from Oracle 11.2.0.4 to 12.1.0.2.13 ( SBP 12.1.0.2.5 201511 for Exadata).

One open question is the new 12c feature regarding Advanced Index Compression.

In the note

1888485 - Database Parameter for 12.1.0.2

they mentioned the hidden parameter "_advanced_index_compression_options" with the recommend value of 16.

In the referenced note

2138262 - Oracle Database 12c Advanced Compression for SAP Systems

it is mentioned

"...By setting the init.ora parameter _advanced_index_compression_options=16 all newly..."

For me it is not clear if this hidden parameter is recommended always to set or case by case? If it is set then all indexes are compressed.

Does someone has experience with this configuration?

thanks and regards

fabian

[stefan_koehler]

Hi Fabian,

parameter "_advanced_index_compression_options" is for a feature (with costs) called Advanced Index Compression. So the first question would be how you have purchased your Oracle EE license and if you have included the EE Advanced Compression Option in it.

Secondly this feature solves the issue of the "normal" index key compression, where you have to define the prefix (number of compressed columns) which can be very hard as some data is skewed on leaf block level.

More details about how Advanced Index Compression really works can be found in Richard Foote's blog: Advanced Index Compression | Richard Foote

To make it short: If you have the license - go for it (especially if you already have implemented the "normal" index key compression in the past).

Regards

Stefan

[fschoen]

Hi Stefan

Thanks for the reply.

Stefan Koehler wrote:

Hi Fabian,
parameter "_advanced_index_compression_options" is for a feature (with costs) called Advanced Index Compression. So the first question would be how you have purchased your Oracle EE license and if you have included the EE Advanced Compression Option in it.

Secondly this feature solves the issue of the "normal" index key compression, where you have to define the prefix (number of compressed columns) which can be very hard as some data is skewed on leaf block level.

More details about how Advanced Index Compression really works can be found in Richard Foote's blog: Advanced Index Compression | Richard Foote

To make it short: If you have the license - go for it (especially if you already have implemented the "normal" index key compression in the past).

It's not about how the Advanced Index Compression works, it's more how it works in the SAP environment when set as "default" with configure the hidden parameter.

Do you have some experience/customers who had issues with the setting to 16?

Would it make sense to rebuild the "prefixed indexes" to "advanced low" or leave it in respect to a stable, working environment?

To make it short too: Yes, we have the option licensed and we use partially the index key compression. So we are using it also for the first migrated database and see if there are some negative side effects.

regards

fabian

[stefan_koehler]

Hi Fabian,

> It's more how it works in the SAP environment when set as "default" with configure the hidden parameter. Do you have some experience/customers who had issues with the setting to 16?

The feature works the same way as in any other (non-SAP) system environment. Yes, some clients are using it as they already implemented (normal) index key compression before, but hit the limit with hard coded prefix (and partially blown-up indexes).

> Would it make sense to rebuild the "prefixed indexes" to "advanced low" or leave it in respect to a stable, working environment?

The parameter is especially useful in case of R3load migrations (otherwise you would need to analyze the corresponding indexes first and add the corresponding compression clause in the new environment / R3load template). If you would benefit from the new enhancement in your current environment with already implemented normal index key compression (on some indexes) depends on your data and (possibly) partially blown-up indexes. For any other (non-compressed) indexes it would be useful of course as it is dynamic on leaf block level.

> Yes, we have the option licensed and we use partially the index key compression. So we are using it also for the first migrated database and see if there are some negative side effects.

Then go for it

Regards

Stefan