Using Oracle HCC Compression on VMware Workstation without Exadata or ZFSSA disk

Hybrid Columnar Compression is one of the new features that Oracle has been touting to persuade DBAs on the virtues of their Exadata and ZFSSA storage solutions.

As stated in my bio, I currently get paid by EMC, but this blog post is not another critique of Oracle sales massaging technical numbers on the assumption that you can use HCC for OLTP loads.

Rather, this is a USE AT YOUR OWN RISK method to explore what HCC can and cannot do. Using this method will render your databases unsupported by Oracle and you must NOT do this on any production or mission critical system. Please note I accept no responsibility for anyone destroying critical data from trying any of this.

Back in 2009, Luca Canali of CERN presented a remarkable paper exploring the internals of HCC. Many DBAs would like an opportunity to explore the capabilities of the technology, but without an Exadata or ZFSSA in your basement, that gets tricky.

I believe there is a ZFSSA simulator that you can download and play with, but Luca demonstrated that you can in fact enable HCC capabilities on any storage platform at all, including your humble VMDK virtual disk.

It is no secret that HCC was originally developed to be hardware agnostic, like almost every other feature of the Oracle database, and that some suit in marketing decided that HCC was the killer-app feature that would persuade people to stomp down their hard earned cash and buy Oracle storage over someone elses. And so the word went forth throughout the Kingdom of Larry that henceforth HCC would only work if the database found itself upon a rather limited set of Oracle branded storage.

Indeed, if you try to create an HCC compressed table when you are not on Oracle blessed disks, you get the following error:

SQL> create table hcc_emp_1 compress for archive high as select * from emp;
create table hcc_emp_1 compress for archive high as select * from emp
                                                                  *
ERROR at line 1:
ORA-64307: hybrid columnar compression is only supported in tablespaces
residing on Exadata storage

 

What Luca discovered, when he wasn’t busy helping to smash atoms into one another at insanely high speeds looking for the God particle, is that the HCC-happy switch is actually stored inside of ASM in a special attributes file. The attribute is called cell.smart_scan_capable.

However, you can’t simply flip the on-switch, as an evil spell had been cast to protect the feature from the curious:

SQL> alter diskgroup HCC_DATA set attribute 'cell.smart_scan_capable' = 'TRUE';
alter diskgroup HCC_DATA set attribute 'cell.smart_scan_capable' = 'TRUE'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15242: could not set attribute cell.smart_scan_capable
ORA-15287: could not set disk group attribute cell.smart_scan_capable due to incompatible disks
ORA-15285: disk 'ORCL:HCCDATA' violates disk group attribute cell.smart_scan_capable
ORA-15283: ASM operation requires compatible.rdbms of 11.1.0.7.0 or higher

 

Not to be disheartened, Luca found that our old friend kfed could be used to extract the protected file, turn the feature on, and put the file back again.

This blog post is built on his work and shows how to enable HCC on a 11.2.0.1 database running on VMware Workstation with a humble virtual disk as storage.

First, we should build a new disk group to play with. If you only have one disk group then that works too, but this allows me a little more room for blowing things up.

In this example I have already prepared a new disk and stamped it with ASMLib, labelling the disk as HCCDATA. I am going to create a disk group called HCC_DATA and set both the ASM and RDBMS compatibility level to 11.2.0.0.0

[oracle@localhost ~]$ sqlplus "/ as sysasm"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 8 19:49:43 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Automatic Storage Management option

SQL> create diskgroup HCC_DATA external redundancy disk 'ORCL:HCCDATA' attribute 'au_size' = '4M';

Diskgroup created.

SQL> alter diskgroup HCC_DATA set attribute 'compatible.asm' = '11.2.0.0.0';

Diskgroup altered.

SQL> alter diskgroup HCC_DATA set attribute 'compatible.rdbms' = '11.2.0.0.0';

Diskgroup altered.

 
The attributes for each disk group are stored in file 9, a system file used by ASM to track important data about the layout of the storage.

So now we need to find file 9. We can find it by inspecting the internal view x$kffxp which stores extents of files in ASM. The following query joins several internal views to show file 9 for each disk group, plus the disk where file 9 is physically located:

set linesize 132
col dsk_group   for a20
col dsk_grp_num for 99999999999
col au_size_mb  for 999999999
col au_num      for 999999
col asm_dskn    for a30
col full_path   for a30

select 
  dskg.name_kfgrp dsk_group,
  aloc.group_kfdat dsk_grp_num,
  (dskg.ausize_kfgrp/1048576) au_size_mb,
  aloc.aunum_kfdat au_num,
  dskn.path_kfdsk full_path
from 
  x$kfdat aloc,
  x$kffxp fext,
  x$kfgrp dskg,
  x$kfdsk dskn
