This content has been marked as final. Show 7 replies
With package dbms_redefinition
I suggest please see:
Master Note for OLTP Compression [ID 1223705.1]
Thanks for the provided suggestion AlexEric ,
I've tested dbms_redefinition and it works ok when there are no global indexes on the partitioned table.
When used for compression of a partition(interim table is created with COMPRESS option), it is mandatory to use DBMS_REDEFINITION.CONS_USE_PK approach.
In our case, the primary key constraint uses a global index and this leads to very slow execution of DBMS_REDEFINITION.FINISH_REDEF_TABLE.
During the execution , the session does 'exchange partition' under the hood, which in our caes does:
- full table scan of the partition to be compressed
- full table scan of the interim table
- sort output
The worst part, in this case, is that during all this time, there is an exclusive lock on the WHOLE partitioned table. This means that all SELECTs and DMLs, regardless of what partitions they use, are blocked during DBMS_REDEFINITION.FINISH_REDEF_TABLE execution.
The execution time of DBMS_REDEFINITION.FINISH_REDEF_TABLE is proportional to the size of the partition that is going to be redefined.
Details of this case are provided in this Metalink note: Full Table Scan Of Non-partitioned Table During Exchange Partition Operation [ID 728654.1]
We consider replacing our global indexes with local ones, but this has a requirement the partitioning key to be part of the index that will enforce the primary key constraint.
When global indexes are used, DBMS_REDEFINITION is not what I'll call ONLiNE, because of locking issue that could block all other sessions.
Thanks for the provided note Helios- Gunes EROL
In our case, we'd like to use the BASIC compression since OLTP Compression is part of the Advanced compression feature, and it costs extra.
If we manage to compress it online, using the BASIC compression, this would be more than enough, it's not so frequently modified data.
Edited by: Adrian Angelov on Jun 12, 2012 10:12 AM
The other method we've tried is ALTER TABLE ... MOVE PARTITION ... TABLESPACE ... COMPRESS ... UPDATE INDEXES:
but this is not 100% ONLINE too, since for 'short' periods of time the LOCAL INDEXES(only the partitions that are currently moved) are UNUSABLE. Details are provided in Metalink note: Local Index UNUSABLE for Short Period in Alter Table Move Partition [ID 1374199.1].htm
Our tests revealed that for a 7GB partition, the time frame during which an index partition corresponding to the currently modified table partition was UNUSABLE for ~41 seconds. This is not acceptable and I won't call it ONLINE method too.
in the end, we've decided to do it using ALTER TABLE ... MOVE PARTITION .... COMPRESS UPDATE INDEXES; despite of the problem with the local indexes.
It's not a perfect world, considerations all the time.
Back with the results, if anyone is interested / planning to do this in future.
We've done ALTER TABLE ... MOVE PARTITION ... COMPRESS UPDATE INDEXES for ~80 partitions on 8 tables, a total of ~200GB. Everything went fine and we managed to compress the partitions to 110GB.
A day after the compression took place, we experienced a total application hang for ~8 minutes, the reason: 'enq: TX - index contention' on one of the global indexes on one of the above 8 tables for all sessions that try to insert into the table.
It turned out that all global indexes on the above 8 tables are x2 or x3 times bigger compared to their sizes prior the execution of ALTER TABLE ... MOVE PARTITION ... COMPRESS UPDATE INDEXES .
During the problematic 8 minutes a session was reading the primary key index(using 'db file sequential reads') and all other sessions that try to insert in the problematic table were stuck on 'enq: TX - index contention', blocked by the session reading the primary key index.
We've logged a service request and the analyst wrote that we hit bug 13827402 and rebuild of the indexes will most probably help(after the rebuild of the global indexes, the sizes are back to the original values)
Edited by: Adrian Angelov on Jun 26, 2012 11:05 AM