Effective compression ratio with minimal performance overhead
Date: Mon, 23 May 2022 01:01:14 +0530
Message-ID: <CAKna9VZrjFPCSwB8Y7YvE-jWJLqn6fVvK-_6jEe48PLSZfXO-A_at_mail.gmail.com>
Hello All, It's an OLTP kind of database in which there is a storage space crunch on the cluster and we have been asked to see if we can get some space back through table/index compression. And while analyzing the compression techniques , i got to know for OLTP tables i.e. heavy conventional DMLS and READ queries, advanced compression is best suited(i.e. ROW STORE COMPRESS ADVANCED). And this will also compress the future incoming data those inserted/updated/deleted using conventional DML operation. This is having minimal overhead on DMLs. And as we have exadata so out of possible HCC compression techniques , 'compress for query low' will give the highest compression ratio with minimal overhead on the DMLS but it won't compress the incoming data through conventional DML operation.
While testing these above two compression options on set of data , I am seeing below results,
With Advanced compression, a table of size ~500GB is reduced to ~100GB. But
the execution time of a batch INSERT query (using a batch size of 100) is
increased from ~6minutes to ~13minutes for 10million rows. And run time of
a multi threaded 'UPDATE' using pipelined function has been increased from
~11minutes to ~29minutes. It was updating 16million rows.
With HCC compression(i.e compress for query low), table of size ~500Gb is
reduced to ~31GB. But the performance of a batch INSERT query (using a
batch size of 100) remains the same as with an uncompressed table. And the
same UPDATE was somehow failing with a deadlock error, yet to get the cause.
So I want to understand from experts here, Is there a better way of
compressing the OLTP type data and is this 100% degradation in DML
performance expected with Advanced compression? And on the HCC compression
for INSERTS we are not seeing any performance degradation , so does that
Basically here the data is loaded using conventional batch Inserts then gets updated in parallel in bulk and purged using DELETE statements. I hav not checked/tested index compression , as I understand they are a bit tricky as we may not get storage benefit from all types of indexes(say for e.g unique index etc). The oracle version is 12.1.0.2.0 and its Exadata.
Regards
Lok
-- http://www.freelists.org/webpage/oracle-lReceived on Sun May 22 2022 - 21:31:14 CEST