Re: Effective compression ratio with minimal performance overhead

Tested table compression with the same dummy table with below table compression options. In this test I kept the index uncompressed only. The results are mind boggling. The FTS with none of the compression options going for smartscan and thus the response time has been multifold for the full scan. And also seeing high response time for the bulk Update too. Bulk Insert performance seems quite better with the compressed table.

So from these results the table compression options seem to have a significant performance overhead. Please correct me if I'm missing anything here.

alter table big_table move ROW STORE COMPRESS ADVANCED ;

alter index idx1 rebuild;

alter table big_table move compress for query low ;

alter index idx1 rebuild;

alter table big_table move compress for query high ;

alter index idx1 rebuild;

  Table Size (MB) Bulk Insert performance Bulk Update performance Index Range scan performance Table full scan performance Index full scan performance
No Compress 1184 14.5 sec 7 sec 7.9 sec 1min 11sec 5min 28 sec Row store Compressed Advanced 520 1 sec 15 sec 3 sec 17 min 11 sec 5min 20sec
Compress for query low 216 1.2 sec 11.3 sec 7.19sec 4min 52 sec 5min 29 sec Compress for query high 88 1.8 sec 10.9 sec 9.1 sec 5min 8 sec 5min 44 sec

On Fri, May 27, 2022 at 2:05 AM Lok P <loknath.73_at_gmail.com> wrote:

> I tried running 'analyze index validate structure' and not seeing much of
> the indexes in the list though but I got ~5 partitioned indexes which are
> showing to be OPT_CMPR_PCTSAVE>20 if we compress those using prefix 1 or 2.
> And then, while trying to compress and see the behavior , i got to know
> that index prefix compression can't be done through ALTER INDEX REBUILD
> command for a existing partitioned index , and it can only be done by
> dropping and creating the partitioned index with the prefix length.
>
> However, Advanced index compression (i.e. compress advanced low) can be
> done by "alter index rebuild partition" command in this database version
> 12.1 for selected historical partitions if we want it that way. And this
> can be really helpful just in case we don't want to compress the live
> partition but all. However there is no way we can define/modify the
> existing index property such that all the future partitions will by default
> be created as compressed. For that to happen, we have to drop and create
> the index from scratch as "compress advanced low".
>
> And also in the blog below , it states below and thus it looks like we can
> go by "compress advanced low" as the default compression option and see the
> gain.
>
> "*When compress is enabled for an index using Advanced Index Compression
> Low, the database determines the prefix column count within compressed
> indexes (no need to run Analyze Index as is needed for Prefix Compression).
> Additionally, rather than using a static prefix count for all index leaf
> block (as is done with Prefix Compression), it aims towards computing an
> optimal prefix count for every index leaf block in the index*"
>
>
> https://blogs.oracle.com/dbstorage/post/got-indexes-want-to-reduce-your-database-storage-then-you-need-to-know-about-advanced-index-compression
>
> Below are the details in which I tried to test the index compression on a
> dummy table and apart from its impact on Bulk UPDATE query performance
> others look considerably okay.
>
> https://gist.github.com/oraclelearner/23ed1999dabecd7b0ca0431ad4954ae3
>
> Index Size (MB) Bulk Insert performance Bulk Update performance Index
> Range scan performance Table full scan performance Index full scan
> performance
> No Compress 211 14.5 sec 7 sec 7.9 sec 1min 11 sec 5min 28 sec
> Compress 1 128 14.2 seconds 11.7 sec 9.4 sec 1min 33 sec 6min 51 sec
> Compress Advanced low 128 14.4 sec 11.9 sec 10 sec 1min 18 sec 6min 58 sec
>
>
> Will do a similar test for table compression too and update.
>
> Regards
>
> Lok
>
>
>
> On Mon, May 23, 2022 at 8:56 PM Andy Sayer <andysayer_at_gmail.com> wrote:
>
>> Oracle will tell you what it thinks the perfect number of keys to include
>> is if you use analyze index. Check the index_stats view after (run your
>> analyze commands in your dev environment if your data is representative
>> there).
>>
>> If you’re going to guess, best to go too few than too many.
>>
>> Thanks,
>> Andy
>>
>> On Mon, 23 May 2022 at 16:17, Lok P <loknath.73_at_gmail.com> wrote:
>>
>>> Thank You Andy.
>>>
>>> The top consumers were tables though, but will definitely see how much
>>> size the index compression is giving back.
>>>
>>> Regarding the index compression, I see the "compress advanced high" and
>>> "compress advance low" options are not available untill 12.2 and we have
>>> this database on version 12.1. So is it okay to just grab non-unique
>>> indexes(say top ~10 partition/non partitioned indexes) based on size
>>> descending. And then go for simple compression scripts like below without
>>> any prefix length and let Oracle do the compression on all the keys? OR do
>>> we really have to find the exact prefix columns on which we should do the
>>> compression else will have some performance penalty?
>>>
>>> Alter index IDX1 REBUILD TABLESPACE TBS_IDX COMPRESS <LOCAL> PARALLEL 16;
>>>
>>>
>>>
>>> On Mon, 23 May 2022, 7:34 pm Andy Sayer, <andysayer_at_gmail.com> wrote:
>>>
>>>> Not to distract from all the complexities of table compression. Have
>>>> you tested how much space you can save with standard prefix index
>>>> compression? You might find it both reduce your storage requirements and
>>>> improve your performance across the board.
>>>>
>>>> Thanks,
>>>> Andrew
>>>>
>>>> On Mon, 23 May 2022 at 14:34, Lok P <loknath.73_at_gmail.com> wrote:
>>>>
>>>>> Thank You All. I think the DML(mainly INSERT) which i have tested on
>>>>> the HCC compressed(compress for query low) table, it was performing in same
>>>>> speed as non compressed table , because it was not compressing those newly
>>>>> inserted data. Its just meant for compressing the existing data in the
>>>>> table. And Advanced compression is the one seems to be really compressing
>>>>> the future/incoming DML/data but giving significant(100%) performance
>>>>> overhead. So i was thinking if its okay to just do the HCC compression
>>>>> applied on the existing data and let it incoming data remain uncompressed,
>>>>> but as Mohamed highlighted , if the read queries wont be doing smartscan
>>>>> post this HCC on the existing table , that is going to be an issue.
>>>>>
>>>>> _at_Mohamed, Yes, i was testing the performance of UPDATE and INSERT
>>>>> query on top of the compressed table. There are partitioned tables but the
>>>>> SELECT queries dont partition prune so they gets scanned full and/or
>>>>> through global indexes etc many times.
>>>>>
>>>>> My understanding was that considering this is an OLTP database and we
>>>>> do conventional DML most of the time. As per Doc, Advanced compression is
>>>>> best suited even it gives us least compression ratio. However as i tested
>>>>> the DML performance overhead on the advanced compression table , the
>>>>> execution time is doubled which is little concerning. I have not tested the
>>>>> performance of full scan on the compressed table but the index access seems
>>>>> to be performing with same speed as that of non compressed table.
>>>>>
>>>>> Regarding the HCC compression the doc says , 'compress for query low'
>>>>> is the one which will provide minimal compression ratio or minimal storage
>>>>> space save but its a best compromise for SPACE reduction VS DML and SELECT
>>>>> query performance in a OLTP environment. But as you said it wont perform
>>>>> smartscan post "compress for query low", that is concerning and i have to
>>>>> test it out then.
>>>>>
>>>>> I ran the select query which you posted and its giving
>>>>> "COMP_FOR_QUERY_LOW" as output for the compressed table which is expected.
>>>>>
>>>>> We donot have any tables holding CLOB column though.Also tried running
>>>>> the index fragmentation script in the Jonathan's blog, i see it will give
>>>>> us ~200-300GB of storage space back. But looking for more space reduction.
>>>>>
>>>>> Regards
>>>>>
>>>>> Lok
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Mon, 23 May 2022, 6:12 pm Tim Gorman, <tim.evdbt_at_gmail.com> wrote:
>>>>>
>>>>>> Mladen,
>>>>>>
>>>>>> That is unlikely, as HCC compression (and TDE, for that matter) is
>>>>>> performed immediately prior to direct-path INSERTs, so that completed
>>>>>> blocks (including compression and/or encryption) are written to the
>>>>>> underlying storage layer.
>>>>>>
>>>>>> It would be interesting to see if Exadata somehow changes that?
>>>>>>
>>>>>> Thanks!
>>>>>>
>>>>>> -Tim
>>>>>>
>>>>>>
>>>>>>
>>>>>> On 5/23/2022 5:05 AM, Mladen Gogala wrote:
>>>>>>
>>>>>> On 5/23/22 07:43, Jonathan Lewis wrote:
>>>>>>
>>>>>>
>>>>>> _at_Mladen Gogala <gogala.mladen_at_gmail.com>
>>>>>>
>>>>>> When did that change; I haven't been watching closely but the
>>>>>> compression used to be done at the compute node, and HC decompression at
>>>>>> storage cells, through software, except for the cases where the storage
>>>>>> cell sent the CU to the compute node either to balance the CPU usage or
>>>>>> because the extract from a smart scan was too large for the message buffer.
>>>>>>
>>>>>> Regards
>>>>>> Jonathan Lewis
>>>>>>
>>>>>> Hi Jonathan,
>>>>>>
>>>>>> I think that I've read in some white paper that HCC is done on
>>>>>> storage nodes, transparently to the compute nodes, as of X7. I'll look for
>>>>>> the paper and let you know.
>>>>>>
>>>>>> Regards
>>>>>>
>>>>>> --
>>>>>> Mladen Gogala
>>>>>> Database Consultant
>>>>>> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>>>>>>
>>>>>>
>>>>>>

--
http://www.freelists.org/webpage/oracle-l