How to Estimate Space Savings with Oracle Database 11g Hybrid Columnar Compression and Oracle ZFS Storage Appliance

Part VI of Libraries, Linking, Initialization, and C++ Series
by Raymond Clarke and Louis Romero
Published July 2013

This article provides a procedure for implementing, testing, and evaluating the space savings that you can achieve using storage systems from Oracle and the Hybrid Columnar Compression feature of Oracle Database 11g.

This procedure provided in this article presents a quick, easy way to identify space savings for one of your specific database tables, allowing you to then extrapolate the results and estimate space savings across your entire database.

Oracle has seen compression ratios up to 50x using Hybrid Columnar Compression as well as up to 10x increases in performance for database queries. Higher performance is a direct result of reduced I/O traffic since database tables are retrieved in their compressed format. However, rather than simply trusting Oracle's results, find out what you can achieve with your very own data by following the steps outlined below.

Oracle Database 11g offers multiple types of compression (see related article). The Hybrid Columnar Compression feature is available only when Oracle Database Enterprise Edition is used exclusively with either Oracle ZFS Storage Appliances or Oracle's Pillar Axiom storage systems.

The procedure in this article includes step-by-step instructions for doing the following:

  • Implementing Oracle Database 11g Release 2.0.3 with the latest patches and enabling Hybrid Columnar Compression
  • Configuring the Oracle ZFS Storage 7420 appliance for storing Oracle Database 11g tables
  • Creating a copy of an existing database table and saving it using Hybrid Columnar Compression
  • Comparing the size of the previously existing database table to the newly created table that takes advantage of Hybrid Columnar Compression

Procedure Steps

  1. In a test environment, upgrade to or install Oracle Database 11g Release 2.0.3.

    See Note: 10404530 at My Oracle Support (requires login) to download the Oracle Database 11g Release 2.0.3.

  2. Download and apply the current applicable patches.

    As of June 18, 2013, patch number 14663556 was the only applicable patch. The following substeps describe how to download and install this patch. Before proceeding, please check with your Oracle representative or perform a search on My Oracle Support to find out if any additional or superseding patches should be applied.

    1. Go to My Oracle Support and search for patch 14663556 (or the most current patch).
    2. Download the patch to a machine that is running a browser.
    3. Create a directory to hold the patch, for example, /software on the database server machine.
    4. Transfer the patch file to the patch directory you created, for example, by using ssh.
    5. Change to the patch directory you created.
    6. Unzip the patch file.
    7. Change to the patch directory that was inflated during the unzip process.
    8. Read the ReadMe file associated with the patch and exactly follow its instructions for installing the patch.

      Installing the patch is will yield the output shown in Listing 1 (or something similar):

      

Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_2
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/dbhome_2/oraInst.loc
OPatch version    : 11.2.0.3.4
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_2/cfgtoollogs/opatch/14663
556_Jun_14_2013_13_22_04/apply2013-06-14_13-22-04PM_1.log

Applying interim patch '14663556' to OH '/u01/app/oracle/product/11.2.0/dbhome_2'
Verifying environment and performing prerequisite checks...
All checks passed.
Provide your email address to be informed of security issues, 
install and initiate Oracle Configuration Manager. Easier for 
you if you use your MyOracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  y



Please shutdown Oracle instances running out of this ORACLE_HOME on the local 
system.(Oracle Home = '/u01/app/oracle/product/11.2.0/dbhome_2')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Verifying the update...
Patch 14663556 successfully applied
Log file location: /u01/app/oracle/product/11.2.0/dbhome_2/cfgtoollogs/opatch/14663
556_Jun_14_2013_13_22_04/apply2013-06-14_13-22-04PM_1.log

