Insert Picture Here Insert Picture Here Oracle Advanced
- Slides: 24
<Insert Picture Here>
<Insert Picture Here> Oracle Advanced Compression: Deep Dive into OLTP Table Compression Gregg Christman -- Senior Product Manager Vineet Marwah – Principal Member of Technical Staff
Program Agenda • Data Growth Challenges <Insert Picture Here> • OLTP Table Compression • Customer Experience • Thomson Reuters • Questions and Answers 3
Data Growth 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 4
Oracle Advanced Compression Option • Secure. Files Deduplication • Secure. Files Compression • OLTP Table Compression Relational Data Compression Unstructured Data Compression • Data Pump Data Compression • RMAN Fast Backup Compression • Data Guard Redo Transport Compression Backup Data Compression Network Data Compression • Reduces resource requirements and costs – Storage System – Network Bandwidth – Memory Usage 5
Table Compression • Introduced in Oracle Database 9 i 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 6
OLTP Table Compression • Oracle Database 11 g 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 7
OLTP Table Compression Process Empty Block Initially Partially Compressed Uncompressed Compressed Block Legend Header Data Uncompressed Data Free Space Compressed Data 8
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 9
OLTP Table Compression Employee Table ID FIRST_NAME LAST_NAME 1 John Doe 2 Jane Doe 3 John Smith 4 Jane Doe Initially Uncompressed Block Header 1 • John • Doe 2 • Jane • Doe 3 • John • Smith 4 • Jane • Doe Free Space INSERT INTO EMPLOYEE VALUES (5, ‘Jack’, ‘Smith’); COMMIT; 10
OLTP Table Compression Employee Table ID FIRST_NAME LAST_NAME 1 John Doe 2 Jane Doe 3 John Smith 4 Jane Doe 5 Jack Smith Compressed Block Header John= |Doe= |Jane= |Smith= 1 • • 2 • Jane • 3 • • 4 • • 1 • John • Doe 5 • Jack • Doe 3 • John • Smith 4 • Jane • Doe Free Space Local Symbol Table 11
Table Compression Syntax OLTP Table Compression Syntax: CREATE TABLE emp ( emp_id NUMBER , first_name VARCHAR 2(128) , last_name VARCHAR 2(128) ) COMPRESS FOR OLTP; Direct Load Compression Syntax (default): CREATE TABLE emp ( emp_id NUMBER , first_name VARCHAR 2(128) , last_name VARCHAR 2(128) ) COMPRESS [BASIC]; 12
Table Compression Advisor Estimate Potential Storage Savings • Available in 11 g Release 2 • Available on OTN * – Supports Oracle Database 9 i Release 2 through 11 g Release 1 – Shows projected compression ratio for uncompressed tables – Reports actual compression ratio for compressed tables (11 g Only) * http: //www. oracle. com/technology/products/database/compression-advisor. html 13
Monitoring Table Compression • View: DBA_TABLES, columns: – COMPRESSION ( ENABLED / DISABLED ) – COMPRESS_FOR ( OLTP / BASIC) 14
<Insert Picture Here> Table Compression Results 15
Oracle IT, Oracle’s Internal E-Business Application DB • Oracle’s Internal E-Business Suite Production System deployed ACO in 2009 – 4 -node Sun E 25 K RAC, 11 g. R 1 – Average overall storage savings 3 x • Table compression 4 x • Index compression 2 x • LOB compression 2. 3 x – 231 TB of realized storage savings primary, standby and test systems • Additional benefits were also accrued in Dev clones and Backups • Payroll, Order-2 -Cash, AP/AR batch flows, Self-Service flows run without regression, Queries involving full table scans show speedup © 2009 Oracle Corporation - Confidential 16 16
Oracle IT, Oracle’s Internal Beehive Email DB • Production system on 11 g. R 1 & Exadata for Primary and Standby – – Using Exadata Storage Servers for storage Average Compression Ratio: 2 x Storage savings add up with standby, mirroring, flash recovery area Compression went production in 2009 • Consolidate 90 K employees on this email server, more being migrated – Savings As of April 2010 • Beehive Saved 365 TB of storage using Advanced Compression – Incrementally saves 2. 6 TB/day based on db size growth • Savings higher with Sun user migration • Compression also helped improve performance by caching only compressed emails in memory and reducing I/O latencies © 2009 Oracle Corporation - Confidential 17 17
SAP R/3, BW, Leading Global Company • Compression on SAP databases at Leading global company – Tests done using Oracle database 11 g. R 2 – SAP R/3 DB • 4. 67 TB Uncompressed • 1. 93 TB Compressed – 2. 4 x savings factor – SAP BW DB • 1. 38 TB Uncompressed • . 53 TB Compressed – 2. 6 x savings factor • Leverage 11 g Compression for Tables, Indexes and LOB data © 2009 Oracle Corporation - Confidential 18 18
Metalink • See Metalink Note #1061366. 1 – Lists recommended Advanced Compression patches applicable to all customers 19
Customer Experience 20
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 21
Oracle Products Available Online Oracle Store Buy Oracle license and support online today at oracle. com/store 22
23
24
- Insert picture here
- Processing page
- How to insert an image in oracle apex application
- Oracle database analytics
- Oracle advanced security
- Oracle advanced cost accounting
- Advanced queing
- Reduce storage oracle
- Oracle advanced replication
- Insert logo here
- Insert logo here
- Insert pic here
- Insert photo here
- Voice brand name
- Insert your text here
- Please insert here meme
- Insert your text here
- Type name here
- Please insert here
- Please insert here
- Add your title here
- Insert logo here
- Add your text here
- Insert logo here
- Insert logo here