Home > Oracle Advanced Compression

Oracle Advanced Compression


Oracle Advanced Compression: 
Reduce Storage, Reduce Costs, Increase Performance 

Bill Hodak

Principal Product Manager


2  

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. 
The development, release, and timing of any features or functionality described for Oracle��s products remains at the sole discretion of Oracle.


3  

Agenda 

  • Data Growth Challenges
  • Advanced Compression Feature Overview
    • Relational Data Compression
    • Unstructured Data Compression
    • Backup Data Compression
    • Network Transport Data Compression
  • Questions and Answers

4  

Challenges 

  • Explosion in Data Volumes
    • Government Regulations (Sarbanes-Oxley, etc)
    • User Generated Content (Web 2.0)
    • Application Consolidation
  • IT Managers Must Support Larger Volumes of Data with Limited Technology Budgets
    • Need to optimize storage consumption
    • Also maintain acceptable application performance
  • Intelligent and Efficient Compression Technology can Help Address These Challenges

5  

Oracle Advanced Compression Option 

  • Reduces resource requirements and costs
    • Storage System
    • Network Bandwidth
    • Memory Usage
 

Relational Data Compression 

Unstructured Data Compression 

Backup Data Compression 

Network Data Compression 

  • OLTP Table Compression
 
  • SecureFiles Deduplication
  • SecureFiles  Compression
 
  • Data Pump Data Compression
  • RMAN Fast Backup Compression
 
  • Data Guard Redo Transport Compression

6  

Oracle Advanced Compression Option 

  • Reduces resource requirements and costs
    • Storage System
    • Network Bandwidth
    • Memory Usage
 

Relational Data Compression 

Unstructured Data Compression 

Backup Data Compression 

Network Data Compression 

  • OLTP Table Compression
 
  • SecureFiles Deduplication
  • SecureFiles  Compression
 
  • Data Pump Data Compression
  • RMAN Fast Backup Compression
 
  • Data Guard Redo Transport Compression

7  

Table Compression 

  • Introduced in Oracle Database 9i Release 2
    • Compression during bulk load operations (Direct Load, CTAS)
    • Data modified using conventional DML not compressed
  • Optimized compression algorithm for relational data
  • Improved performance for queries accessing large amounts of data
    • Fewer I/Os
    • Buffer Cache efficiency
  • Data is compressed at the database block level
  • Compression enabled at either the table or partition level
  • Completely transparent to applications

8  

OLTP Table Compression 

  • Oracle Database 11g extends table compression for OLTP data
    • Support for conventional DML Operations (INSERT, UPDATE)
  • New algorithm significantly reduces write overhead
    • Batched compression minimizes impact for OLTP transactions
  • No impact on reads
    • Reads may actually see improved performance due to fewer I/Os and enhanced memory efficiency

 


9  

OLTP Table Compression Process 

Initially Uncompressed Block 

Compressed Block 

Partially Compressed Block 

Compressed Block 

Empty

Block 

Legend

       
 

Header Data 

Free Space 

Uncompressed Data 

Compressed Data


10  

Block-Level Batch Compression 

  • Patent pending algorithm minimizes performance overhead and maximizes compression
  • Individual INSERTs and UPDATEs do not cause recompression
  • Compression cost is amortized over several DML operations
  • Block-level (Local) compression keeps up with frequent data changes in OLTP environments
    • Competitors use static, fixed size dictionary table thereby compromising compression benefits

11  

OLTP Table Compression 

Smith 

John 


Doe 

Jane 


Doe 

Jane 


Doe 

John 


LAST_NAME 

FIRST_NAME 

ID 

Employee Table 

Initially Uncompressed Block 

INSERT INTO EMPLOYEE

    VALUES (5, ��Jack��, ��Smith��);

COMMIT; 

1•John•Doe 2•Jane• Doe 3•John•Smith  4• Jane • Doe

Free Space 

Header

 


12  

OLTP Table Compression 

Block 

John=|Doe=|Jane=|Smith= 

Header 

Doe 

Jane 


Smith 

John 


Smith 

Jack 


Doe 

Jane 


Doe 

John 


LAST_NAME 

FIRST_NAME 

ID 

Employee Table 

1•John•Doe 2•Jane• Doe 3•John•Smith  4• Jane • Doe

Free Space 

1••  2•• 3•• 4 •  •   5•Jack•

Free Space 

Compressed Block 

Local

Symbol Table


13  

Table Compression Syntax 

OLTP Table Compression Syntax:

    CREATE TABLE emp (

        emp_id NUMBER

    , first_name VARCHAR2(128)

    , last_name VARCHAR2(128)

    ) COMPRESS FOR OLTP; 

