by Tom Luckenbach
Published February 2013
This article discusses how to enable Hybrid Columnar Compression when using Oracle's Pillar Axiom storage systems.
Note: Refer to the "See Also" section for a link to a white paper that delves further into the technical details and business benefits of using Hybrid Columnar Compression.
Hybrid Columnar Compression enables high levels of data compression and provides cost savings, space savings, and performance improvements due to reduced I/O. It is optimized to use both database and storage capabilities on Oracle's storage devices and is an enabling technology for both warehouse compression and archive compression.
Average storage savings can range from 10x to 15x depending on which Hybrid Columnar Compression level is implemented; real-world customer benchmarks have resulted in storage savings of up to 204x.
Even an average storage savings of 10x can reduce and often eliminate the need to purchase new storage for several years. For example, a 100-terabyte database achieving 10x storage savings would utilize only 10 terabytes of physical storage. With 90 terabytes of storage now available, storage purchases could potentially be delayed for a significant amount of time. The 90 terabytes of storage could even be used to store up to nine more databases with 100 terabytes of data compressed to 10 terabytes of actual disk space each.
Hybrid Columnar Compression is supported only on Oracle's storage systems. The steps in the following sections outline the process of setting up Oracle Database for use with Hybrid Columnar Compression on the Pillar Axiom 600 storage system. The procedure is very similar regardless of whether you are creating a new database or you want to compress tables from an existing database.
Hybrid Columnar Compression on a Pillar Axiom storage system is available with Oracle Database 11g Release 2. There is only one prerequisite for enabling Hybrid Columnar Compression on a Pillar Axiom storage system: you must use Oracle Automatic Storage Management for volume management.
Note: Refer to the "See Also" section for a link to more information about Oracle Automatic Storage Management.
There are three basic steps you must perform to create or use a database to leverage Hybrid Columnar Compression:
These steps are explored in more detail in the following sections.
Note: You may also install a patch set update (PSU), such as Patch 13923374 — Oracle's Grid Infrastructure 184.108.40.206.3, because it contains the Hybrid Columnar Compression patch (13041324) and others.
To enable Hybrid Columnar Compression within a database, you must change three attribute settings in the underlying Oracle Automatic Storage Management disk group:
compatible.asmmust be set to
compatible.rdbmsmust be set to
storage.typemust be set to
For more details on setting disk group attributes, look for the reference to Hybrid Columnar Compression in this document.
The following steps illustrate how to create Oracle Automatic Storage Management disk groups that are compatible with Hybrid Columnar Compression:
Note: It is recommended that you use the whole disk for Oracle Automatic Storage Management disks. There is no need to create any explicit partitions.
For example, with Oracle Linux, the
root user would use the following command to tag the entire disk as an Oracle Automatic Storage Management candidate:
# oracleasm createdisk DATA01 /dev/mapper/2000b080000001269
With Oracle Solaris, the
root user must run the following command to change the ownership to the
oracle user and group for each of the devices to be used in Oracle Automatic Storage Management, for example:
# chown -L oracle:oinstall /dev/rdsk/c0t000B080001003904d0s2
mpathadm list LU command will list out the multipath devices as well.
asmcatool, create one or more disk groups using a level of redundancy set to
Note: When using Oracle Linux, you must (currently) set the default path to the disks to
/dev/oracleasm/disks. Otherwise, the disks might not be properly identified as Pillar Axiom storage.
set attributecommands. For example, Listing 1 shows an example for a disk group called
[Oracle @ myhost] $ export ORACLE_HOME=/u01/app/220.127.116.11/grid [Oracle @ myhost] $ export ORACLE_SID=+ASM [Oracle @ myhost] $ sqlplus / as sysasm SQL * Plus: Release 18.104.22.168.0 Production on Tue May 1 18:04:06 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 22.214.171.124.0 - 64bit Production With the Real Application Clusters and Automatic Storage Management options SQL> alter system set 'compatible'='126.96.36.199' scope=spfile sid='*'; System altered. SQL> alter diskgroup DATA set attribute 'compatible.asm'='188.8.131.52'; Disk group altered. SQL> alter diskgroup DATA set attribute 'compatible.rdbms'='184.108.40.206'; Disk group altered. SQL> alter diskgroup DATA set attribute 'storage.type'='AXIOM'; Disk group altered.
Note: There is a check that verifies whether the disk group is really Pillar Axiom storage. If you attempt to execute the last SQL command on non-Oracle storage, you get an error message similar to this:
ORA-15287: could not set disk group attribute storage.type due to incompatible disks ORA-15285: disk '/dev/mapper/XXXXXXXX' violates disk group attribute storage.type
The remaining steps deal with the data in the database. Whether you are creating a new database or creating new tables in an existing database for the compressed data, the basic requirement is the same. That is, in all cases, you will use table spaces on Oracle Automatic Storage Management disk groups that are compatible with Hybrid Columnar Compression.
You can either create the Hybrid Columnar Compression compressed table on an existing disk group or create a new disk group, depending on space or operational considerations. The key item to heed in this step is that the compatibility setting for the database must be consistent with the disk groups. That is, you must also set the database compatibility to the same version as the disk groups, which is
220.127.116.11 or higher.
Ultimately, there is a variety of methods for actually creating the compressed data. Here are two example SQL statements for creating compressed table data using Hybrid Columnar Compression. The first command below will make a copy of the table for an archive use case. The second command will compress the table data directly (rather than making a new table) using the "alter table move" method. It will compress the data "on the fly" with the designated type of compression suitable for an online use case.
SQL> create table MYDATA_COMP_ARCHIVE compress for ARCHIVE HIGH as select * from MYDATA; SQL> alter table MYDATA move compress for QUERY LOW;
For a more comprehensive discussion of Hybrid Columnar Compression, especially around approaches for table migration and other essential best practices, please review this white paper: "Hybrid Columnar Compression (HCC) on Exadata."
A backup of an Hybrid Columnar Compression–enabled Oracle database will retain the settings upon recovery. If
compatible=18.104.22.168 has been set on the source Oracle database, then Hybrid Columnar Compression will be available upon recovery of a properly patched destination.
If you are creating a new database, you can use the Oracle Database Creation Assistant (
dbca). Here is a "trick" to set the database compatibility when using the
dbca tool. When you are on Step 6 of 12, you might get a warning about mismatched compatibility between the Oracle Automatic Storage Management disk group and the database. To satisfy this requirement and defeat the warning, temporarily set Storage Type to File System, and then click through to Step 9. Click All Initialization Parameters to set the
compatible parameter to
22.214.171.124. Then, go back to Step 6, proceeding as usual, except this time set Storage Type to Oracle Automatic Storage Management.
Tom Luckenbach has been working in the enterprise hardware and software marketplace since 1988. For the last 10 years, he has focused on enterprise storage solutions. Even though he was not born in California, he calls San Francisco his hometown. He is married to a California-born writer and lives with their three kids in Durham, North Carolina. He enjoys fly-fishing when he can.
|Revision 1.0, 02/27/2013|