OPatch succeeded.

      Listing 1

    9. Look for the OPatch succeeded message shown in Listing 1.
    10. Run the opatch lsinventory command to verify that the correct patch was installed. Review the output, which should be similar to what is shown in Listing 2, and look for a message that contains the matching patch number and date installed (shown in bold in Listing 2); this message is definitive proof of successful patch installation:
      11. Oracle Interim Patch Installer version 11.2.0.3.4

Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_2
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/dbhome_2/oraInst.loc
OPatch version    : 11.2.0.3.4
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_2/cfgtoollogs/opatch/opatc
h2013-06-14_13-29-47PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_2/cfgtoollogs/opatch/lsinv
/lsinventory2013-06-14_13-29-47PM.txt

---------------------- ----------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.3.0
There are 1 products installed in this Oracle Home.


Interim patches (1) :

Patch  14663556     : applied on Fri Jun 14 13:23:21 MDT 2013
Unique Patch ID:  15608124
   Created on 10 Mar 2013, 07:11:07 hrs UTC
   Bugs fixed:
     14663556


--------------------------------------------------------------------------------
OPatch succeeded.

      Listing 2

  3. Confirm that SNMP queries sent to the Oracle ZFS Storage Appliance are working by running the snmpget command:

    On Oracle Solaris:

    

/usr//sfw/bin/snmpget -v1 -c public <IP address of storage 
appliance> 1.3.6.1.4.1.42.2.225.1.4.2.0

    On Linux:

    

/usr/bin/snmpget -v1 -c public <IP address of storage 
appliance> 1.3.6.1.4.1.42.2.225.1.4.2.0

    The following is the expected response from either operating system:

    SNMPv2-SMI::enterprises.42.2.225.1.4.2.0 = STRING: "Sun ZFS Storage 7420"

    The snmpget command is part of the NET-SNMP RPM suite. If a binary is not found, mount an ISO image that corresponds to the running distribution on the database server machine and install all NET-SNMP RPMs. Note that there are dependencies that must be resolved. These dependencies will vary from distribution to distribution depending upon what RPMs are loaded on the database server machine. Run rpm -Uvh net-snmp* and start loading the dependent RPMs until the NET-SNMP subsystem loads successfully.

  4. Create a project and share it on the Oracle ZFS Storage Appliance.

    This step assumes that the Oracle ZFS Storage Appliance has already been set up and configured and simply needs to have a storage area defined for where the database tables will reside. All file systems and LUNs in the Oracle ZFS Storage Appliance are managed as shares, which can be grouped into projects for administration purposes. For further description of shares and projects.

    1. Connect to a system that supports ssh and bash.
    2. Copy the HCC-ZFS-PROJ-SHR script that is in the appendix of this document into a file called HCC-ZFS-PROJ-SHR.
    3. Review and customize the HCC-ZFS-PROJ-SHR script. (The script is annotated with recommended changes.)
    4. Execute the following command:

      5. ssh -T root@<IP address of storage appliance> < HCC-ZFS-PROJ-SHR

  5. Mount the Oracle ZFS Storage Appliance share on the database server machine:

    The fstab entry below shows the syntax for an NFS mount on the Oracle ZFS Storage Appliance.

    

<IP address of storage appliance>:<share mount pt as defined by storage appliance> 
nfs nfsvers=3,proto=tcp,hard,intr,rsize=<# of bytes>,wsize=<# of bytes>

    Oracle Solaris example:

    

10.80.16.16:/export/HCC/HCCTABLESPACE - /HCC/D1 nfs - yes 
rw,bg,hard,rsize=32768,wsize=32768,vers=3,forcedirectio,nointr,proto=tcp,suid

    Linux example:

    

10.80.16.16:/export/HCC/HCCTABLESPACE /HCC/D1 nfs 
nfsvers=3,proto=tcp,hard,intr,rsize=32768,wsize=32768

    Note that rsize and wsize are moot when Direct NFS is enabled. In that case, the DNFS subsystem will determine the optimal read and write transfer sizes.

  6. Enable Direct NFS:
    1. First, shut down the database. This will also enable the previously installed patches for Oracle Database 11g. To shut down the database, set ORACLE_SID, ORACLE_BASE, and ORACLE_HOME as appropriate and run sqlplus: 
      2. 

