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

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


DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google