Direct Load Compression Syntax (default):

    CREATE TABLE emp (

        emp_id NUMBER

    , first_name VARCHAR2(128)

    , last_name VARCHAR2(128)

    ) COMPRESS [BASIC]; 

 


14  

Table Compression Advisor 
Estimate Potential Storage Savings 

  • Available in 11g Release 2
  • Available on OTN *
    • Supports Oracle Database 9i Release 2 through 11g Release 1
    • Shows projected compression ratio for uncompressed tables
    • Reports actual compression ratio for compressed tables (11g Only)
 

  

* http://www.oracle.com/technology/products/database/compression/compression-advisor.html


15  

Monitoring Table Compression 

  • View: DBA_TABLES, columns:
    • COMPRESSION ( ENABLED / DISABLED )
    • COMPRESS_FOR ( FOR ALL OPERATIONS / DIRECT LOAD ONLY )

 


16  

<Insert Picture Here> 

Table Compression Results


17  

Oracle��s Internal E-Business Suite DB 

    • Overall database storage savings:   3x
      • Table compression           4x
      • Index compression           2x
      • LOB compression           2.3x
    • 95 TB of Total Storage Savings!
      • Primary, standby, test, dev, and backup
  • Payroll, Order-2-Cash, AP/AR batch flows, Self-Service flows run without regression, Queries involving full table scans show speedup

18  

Oracle��s Internal Beehive Email DB 

  • Average Compression Ratio:  2x
  • Oracle Database 11g Release 1
  • Exadata Storage Servers
  • Storage savings add up with standby, mirroring, flash recovery area
    • Phase I in production
      • Email for 28K employees
      • 195 TB of storage savings with SecureFiles Compression
    • Phase II, Dec 2009
      • Migrate all 90K employees on this email server
      • 581TB estimated storage savings with SecureFiles Compression
  • Performance improved by caching more data due to compression - reducing I/O latencies

19  

SAP R/3, BW, Leading Global Company 

  • Compression on SAP databases at leading global company
    • Oracle Database 11g Release 2
    • SAP R/3 DB
      • 4.67TB Uncompressed
      • 1.93 TB Compressed
        • 2.4x compression ratio
    • SAP BW DB
      • 1.38 TB Uncompressed
      • .53 TB Compressed
        • 2.6x compression ratio
  • Leverage 11g compression for Tables, Indexes and LOB data

20  

Real Networks 

  • Rhapsody Digital Music Subscription Service 
  • Compression results using 11g Release 1
    • Average Compression Ratio:           2.8x
    • Highest compression ratio:  8x
    • Total Savings:   3.5TB

21  

CERN 

  • Oracle Database 11g Release 2
  • Average Compression Ratio:  2x 
  • Highest Compression Ratio  6x
  • Scientific application supporting particle research 

22  

Oracle Advanced Compression Option 

  • Reduces resource requirements and costs
    • Storage System
    • Network Bandwidth
    • Memory Usage
 

Relational Data Compression 

Unstructured Data Compression 

Backup Data Compression 

Network Data Compression 

  • OLTP Table Compression
 
  • SecureFiles Deduplication
  • SecureFiles  Compression
 
  • Data Pump Data Compression
  • RMAN Fast Backup Compression
 
  • Data Guard Redo Transport Compression

23  
 
 
 

Introduction to SecureFiles 

  • Next-generation high performance LOB
    • Superset of LOB interfaces allows easy migration from LOBs
    • Transparent deduplication, compression, and encryption
    • Leverage the security, reliability, and scalability of database
  • Enables consolidation of file data with associated relational data
    • Single security model
    • Single view of data
    • Single management of data
    • Scalable to any level using SMP scale-up or grid scale-out

24  
 
 
 

SecureFiles Deduplication 

Secure Hash 

  • Enables storage of a single physical image for duplicate data
  • Significantly reduces space consumption
  • Dramatically improves writes and copy operations
  • No adverse impact on read operations
    • May actually improve read performance for cache data
  • Duplicate detection happens within a table, partition or sub-partition
  • Specially useful for content management, email applications and data archival applications

25  
 
 
 

SecureFiles Compression 

  • Significant storage savings for unstructured data
    • Three levels of compression (LOW/[MEDIUM]/ HIGH ) provide desired ratios
    • 2-3x compression for typical files (combination of doc, pdf, xml)
  • Compression Level LOW (NEW in 11.2)
    • Compression algorithm optimized for high performance
      • 3x less CPU utilization than default SecureFiles Compression
      • Maintains 80% compression of default SecureFiles Compression
  • Allows for random reads and writes to Compressed SecureFile data 
  • Can be specified at a partition level 
  • Automatically detects if SecureFile data is compressible 
  • Independent of table or index compression 

26  
 
 
 

SecureFiles Compression Syntax 

Compression Syntax

    CREATE TABLE t1 (a CLOB)

    LOB(a) STORE AS SECUREFILE (

    COMPRESS

    CACHE

    ); 

Deduplication Syntax

    CREATE TABLE t1 (a CLOB)

    LOB(a) STORE AS SECUREFILE (

    DEDUPLICATE

    CACHE

    );

27  

Oracle Advanced Compression Option 

  • Reduces resource requirements and costs
    • Storage System
    • Network Bandwidth
    • Memory Usage
 

Relational Data Compression 

Unstructured Data Compression 

Backup Data Compression 

Network Data Compression 

  • OLTP Table Compression
 
  • SecureFiles Deduplication
  • SecureFiles  Compression
 
  • Data Pump Data Compression
  • RMAN Fast Backup Compression
 
  • Data Guard Redo Transport Compression

28  

Data Pump Compression 

  • Metadata compression available since Oracle Database 10g
  • Oracle Database 11g extends compression to table data during exports
    • No need to decompress before import
    • COMPRESSION={ALL | DATA_ONLY | [METADATA_ONLY] | NONE}
  • Single step compression of both data and metadata
    • Compressed data directly hits disk resulting in reduced disk space requirements
    • Internal tests reduced dump file size up to 75%
  • Application transparent
    • Complete Data Pump functionality available on compressed files

 


29  

Advanced Compression 
New in Oracle Database 11g Release 2 

  • RMAN Backup Compression
    • Compression Level LOW (New in 11.2)
      • Fastest compression algorithm
      • Best suited when backup is constrained by CPU
    • Compression Level MEDIUM (New in 11.1)
      • Balance between CPU usage and compression ratio
      • Formerly Fast RMAN Backup Compression
        • 11.1 syntax supported in 11.2
    • Compression LEVEL HIGH (New in 11.2)
      • Best compression ratio and highest CPU utilization
      • Best suited when backup is constrained by network or I/O

30  

Backup Compression Syntax 

     Data Pump Syntax

    PROMPT> expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_comp.dmp COMPRESSION=ALL 

    FAST RMAN Backup Compression Configuration

    RMAN> configure compression algorithm 'MEDIUM'; 

      RMAN Backup Compression Syntax

    RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG; 

 


31  

<Insert Picture Here> 

RMAN Compression Results


32  

RMAN Compression Overview 

  • Data from Oracle��s implementation of Oracle Applications
    • 3.5 GB Database
    • Oracle Enterprise Linux
    • Oracle Database 11g Release 1
  • Test 1:  Slow I/O (16 MB/s)
    • 11g RMAN without Compression
    • 10g RMAN with Compression
    • 11g RMAN with MEDIUM Compression
  • Test 2:  Fast I/O (200 MB/s)
    • 11g RMAN without Compression
    • 10g RMAN with Compression
    • 11g RMAN with MEDIUM Compression

33  

Backup Compression Results 

Backup Size Comparison 

GB 

 Compression reduced backup

 size by 6x


34  

Backup Compression 

Backup Speed Comparison

Slow I/O (Tape) 

Time (Seconds) 

 11g Compression

 is almost 2.5x

 faster than 10g

 Compression


35  

Backup Compression 

Backup Speed Comparison

Fast I/O (Disk) 

Time (Seconds) 

 11g Compression

 is almost 2.5x

 faster than 10g

 Compression


36  

Oracle Advanced Compression Option 

  • Reduces resource requirements and costs
    • Storage System
    • Network Bandwidth
    • Memory Usage
 

Relational Data Compression 

Unstructured Data Compression 

Backup Data Compression 

Network Data Compression 

  • OLTP Table Compression
 
  • SecureFiles Deduplication
  • SecureFiles  Compression
 
  • Data Pump Data Compression
  • RMAN Fast Backup Compression
 
  • Data Guard Redo Transport Compression

37  

Network Compression 
Data Guard Redo Transport Services 

  • Compress network traffic between primary and standby databases
  • Lower bandwidth networks (<100Mbps)
    • 15-35% less time required to transmit 1 GB of data
    • Bandwidth consumption reduced up to 35%
  • High bandwidth networks (>100 Mbps)
    • Compression will not reduce transmission time
    • But will reduce bandwidth consumption up to 35%
  • Syntax:

LOG_ARCHIVE_DEST_3='SERVICE=denver SYNC COMPRESSION=ENABLE|[DISABLE]'

  • Ref. MetaLink 729551.1 ��Redo Transport Compression in a Data Guard Environment��

38  

Redo Transport Compression 

  • More efficient bandwidth utilization, up to 5x compression ratio
  • Compression did not impact throughput or response time
 

Validation performed by CTC in collaboration with Oracle Japan Grid Center 

2X Compression ! 

5X Compression ! 