sqlplus / as sysdba << EOF
shutdown [immediate|abort]
EOF
    2. By default, Oracle Database is shipped with Kernel NFS. The change from Kernel NFS to Direct NFS is a matter of setting up a symbolic link to point to the Direct NFS shared library. Use one of the following methods:

      3. First Method:

      As the oracle user, run the following commands:

      

cd $ORACLE_HOME/lib
ls -l libodm11.so
lrwxrwxrwx 1 oracle oinstall 14 Dec 11 15:16 libodm11.so -> libodmd11.so

      To enable Direct NFS on the database server machine, first remove the symbolic link, libodm11.so, using the following command:

      rm -f libodm11.so

      Next, create a new symbolic link to point to the Direct NFS shared library using the following command:

      ln -s libnfsodm11.so libodm11.so

      Verify the results using the following command:

      ls -l libodm11.so

      The output should look similar to the following:

      lrwxrwxrwx 1 oracle oinstall 14 Dec 11 15:16 libodm11.so -> libnfsodm11.so

      Alternate Method:

      From Oracle Database 11g Release 2 and onward, you can also enable Direct NFS by doing the following:

      

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dnfs_on
To disable Direct NFS;
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dnfs_off
  7. Start up the database:
    1. Set ORACLE_SID, ORACLE_BASE, and ORACLE_HOME as appropriate and run sqlplus to start up the database: 
      2. 

sqlplus / as sysdba << EOF
startup
EOF
    2. Tail the alert log file located here: 
      3. 

$ORACLE_BASE/diag/rdbms/<lowercase DB instance name>/<uppercase 
DB instance name>/trace/alert_<uppercase DB instance name>.log
    3. Look for the last occurrence of Direct NFS and make sure it is shown as UP. There should be a series of entries similar to those shown in Listing 3.
      4. 

Direct NFS: attempting to mount /export/ HCC/HCCTABLESPACE on 
the appliance 10.80.16.16 defined in mtab
Direct NFS: channel config is:
channel id [0] local [] path [10.80.16.16]
Direct NFS: mount complete dir /export/ HCC/HCCTABLESPACE on 
10.80.16.16 mntport 44013 nfsport 2049
Direct NFS: channel id [0] path [10.80.16.16] to filer 
[10.80.16.16] via local [] is UP
Direct NFS: channel id [1] path [10.80.16.16] to filer 
[10.80.16.16] via local [] is UP

      Listing 3

  8. Create a tablespace on the Oracle ZFS Storage Appliance:

    9. The following example creates a tablespace named ZFSTS1_HCC as the file /HCC/ZFSTS1_HCC.dbf on the Oracle ZFS Storage Appliance mount point with a size of 1 GB. This tablespace is defined with Hybrid Columnar Compression as the default.

    

sqlplus / as sysdba << EOF

CREATE TABLESPACE ZFSTS1_HCC DATAFILE '/HCC/D1/ZFSTS1_HCC.dbf' SIZE 1024m DEFAULT
COMPRESS;

EOF
  9. Copy an existing table and compress it using Hybrid Columnar Compression:

    This step creates a new table in the defined tablespace ZFSTS1_HCC that was created in the previous step. The WHERE clause in the first statement below is what forces the creation of the table. The new table is called SH.SALES2 and will be populated with data from the existing table SH.SALES (in the next step). The new table is set to utilize Hybrid Columnar Compression.

    The example uses a sample schema that was installed by the Oracle Database Configuration Assistant. It creates a tablespace named ZFSTST1_HCC along with the appropriate tables. A subset of the tables in the ZFSTST1_HCC tablespace includes the following:

    1. Human Resources
    2. Order Entry
    3. Online Catalog
    4. Product Media
    5. Information Exchange
    6. Sales History
      1. SALES
      2. COSTS
      3. CAL_MONTH_SALES_MV
      4. DIMENSION_EXCEPTIONS
      5. SUPPLEMENTARY_DEMOGRAPHICS
      6. COUNTRIES
      7. CUSTOMERS
      8. PROMOTIONS
      9. CHANNELS
      10. PRODUCTS
      11. TIMES

    Run the following commands to create the compressed table SH.SALES2 in the tablespace ZFSTS1_HCC.

    

