slide1 n.
Skip this Video
Download Presentation
Compression and Performance in Oracle Database 12 c

Loading in 2 Seconds...

play fullscreen
1 / 39

Compression and Performance in Oracle Database 12 c - PowerPoint PPT Presentation

  • Uploaded on

Compression and Performance in Oracle Database 12 c. Gregg Christman Senior Product Manager – ACO/HCC.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Compression and Performance in Oracle Database 12 c' - jerold

Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
compression and performance in oracle database 12 c
Compression and Performance in Oracle Database 12c

Gregg ChristmanSenior Product Manager – ACO/HCC


The preceding 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.

program agenda
Program Agenda
  • Data Growth Challenges
  • Basic Compression / Advanced Compression Option
  • Index Compression / Hybrid Columnar Compression
  • Customer Experience
  • Questions
data growth challenges
Data Growth Challenges
  • IT must support fast-growing amounts of data
    • Explosion in online access and content
    • Government data retention regulations
  • Performance often declines as data grows
  • Automate the compression and movement of data
    • Reduce storage costs
    • Improve performance
  • Need to manage data growth
    • Without hurting performance
    • Without growing costs
    • With minimal administrative intervention
basic compression
Basic 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

Customers indicate that typical compression ratio is 2x to 4x

Data is compressed at the database block level

Compression enabled at either the table or partition level

Completely transparent to applications


ACO Compression Capabilities

Flashback Data Archive

History table

  • Advanced Row Compression
    • Compress data partitions/tables
  • Advanced LOB CompressionAdvanced LOB Deduplication
      • Compress and deduplicate unstructured data
  • RMAN/DataPump Compression
      • Compress backups
  • Data Guard Redo Transport Compression
  • Advanced Network Compression
      • Compress network traffic
  • Flashback Data Archive History Table

Oracle Database Enterprise Edition

Data Partitioning



Compression Option





2x to 4xCompression Typical


advanced row compression
Advanced Row Compression
  • Partition/table/tablespace data compression
    • Support for conventional DML Operations (INSERT, UPDATE)
    • Customers indicate that 2x to 4x compression ratio’s typical
  • Significantly eliminates/reduces write overhead of DML’s
    • Batched compression minimizes impact on transaction performance
  • “Database-Aware” compression
    • Does not require data to be uncompressed – keeps data compressed in memory
    • Reads often see improved performance due to fewer I/Os and enhanced memory efficiency
real world compression results erp database 10 largest tables

Table Scan Performance

DML Performance

Less than

3% Overhead

2.5x Faster





Real World Compression Results- ERP Database 10 Largest Tables

Storage Utilization

3x Smaller


advanced lob compression deduplication
Advanced LOB Compression/Deduplication
  • LOBS typically experience a reduction of 2x to 3x times in size
      • Automatically avoids compressing data that would not benefit from compression
      • Useful for content management, email applications and data archival applications
  • No adverse impact on read operations
    • Often improves read performance for cache data
  • Enables storage of a single physical image for duplicate data
    • Significantly reduces space consumption
    • Dramatically improves writes and copy operations
datapump compression
DataPump Compression
  • Metadata compression since Oracle Database 10g
  • Compression for table data during exports
    • No need to decompress before import
  • 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 DataPump functionality available on compressed files
rman backup compression
RMAN Backup Compression
  • Dramatically reduce the storage requirements for backup data
    • Backup data is compressed before it is written to disk or tape and doesn’t need to be uncompressed before recovery
  • Three levels of RMAN Compression: LOW, MEDIUM, and HIGH
    • Compression Level LOW
          • Best suited when backup is constrained by CPU
    • Compression Level MEDIUM
    • Balance between CPU usage and compression ratio
    • Compression LEVEL HIGH
      • Best compression ratio and highest CPU utilization
      • Best suited when backup is constrained by network or I/O
data guard redo transport compression
Data Guard Redo Transport Compression
  • 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%
advanced network compression
Advanced Network Compression
  • Compresses network data to be transmitted at the sending side and then uncompressed at the receiver
    • Reduces the size of the session data unit (SDU) transmitted over a data connection
      • Reducing the size of data reduces the time required to transmit the SDU
      • On narrow bandwidth connections, with faster CPU, it can significantly improve performance
        • SQL query response becomes faster due to the reduced transmission time
flashback data archive history tables
Flashback Data Archive History Tables
  • Flashback Data Archive - FDA
    • Transparently tracks changes when they are made
    • Tracked in separate history tables
    • History tables are secure, tamper-proof
    • FDA History Table Optimization
      • Advanced Row Compression
      • Advanced LOB Compression and Deduplication
      • Compression Tiering
  • Use Cases
      • Governance / regulatory / compliance (GRC)
      • Substitute for app development
      • Error correction
      • Replacement for CDC and DW
