Best Practices for Getting Started with Oracle Database In-Memory Maria Colgan Master Product Manager For Database In-Memory October 27 2015
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
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 © 2015, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
3
What is Oracle Database In-Memory
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Oracle Database In-Memory Goals Real-Time Analytics 100X
Accelerate Mixed Workload Transactions
Enable Real-Time business Decisions
Risk-Free
Trivial to Implement
Proven Scale-Out, Availability, Security
No Application Changes Not Limited by Memory
Analytics
Run analytic on operational Systems
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
5
Breakthrough: Dual Format Database Normal Buffer Cache
New In-Memory Format
• BOTH row and column formats for same table SALES
SALES
Row Format
Column Format
• Simultaneously active and transactionally consistent • Analytics & reporting use new in-memory Column format • OLTP uses proven row format
SALES
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
6
Program Agenda 1
Installing & Configuring the In-Memory Column Store
2
Populating the In-Memory Column Store
3
Querying the In-Memory Column Store
4
What the Optimizer Knows
5
Getting the most from Database In-Memory
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
7
How Do I Install & Configure The In-Memory Column Store?
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Installing : Oracle Database In-Memory • Automatically installed as part of Database 12c •
No additional steps required
Note: Database InMemory is not enabled by default Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
9
Configuring : In-Memory Column Store System Global Area SGA Buffer Cache
Large Pool
Shared Pool
Other shared Memory Components
Redo Buffer
In-Memory Area
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Configuring : In-Memory Column Store SELECT * FROM V$SGA;
• New Component of SGA
NAME VALUE ------------------ --------Fixed Size 2927176 Variable Size 570426808 Database Buffers 4634022912 Redo Buffers 13848576 In-Memory Area 1024483648
• Controlled by INMEMORY_SIZE parameter •Minimum size of 100MB
• SGA_TARGET must be large enough to accommodate
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Configuring : In-Memory Column Store Don’t get carried away
•Don’t give all memory to SGA •Don’t want any aspect of execution plan to spill to disk • Ensure PGA_TARGET is set large enough to keep joins & sorts in memory •Use Parallel Execution to maximize PGA usage Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Composition of In-Memory Area In Memory Area IMCU
IMCU
IMCU
IMCU
IMCU
IMCU
IMCU
• Contains two subpools: SMU
SMU
SMU
SMU
SMU
SMU
SMU
SMU
IMCU
Metadata Column Format Data
– IMCU pool: Stores In Memory Compression Units (IMCUs) – SMU pool: Stores Snapshot Metadata Units (SMUs)
• IMCUs contain column formatted data • SMUs contain metadata and transactional information Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
13
Composition of In-Memory Compression Unit (IMCU) An IMCU to one or more extents A table inmaps memory is composted of one or more IMCUs
IMCU header ROWID
Column CUs
EMPID
DEPT
NAME
SALARY
Employee Table Extent #1 Blocks 1-120
Extent #2 Blocks 160-182
Extent #3 Blocks 201-301
. . .
. . . Extent #13 Blocks 140-159
Extent #14 Blocks 382-490
IMCU header Extent #15 Blocks 310-381
ROWID
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Column CUs
EMPID
NAME
DEPT
SALARY
14
Composition of In-Memory Area • V$INMEMORY_AREA: Current sizes of pools and pool statuses • V$IM_HEADER: List of IMCUs currently in the In-Memory column store
SQL> SELECT * from V$INMEMORY_AREA; POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS ---------- ----------- ---------- --------------1MB POOL 1307574272 135266304 DONE 64KB POOL 318767104 524288 DONE
SQL> SELECT OBJD, TSN, ALLOCATED_LEN, NUM_ROWS, NUM_COLS FROM V$IM_HEADER; OBJD TSN ALLOCATED_LEN NUM_ROWS NUM_COLS ---------- ---------- ------------- ---------- ---------92918 5 19922944 532246 60 92918 5 19922944 532480 60 92918 5 19922944 522496 60 92918 5 11534336 532480 60 92918 5 19922944 530176 60 92918 5 19922944 532480 60 92918 5 15728640 419562 60
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Tips To Remember Configuring In-Memory Column Store
• Database In-Memory not enable by default • INMEMORY_SIZE must be set to 100MB or larger • SGA_TARGET Existing SGA_TARGET INMEMORY_SIZE (Single Instance) • SGA_TARGET (Existing SGA_TARGET INMEMORY_SIZE)*1.1 (RAC) • PGA_TARGET PARALLEL_MAX_SERVERS 2GB • If you can’t avoid spilling to temp do it efficiently • Increase size of temp writes from 200KB to 1MB
• Set _smm_auto_max_io_size =1024 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
How Do I Get Data In And Out Of The In-Memory Column Store?
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
In-Memory A Store – Not A Cache In Memory Area In-Memory Area IMCU
IMCU
IMCU
IMCU
IMCU
IMCU
IMCU
• What is a store? – A static pool of memory SMU
SMU
SMU
SMU
SMU
SMU
SMU
SMU
IMCU
Metadata
• You decide what objects are populated in memory • Objects don’t age out • Objects automatically kept transactionally consistent
Column Format Data
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
18
Populating : In-Memory Column Store • New INMEMORY ATTRIBUTE
ALTER TABLE sales INMEMORY;
• Eligible segment types are
ALTER TABLE sales NO INMEMORY;
• Tables • Partitions • Subpartition
CREATE TABLE PARTITION BY (PARTITION (PARTITION
customers …… LIST p1 …… INMEMORY, p2 …… NO INMEMORY);
• Materialized views
• Following types not eligible • IOTs • Hash clusters • Out of line LOBs
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Pure OLTP Features
Populating : In-Memory Column Store • Possible to populate only certain
ALTER TABLE sales INMEMORY NO INMEMORY (Delivery_note);
columns • Two phase approach 1.
INMEMORY attribute on Table automatically inherited by columns
2.
Need to remove attribute from the columns you don’t want populated
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Populating : In-Memory Column Store Pure In-Memory Columnar
• Pure in-memory column format • Not persistent, and no logging • Quick to change data: fast OLTP
•
2x to 20x compression typical
• Enabled at table or partition SALES
• Just active data in-memory
• Available on all hardware platforms
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
21
Populating : In-Memory Column Store • Population completed by new
set of background processes
ora_w001_orcl • Number of processes
controlled by parameter INMEMORY_MAX_POPULATE_SERVERS
• Default ½ CPU_COUNT Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Populating : In-Memory Column Store Careful Balancing Act
More worker processes
Less worker processes
• Faster Population
• Slower Population
• More CPU resources used
• Less CPU resources used
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Populating : In-Memory Column Store CREATE TABLE orders (c1 number, c2 varchar(20), c3 number) INMEMORY PRIORITY CRITICAL;
• Order in which objects are
populated controlled by PRIORITY subclause • 5 levels Critical - None • Default – None (populates on
first access)
Note: Does not control the speed of population Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Populating : In-Memory Column Store ALTER MATERIALIZED VIEW mv1 INMEMORY MEMCOMPRESS FOR QUERY LOW;
• Objects compressed during
population • New compression techniques •
CREATE TABLE trades (Name varchar(20), Desc varchar(200)) INMEMORY MEMCOMPRESS FOR DML(desc);
Focused on scan performance
• Controlled by MEMCOMPRESS • Multiple levels of compression • Possible to use a different level for
different partitions in a table
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Populating : In-Memory Column Store CREATE TABLE ORDERS …… • PARTITION BY RANGE …… (PARTITION p1 …… INMEMORY MEMCOMPRESS FOR CAPACITY HIGH PARTITION p2 …… INMEMORY MEMCOMPRESS FOR QUERY LOW, PARTITION p3 …… INMEMORY MEMCOMPRESS FOR DML, : PARTITION p200 …… NO INMEMORY );
Different levels • FOR DML
Use on tables or partitions with very active DML activity • FOR QUERY LOW / HIGH
Default mode for most tables • FOR CAPACITY LOW / HIGH
For less frequently accessed segments
• Easy to switch levels as part of
ILM strategy Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Identifying : Tables With INMEMORY Attribute SELECT table_name, inmemory FROM USER_TABLES;
TABLE_NAME INMEMORY ------------------CHANNELS DISABLED COSTS CUSTOMERS DISABLED PRODUCTS ENABLED SALES TIMES DISABLED
• New INMEMORY column in
*_TABLES dictionary tables • INMEMORY is a segment attribute • 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 © 2015, Oracle and/or its affiliates. All rights reserved. |
Monitoring : In-Memory Column Store Population • V$IM_SEGMENTS – Indicates which objects are currently populated in-memory – Shows current size of each segment in-memory – Shows how much remains to be populated SQL> select segment_name, populate_status, inmemory_priority, inmemory_size, bytes_not_populated from v$im_segments; SEGMENT_NAME -----------ACCOUNTS SALES
POPULATE_STATUS --------------STARTED COMPLETED
INMEM_PRIORITY INME_SIZE BYTES_NOT_POPULATED ------------- ------------ ------------------HIGH 196606 2434886912 CRITICAL 135790592 0
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Tips To Remember Populating In-Memory Column Store
• In-Memory column store is not a cache – Once populated objects not automatically aged out
• Only objects with the INMEMORY attributes are candidates
• Object with a PRIORITY are populated at database startup – Population is triggered after IMCO wakes up for the first time (2-3 minutes after open) – On demand objects can supersede the population order if accessed
• Use (g)V$IM_SEGMENTS to monitor the progress of population – On RAC the bytes_not_populated column will not be ZERO
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
How Do I Know What Tables Should Go Into The In-Memory Column store?
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Oracle In-Memory Advisor • New In-Memory Advisor • Analyzes existing DB workload
via AWR & ASH repositories • Provides list of objects that would benefit most from being populated into IM column store Note: Diagnostic & Tuning Packs required
Copyright © 2015, 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 © 2015, Oracle and/or its affiliates. All rights reserved. |
Why Are Analytic Queries Faster In The In-Memory Column Store?
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Why is an In-Memory scan faster than the buffer cache? Buffer Cache
SELECT COL4 FROM MYTABLE; X X X X X
RESULT
Row Format
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
34
Why is an In-Memory scan faster than the buffer cache? IM Column Store
SELECT COL4 FROM MYTABLE;
RESULT RESULT
Column Format
X X X X X
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
35
Oracle In-Memory Column Store Storage Index Example: Find all sales from stores with a store_id of 8 Memory Min 1 Max 3
• Min / max value is recorded for each column unit in a storage index
Min 4 Max 7
Min 8 Max 12
SALES Column Format
Min 7 Max 15
• Each column is the made up of multiple column units
?
• Storage index provides partition pruning like performance for ALL queries
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
36
Orders of Magnitude Faster Analytic Data Scans Memory
REGION
Example: Find sales in region of CA
• Scans use super fast SIMD vector instructions
CPU Vector Register
CA
Load multiple region values
• Each CPU core scans local in-memory columns
CA CA
Vector Compare all values an 1 cycle
CA
> 100x Faster
• Originally designed for graphics & science • Billions of rows/sec scan rate per CPU core • Row format is millions/sec Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
37
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 © 2015, Oracle and/or its affiliates. All rights reserved. |
Identifying : INMEMORY Table Scan IM scan bytes in-memory • NEW Session level statistics • Best way to determine if In-Memory was use • Best way to measure the benefits of In-Memory scan •
IM scan bytes uncompressed IM scan CUs columns accessed IM scan CUs columns decompressed IM scan CUs columns theoretical max IM scan rows IM scan rows range excluded IM scan rows excluded IM scan rows optimized IM scan rows projected IM scan CUs predicates received IM scan CUs predicates applied IM scan CUs predicates optimized IM scan CUs pruned IM scan segments minmax eligible ….
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
39
Identifying : INMEMORY Table Scan IM scan bytes in-memory • New stats visible in V$SYSSTAT or AWR How many rows were scanned How many rows were avoided by dictionary pruning How many predicates were optimized How many IMCUs were min-max pruned
Etc ..
IM scan bytes uncompressed IM scan CUs columns accessed IM scan CUs columns decompressed IM scan CUs columns theoretical max IM scan rows IM scan rows range excluded IM scan rows excluded IM scan rows optimized IM scan rows projected IM scan CUs predicates received IM scan CUs predicates applied IM scan CUs predicates optimized IM scan CUs pruned IM scan segments minmax eligible ….
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
40
Joining and Combining Data Also Dramatically Faster Example: Find total sales in outlet stores
Sales
Type=‘Outlet’
Amount
StoreID in 15, 38, 64 Store ID
Store ID
Type
Stores
• Converts joins of data in multiple tables into fast column scans • Joins tables 10x faster
Sum Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
41
Identifying : INMEMORY Joins
• Bloom filters enable joins to be converted into fast column scans • Tried and true technology originally released in 10g • Same technique used to offload joins on Exadata
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Generates Reports Instantly Example: Report sales of footwear in outlet stores Products
In-Memory Report Outline
Sales
report outline • Then report outline filled-in
Stores
Outlets
Footwear Footwear
• Dynamically creates in-memory
during fast fact scan
$ $$
• Reports run much faster
$ $$$
• Without predefined cubes
• Also offloads report filtering to
Exadata Storage servers Outlets
Sales
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
43
Identifying : INMEMORY Aggregation
Hash Group By
Vector Group By
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Tips To Remember Querying In-Memory Column Store
• Biggest benefits come for queries that – Select just a subset of the columns from a table – Have selective filter where clause predicates • E.g. equality predicates, in-lists or range predicates work best
• Sorting the data on disk on filter column benefits storage index – Attribute clustering can help with this
• Works best with selective equality joins – Generates a bloom filter
• Non equality joins won’t benefit as much – Consider using materialized views
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Tips To Remember Querying In-Memory Column Store
• Vector Transform works best when – INMEMORY_SIZE parameter is set to a non-zero value – The join columns between the tables contain "mostly” unique keys or numeric keys – The fact table (largest table in the query) is at least 10X larger than the other tables – The tables are populated into the IM column store
• NOTE: Table don’t need to be in In-Memory vector group by offloaded to Exadata storage too
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
If In-Memory Usage Shows Up In The Plan What Does The Optimizer Know
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Be Afraid ….Optimizer gets to Decide • The Optimizer gets to pick the execution plan for queries – It decides if In-Memory column store will be used or not
• Traditional cost model assumes all scan operations will read data from disk • 12.1.0.2 the cost model was expanded to account for In-Memory scans too • Cost is now computed based on statistics maintained on – Objects: tables, columns, indexes, partitions etc. – System: CPU speed, IO throughput, etc. – In-Memory tables: In-Memory specific statistics
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
You are still on the hook to get these statistics correct
48
New In-Memory Statistics • Automatically computed on the fly during hard parse • Computed at the segment level – table or (sub)partition – # IMCUs – # IM Blocks – IM Quotient • Fraction of table populated in In-Memory column store • Value between 0 and 1
– # IM Rows – # IM Transaction Journal Rows
• In-Memory statistics are RAC-aware (DUPLICATE and DISTRIBUTE) Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
49
Optimizer Statistics Gathering Recap • Use DBMS_STATS.GATHER_*_STATS procedures to gather statistics – Analyze command deprecated since 8i – Use default values as much as possible especially AUTO_SAMPLE_SIZE
• Use histograms to make the Optimizer aware of any data skews – New types of histograms in 12c provide more detailed information
• Use extended statistics to make the Optimizer aware of correlation – Column group statistics used for both single table cardinality estimates, joins & aggregation
• Use constraints to indicate not-null, Primary Key, & Foreign key columns Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
I Get It! Where Do I Begin
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Getting The Most From In-Memory Understand Where it Helps
• Fast cars speed up travel, not meetings • In-Memory speeds up analytic data access, not: – Network round trips, logon/logoff – Parsing, PL/SQL, complex functions – Data processing (as opposed to access) • Complex joins or aggregations where not much data is filtered before processing
– Load and select once – Staging tables, ETL, temp tables
Know your bottleneck!
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
52
Getting The Most From In-Memory The Driver Matters
• Avoid stop and go traffic – Process data in sets of rows in the Database – Not one row at a time in the application
• Plan ahead, take shortest route – Help the optimizer help you: Gather representative set of statistics using DBMS_STATS
• Use all your cylinders – Enable parallel execution – In-Memory removes storage bottlenecks allowing more parallelism
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
53
Where can I get more information
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Oracle Database In-Memory Schedule for Oracle Open World Date
Title
Location
Speaker
Tuesday Oct 27th 11:00 – 11:45
Best Practices for Getting Started with Oracle Database In-Memory
Moscone South Room 104
Maria Colgan Product Manager, Oracle
Tuesday Oct 27th 17:00 – 17:45
Oracle Database In-Memory— What’s New and What’s Coming
Moscone South Room 103
Juan Loaiza Senior Vice President, Oracle
Wednesday Oct 28th 13:45 – 14:30
Oracle Database In-Memory Customer Panel
Moscone South Room 102
Maria Colgan with 4 In-Memory Customers
Wednesday Oct 28th 15:00 – 15:45
Oracle Database In-Memory: Under the Hood
Moscone South Room 103
Tirthankar Lahiri – Vice President, Oracle
Monday 14:00 Tuesday 08:45 Wednesday 08:45 Thursday 09:30
Oracle Database In-Memory Boot Camp: Everything You Need to Get Started
Hotel Nikko Room Peninsula
Andy Rivenes & Andy Yao
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Nikko Hotel Hands-on Labs
WE ARE HERE Moscone South
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Additional Resources Join the Conversation https://twitter.com/db_inmemory
https://blogs.oracle.com/in-memory/ https://www.facebook.com/OracleDatabase http://www.oracle.com/goto/dbim.html
Related White Papers • Oracle Database In-Memory White Paper • Oracle Database In-Memory Aggregation Paper • When to use Oracle Database In-Memory • Oracle Database In-Memory Advisor
Related Videos • In-Memory YouTube Channel • Managing Oracle Database In-Memory • Database In-Memory and Oracle Multitenant • Industry Experts Discuss Oracle Database In-Memory • Software on Silicon Any Additional Questions • Oracle Database In-Memory Blog • My email:
[email protected] Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
57
