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.

[Former Member]

Hi,

On point#3 - I trust it is not a clustered table?

Compression impacts the update/inserts badly if you compress the tables which have >255 columns or if you compress the clustered tables. I don't think compression is the direct cause which is impacting SELECT statement in your case (As said above, it need to be checked in detail).

Regards,

Nick Loy

[Former Member]

Hi Nick,

No it's not a clustered table it's a transparent table.

Kind Regards

[Former Member]

Hi,

On transparent tables it's a known issue, take a look at below note which says not to compress the transparent tables.

We observed delayed insert/updates on few of the transparent tables after the compression.

Solution we applied was disabling the table compression for newly allocated blocks. You can uncompress the table without losing the compression benefits. Only new allocated blocks will be in uncompressed mode and you will get your performance back.

                         1847870  - Oracle 11g: Advanced Compression - Known issues and solution            

Regards,

Nick Loy

[Former Member]

Hi,

But this case is not neither an insert nor an update operation, it's a SELECT sentence.

For SELECT sentences make no sense to have a different behavior just marking the table as uncompressed without table reorg, right?

Kind Regards

[Former Member]

Yes,

It shouldn't behave like this!

Can you just "uncompress" the table and check whether select is faster or not?

Regards,

Nick Loy

[Former Member]

Hi Nick,

I have deactivated the compression and run the query again but there is no difference in the CPU time.

Kind Regards