Transform Batch to Real Time with the Oracle Database In-Memory 12c Option on Oracle Systems


Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Transform Batch to Real Time with the Oracle Database In-Memory 12c Option on Oracle Systems Vince Carbone Hui Dong David Lutz Oracle Performance Engineering October 1, 2014 Oracle Confidential – Internal/Restricted/Highly Restricted Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Safe Harbor Statement 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. Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Program Agenda Configuring the In-Memory Column Store Populating the In-Memory Column Store Managing the In-Memory Column Store on RAC Querying the In-Memory Column Store 1 2 3 4 Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Program Agenda 1 2 3 4 Configuring the In-Memory Column Store Populating the In-Memory Column Store Managing the In-Memory Column Store on RAC Querying the In-Memory Column Store Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Memory Bandwidth Enables Batch to Real-Time DISK PCI FLASH DRAM 12 GB/sec ~1280 GB/sec 58 GB/sec • In-memory delivers • Over 100x more throughput than disk • Over 14x more throughput than flash • What was batch can now be run real time Oracle SuperCluster T5-8 (Full Rack) Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Breakthrough: Dual Format Database • BOTH row and column formats for same table • Simultaneously active and transactionally consistent • Analytics & reporting use new in-memory Column format • OLTP uses proven row format Memory Memory SALES SALES Row Format Column Format Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | SPARC Systems For Maximum In-Memory Performance Large Memory/High Memory Bandwidth • IM performs best when complete working set is in memory • Scale-up on large SMPs • Scale-out on SuperCluster T4-1 512GB T5-2 1TB T5-4 2TB T5-8 4TB M6-32 32TB SuperCluster T5-8 and M6-32 Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | System Global Area SGA Buffer Cache Shared Pool Redo Buffer Large Pool Other shared Memory Components In-Memory Area Configuring : In-Memory Column Store Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Configuring : In-Memory Column Store • Controlled by INMEMORY_SIZE parameter •Minimum size of 100MB • Must be large enough to accommodate both column store and meta-data area • SGA_TARGET must be large enough to accommodate • Implemented using Optimized Shared Memory (OSM) SELECT * FROM V$SGA; NAME VALUE ------------------ --------- Fixed Size 2927176 Variable Size 570426808 Database Buffers 4634022912 Redo Buffers 13848576 In-Memory Area 1024483648 Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Configuring : In-Memory Column Store Oracle Multitenant with Pluggable Databases 11 ERP IMPDB1 IMPDB2 Container Database INMEMORY_SIZE=0G INMEMORY_SIZE=400G INMEMORY_SIZE=1600G INMEMORY_SIZE=2000G Example: SQL> alter session set container = IMPDB1; SQL> alter system set inmemory_size = 400g; SQL> alter session set container = IMPDB2; SQL> alter system set inmemory_size = 1600g; Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | 12 ERP IMPDB1 IMPDB2 Container Database INMEMORY_SIZE=0G INMEMORY_SIZE=1000G INMEMORY_SIZE=1600G INMEMORY_SIZE=2000G Over subscription is possible! Configuring : In-Memory Column Store Oracle Multitenant with Pluggable Databases Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Configuring : In-Memory Column Store • New INMEMORY ATTRIBUTE • Following segment types are eligible • Tables • Partitions • Subpartition • Materialized views • Following segment types not eligible • IOTs • Hash clusters • Out of line LOBs CREATE TABLE customers …… PARTITION BY LIST (PARTITION p1 …… INMEMORY, (PARTITION p2 …… NO INMEMORY); ALTER TABLE sales INMEMORY; ALTER TABLE sales NO INMEMORY; Pure OLTP Features Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Oracle Compression Advisor And In-Memory • Easy way to determine memory requirements • Use DBMS_COMPRESSION • Applies MEMCOMPRESS to sample set of data from a table • Returns estimated compression ratio Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Program Agenda Configuring the In-Memory Column Store Populating the In-Memory Column Store Managing the In-Memory Column Store on RAC Querying the In-Memory Column Store 1 4 2 3 Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Oracle In-Memory Columnar Technology • Pure in-memory column format • Not persistent, and no logging • Quick to change data: fast OLTP • 2x to 20x compression • Enabled at table or partition level • Brings existing data into memory and formats it in an optimized columnar format SALES Pure In-Memory Columnar Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | • Population is highly parallel and scalable • Completed by new set of background processes • ora_wNNN_orcl • Number of processes controlled by • INMEMORY_MAX_POPULATE_SERVERS Column Format Memory IMCU IMCU IMCU IMCU IMCU IMCU Populating : In-Memory Column Store Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | • Each object populated in the Column Store is actually made up of multiple In-Memory Compression Units • Each IMCU contains the column entries for a subset of rows in the object • Each column is encoded and/or compressed individually Populating : In-Memory Column Store Column Format Memory IMCU IMCU IMCU IMCU IMCU IMCU Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Populating : In-Memory Column Store • New INMEMORY attribute • Possible to populate only certain columns from a table or partition • Order (not speed) in which objects are populated controlled by PRIORITY subclause • Critical, high, medium, low • Default – none (populate on first access) ALTER TABLE sales INMEMORY; ALTER TABLE sales NO INMEMORY; ALTER TABLE sales INMEMORY NO INMEMORY (PROD_ID); CREATE TABLE orders (c1 number, c2 varchar(20), c3 number) INMEMORY PRIORITY CRITICAL NO INMEMORY (c1); Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Populating : In-Memory Column Store • Objects compressed during population • Different levels • FOR DML - Use on tables or partitions with very active DML activity • FOR QUERY - Default mode for most tables • FOR CAPACITY - For less frequently accessed segments • Great for ILM strategy CREATE TABLE ORDERS …… PARTITION BY RANGE …… (PARTITION p1 …… INMEMORY NO MEMCOMPRESS PARTITION p2 …… INMEMORY MEMCOMPRESS FOR DML, PARTITION p3 …… INMEMORY MEMCOMPRESS FOR QUERY, : PARTITION p200 …… INMEMORY MEMCOMPRESS FOR CAPACITY ); Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Identifying : Tables With INMEMORY Attribute • New INMEMORY column in *_TABLES dictionary views • INMEMORY is a segment attribute SELECT table_name, inmemory FROM USER_TABLES; TABLE_NAME INMEMORY ------------ -------- CHANNELS DISABLED COSTS CUSTOMERS DISABLED PRODUCTS ENABLED SALES TIMES DISABLED • USER_TABLES doesn’t display segment attributes for logical objects • Both COSTS & SALES are partitioned => logical objects • INMEMORY attribute also reported in *_TAB_PARTITIONS Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Identifying : Tables With INMEMORY Attribute • New view V$IM_SEGMENTS • Indicate: • Objects populated in memory • Current population status • Can also be used to determine compression ratio achieved SELECT segment_name name, population_status status FROM v$IM_SEGMENTS; NAME STATUS ------------ -------- PRODUCTS COMPLETED SALES STARTED Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Identifying : Columns Without the INMEMORY Attribute Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Program Agenda Configuring the In-Memory Column Store Populating the In-Memory Column Store DML and the In-Memory Column Store Managing the In-Memory Column Store on RAC Querying the In-Memory Column Store 1 4 2 3 Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | IMCU JOURNAL DML and the In-Memory Column Store • A journal is used to keep the column store transactionally consistent • DML operations processed in row store as they are today • Corresponding entry in column store marked stale • Copy of changed row stored in Transaction Journal Memory Column Format Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | IMCU JOURNAL DML and the In-Memory Column Store • In-Memory Column Store is never out of date • Read consistency achieved by merging contents of column and the transaction journal • When number of entries in journal hits internal threshold CU is refreshed • This is an online operation – columns store always available Memory Column Format Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Program Agenda Configuring the In-Memory Column Store Populating the In-Memory Column Store Managing the In-Memory Column Store on RAC Querying the In-Memory Column Store 1 2 3 4 Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Scale-Out In-Memory Database to Any Size • Scale-Out across servers to grow memory and CPUs • In-Memory queries parallelized across servers to access local column data • Scale-out policy is a segment level (table, partition, sub partition) property Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Scale-Out In-Memory Database to Any Size • Policy is user-specifiable • Controlled by DISTRIBUTE subclause • Distribute by rowid range • Distribute by partition • Distribute AUTO • Parallel Scan Affinity • Requires parallel_degree_policy=AUTO ALTER TABLE sales INMEMORY DISTRIBUTE BY PARTITION; ALTER TABLE COSTS INMEMORY DISTRIBUTE AUTO; Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Oracle Database In-Memory: Unique Fault Tolerance • Similar to storage mirroring • Duplicate in-memory columns on another node • Enabled per table/partition • Application transparent • In-Memory Column Store remains available by using duplicate after failure Only Available on Engineered Systems Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Oracle Database In-Memory: Unique Fault Tolerance • Policy is user-specifiable • Controlled by DUPLICATE subclause • DUPLICATE • DUPLICATE ALL ALTER TABLE sales INMEMORY DUPLICATE; ALTER TABLE COSTS INMEMORY DISTRIBUTE AUTO DUPLICATE ALL; Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Program Agenda Configuring the In-Memory Column Store Populating the In-Memory Column Store Managing the In-Memory Column Store on RAC Querying the In-Memory Column Store 1 2 3 4 Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Why is an In-Memory scan faster than the buffer cache? SELECT COL4 FROM MYTABLE; X X X X X RESULT Row Format Buffer Cache Column Format IM Column Store Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Oracle In-Memory Column Store Storage Index • Each column is the made up of multiple column units •Min / max value is recorded for each column unit in a storage index • Storage index provides partition pruning like performance for ALL queries Memory SALES Column Format Min 1 Max 3 Min 4 Max 7 Min 8 Max 12 Min 13 Max 15 Example: Find sales from stores with a store_id of 8 or higher Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Orders of Magnitude Faster Analytic Data Scans Load multiple PROMO_ID values Vector Compare all values in 1 cycle CPU PROMO_ID 9999 9999 9999 9999 Example: Find all sales With PROMO_ID 9999 VECTOR REGISTER Memory • Each CPU core scans local in- memory columns • Scans use super fast SIMD vector instructions • Originally designed for graphics & science • Billions of rows/sec scan rate per CPU core • Row format is millions/sec Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Identifying : INMEMORY Table Scan • Optimizer fully aware • Cost model adapted to consider INMEMORY scan • New access method TABLE ACCESS IN MEMORY FULL • Can be disabled via new parameter • INMEMORY_QUERY Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Identifying : INMEMORY Table Scan • NEW Session level statistics • Best way to determine if In-Memory was used • Best way to measure the benefits of In-Memory scan Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Joining and Combining Data Also Dramatically Faster • Converts joins of data in multiple tables into fast column scans • Joins tables 10x faster Example: Find all orders placed on Christmas eve LINEORDER DATE_DIM DateKey Amount Datekey is 24122013 Type=d.d_date='December 24, 2013' Date Sum DateKey Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | In-memory Joins • Simple two-table join between the DATE_DIM and LINEORDERS table • IM column store takes advantage of Bloom Filter to process table joins very efficiently SELECT SUM(lo_extendedprice * lo_discount) revenue FROM lineorder l, date_dim d WHERE l.lo_orderdate = d.d_datekey AND l.lo_discount BETWEEN 2 AND 3 AND d.d_date='December 24, 2013'; Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Creation and Use of Bloom Filter • Bloom filters enable joins to be converted into fast column scans • Introduced in Oracle Database 10g. Very efficiently applied to column format data via SIMD. • Same technique used to offload joins on Exadata Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Summary: Oracle Database In-Memory Powering the Real-Time Enterprise • Extreme Performance: Analytics & OLTP • Extreme Scale-Out & Scale-Up • Extreme Availability • Extreme Simplicity AGILE EFFICIENT DATA- DRIVEN Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
还剩43页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 5 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf