by Raymond Clarke and Louis Romero
Published July 2013
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:
See Note: 10404530 at My Oracle Support (requires login) to download the Oracle Database 11g Release 2.0.3.
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.
/softwareon the database server machine.
Installing the patch is will yield the output shown in Listing 1 (or something similar):
Oracle Interim Patch Installer version 18.104.22.168.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 : 22.214.171.124.4 OUI version : 126.96.36.199.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, 188.8.131.52.0... Patching component oracle.rdbms.rsf, 184.108.40.206.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.
OPatch succeededmessage shown in Listing 1.
opatch lsinventorycommand 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:
Oracle Interim Patch Installer version 220.127.116.11.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 : 18.104.22.168.4 OUI version : 22.214.171.124.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 126.96.36.199.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.
On Oracle Solaris:
/usr//sfw/bin/snmpget -v1 -c public <IP address of storage appliance> 188.8.131.52.184.108.40.206.220.127.116.11.0
/usr/bin/snmpget -v1 -c public <IP address of storage appliance> 18.104.22.168.22.214.171.124.126.96.36.199.0
The following is the expected response from either operating system:
SNMPv2-SMI::enterprises.188.8.131.52.4.2.0 = STRING: "Sun ZFS Storage 7420"
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.
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, refer to the Oracle documentation.
HCC-ZFS-PROJ-SHRscript that is in the appendix of this document into a file called
HCC-ZFS-PROJ-SHRscript. (The script is annotated with recommended changes.)
ssh -T root@<IP address of storage appliance> < HCC-ZFS-PROJ-SHR
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
10.80.16.16:/export/HCC/HCCTABLESPACE /HCC/D1 nfs nfsvers=3,proto=tcp,hard,intr,rsize=32768,wsize=32768
wsize are moot when Direct NFS is enabled. In that case, the DNFS subsystem will determine the optimal read and write transfer sizes.
ORACLE_HOMEas appropriate and run
sqlplus / as sysdba << EOF shutdown [immediate|abort] EOF
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
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
ORACLE_HOMEas appropriate and run
sqlplusto start up the database:
sqlplus / as sysdba << EOF startup EOF
$ORACLE_BASE/diag/rdbms/<lowercase DB instance name>/<uppercase DB instance name>/trace/alert_<uppercase DB instance name>.log
Direct NFSand make sure it is shown as
UP. There should be a series of entries similar to those shown in Listing 3.
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  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  path [10.80.16.16] to filer [10.80.16.16] via local  is UP Direct NFS: channel id  path [10.80.16.16] to filer [10.80.16.16] via local  is UP
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
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:
Run the following commands to create the compressed table
SH.SALES2 in the tablespace
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
ALTER command can be specified in the
CREATE TABLE command.
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
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
SH.SALES2by the size of old table
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.
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.
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
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|