Oracle Advanced Compression - Performance

[Former Member]

Hi all,

Three Performance-related points in this thread:

1. Index Compression:

I have read in several threads and in the SAP  note 1436352 - Oracle Database 11g Advanced Compression for SAP Systems than the indexes should compressed before than the table.

My question is, can this impact in the performance after compression?

I mean, if I follow this sequence would I have any performance problem:

- Table Online Reorg with brSpace and option -c ctab:

brspace -u / -f tbreorg -o <Owner> -t <Table> -c ctab -p 4

- Index Rebuild with brSpace and option -c cind:

brspace -u / -c force -f idrebuild -o <Owner> -i <Index> -c cind -p 4

2. Number of columns:

I noticed that there is a limitation of 255 columns in the OLTP compression, but my question is if the number of columns could negatively impact in performance accessing compressed tables (Read Operations). In my case the table has 225 columns.

3. Poor performance in Read Operations:

I have compressed a serveral GL tables in our test environment and the times for the same SELECT sentence is around 30% slower after compression. The compression ratio was very good.

As you can imagine, we have applied the compression following the point number 1 of this thread and the table has got 225 columns.

Is there any factor that can impact negatively in read operations in compression tables?

My Oracle version is 11.2.0.3.0 and SAP 7.01

Thanks in advance.

[fidel_vales · Accepted solution]

Hello,

if you are going to compress a table, you can compress the indexes at the same time, you could save time:

brspace -u / -f tbreorg -o <Owner> -t <Table> -c ctabind -p 4

If your table has 255 you should NOT compress the table. it is a limitation of how oracle handles such tables, the likelihood of having performance problems due to chained/migrated rows is much higher. It is recommended that you do not compress tables with more than 250 columns (or decide on a more conservative approach) in order to avoid problems if more columns are added to the table in the future.

for the 3rd point I would have to guess and I do not like it. there could be several reasons and they might be related to the reorganitation itself  (like the clustering factor is different) not to the compression.

it is also important that, in order to avoid known bugs in compression, you have the latest SBP (February 2014) but it should not affect much to SELECT, more to INSERT (in general)