aco benefits
ACO Benefits
  • Transparent: 100% Application Transparent
  • Smaller: Reduces Footprint
    • CapEx: Lowers server & storage costs for primary, standby, backup, test & dev databases …
    • OpEx: Lowers heating, cooling, floor space costs …
      • Additional ongoing savings over life of a database as database grows in size
  • Faster: Transactional, Analytics, DW
    • Greater speedup from in-memory: 3-10x more data fits in buffer cache & flash cache
    • Faster queries
    • Faster backup & restore speeds
  • End-to-end Cost / Performance Benefits across CPU, DRAM, Flash, Disk & Network
index key compression
Index Key Compression
  • Included with Oracle Database Enterprise Edition
  • Customers report 2x compression is typical
    • Compression ratio depends on how many columns are selected/compressibility of those columns
  • ANALYZE INDEX will give advice on whether / how many columns to choose
    • Index data is NOT decompressed when read from disk into memory
  • Does not require ACO or an ACO license – can compress just indexes and not data
why hybrid columnar compression
Why Hybrid Columnar Compression
  • Traditionally, data has been organized in a row format
  • Alternative approach: store data in a column format
    • Storing column data together dramatically increases compression
  • Exadata storage optimized to maximize query performance on HCC-compressed tables
          • Takes advantage of the processing power, memory and Infiniband network that are integral parts of the Exadata storage server
          • Hybrid Columnar Compression extended to Pillar Axiom and Sun ZFS Storage Appliance (ZFSSA) storage
about hybrid columnar compression
About Hybrid Columnar Compression
  • Hybrid Columnar Compressed Tables
    • Compressed tables can be modified using conventional DML operations
    • Useful for data that is bulk loaded and queried
  • How it Works
    • Tables are organized into Compression Units (CUs)
      • CUs are multiple database blocks
    • Within Compression Unit, data is organized by column instead of by row
      • Column organization brings similar values close together, enhancing compression

Compression Unit

Column 4

Column 1

Column 2

Column 5

Column 3

10x to 15x Reduction

compression units

Logical Compression Unit















Compression Units
  • Compression Unit
    • Logical structure spanning multiple database blocks
    • Data organized by column during data load
    • Each column compressed separately
    • All column data for a set of rows stored in compression unit
query and archive compression
Query and Archive Compression
  • Query Compression
  • 10x average storage savings
  • 10x reduction in Scan IO
  • Archive Compression
  • 15x average storage savings
    • Up to 70x on some data
  • For cold or historical data

Optimized for Space

Optimized for Speed on Exadata

Reclaim 93% of Disks

Keep Data Online

Smaller Warehouse

Faster Performance

query compression levels
Query Compression Levels
  • Query Compression provides two levels of compression
    • HIGH typically provides a 10x reduction in storage
    • LOW typically provides a 6x reduction
  • Optimized to increase scan query performance
  • Default is HIGH
    • Maximizes storage savings and query performance benefits
archive compression
Archive Compression
  • Archive Compression provides two levels of compression
    • HIGH typically provides a 15x reduction in storage
    • LOW typically provides a 10x reduction
  • Best approach for Information Lifecycle Management (ILM) and data archival
    • Minimum storage footprint
    • No need to move data to tape or less expensive disks
    • Data is always online and always accessible
      • Intended for tables or partitions that store data that is rarely accessed
      • Run queries against historical data (without recovering from tape)
      • Update historical data
      • Supports schema evolution (add/drop columns)
customer compression examples
Customer Compression Examples
  • Data Warehouse Customers (Warehouse Compression)
    • Top Financial Services 1: 11x
    • Top Financial Services 2: 24x
    • Top Financial Services 3: 18x
    • Top Telco 1: 8x
    • Top Telco 2: 14x
    • Top Telco 3: 6x
  • Scientific Data Customer (Archive Compression)
    • Top R&D customer (with PBs of data): 28x
  • OLTP Archive Customer (Archive Compression)
    • SAP R/3 Application, Top Global Retailer: 28x
    • Oracle E-Business Suite, Oracle Corp.: 23x
    • Custom Call Center Application, Top Telco: 15x

Compression and Performance with ACO and SAPMilt Simonds, Director Enterprise Platform Delivery

who we are
Who we are
  • Pharmaceutical wholesale distributor
  • Annual revenue of $80b.
  • Fortune 30 Company