Posts Tagged ‘Oracle’


Oracle Advanced Compression

The Oracle Database 11g Advanced Compression Option introduces a comprehensive set of compression capabilities to help customers maximize resource utilization and reduce costs. It allows IT administrators to significantly reduce their overall database storage footprint by enabling compression for all types of data – be it relational (table), unstructured (file), or backup data. Although storage cost savings are often seen as the most tangible benefit of compression innovative technologies included in the Advanced Compression Option are designed to reduce resource requirements and technology costs for all components of your IT infrastructure, including memory and network bandwidth.

Compression for Table Data

Oracle has been a pioneer in database compression technology. Oracle Database 9i introduced Basic Table Compression several years ago that compressed data that was loaded using bulk load operations. Oracle Database 11g Release 1 introduced a new feature called OLTP Table Compression that allows data to be compressed during all types of data manipulation operations, including conventional DML such as INSERT and UPDATE. In addition, OLTP Table Compression reduces the associated compression overhead of write operations making it suitable for transactional or OLTP environments as well. OLTP Table Compression, therefore, extends the benefits of compression to all application workloads.

It should be noted that Basic Table Compression is a base feature of Oracle Database 11g Enterprise Edition (EE). OLTP Table Compression is a part of the Oracle Advanced Compression option, which requires a license in addition to the Enterprise Edition.

OLTP Table Compression

Oracle’s OLTP Table Compression uses a unique compression algorithm specifically designed to work with OLTP applications. The algorithm works by eliminating duplicate values within a database block, even across multiple columns. Compressed blocks contain a structure called a symbol table that maintains compression metadata. When a block is compressed, duplicate values are eliminated by first adding a single copy of the duplicate value to the symbol table. Each duplicate value is then replaced by a short reference to the appropriate entry in the symbol table. Through this innovative design, compressed data is self-contained within the database block as the metadata used to translate compressed data into its original state is stored in the block. When compared with competing compression algorithms that maintain a global database symbol table, Oracle’s unique approach offers significant performance benefits by not introducing additional I/O when accessing compressed data.

Benefits of OLTP Table Compression

The compression ratio achieved in a given environment depends on the nature of the data being compressed; specifically the cardinality of the data. In general, customers can expect to reduce their storage space consumption by a factor of 2 to 3 by using the OLTP Table Compression feature. That is, the amount of space consumed by uncompressed data will be two to three times larger than that of the compressed data. The benefits of OLTP Table Compression go beyond just on-disk storage savings. One significant advantage is Oracle’s ability to read compressed blocks directly without having to first uncompress the block. Therefore, there is no measurable performance degradation for accessing compressed data. In fact, in many cases performance may improve due to the reduction in I/O since Oracle will have to access fewer blocks. Further, the buffer cache will become more efficient by storing more data without having to add memory.

Minimal Performance Overhead

As stated above, OLTP Table Compression has no adverse impact on read operations. There is additional work performed while writing data, making it impossible to eliminate performance overhead for write operations. However, Oracle has put in a significant amount of work to minimize this overhead for OLTP Table Compression. Oracle compresses blocks in batch mode rather than compressing data every time a write operation takes place. A newly initialized block remains uncompressed until data in the block reaches an internally controlled threshold. When a transaction causes the data in the block to reach this threshold, all contents of the block are compressed. Subsequently, as more data is added to the block and the threshold is again reached, the entire block is recompressed to achieve the highest level of compression. This process repeats until Oracle determines that the block can no longer benefit from further compression. Only transactions that trigger the compression of the block will experience the slight compression overhead. Therefore, a majority of OLTP transactions on compressed blocks will have the exact same performance as they would with uncompressed blocks.

OLTP Table Compression Syntax

CREATE TABLE emp (

emp_id NUMBER

, first_name VARCHAR2(128)

, last_name VARCHAR2(128)

) COMPRESS FOR OLTP;

Compression for File Data

SecureFiles, a new feature in Oracle Database 11g, offers a ‘best-of-both-worlds’ architecture for storing unstructured content, such as documents, spreadsheets and XML files. SecureFiles is specifically engineered to deliver high performance for file data comparable to that of traditional file systems while retaining the advantages of the Oracle database. SecureFiles is designed as a superset of the ANSI standard LOB data type and offers easy migration from existing BasicFile LOBs, the precursor to SecureFiles. With SecureFiles, organizations can now manage all relational data and associated file data in Oracle using a single security/audit model, a unified backup & recovery process, and perform seamless retrievals across all information. The Advanced Compression Option of Oracle Database 11g includes technologies that drastically reduce the storage footprint of SecureFiles data.

SecureFiles Deduplication

It is extremely common for applications to store exact replicas of files. A typical example is an email application where multiple users may receive the same attachment. SecureFiles Deduplication is an intelligent technology included with the Advanced Compression option that eliminates duplicate copies of SecureFiles data. Oracle stores one image of the SecureFiles data and replaces the duplicate copies with references to this image. Consider an email application where 10 users receive an email with the same 1MB attachment. Without SecureFiles Deduplication, the system would store one copy of the file for each of the 10 users – requiring 10MB of storage. If the email application in our example had used SecureFiles with Deduplication, it would have stored the 1MB attachment just once. That’s a 90% savings in storage requirements. In addition to the storage savings, SecureFiles Deduplication also increases application performance. Specifically, write and copy operations are much more efficient since only references to the SecureFiles image are written. Further, read operations may improve if duplicate SecureFiles data already exists in the buffer cache.

SecureFiles Deduplication Syntax

