In this Blog we will take-out a newly created Oracle ZFS VMware Simulator for a test ride by trying to create some Oracle HCC (Hybrid Columnar Compression) data.
If you don’t have access to an Oracle storage platform, you may want to visit my previous posts in the series Part I or Part II
Two types of Oracle HCC Compression
Warehouse (Query) compression
For warehouse compression, the compression algorithm has been optimized for query performance, specifically for scan-oriented queries used heavily in data warehouses. This approach is ideal for tables that will be queried frequently.
Archive compression
With archive compression, the compression algorithm has been optimized for maximum storage savings. This approach is ideal for tables that are infrequently accessed.
Note that for compressing or decompressing data, archive compression may consume a significant amount of CPU compared to warehouse compression.
Achievable compression ratios vary depending upon the compression type and customer data, however Oracle reports seeing the following ratios:
- Query High ~10:1
- Query Low ~6:1
- Archive Low ~10:1
- Archive High ~15:1
Let’s see how we get on.
The Test
For this test I am going to use Dominic Giles SwingBench ‘customer’ table from the Sales Order Entry schema (SOE) as the source of my test data. The SwingBench wizard can be accessed from the Java application or command line, as you can see from the bellow I will be using the CLI interface.
~/swingbench/bin/oewizard -allindexes -cl -create -cs //localhost/PSTG -dba system -dbap oracle -ts soe -nopart -p soe -scale 4 -tc 8 -u soe -v
Create Test Tables
CONNECT pure/pure;
Prompt No Compression
create table customers as select * from soe.customers;
commit;
Prompt Basic Method
create table customers_classic compress as select * from customers where 1=2;
alter table customers_classic nologging;
insert /*+ append */ into customers_classic select * from customers;
commit;
Prompt OLTP Method
create table customers_oltp compress for oltp as select * from customers where 1=2;
insert into customers_oltp select * from customers;
commit;
Prompt Query Low
create table customers_query_low compress for query low as select * from customers where 1=2;
alter table customers_query_low nologging;
insert /*+ append */ into customers_query_low select * from customers;
commit;
Prompt Query High
create table customers_query_high compress for query high as select * from customers where 1=2;
alter table customers_query_high nologging;
insert /*+ append */ into customers_query_high select * from customers;
commit;
Prompt Archive Low
create table customers_archive_low compress for archive low as select * from customers where 1=2;
alter table customers_archive_low nologging;
insert /*+ append */ into customers_archive_low select * from customers;
commit;
Prompt Archive High
create table customers_archive_high compress for archive high as select * from customers where 1=2;
alter table customers_archive_high nologging;
insert /*+ append */ into customers_archive_high select * from customers;
commit;
If you experience the in-famous ORA-64307 error message and you are using the Oracle ZFS Simulator, re-visit my Part I & Part II Blogs and check your configuration.
Results
Ok, let’s see what compression ratios we achieved.
set feed off
set pages 100
col TABLE_NAME heading ‘Table Name’ format a25
col OWNER heading ‘Owner’ format a20
col COMPRESS_FOR heading ‘Compress For’ format a20
col COMPRESSION heading ‘Compression’ format a20
set lines 100
set echo off
set timing on
Prompt Tables using compression
SELECT SEGMENT_NAME, COMPRESSION, COMPRESS_FOR, sum(BYTES)/1024/1024 MB
FROM DBA_SEGMENTS DS
WHERE DA.OWNER=’PURE’
GROUP BY SEGMENT_NAME, COMPRESSION, COMPRESS_FOR, BYTES;
Achieved ratios are not maybe as high as expected, but the table was pretty small 512MB, so my next project will be to repeat the tests with a larger data set and compare results.
However, the compression ratios are secondary, what this test did demonstrate is the ability to experiment and test out HCC data without access to an Oracle Engineered solution.
| Table Name | Achieved Ratio |
| CUSTOMERS | 1 : 1 |
| CUSTOMERS_CLASSIC | 1.31 : 1 |
| CUSTOMERS_OLTP | 1.10 : 1 |
| CUSTOMERS_QUERY_LOW | 2.29 : 1 |
| CUSTOMERS_QUERY_HIGH | 3.37 : 1 |
| CUSTOMERS_ARCHIVE_LOW | 4.00 : 1 |
| CUSTOMERS_ARCHIVE_HIGH | 5.33 : 1 |
[twitter-follow screen_name=’RonEkins’ show_count=’yes’]
Leave a Reply