sqlplus / as sysdba << EOF
CREATE TABLE SH.SALES2 TABLESPACE ZFSTS1_HCC AS SELECT * FROM SH.SALES WHERE 1=0;

ALTER TABLE SH.SALES2 COMPRESS FOR ARCHIVE HIGH;

EOF

    Note: The ALTER command can be specified in the CREATE TABLE command.

  10. Populate the compressed table:

    11. This step causes data from the previously existing table to be copied into the new table and automatically compressed using Hybrid Columnar Compression, since that's how the new table is configured.

    

sqlplus / as sysdba << EOF

INSERT /*+ APPEND */ INTO SH.SALES2 SELECT * FROM SH.SALES;

EOF
  11. Retrieve the size of the original table and the size of the compressed table using the following commands:
    12. 

sqlplus / as sysdba << EOF

SELECT segment_name,sum(bytes/1024/1024) FROM dba_segments WHERE owner='SH' GROUP
BY segment_name ORDER BY 2;

EOF

    Output similar to the following is produced:

    

SEGMENT_NAME                            SUM(BYTES/1024/1024)
SALES2                                                     2
SALES                                                    128
  12. To estimate the total savings for your whole database, calculate the compression ratio by dividing the total size of the new table SH.SALES2 by the size of old table SH.SALES.

    13. If, for example, your SH.SALES2 table is only 2 GB and the original SH.SALES table was 128 GB, you've achieved a 60x compression ratio. Note that the compression ratio will vary depending upon the source data.

Conclusion

While Oracle has performed numerous tests that show up to 50x space savings when using Hybrid Columnar Compression, we are very interested in learning about the results that our customers can achieve with their own data. Oracle invites you to share the results of this exercise with your Oracle technical representative so that you might discuss approaches for making the best use of Hybrid Columnar Compression in your environment. Additional analysis might be required in order to more closely align a recommended solution or strategy to your specific needs.

Appendix A—Script for Creating a Project and Share

The HCC-ZFS-PROJ-SHR script shown in Listing 4 provides a sequence of commands for creating a project and a share on the Oracle ZFS Storage Appliance. The script can be customized for your environment and is annotated to explain what parameters need to be customized.



#
# Create project
#
shares
project HCC
#
# Set project characteristics
#
set mountpoint=/export/HCC
set default_permissions=770
#
# default_user and default_group should be the UID/GID of the database owner.
#
set default_user=100
set default_group=101
set logbias=latency
set rstchown=false
set recordsize=64K
commit
#
# Create share
#
cd /
shares
select HCC
filesystem HCCTABLESPACE
set root_user=root
set root_group=sys
commit

Listing 4

See Also

Oracle Disk Storage Systems Documentation page

About the Authors

Raymond Clarke is a lecturer and consultant with expertise spanning thirty+ years in storage, I/O subsystems and server performance management, capacity planning, and storage infrastructure solutions development. Ray helped start the Storage Network Industry (SNIA) Data Management Forum and served on the forum's board of directors from its inception for many years. Ray has held several technical storage solutions, consulting, and technology evangelizing positions related to the development of long-term digital information retention and preservation and archive solutions. Ray is currently a Senior Enterprise Storage Solutions Consultant in Oracle's Enterprise Solutions Group, supporting field enablement and providing storage area networking subject matter expertise.

Louis Romero is a member of the Oracle Solution Center where he delivers technical solutions for the entire Oracle product line to customers. Louis specializes in systems integration and his development background complements his ability to provide Oracle "red stack" solutions at any level of detail. Louis' business acumen enables him to explain technical content to wide and disparate audiences, regardless of their background. His interests are data protection, virtualized hardware, and big data.

Revision 1.0, 07/18/2013