CREATE TABLE images (

image_id NUMBER,

image BLOB)

LOB(image) STORE AS SECUREFILE

(TABLESPACE lob_tbs DEDUPLICATE);

SecureFiles Compression

The Advanced Compression Option of Oracle Database 11g provides yet another mechanism to control the size of your SecureFiles data. In addition to Deduplication discussed earlier, SecureFiles Compression utilizes industry standard compression algorithms to further minimize the storage requirements of SecureFiles data. With SecureFiles compression, typical files such as documents or XML files, experience a reduction of 2 to 3 times in size. Using built-in intelligence, SecureFiles Compression automatically avoids compressing data that would not benefit from compression – for instance a document that was compressed via a 3rd party tool before being inserted into the database as a SecureFiles file. Applications are still able to perform random reads and writes on compressed SecureFiles data since the compressed data is broken down into small chunks of data. This can vastly improve performance when compared with compressing entire files before inserting them into the database.

There are three levels of SecureFiles compression available: LOW, MEDIUM, and HIGH. By default, SecureFiles Compression uses the MEDIUM level, which typically provides good compression with a modest CPU overhead of 3-5%. SecureFiles Compression LOW, new in Oracle Database 11g Release 2, is optimized for high performance. SecureFiles Compression LOW actually maintains about 80% of the compression achieved through MEDIUM, while utilizing 3x less CPU. Finally, SecureFiles Compression HIGH achieves the highest storage savings but incurs the most CPU overhead.

SecureFiles Compression Syntax

CREATE TABLE images (

image_id NUMBER,

image BLOB)

LOB(image)STORE AS SECUREFILE

(TABLESPACE lob_tbs COMPRESS);

Compression for Backup Data

In addition to compressing data stored inside the database, Oracle Advanced Compression also includes the capability to compress backed up data. Recovery Manager (RMAN) and Data Pump are the two most commonly used tools to backup the data stored inside an Oracle Database. RMAN makes a block-by-block backup of the database data, also known as a “physical” backup, which can be used to perform database, tablespace or block level recovery. Data Pump is used to perform a “logical” backup by offloading data from one or more tables into a flat file. Oracle Advanced Compression includes the capability to compress the backup data generated by both of these tools.

Recovery Manager Compression

The continuous growth in enterprise databases creates an enormous challenge to database administrators. The storage requirements for maintaining database backups and the performance of the backup procedures are directly impacted by database size. Oracle Advanced Compression includes RMAN compression technology that can dramatically reduce the storage requirements for backup data. Due to RMAN’s tight integration with Oracle Database, backup data is compressed before it is written to disk or tape and doesn’t need to be uncompressed before recovery – providing an enormous reduction in storage costs.

There are three levels of RMAN Compression: LOW, MEDIUM, and HIGH. The amount of storage savings increases from LOW to HIGH, while potentially consuming more CPU resources.

Syntax for setting the RMAN compression algorithm:

RMAN> SET COMPRESSION ALGORITHM ‘LOW|MEDIUM|HIGH’;

Syntax for taking a compressed RMAN backup:

RMAN> backup as COMPRESSED BACKUPSET database archivelog all;

Data Pump Compression

The ability to compress the metadata associated with a Data Pump job was first provided in Oracle Database 10g Release 2. In Oracle Database 11g, this compression capability has been extended so that table data can be compressed on export. Data Pump compression is an inline operation, so the reduced dump file size means a significant savings in disk space. Unlike operating system or file system compression utilities, Data Pump compression is fully inline on the import side as well, so there is no need to uncompress a dump file before importing it. The compressed dump file sets are automatically decompressed during import without any additional steps by the Database Administrator.

In the following compression example from the Oracle sample database, the OE and SH schemas were exported while simultaneously compressing all data and metadata. The dump file size was reduced by 74.67%.

Three versions of the gzip (GNU zip) utility and one UNIX compress utility were used to compress the 6.0 MB dump file set. The reduction in dump file size was comparable to Data Pump compression. Note that the reduction in dump file size will vary based on data types and other factors.

Full Data Pump functionality is available using a compressed file. Any command that is used on a regular file will also work on a compressed file. Users have the following options to determine which parts of a dump file set should be compressed:

ALL enables compression for the entire export operation.

DATA-ONLY results in all data being written to the dump file in compressed format.

METADATA-ONLY results in all metadata being written to the dump file in compressed format. This is the default.

NONE disables compression for the entire export operation.

For more information about Oracle Data Pump, please visit http://www.oracle.com/technology/products/database/utilities/index.html

How to Enable Data Pump Compression

The example below shows how to enable compression for Data Pump:

expdp hr FULL=y DUMPFILE=dpump_dir:full.dmp COMPRESS;

Compression for Network Traffic

Data Guard provides the management, monitoring, and automation software infrastructure to create, maintain, and monitor one or more standby databases to protect enterprise data from failures, disasters, errors, and data corruptions. Data Guard maintains synchronization of primary and standby databases using redo data (the information required to recover a transaction). As transactions occur in the primary database, redo data is generated and written to the local redo log files. Data Guard Redo Transport Services are used to transfer this redo data to the standby site(s). With Advanced Compression, redo data may be transmitted in a compressed format to reduce network bandwidth consumption and in some cases reduce transmission time of redo data. As of Oracle Database 11g Release 2, redo data can be transmitted in a compressed format when the Oracle Data Guard configuration uses either synchronous redo transport (SYNC) or asynchronous redo transport (ASYNC).

For more information about Oracle Data Guard, please visit http://www.oracle.com/technology/deploy/availability/index.html

Advertisements