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

Compression and performance in oracle database 12 c
1 / 39

  • Uploaded on
  • Presentation posted in: General

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

Compression and Performance in Oracle Database 12 c

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

Compression and performance in oracle database 12 c

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

Compression techniques

Compression Techniques

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

Compression techniques1

Compression Techniques

Compression and performance in oracle database 12 c

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

    Compression techniques2

    Compression Techniques

    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

    Compression techniques3

    Compression Techniques

    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

    Customer experience

    Customer Experience

    Compression and performance in oracle database 12 c

    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

    Landscape details

    Landscape Details

    A changing landscape

    A changing landscape

    Advanced compression

    Advanced Compression



    Compression and performance in oracle database 12 c

    Thank you!



  • Login