CHAPTER
8
Compression and Archiving
In this chapter, we focus on three new features that mainly relate to expanded database capability, compression, and archiving of data:
NFS server in the database
New Oracle Advanced Index Compression type
Improvements to Hybrid Columnar Compression (HCC) on Exadata
The use of these new features provides greater flexibility in implementation, eases administration, and reduces storage costs. With the NFS server feature, you add a high-availability component to NFS that you might not have in a traditional NFS server environment.
NFS Server in the Database
Oracle Database 12c Release 2 can now be used as an NFS server. Files and their related metadata can be stored in the database. This opens the opportunity to store files, such as Bash and SQL scripts, in the database. Those files can then be replicated and manipulated using database technologies such as PL/SQL and Java stored procedures.
Clients on other servers and desktops use a standard NFS mount command to access the exported file system. At the OS level, the Oracle database responds directly to requests from the NFS daemon in the OS kernel.
Notice that the Oracle documentation uses OFS and NFS somewhat interchangeably. NFS is the industry standard for network file system sharing (thus the acronym). Oracle provides a combination of OS features in Oracle File System (OFS): creating file systems, mounting file systems, dropping file systems, and making those file systems available via the NFS functionality native to the OS.
To create the file system to export, you need something for OFS to export via the DBMS_FS package, such as an Oracle Database File System (DBFS), so first we’ll review how DBFS works before setting up NFS. DBFS has been available since Oracle Database 11g, but it has its own set of enhancements in Oracle Database 12c Releases 1 and 2.
Prerequisites for Setting Up an NFS Server in the Database
These are the prerequisites to installing an in-database NFS server:
To provide NFS access, you must have a DBFS file system already created.
The client(s) must be able to mount the file systems you’ll be exporting with NFS server.
For Oracle Database 12c Release 2, OFS is only available for Solaris and Linux:
For Linux, the kernel module found in the fuse package must be installed.
For Solaris, the kernel module is found in libuvfs.so.
Keep in mind that DBFS already had many of the features of NFS but required Oracle-specific tools and drivers to access the files in DBFS. With an in-database NFS server, no additional tools are required for any remote server or PC that has a generic, industry-standard NFS client installed.
Creating a DBFS File System
Creating a DBFS file system using Oracle Database 12c Release 2 is even easier than in previous releases. Remember that DBFS creates a file system within an Automatic Storage Management (ASM) disk group and exposes it at the OS level to look like another mount point. Once the DBFS file system is ready, you can set up the NFS server next. Before all else, however, you need to install the fuse package, as described next, to allow mounting the DBFS file system on Linux.
Installing Kernel Modules for fuse
To install the fuse RPM package to use DBFS, install it with yum:
Verify that the fuse group exists and add the oracle user to that group:
Finally, make sure your Linux library directory can find the module libfuse.so:
Create a Database for DBFS
A standalone, non-CDB database is required to host DBFS. Use the Database Configuration Assistant (dbca) to create the database. Figure 8-1 shows the creation of a non-CDB named DBFSDB.
FIGURE 8-1. Creating a database for DBFS
Creating the DBFS Tablespace and File System
Setting up a user to manage DBFS is straightforward and needs to be done only once. You can create a DBFS file system in an existing ASM disk group, but in this case I’ve dedicated a disk group called DBFS_FS to host DBFS. Here are the commands:
The next step happens at the OS level by running the SQL script dbfs_create_filesystem.sql. You connect as the user DBFS_OWNER and specify the tablespace you created to hold the DBFS files as well as the external name of the file system—in this case it is FS122:
SQL> @$ORACLE_HOME/rdbms/admin/dbfs_create_filesystem.sql dbfs_ts FS122No errors.SQL>
The script dbms_create_filesystem.sql simplifies your work by calling these procedures and steps:
DBMS_DBFS_SFS.CREATEFILESYSTEM Create the file system
DBMS_DBFS_CONTENT.REGISTERSTORE Register the content store
DBMS_DBFS_CONTENT.MOUNT Mount the file system
DBMS_FUSE.FS_CHMOD Permissions on file system
Now that the DBFS file system is in place, present it to the OS file system using the root account:
Next, as the oracle user, mount the DBFS file system using the dbfs_client command with the DBFS database owner DBFS_OWNER:
Enter the password for the DBFS_OWNER account (again). The session appears to hang—but remember that the DBFS client is running in this session. You can set this up as a background process on server startup using an Oracle Wallet, but that is beyond the scope of this chapter; you can use a spawned process with a password file as well:
Moving on to NFS, you can see from the following session that the file system is in place and you can create files there. The files are actually stored in ASM.
Notice that the subdirectory FS122 under /u02dbfs is the name we gave to the DBFS file system when we ran dbfs_create_filesystem.sql.
Installing NFS Server in the Database
To use an in-database NFS server, you need to enable OFS at the OS level first. To enable OFS, you simply need to “make” the corresponding file:
At the database level, you use the DBMS_FS PL/SQL package to manage the OFS and DBFS mount points.
Using the NFS Client Interface
The DBMS_FS package contains four procedures that create, mount, unmount, and destroy an Oracle File System. To use this package, the user needs to have SYSDBA administrative privilege in the database and root permissions on the operating system. The following are some examples of using the procedure in the DBMS_FS package and their related parameters.
The first step is to create an Oracle File System (OFS) using the MAKE_ORACLE_FS procedure. Here, I’m creating a file system of type ofs and a name of fs122dev and storing it in the tablespace DBFS_TS:
Table 8-1 gives a more detailed description of each parameter to MAKE_ORACLE_FS.
TABLE 8-1. MAKE_ORACLE_FS Parameters
To mount the file system, I’ll use the mount point /u01/app/oracle/u03dbfs2, which already exists:
Run the procedure DBMS_FS.MOUNT_ORACLE_FS to mount the new OFS file system:
Table 8-2 shows the rest of the options available; Table 8-3 has the various mount options for the file system.
TABLE 8-2. MOUNT_ORACLE_FS Parameters
TABLE 8-3. Mount Options for MOUNT_ORACLE_FS Procedure
Unmounting the file system at some point is inevitable, so use the procedure DBMS_FS.UNMOUNT for that, as shown next. Table 8-4 has the full parameter list.
TABLE 8-4. UNMOUNT_ORACLE_FS Parameters
Once unmounted, you can drop the file system as easily as you created it:
Table 8-5 shows the parameters available for DESTROY_ORACLE_FS. Overall, this is much easier to manage than DBFS.
TABLE 8-5. DESTROY_ORACLE_FS Parameters
Having an in-database NFS server allows for greater design flexibility by storing files and their related metadata in the database. This new feature combined with either Oracle Data Guard or Oracle GoldenGate opens up new opportunities for replicating files and ensuring high availability.
Tuning NFS Server in the Database
There is one parameter related to an in-database NFS server, OFS_THREADS, which controls the initial number of worker threads that are started (the default is 4). As server load increases, additional threads are automatically started. You can adjust the OFS_THREADS parameter with the ALTER SYSTEM command, as shown in this example:
The value for OFS_THREADS in a RAC environment should be the same across all instances; you can set this parameter only in a Linux environment.
OFS Dynamic Performance and Data Dictionary Views
The primary dynamic performance view you use with OFS is V$OFSMOUNT. The data dictionary view USER_DBFS_HS_FILES shows the files in the mounted OFS file system owned by the current user. These files are also visible from the OS.
Using Advanced Index Compression
In Oracle Database 12c Release 2, Advanced Index Compression has another option in addition to COMPRESS ADVANCED LOW; you can now also specify COMPRESS ADVANCED HIGH. This new option for index compression compresses at a higher compression ratio than in Oracle Database 12c Release 1. Of course, this greater compression ratio comes at a cost of CPU overhead. It is also necessary to set the database parameter COMPATIBLE to 12.2.0 or higher to use this new feature.
This section quickly reviews the index compression types available in previous releases before diving into the latest compression enhancements. Index compression has been available since Oracle8i Database as a deduplication mechanism, but it wasn’t until Oracle Database 12c Release 1 that Oracle completely reinvented index compression. Of course, you can still use the previous index compression methods—in fact, that is your only option if you don’t have the license for Advanced Index Compression.
Using COMPRESS in Oracle Database 11g
The COMPRESS option for most index types has been available since Oracle8i Database and operates much like Advanced Index Compression works on heap tables: it coalesces duplicate index values in leaf blocks. It’s a logical deduplication of index entries, not a binary compression of the index block. The COMPRESS clause in a CREATE INDEX statement defaults to all columns in the index for a nonunique index, and defaults to all columns except the last one for a multikey unique index. An example of two indexes compressed with the COMPRESS clause on a copy of the DBA_OBJECTS table shows how well (or how poorly) the legacy compression algorithm works. As a prerequisite to the discussion on Advanced Index Compression, let’s create a few indexes on that table, one multicolumn unique index and one nonunique index on a couple of columns with many duplicate values:
The results are not too surprising—unique or primary keys won’t compress well or will even get larger! Indexes with many duplicates will compress very well, especially with duplicates on the trailing column of the index.
Although the CPU overhead is somewhat low for using the COMPRESS clause and can significantly reduce index storage space requirements, it has a couple of downsides. As you can see in the previous examples, it doesn’t help at all with unique or primary key indexes. You might have a multicolumn index that has lots of duplicates on the leading edge of the index or only occasional groups of rows whose trailing index column is duplicated. Daily changes to the indexed table may favor a COMPRESS level of 3 one day, but COMPRESS level 2 another day. As in the previous example, your index may even be larger with compression! In those cases, you get limited compression benefits. Oracle Advanced Index Compression addresses all of these issues.
Using COMPRESS ADVANCED LOW in Oracle Database 12c Release 1
Oracle Database 12c Release 1 introduced a new type of index compression called Advanced Index Compression. It made index compression more automated because you no longer had to guess how many prefix columns you needed for any given index: Oracle figures out the optimal number of prefix columns on a block by block basis. Some index blocks may not compress at all. Thus, it’s rare that an index compressed in Oracle Database 12c Release 1 will grow larger. Following our previous example, let’s re-create those indexes with COMPRESS ADVANCED LOW:
The unique index with Advanced Index Compression dropped in size significantly, and the nonunique index compressed to the same level.
Using COMPRESS ADVANCED HIGH
In Oracle Database 12c Release 2, you can use COMPRESS ADVANCED HIGH in addition to the two compression methods previously discussed. The differences between COMPRESS ADVANCED HIGH and COMPRESS ADVANCED LOW are almost as significant as those between COMPRESS ADVANCED LOW and COMPRESS! To finish our index compression face-off, let’s compress those two indexes with COMPRESS ADVANCED HIGH instead:
Those aren’t typos or Oracle bugs—the unique index is now less than half the size of the uncompressed index, and for the nonunique index, the size is one-tenth of the original.
Here are several reasons why COMPRESS ADVANCED HIGH is an even better method for generating a smaller index footprint:
Additional binary compression methods are available, with different methods available for each index block.
HCC methods from Exadata use a column-based index structure instead of row-based index structure.
Any index block can have a compressed section and a noncompressed section.
New INSERTs into an index block can trigger recompression (as with table compression) and avoid the allocation of new leaf blocks.
ROWID list compression reduces the ROWID footprint even with single-column unique indexes.
Still, the better compression level doesn’t come for free; the CPU requirements for compress and decompress operations with COMPRESS ADVANCED HIGH are measurably higher—but if you have extra CPU cycles available, your increased CPU wait time may be offset by reduced I/O because you’ll be reading fewer index blocks for almost every type of index access method.
To use COMPRESS ADVANCED LOW, the COMPATIBLE parameter must be set at 12.1.0 or higher; COMPRESS ADVANCED HIGH requires COMPATIBLE to be set to at least 12.2.0.
You can’t use either level of Advanced Index Compression with these index types:
Bitmap indexes
Index-organized tables (IOTs), but you can use basic index compression
Function-based indexes
Improvements to Hybrid Columnar Compression (HCC)
There are two new features in Oracle Database 12c Release 2 related to HCC (available on Oracle ZFS or Exadata storage). The first feature is the use of array inserts with HCC. The second feature relates to HCC use with Automatic Data Optimization (ADO) row-level policies. Let’s take a look at how to use each of these new features along with some possible scenarios in which they can be used.
HCC Array Inserts
Prior to Oracle Database 12c Release 2, if you were using HCC during loading or inserting of data, you needed to be aware of certain limitation or optimization methods. The use of direct path technique such as the INSERT … APPEND hint, Parallel DML, Direct Path SQL*Loader, and Create Table as Select (CTAS) was recommended to maximize storage savings. With Oracle Database 12c Release 2, that restriction has been loosened. HCC can now use array inserts for loading or inserting data. This new feature can be used to load tables with HCC using an INSERT … SELECT statement without the APPEND hint. Array inserts used in PL/SQL and Oracle Call Interface (OCI) code can also take advantage of this new feature. This new flexibility makes loading tables with HCC easier.
HCC with Automatic Data Optimization (ADO)
Information Lifecycle Management (ILM) continues to evolve in Oracle Database 12c Release 2. DBAs now can use row-level policies with HCC. This new feature should be of interest to those running Exadata, and other supported Oracle Storage, in a data warehouse environment.
Here is an example of a partitioned table with a row-level policy defined:
When upgrading to Oracle Database 12c Release 2, the choice of simply altering tables to use row-level policy with HCC might be easier than rewriting all of your table creation DDL. During the upgrade process, consider using use Advanced Index Compression for the rows, where appropriate, for even greater storage savings. The following examples demonstrate altering a table to use an ADO row-level policy with different compression levels.
The first example alters a table on HCC storage to use ADO row-level policy with COMPRESS FOR QUERY HIGH:
This example alters a table on HCC storage to use ADO row-level policy with Advanced Index Compression:
Summary
This chapter covered some new features that can help make your life as a DBA easier. Having the in-database NFS server feature opens up new possibilities for how data is shared when used in combination with Oracle Data Guard or Oracle GoldenGate. Improvements in loading HCC tables removes a prior limitation that was once corrected using SQL hints. Indexes can now use the option of COMPRESS ADVANCED HIGH, which can save even more storage than in Oracle Database 12c Release 1. The development and testing of Oracle Database 12c Release 2 took some time but is well worth the wait. In the next chapter, we’ll take a look at the new features for big data, business intelligence, and data warehousing.