Exadata Hybrid Columnar Compression – HCC : Comparison between Compress for Query High and Archive High

advertisements

_____________________________________________________________________________________________________________________

Here I am creating a test from all_objects view.  

SQL> create table object_list as select * from all_objects;

Table created.

SQL> insert into object_list (select * from object_list);

96558 rows created.

..

..

SQL>  insert into object_list (select * from object_list);

12359424 rows created.

SQL> commit;

Commit complete.

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name ='OBJECT_LIST';

SUM(BYTES)/1024/1024

--------------------

                2743

Compare the time for table creation

SQL> set timing on

SQL> set autotrace on stat

SQL> create table OBJECT_LIST_Comp_Query_high compress for query high as select * from OBJECT_LIST;

Table created.

Elapsed: 00:01:37.46

SQL> create table OBJECT_LIST_Comp_ARCH_HIGH compress for archive high  as select * from OBJECT_LIST;

Table created.

Elapsed: 00:07:01.59

The time required for compress for archive is more than the time required for compress for query option.  This would be a one time cost in terms of table creation.

SQL>  select segment_name,bytes/1024/1024/1024 SizeGB from user_segments where segment_name like 'OBJECT_LIST%';

SEGMENT_NAME                       SIZEGB

------------------------------ ----------

OBJECT_LIST                    2.67871094

OBJECT_LIST_COMP_ARCH_HIGH         .15625

OBJECT_LIST_COMP_QUERY_HIGH      .2578125

Elapsed: 00:00:00.02

There are two columns available (Compression and Compress_for) with user_tables to check compression options on the table.

SQL> select TABLE_NAME, COMPRESSION, COMPRESS_FOR from user_tables where table_name like 'OBJECT_LI%';

TABLE_NAME                     COMPRESS COMPRESS_FOR

------------------------------ -------- ------------

OBJECT_LIST                    DISABLED

OBJECT_LIST_COMP_ARCH_HIGH     ENABLED  ARCHIVE HIGH

OBJECT_LIST_COMP_QUERY_HIGH    ENABLED  QUERY HIGH

Comparison between the reads and Smart scan Bytes

SQL> select count(*) from object_list;

  COUNT(*)

----------

  24718848

SQL> select sstat.name, my.value/1024/1024

    from   v$sysstat sstat, v$mystat my

    where  sstat.statistic#=my.statistic#

    and    (sstat.name = 'physical read total bytes'

or sstat.name like 'cell physical IO interconnect bytes%')

    /

NAME                                               MY.VALUE/1024/1024

-------------------------------------------------- ------------------

physical read total bytes                                  2698.96094

cell physical IO interconnect bytes                        294.481583

cell physical IO interconnect bytes returned by sm         294.481583

art scan

SQL> select count(*) from OBJECT_LIST_COMP_QUERY_HIGH    ;

  COUNT(*)

----------

  24718848

SQL> select sstat.name, my.value/1024/1024

    from   v$sysstat sstat, v$mystat my

    where  sstat.statistic#=my.statistic#

    and    (sstat.name = 'physical read total bytes'

or sstat.name like 'cell physical IO interconnect bytes%')

    /

NAME                                               MY.VALUE/1024/1024

-------------------------------------------------- ------------------

physical read total bytes                                  257.390625

cell physical IO interconnect bytes                        7.09282684

cell physical IO interconnect bytes returned by sm         4.69438934

art scan

SQL> select count(*) from OBJECT_LIST_COMP_ARCH_HIGH;

  COUNT(*)

----------

  24718848

SQL> select sstat.name, my.value/1024/1024

    from   v$sysstat sstat, v$mystat my

    where  sstat.statistic#=my.statistic#

    and    (sstat.name = 'physical read total bytes'

or sstat.name like 'cell physical IO interconnect bytes%')

    /

NAME                                               MY.VALUE/1024/1024

-------------------------------------------------- ------------------

physical read total bytes                                  155.476563

cell physical IO interconnect bytes                        6.54924011

cell physical IO interconnect bytes returned by sm         3.63517761

art scan

So, the physical reads total bytes comparison would be like this.

Normal Table > Compress for Query > Compress For Archive.

Compare the Speed of Execution

SQL> select count(*) from object_list;

  COUNT(*)

----------

  24718848

Elapsed: 00:00:01.19

SQL> select count(*) from OBJECT_LIST_COMP_ARCH_HIGH         ;

  COUNT(*)

----------

  24718848

Elapsed: 00:00:00.45

SQL> select count(*) from OBJECT_LIST_COMP_QUERY_HIGH      ;

  COUNT(*)

----------

  24718848

Elapsed: 00:00:00.37

We cannot compare the results in one word in exadata server; but below mentioned chart will give some relative comparison. J

	TABLE
Operation	Normal	Compress For Query	Compress For Archive
Speed of Select queries	Fast	Fastest	Faster
Time For Table Creations	Fastest	Faster	Fast
Physical reads	High	Lowest	Low
Bytes from Smart Scan	High	Lowest	Low

_____________________________________________________________________________________________________________________

Website Stats