1 / 33

In-Memory Database Processing with Oracle Database 12c

In-Memory Database Processing with Oracle Database 12c. Kevin Jernigan Senior Director Product Management System Technologies, Oracle.

matsu
Télécharger la présentation

In-Memory Database Processing with Oracle Database 12c

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. In-Memory Database Processing with Oracle Database 12c Kevin JerniganSenior Director Product Management System Technologies, Oracle

  2. The following 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. In-Memory Trends and Impact Memory is Faster, Cheaper and has more Capacity Today In-Memory Capacity Increased In-Memory Cost Decreased In-Memory Significantly Faster 2002 256 MB/DIMM 2002 $0.2/MB Disk 5ms response 2012 16 GB/DIMM 2012 $0.009/MB DRAM 100ns response DRAM: 64x More Capacity 25x Cheaper 50,000X Faster Flash 0.25ms response 2012 $0.0005/MB 2012 100 GB/DOM 400x Cheaper Flash: 20x Faster 400x More Capacity Faster OLTP & Faster Analytics, More Users, More Data

  4. Oracle TimesTen Technology Leader in IMDB Pure IMDB – all data must fit in memory Market Leader in IMDB 1000s of customers Oracle Database Technology Leader in Database & IMDB 30 years of Database innovations 10 years of IMDB innovations Hybrid on-disk, in-memory database Market Leader in Databases Over 300,000 customers Application Application Application Oracle In-Memory SQL Relational Databases Oracle TimesTen and Oracle Database In-Memory DB for the Middle Tier In-Memory DB for the Database Tier

  5. Engineered for Business Intelligence with Exalytics In-memory columnar compression Complex queries Optimized for OLTP Microsecond response time Fully transactional and highly available Oracle TimesTen In-Memory Database In-Memory Database for OLTP and Business Intelligence InfiniBand

  6. In-Memory Database Innovations • Scale-up & Scale-out • Scales across cores, cpu’s, and clusters • Caching in memory • Active data kept in memory • 1 socket bandwidth: 30GB/s • Compression • More data fits in memory • Higher effective • memory bandwidth • Column Store • Efficient Column-wise Processing

  7. OLTP Databases run with active data mostly in memory In-Memory Column Store for Data Warehousing In-Memory Parallel Query provides data distribution in memory of each cluster node In-Memory Functionality in Production Caching & Processing Data In Memory • Caching in memory • Active data kept in memory • One socket bandwidth: 30GB/s • In-Memory Result Set Cache on Client & Server • (Oracle 11g Release 1) • In-Memory Scale-out for OLTP: RAC, Cache Fusion • In-Memory Parallel Query • (Oracle 11g Release 1) • In-Memory Column Store for DW • (Oracle 11g Release 2) • Smart Memory Scans • (11g Release 2) Pre-2007 2007 2009 2010 2007

  8. Cost of Disk, Speed of DRAM All data stored on DISK Warm data cached in FLASH Hot data cached in DRAM Optimal Price-Performance Exadata Database In Memory Machine Oracle Exadata Optimizes I Memory, Flash, Disk D-RAM Flash Disk

  9. Multi-core and Multi-processor parallelism utilized by parallel query Database Optimizations for 20+ years 2x speedup with Parallel Memory Affinity, 2011 Scale-out with RAC Database Optimizations for 10+ years In-Memory Parallel Query provides parallel processing in memory across cluster, 2007 In-Memory Functionality in Production Scale-up & Scale-out • Scale-up & Scale-out • Multi-core parallelism • Pool resources across a cluster • Cache Fusion Enhancements • 20+ years of scale-up optimizations • In-Memory PQ Enhancements • (11g Release 2) • Parallel Memory Affinity • (11g Release 2) • In-Memory Parallel Query (11g Release 1) • 10+ years of scale-out optimizations for RAC Pre-2007 2009 2011 2007

  10. Cache #2 Cache #3 Cache #1 In-Memory PQ for High Performance Analytics Oracle Database 11g Release 1 Columnar & Compressed in memory • Cluster-wide parallel processing benefits greatly from memory • Data is read from disk into memory before processing • Each node owns a subset of the processed data • Cluster-wide memory is leveraged for a single parallel operation • NO I/O once the data in memory Table

  11. In-Memory Processing Oracle Database 11g Release 2 • Parallel Memory Affinity on Exadata • Higher Bandwidth for X2-8 Parallel Queries • Granule allocations and distribution • Parallel execution message buffer allocation • NUMA-aware Auto DOP calculation • Smart In-memory scan on Exadata • If in-memory access becomes CPU bound, dynamic partial offloading of processing to Exadata • Optimal utilization of system resources when using HCC compression • High data reduction through Exadata filtering and projection

  12. Index Compression since 1997 2x reduction in index size Table Compression since 1999 2x reduction in table size Basic, OLTP Compression in 2002-7 2x-4x reduction preserved through Updates Hybrid Columnar Compression, 2009 10x for Warehousing, 15x-50x for Archiving In-Memory Functionality in Production Compressing Data In Memory • Compression • More fits in memory • Higher effective • memory bandwidth • Bitmap Index Compression (Oracle 8) • Prefix Index Compression (Oracle 8i) • Table (IOT) Compression (Oracle 8i) • Basic Table Compression (Oracle 9i Release 2) • OLTP Table Compression (Oracle 11g Release 1) • Unstructured Data Compression (Oracle 11g Release 1) • Hybrid Columnar Compression (Oracle 11g Release 2) 1999 2002 2007 1997 2009 2010

  13. In-Memory Column Store, 2009 For Warehouse Hybrid Columnar Compression, reduces memory footprint by 10x Highly optimized processing over in-memory columnar data Complements Columnar in Storage on Flash Cache & Disk, 2009 In-Memory Functionality in Production In-Memory Column Store for DW • Column Store • Efficient Column-wise Processing • In-Memory Column Store for Warehousing • Columnar Flash Cache • (11g Release 2) • Columnar on Disk for DW • Columnar on Disk for Archiving • (11g Release 2) • In-Memory optimizations for Columnar Processing • (11g Release 2) 2010 2011 2009

  14. 12c In-Memory Database Roadmap • Caching • Exadata Write Back Flash Cache • Improved Replacement Algorithm for DW In-Memory Cache • In-Memory Global Temporary Tables • In-Memory Caching & processing for Unstructured Data • Column Store • Rowsets – memory optimized, set-at-a-time query processing • Extended to text, spatial, XML, 12.1 • Columnar for OLTP • Automatic Columnarization • Row-level locking for EHCC • Scale-up & Scale-out • In-Memory PQ optimizations • Cache Fusion Protocol enhancements • Exadata Exafusion • Compression • Query on-compressed optimizations • Automatic compression using Automatic Data Optimization • Wide table compression

  15. 12.1 In-Memory Global Temporary Tables Accelerate Analytic & Reporting apps by eliminating I/O • Global Temporary Tables (GTT) are frequently used in OLTP and DW • e.g. staging of intermediate results in reports • Starting 12.1, GTTs run purely in-memory • They do not generate I/O for redo, undo, data • Enables use of Global Temporary Tables on Active Data Guard • GTTs used by Oracle Apps and other OLTP Apps • Reduced WAN bandwidth to remote Standby • Faster Database Recovery • Less log data to restore and apply

  16. 12.1 In-memory improvements for Unstructured Data Processing Columnar & Exadata Smart Scans on unstructured data • Spatial & Text Data supported with HCC • Spatial customers tend to have large volumes of data • HCC gives high compression ratio for Spatial Data • e.g. Santos Oil & Gas, 52x in production • Text Indexes on LOBs supported on HCC tables • Unstructured Data supported for Exadata Smart Scans • Filters on small CLOBs (< 4KB) pushed down to Exadata Cells • Like expressions, e.g. like(html, ‘Exadata’) • Pattern Matching, e.g. regexp_like(html, ‘.*Exadata V[0-9]*.*’)

  17. 12.1 In-memory improvements for Unstructured Data Processing Increased in-memory processing, parallelism • Fast String operations on LOBs • concat, substr, etc, average 2.6x speedup • Faster Temporary LOBs, by more than 10x for common operations like append • Parallel DML on LOBs for non-partitioned tables • Faster Spatial and Graph queries from statistics and cost based optimizations • Faster XML operations, Parallel Query for XML generation operators • Faster Queues, Faster R statistical analysis

  18. 12.1 In-Memory Queue based Fast Auditing Unified Audit Trail and Unified Audit Configuration • Use of in-memory queues to avoid overhead on user transaction enables auditing to be enabled on all apps • Audit queues are persisted using SecureFile store • Efficient management and Cleanup of audit trail • Partitioned audit trail for faster cleanup • Size based partitions to store audit trail data • Conditional audit support for selective auditing

  19. In-Memory optimizations for Pluggable Databases 6x less resource, 5x more scalable OLTP benchmark comparison Only 3GB of memory vs 20GB memory used for 50 databases Pluggable databases scaled to over 100 while separate database instances maxed at 50 Memory Utilized Database Instances

  20. 12.1 Advanced Row Compression • Queries on Compressed Data • In-memory Scans • Data is never expanded into uncompressed form in memory • In 12.1, up to 3x faster In-memory Scans for Complex predicates on Columns with small number of distinct values select … from Customers where name like ‘%C%Bank’

  21. Compressed Column Store for fast analytics Row Store for fast OLTP 12.1 Automatic Data Optimization • As data ages: • Activity declines • Volume grows • Older data primarily • for reporting Compliance & Reporting Reporting OLTP 10x compressed 15x compressed alter table … add policy … compress for query after 3 months of no modification … compress for archive after 1 year … This Quarter Prior Years This Year Archive Compressed Column Store for max compression As data cools down, Advanced Data Optimization automatically converts data to columnar compressed Online

  22. Columnar for OLTP Exadata 12c, Automatic Columnarization This Quarter This Year • Automatic Columnarization • As partitions age, automatically move to HCC compressed format • Enables HCC for OLTP data As Data Cools On update ALTER TABLE sales ILM ADD CompressionPolicy Column Store Compress for Query AFTER 120 days from creation;

  23. Columnar for OLTP Exadata 12c, Row Level Locking for Column Store • Row Stores have traditionally been the choice for OLTP • Column Stores provide higher compression ratio & better Analytic Performance • HCC’s innovative hybrid approach enables single I/O for row access • HCC Row Level Locking innovation enables concurrent single row updates • Scales linearly with number of users • Row stores still best for OLTP data

  24. 12.1 In-Memory Parallel Execution • Database release 12.1 introduces high performance parallel query processing on memory cached data • Queries run from tables in database buffer cache • Harnesses memory capacity of entire database cluster for queries • An affinity algorithm places fragments of a object (partitions) in memory on different RAC nodes • Data is kept compressed in memory • Memory has 100x the bandwidth of disk

  25. Exafusion InfiniBand Protocol for OLTP Exadata 12c • 12c Exafusion focuses on latency and CPU usage • Improves RAC scale-out performance on OLTP • Optimizes lock and remote block RAC Cache Fusion requests • User mode InfiniBand verbs directly access InfiniBand hardware • 2x latency improvement

  26. Oracle In-Memory Database Best In-Memory Database for the Enterprise Combines DRAM, Flash and Disk Speed of In-Memory & Cost of Disk With no limit on database size • 12c Release 1 • Big Memory Cluster • & 2x more index compression on Exadata • Rowset Processing • In-Memory Column Store for Unstructured Data • OLTP Wide Table Compression • HCC Row Level Locking • In-Memory Column Store for DW • Columnar Compression • Columnar Processing • Cache Fusion Optimizations • In-Memory Parallel Query Optimizations • In-Memory Storage Index on Exadata Storage • In-Memory Parallel Query • OLTP Compression • Unstructured Data Compression • Client SQL/PL/SQL Result Cache • Server SQL/PL/SQL Result Cache • 20+ years of scale-up optimizations • 10+ years of scale-out optimizations • Prefix Index Compression • Bitmap Index Compression • Basic & IOT Table Compression • And much more Oracle Runs Every OLTP App Unchanged In Memory 2009 2012 Pre-2007 2007 2008

  27. HA Features Key for OLTP Much more than in-memory needed for an Enterprise Database • Human Error Recovery • Granular Row & Table level Flashback • DB level Flashback • Recyclebin to recover from drop • Transaction backout • Online Table Redefinition & Rebuild • No read locks, no write locks, not even row level • Can be done at table, index or partition level • Online Patching & Online DB Upgrades • Online Application upgrades using Editioning • Distributed Transaction & Distributed Recovery • No Data Redistribution needed on node add/removal • …100s more Enterprise HA features • Fast-Start Recovery • Start database without waiting for Undo to be applied • Parallel & Background Recovery • All data immediately queryable, no wait for load • Data Guard • Zero Data Loss • Fast Failover to Standby • Compressed Redo Transport • Automatic detection and repair of corrupted blocks on primary • Incremental Backup • Intra-file parallel backup & Restore • Block level Media Recovery

  28. And Other Major Functional Areas Key for OLTP Full Oracle Database Functionality • Performance • Manageability • Security • Advanced Queues • Distributed Transactions • Replication • Optimizer • SQL features • SQL Pre-compiled Shared Cursors • Stored Procedure Language • Triggers • Constraints • …

  29. Oracle’s Vision for In-Memory Industry-leading OLTP & Industry-leading In-Memory DB Every one of the thousands of features of the Oracle Database work with Oracle In Memory Distributed & Clustering: Distributed Transaction & XA Compliant, Distributed Query, Distributed Multi-versioning Read Consistency, Cluster-wide Parallel Transactions & Read Consistency (with Global Lamport Clock), … High Availability: Recovery time independent of size of unrecovered transaction, Online DDLs: add column, partition move, index rebuild, …, No limit on size of a transaction size or limit on rollback, … Bi-Temporal Database, Provenance, Flashback Transaction Query, … and 1000s of more features needed for Enterprise Applications runs all Packaged Applications, Oracle & others

  30. Oracle Database In-Memory Industry Leading In-Memory Database • Industry’s most comprehensive set of Innovations for In-Memory • Caching, Scaling, Compression & Columnar • In-Memory complements 1000s of Oracle’s Enterprise features • 12c a major release focused on in-memory innovations • Combines DRAM & Flash Memory & Disk for best price/performance • Oracle In-Memory runs every Oracle or 3d party app unchanged

More Related