Compression and performance in oracle database 12 c
This presentation is the property of its rightful owner.
Sponsored Links
1 / 39

Compression and Performance in Oracle Database 12 c PowerPoint PPT Presentation


  • 36 Views
  • Uploaded on
  • Presentation posted in: General

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

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

    Option

    Advanced

    Compression Option

    Active

    Historical

    Less

    Active

    2x to 4xCompression Typical

    9


    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

    Time

    (seconds)

    Time

    (seconds)

    Real World Compression Results- ERP Database 10 Largest Tables

    Storage Utilization

    3x Smaller

    MB


    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

    BLOCK HEADER

    BLOCK HEADER

    BLOCK HEADER

    BLOCK HEADER

    CU HEADER

    C3

    C7

    C5

    C8

    C1

    C4

    C2

    C6

    C8

    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


    Recommendations

    Recommendations


    Compression and performance in oracle database 12 c

    Thank you!


    Questions

    Questions?


  • Login