where 1=1
and fext.number_kffxp = 9
and aloc.fnum_kfdat= fext.number_kffxp
and aloc.aunum_kfdat = fext.au_kffxp
and dskg.number_kfgrp = aloc.group_kfdat
and dskn.grpnum_kfdsk = aloc.group_kfdat
order by aloc.group_kfdat

 
Executing this script produces the following output:

DSK_GROUP             DSK_GRP_NUM AU_SIZE_MB  AU_NUM FULL_PATH
-------------------- ------------ ---------- ------- ------------------------------
HCC_DATA                        1          4      45 ORCL:HCCDATA
MYDATA                          2          1      49 ORCL:MYDATA

 
So we can see that for our new HCC_DATA diskgroup, file 9 is located in allocation unit 45 on the disk ORCL:HCCDATA. Further we can see that HCC_DATA is using a 4MB allocation unit size.

As you can see I am using ASMLib on this system that adds a layer of indirection to the process. To find out what device ORCL:HCCDATA really is, we can use the following quick shell script:

/etc/init.d/oracleasm querydisk -d `/etc/init.d/oracleasm listdisks -d` | \
cut -f2,10,11 -d" " | \
perl -pe 's/"(.*)".*\[(.*), *(.*)\]/$1 $2 $3/g;' | \
while read v_asmdisk v_minor v_major
do
v_device=`ls -la /dev/oracleasm/disks | grep " $v_minor, *$v_major " | awk '{print $10}'`
echo "ASM disk $v_asmdisk based on /dev/oracleasm/disks/$v_device [$v_minor, $v_major]"
done

 
Executing this from the Linux prompt as oracle we get the following:

ASM disk HCCDATA based on /dev/oracleasm/disks/HCCDATA [8, 33]
ASM disk MYDATA based on /dev/oracleasm/disks/MYDATA [8, 17]

 
So file 9 of disk group HCC_DATA is located in allocation unit 45 of device /dev/oracleasm/disks/HCCDATA

Now we can use the kfed tool to extract the block of the allocation unit we want to see. Luca tells us this is block number 3. I strongly recommend you shut down all databases and the ASM instance before you do this!

[oracle@localhost ~]$ kfed read /dev/oracleasm/disks/HCCDATA aunum=45 blknum=3 aus=4M text=hccdata.txt

 
We can now inspect the contents of hccdata.txt to see if we can find the attribute we want to change:

If you look through the file you made, you should find the following lines:

kfede[0].name:       smart_scan_capable ; 0x034: length=18
kfede[0].value:                   FALSE ; 0x074: length=5

 
So now we can change the FALSE to a TRUE, and put the changes back. Edit the file and change the setting as follows:

kfede[0].name:       smart_scan_capable ; 0x034: length=18
kfede[0].value:                    TRUE ; 0x074: length=5

 
Now we can overwrite the original part of file 9 using the kfed tool again:

[oracle@localhost ~]$ kfed write /dev/oracleasm/disks/HCCDATA aunum=45 blknum=3 aus=4M text=hccdata.txt

 
Now restart ASM and mount all disk groups. Then restart your test database where you want to test HCC.

First, let’s make sure the test database knows that our fake Exadata storage really can’t do offload processing:

SQL> alter system set cell_offload_processing=FALSE;

System altered.

 
In my test database I created a test tablespace called HCCDATA built on the HCC_DATA diskgroup:

SQL> create tablespace HCCDATA datafile '+HCC_DATA'
  2  size 64M autoextend on maxsize 512M;

Tablespace created.

 
Now we can connect as a user and try to create an HCC compressed table. If we use a non HCC enabled tablespace we get the predictable error:

SQL> connect scott/tiger
Connected.
SQL> create table hcctest tablespace USERS compress for archive high as select * from emp;
create table hcctest tablespace USERS compress for archive high as select * from emp
                                                                                 *
ERROR at line 1:
ORA-64307: hybrid columnar compression is only supported in tablespaces
residing on Exadata storage

 
But what if we try on our HCC enabled tablespace:

SQL> create table hcctest tablespace HCCDATA compress for archive high as select * from emp;

Table created.

 
And there you have it, HCC on any storage platform you like.

Have fun.

Cheers Luca!

Advertisements

2 thoughts on “Using Oracle HCC Compression on VMware Workstation without Exadata or ZFSSA disk

    • Hi Jagjeet,

      Thanks for letting me know that things have changed in 11.2.0.3. I intend to check this out myself when time allows, and maybe find an alternative method of allowing HCC on non-Oracle storage systems. After all, Oracle users have paid for the functionality, why should they not be allowed to use it!

      Gruff.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s