1 / 39

Compression and Performance in Oracle Database 12 c

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

jerold
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. 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.

  3. Program Agenda • Data Growth Challenges • Basic Compression / Advanced Compression Option • Index Compression / Hybrid Columnar Compression • Customer Experience • Questions

  4. 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

  5. Compression Techniques

  6. 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

  7. Compression Techniques

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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%

  15. 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

  16. 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

  17. 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

  18. Compression Techniques

  19. 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

  20. Compression Techniques

  21. 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

  22. 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

  23. 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

  24. 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

  25. 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

  26. 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)

  27. 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

  28. Customer Experience

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

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

  31. Landscape Details

  32. A changing landscape

  33. Advanced Compression

  34. Recommendations

  35. Thank you!

  36. Questions?

More Related