http://www.ctc-g.co.jp/en/


39  

Summary 
 
 
 

  • Comprehensive data compression capabilities for all types of data
    • Structured, Unstructured, Backup, Network
  • Reduces storage consumption by 2 to 4 times
  • Improves read performance
  • Enhances memory, buffer cache utilization
  • Complete application transparency
  • Benefits diverse application workloads

40  

Open World Sessions 
 
 
 
 

Oracle��s Hybrid Columnar Compression:  The Next-Generation Compression Technology [ S311358 ]

Tuesday 13:00 | Moscone South | Room 102 

Oracle Advanced Compression in Oracle Database 11g Release 2: Value/Performance [S307442 ]

          Thursday 09:00 | Moscone South | Room 102 

Oracle Advanced Compression:  Stories from the Most Trusted Source – Customers! [ S311359 ]

Thursday 12:00 | Moscone South | Room 104 

The Oracle Advanced Compression Experience – Hands on Lab [S311813]

Thursday 15:00 | Marriott Hotel | Golden Gate B2


41  

For More Information 

search.oracle.com 

advanced compression 

Or

OTN: http://www.oracle.com/technology/products/database/compression/index.html


42


43  

 


44  

Real World Table Compression Tests 

  • Data from Oracle��s implementation of Oracle Applications
    • Compressed the 10 Largest Tables
      • Two copies of each table: Compressed and Uncompressed
    • Oracle Enterprise Linux
    • Oracle Database 11g Release 1
  • Test Queries
    • Full Table Scan
    • Index Range Scan
    • DML Operations (Insert, Update, Delete)

45  

Table Compression Test Results 

Storage Utilization 

MB 

 More than 70% Storage Savings


46  

Table Compression Test Results 

Table Scan Performance 

Time

(seconds) 

 Compression is 2.5x Faster


47  

Table Compression Test Results 

Index Range Scan Performance 

Time

(seconds) 

 No Performance Impact on

 Index Scans


48  

Table Compression Test Results 

DML Performance 

Time (seconds) 

 Compression has less than 3% overhead 

Note: DML Workload comprises equal number of INSERT, UPDATE and DELETE statements. Performance overhead may be higher for INSERT only or UPDATE only workload


49  

<Insert Picture Here> 

Competitive Analysis


50  

Competitive Analysis – DB2 

- More compression overhead

- Transactional compression 

- Minimal compression overhead

- Batched compression 

- None 

Unstructured data compression and de-duplication (SecureFiles) 

Some decompression overhead 

Zero decompression overhead  

Table/partition level compression dictionary 

Block level compression 

- New data may not be compressed / offline rebuild symbol table

- Max 4K values per table 

- Adaptive / dynamic compression

- Unlimited compression values 

IBM DB2 

Oracle


51  

Competitive Analysis – DB2 

  • Even without compression, Oracle needs less disk space than DB2:
      • Oracle uses variable length representation for numbers – DB2 uses fixed length 
      • DB2 has more overhead for VARCHAR data 
  • SAP BW data needs 30-50% less disk space if stored in Oracle 

52  

Competitive Analysis – Teradata 

None 

Unstructured data compression and de-duplication (SecureFiles) 

- More compression overhead

- Transactional compression 

- Minimal compression overhead

- Batched compression 

None 

Backup compression 

Limited data type support 

All data types supported  

- Admin must define compression values

- Max 255 compression values 

- Dynamic compression values

- Unlimited values 

Field level compression 

Block level compression  

Teradata 

Oracle


53  

Competitive Analysis –  
Hardware Based Compression
 

Benefits limited to specific hardware environment 

Benefits automatically cascade to all environments 

No memory benefits - data needs to be decompressed 

Improved memory efficiency - data stays compressed in memory 

Data updates are very expensive 

Batched algorithm for minimal compression overhead 

Poor read performance – significant decompression overhead 

Improved read performance in several cases – no decompression overhead  

No fine grained compression -compress at file level 

Fine grained compression – compress at tablespace, table or partition level 

Generic compression algorithm -deficient for databases  

Database specific compression algorithm  

Hardware Compression 

Oracle


54  

Competitive Analysis 


1000 

2000 

3000 

4000 

5000 

6000 

Size, MB 

Oracle No 

Compress 

Competition No 

Compress 

Oracle 

Compress 

Competition 

Compress 

Compression: Oracle vs Competition  

Top 10 Tables in an ERP Application


Search more related documents:Oracle Advanced Compression
Download Document:Oracle Advanced Compression

Recent Documents:

Set Home | Add to Favorites

All Rights Reserved Powered by Free Document Search and Download

Copyright © 2011
This site does not host pdf,doc,ppt,xls,rtf,txt files all document are the property of their respective owners. complaint#nuokui.com
TOP