oracle openworld 2019 › technetwork › database › in-memory › ...- set _smm_auto_max_io_size...

Oracle OpenWorld 2019S A N F R A N C I S C O

Database In-Memory: Tips and Tricks (TRN3482)

Andy Rivenes Product Manager, Database In-Memory

Applications & Product Development

Email: [email protected]

Twitter: @TheInMemoryGuy

Blog: blogs.oracle.com/in-memory

Copyright © 2019 Oracle and/or its affiliates.

mailto:[email protected]

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, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation.

Statements in this presentation relating to Oracle’s future plans, expectations, beliefs, intentions and prospects are “forward-looking statements” and are subject to material risks and uncertainties. A detailed discussion of these factors and other risks that affect our business is contained in Oracle’s Securities and Exchange Commission (SEC) filings, including our most recent reports on Form 10-K and Form 10-Q under the heading “Risk Factors.” These filings are available on the SEC’s website or on Oracle’s website at http://www.oracle.com/investor. All information in this presentation is current as of September 2019 and Oracle undertakes no duty to update any statement in light of new information or future events.

Safe Harbor


http://www.oracle.com/investor

What is Database In-Memory?


Row Format Databases vs. Column Format Databases

Rows Stored Contiguously

• Transactions run faster on row format– Example: Query or Insert a sales order– Fast processing few rows, many columns

Columns Stored Contiguously

•Analytics run faster on column format– Example : Report on sales totals by region– Fast accessing few columns, many rows

SALES

SALES

Until Now Must Choose One Format and Suffer Tradeoffs

QueryQuery

Query


• 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

Breakthrough: Dual Format Database

Buffer CacheNew In-Memory

Column Store

SALES SALESRow

FormatColumnFormat

SALES


Oracle In-Memory: Simple to Implement

1. Configure Memory Capacity• inmemory_size = XXX GB

2. Configure tables or partitions to be in memory• alter table | partition … inmemory;

3. Later drop analytic indexes to speed up OLTP


Where Is Database In-Memory Available?• Database In-Memory is an option for Oracle Database

Enterprise Edition• Database In-Memory was included in the first patchset

(12.1.0.2) for 12.1 and all subsequent Oracle Database releases

• Available:- Database Cloud Service: Extreme Performance- Exadata Cloud Service- Exadata Cloud at Customer- On-premises


How Do I Install & ConfigureThe In-Memory Column Store?


Note: Database In-Memory is not enabled by default

• Automatically installed as part of Oracle Database (12.1.0.2, 12.2, 18c, 19c)

• No additional steps required

Installing : Oracle Database In-Memory


Installing: Apply the Latest Database Proactive Bundle Patch or Release Update

• Database In-Memory fixes and enhancements are only distributed through Database Proactive Bundle Patches or Release Updates• See MOS Notes:

- 2337415.1 – Overview of Database Patch Delivery Methods for 12.2.0.1 and greater

- 1962125.1 – Overview of Database Patch Delivery Methods for 12.1.0.2 and older

• Starting with the latest patches avoids re-inventing the wheel discovering bugs that have already been fixed!


Tip: Configuring In-Memory Column Store

• If you can’t avoid spilling to temp do it efficiently- Increase the size of temp writes from 200KB to 1MB - Set _smm_auto_max_io_size =1024

• Database In-Memory is not enabled by default

• INMEMORY_SIZE must be set to 100MB or larger• SGA_TARGET Existing SGA_TARGET INMEMORY_SIZE

• PGA_TARGET Max expected parallel servers 2GB

(Single Instance)

• SGA_TARGET Existing SGA_TARGET (INMEMORY_SIZE * 1.1) (RAC)

(See MOS Note 1903683.1)


How Does DatabaseIn-Memory Work?


In-Memory A Store – Not A Cache

A

B

C

Buffer Cache

A B C E AF

• What is a cache?

• A pool of memory

• Data automatically brought into memory based on access

• Data automatically aged out • Good example:

Oracle Database Buffer Cache


In-Memory Area: Static Area within SGA

System Global Area SGA

Buffer Cache

Shared Pool

Log Buffer

Large Pool

In-Memory

AreaOther

• Contains data in the new In-Memory Columnar Format

• Controlled by INMEMORY_SIZEparameter

- Minimum size of 100MB

• Can be re-sized larger while database is running (12.2)

• SGA_TARGET must be largeenough to accommodate In-Memory area

15Copyright © 2019 Oracle and/or its affiliates.

Composition of In-Memory Area

• Contains two subpools:- 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

SMU SMU

SMU SMU

SMU SMU

SMU SMU

IMCU IMCU

IMCUIMCU

IMCU IMCU

IMCUIMCU

In Memory Area

Column Format Data

Metadata


Composition of In-Memory Compression Unit (IMCU)

• Unit of column store allocation- Columnar representation of a large

number of rows from an object

- Rows from one or more table extents

• Actual size depends on size of rows, compression factor, etc.

• Each column stored as a separate contiguous Column Compression Unit (column CU)- Rowids also stored as a Column CU

IMCU header

Extent #13Blocks 20-120



ROWID

EMPID NAMEDEPT SALARY

Column CUs

17Copyright © 2019 Oracle and/or its affiliates.

Composition of In-Memory Object

18

An object in memory is composedof one or more IMCUs/SMUs

Employee Table

.

.

.

In-Memory Area (1MB Pool)

In-Memory Area (64 KB Pool)

.

.

.

SMU

.

.

.

An IMCU/SMU is composedof one or more 1MB/64KB

memory segments


Tip: View In-Memory Area Usage

• 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 OBJD, TSN, ALLOCATED_LEN, NUM_ROWS, NUM_COLS FROM V$IM_HEADER;

OBJD TSN ALLOCATED_LEN NUM_ROWS NUM_COLS---------- ---------- ------------- ---------- ----------

92918 5 19922944 532246 6092918 5 19922944 532480 6092918 5 19922944 522496 6092918 5 11534336 532480 6092918 5 19922944 530176 6092918 5 19922944 532480 6092918 5 15728640 419562 60

SQL> SELECT * from V$INMEMORY_AREA;

POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS---------- ----------- ---------- ---------------1MB POOL 1307574272 135266304 DONE 64KB POOL 318767104 524288 DONE


Tip: Database In-Memory Wait on Populate

PL/SQL

DBMS_INMEMORY.POPULATE_WAIT();

• New in 19c -POPULATE_WAIT function in DBMS_INMEMORY package• Based on population priority

setting• Provides an application API

to ensure that objects are populated before being accessed– Can be used to ensure

application SLAs are met


Tip: Key Views

• Confirm all data is fully populated!

• v$inmemory_area – is there enough memory?• v$im_segments – are all objects fully populated?• v$im_header – are all rows populated per object?• v$im_smu_head – are changes affecting journal space?

• v$imeu_header – are IMEs populated?• user_joingroups – have join groups been created and populated?


More Tips To Remember

• In-Memory column store is not a cache- Once populated objects not automatically aged out

• Only objects with the INMEMORY attribute are candidates• Objects with a PRIORITY are populated at database

startup (not the default)- 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

Populating In-Memory Column Store


How Does It Handle Data Changes?


DML and the In-Memory Column Store

• DML operations processed in row store just as they are today

• Corresponding entry in column store marked stale as of SCN

• ROWID of row stored in the Transaction Journal

JOURNAL

In Memory Area

Column Format Data

IMCU

ROW_ID:123765490

SMU

DMLInsert

UpdateDelete

Row Store


DML and the In-Memory Column Store

• In-Memory Column Store is never out of date

• Read-consistency is achieved by merging contents of column, the transaction journal and buffer cache

• Pure In-Memory format means no undo/redo to repopulate IMCUs

JOURNAL

In Memory Area

Column Format Data

IMCU

ROW_ID:123765490

SMU

Row Store


Repopulation and the In-Memory Column StoreIn Memory Area

Original IMCU

IMCUIMCUJOURNAL

ROW_ID:123765490ROW_ID:123800000ROW_ID:257643100ROW_ID:257643100ROW_ID:257643100

Mark original IMCU as old version as of SCN

1 Track any DML operations that occur during IMCU creation in Journal

3

Both versions of the IMCU will exist in IM column store until original is no longer useful or there is space pressure

Original SMUNew version IMCU

Create a new version of IMCU by combining clean rows from original with latest version of changed rows

2

IMCUIMCUJOURNAL

New SMU


Tip: Column Store Sizing

• The column store is fixed in size and objects are fully populated, but …- Inserts will cause the object to grow when the

new rows are populated

- Updates can cause existing rows to expand (i.e. column values) which can affect the size of a re-populated IMCU

• Additional space should be reserved in the column store to allow for DML activity

Allocate Extra Room


What Does The Optimizer Know?


Optimizer: New In-Memory Statistics• Automatically computed on the fly during hard parse• Computed at the segment level – table or partition/subpartition

- # 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)• In 12.2+ IM stats are available in the ALL_TAB_STATISTICS view


Optimizer: In-Memory Aware Cost Model• Cost of In-Memory Scan

- IO cost: Includes the cost of reading:• Invalid rows from disk• Extent map

- CPU cost: Includes• Traversing IMCUs• IMCU pruning using storage indexes• Decompressing IMCUs• Predicate evaluation• Stitching rows• Scanning transaction journal rows


Tip: OPTIMIZER_ADAPTIVE_FEATURES

• OPTIMIZER_ADAPTIVE_FEATURES has been made obsolete in 12.2

• Starting in 12.2:- OPTIMIZER_ADAPTIVE_PLANS – defaults to true- OPTIMIZER_ADAPTIVE_STATISTICS – defaults to false

• In 12.1.0.2:- Patches for bugs 21171382 and 22652097 provide 12.2 behavior- See MOS Note 2187449.1 – Recommendations for Adaptive Features in

Oracle Database 12c• See the Optimizer blog for more information:

blogs.oracle.com/optimizer


Tip: Ensure Plan Stability

• Unpredictable changes to execution plans can destroy performance

• Avoiding plan changes is the only method to avoid performance regression

Solution• Optimizer automatically manages ‘execution plans’• Only known and verified plans are used

SQL Plan Management is controlled plan performanceCopyright © 2019 Oracle and/or its affiliates.

How Does Database In-Memory Scale Out?


RAC: Scale-Out Database In-Memory to Any Size

• Scale-Out across servers to grow memory and CPUs

• Shared nothing architecture

• IMCUs not shipped across interconnect – cache fusion is not in play!

• In-Memory queries are parallelized across servers to access local column data


Tip: PopulationBYTES_NOT_POPULATED

SQL> @racim_sumINST_ID SEGMENT_NAME BYTES BYTES_POPULATED BYTES_NOT_POPULATED INMEMORY_SIZE

---------- -------------------- ---------- --------------- ------------------- -------------1 LINEORDER 730750976 299892736 430858240 2365849602 730750976 199999488 530751488 1577451523 730750976 230858752 499892224 181927936

******************** --------------- -------------sum 730750976 576258048

SQL>


Tip: Shared Pool Usage

• Additional shared pool space required for DBIM on RAC• RAC wide locks required for each database block covered by an

IMCU• Approximately 300 bytes per populated database block• This works out to roughly 10% of the size of the column store

for default compression• The formula on RAC: SGA_TARGET + (INMEMORY_SIZE * 1.1)• (See MOS Note: 1903683.1)


• Similar to storage mirroring

• Duplicate in-memory columns on another node� Enabled per table/partition� Application transparent

• Performance preserved by using duplicate during a node failure

• Performance can be improved by performing joins within each node (partial partition wise joins)

Only Available on Engineered Systems

Engineered Systems: Unique Fault Tolerance


Tip: Duplicate Strategy For a Star Schema• Fact tables are distributed by partition• Dimension tables are duplicated (DUPLICATE ALL)• Co-locates joins between the distributed fact table partitions and the

dimension tables

D

D

D

D

F D

D

D

D

F D

D

D

D

F

Only Available on Engineered SystemsCopyright © 2019 Oracle and/or its affiliates.

How Do I Tell If The In-Memory Column Store Is Being Used?


Use Time Based Analysis Techniques To Evaluate Benefit

• Shows how SQL was executed and where time was spent

• See blogs.oracle.com/In-Memory for a technical brief on creating SQL Monitor active reports

SQL Monitor Active Reports


Which Queries Benefit From Database In-Memory?

is spent accessing data

For a non-trivial amount of rows and execution time, when a significant amount of time …

is spent aggregating datais spent joining data

HASH JOIN

Table A Table B


Scanning & Filtering Query: Traditional data access

Majority of time spent accessing data


Join Query – Traditional hash join

Majority of time spent joining data


Aggregation – Traditional Group By

Query bottlenecked on scan and group

by operation


Tip: Identifying In-Memory Benefits

• Session level statistics

• Best way to determine if In-Memory was used

• Best way to measure the benefits of In-Memory scan

• See blogs.oracle.com/In-Memory for descriptions of the key statistics

IM scan bytes in-memoryIM scan bytes uncompressedIM scan CUs columns accessedIM scan CUs columns decompressedIM scan CUs columns theoretical maxIM scan rowsIM scan rows range excludedIM scan rows excludedIM scan rows optimizedIM scan rows projectedIM scan CUs predicates receivedIM scan CUs predicates appliedIM scan CUs predicates optimizedIM scan CUs prunedIM scan segments minmax eligible ….


How Much Memory Do You Need?


Oracle In-Memory Advisor• In-Memory Advisor – free

download available on OTN for 11.2.0.3+ DBs

• Analyzes existing DB workload via AWR & ASH repositories

• Provides list of objects that would benefit most from being populated into IM column store

Note: Database Tuning Pack license required


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


What If You Don’t Have Enough Memory?


• Objects compressed during population

• New compression techniques� Focused on scan performance

• 2x to 20x compression typical• Multiple levels of compression- FOR DML- FOR QUERY LOW/HIGH- FOR CAPACITY LOW/HIGH

• Possible to use a different level for different partitions in a table

Compression

ALTER MATERIALIZED VIEW mv1 INMEMORYMEMCOMPRESS FOR QUERY LOW;

CREATE TABLE trades (Name varchar(20),Desc varchar(200))

INMEMORYMEMCOMPRESS FOR DML(desc);


• You don't have to populate all columns- But, if excluded columns are accessed

then the query will run against the row-store

• Two phase approach1. INMEMORY attribute on table

automatically inherited by columns2. Need to remove attribute from the

columns you don’t want populated

Columns Can Be Excluded

ALTER TABLE sales INMEMORYNO INMEMORY (delivery_note);


Extend In-Memory Analytics into Storage

In-Memory Columnar scans

In-FlashColumnar

scans

• Exadata automatically transforms table data intoIn-Memory DB columnar formats in Exadata Flash Cache– Enables fast vector processing for storage server queries

• Additional compression for OLTP compressed or uncompressed tables in flash – new in Exadata System Software 18.1

• Enables dictionary lookup and avoids processing unnecessary rows

• Smart Scan results sent back to database in In-Memory Columnar format– Reduces Database node CPU utilization

• Uniquely optimizes next generation Flash as memory

Up to 1.5 TB DRAM per Server

Up to 25.6 TB Flash per Server


• Real-time analytics with no impact on primary database

• Makes full use of memory on standby system

• Standby can populate different data than production database

• Available on Exadata and PaaS Cloud Services

Mixed Workload: In-Memory on Active Data Guard

1 MonthIn-Memory

Production Standby

1 Year In-Memory


How Do I Get Even Faster Performance?


• Join columns in both tables are compressed using the same dictionary

• Joins occur on dictionary values rather than on data- Saves on decompression of data- Save on hashing the data

Join Groups: Faster Hash Joins

SALESVEHICLESNAME is

join column

Example: Find sales price of each Vehicle


• Analytic queries contain complex expressions- Originally evaluated for every row

• In-Memory Expression- SQL expression computed & stored as

additional inmemory columns- All In-Memory optimizations apply to

expression columns (e.g. Vector processing, storage indexes)

• Reduce repeated evaluations- Save CPU by only calculating once

• 3-5x faster complex queries

Analytics Performance: In-Memory Expressions

Net = Price + Price * TaxExample: Compute total sales price

Sales

Tax

Price

Price

+ P

rice

* Ta

x


• Full JSON documents populated using an optimized binary format• Additional expressions can be

created on JSON columns (e.g. JSON_VALUE) & stored in column store• Queries on JSON content or

expressions automatically directed to In-Memory format• E.g. Find movies where

movie.name contains “Jurassic”

• 20 - 60x performance gains observed

Superfast / Multi-Model Analytics: In-Memory JSON

Relational

In-Memory Colum Store

In-Memory Virtual Columns

In-MemoryJSON Format

{"Theater":"AMC 15","Movie":”Sully","Time“:2016-09-09T18:45:00","Tickets":{

"Adults":2}

}

Relational Virtual JSON


In-Memory Dynamic ScansBetter Columnar Scan Performance

• Fast parallelization of simple scans using light-weight threads

• Scan Multiple IMCUs at a time- Multiple threads per scan process

• Number of active threads dynamically controlled by Resource Manager

• Fully leverage CPU resources to maximize columnar scan performance

• Up to 2X performance gains

SELECT SUM(total) FROM salesWHERE region = ‘CA’;

SALESIn-Memory Column Store

Resource Manager

controlled


• External Tables allow transparent access to data outside the DB

• In-Memory For External Tables populates external data into the IM column store for ultra-fast analytics

• All In-Memory Optimizationsapply- Vector processing, JSON

expressions extend transparently to external data

• Up to 100X faster

In-memoryExternal

Tables

RDBMS

In-memory Database

Tables

External Data

DB TABLES

In-Memory For External TablesFast Analytics on External Data

Object Storage

Files

Hadoop


• In-Memory optimized format for NUMBER columns- Instead of software-implemented,

variable-width ORACLE NUMBERs- Enabled using new parameter

inmemory_optimized_arithmetic

• SIMD Vector Processing on optimized inmemory number format

• Aggregation and Arithmetic operators can improve up to 40X

In-Memory Optimized ArithmeticBlazing Fast Numeric operation

Price1.52

4.051045.22

Price 02 35 1F

Price 05 06

Price 0B

Price 2E 17 15

à 1.52

à 4.05

à 10

à 45.22

Oracle Number (SUM) SUM(Price)

Slower Row-by-Row Oracle Number Processing

FASTER SIMD Vector Processing of In-Memory Numbers

SIMD

SUM

SUM(Price)

{


Automatic In-Memory• Eliminates trial and error regarding in-

memory area contents • Constant background action:

- Classifies data as hot, intermediate or cold- Hotter in-memory tables automatically

populated- Colder in-memory tables

automatically removed - Intelligent algorithm takes into account

space-benefit tradeoffs

• Controlled by new parameter inmemory_automatic_level

• Useful for autonomous cloud services since no user intervention required

In-Memory Column Store

Hot Data Intermediate Data Cool Data

Populate

Hot Table

RemoveCold Table

The DBA

Hot Data

ReactRemove cold data,Populate hot data


Where can I get more information?


https://blogs.oracle.com/in-memory/dbim-resources


Join the Conversation

https://twitter.com/TheInMemoryGuy

Database In-Memory Blog

oracle.com – Database In-Memory

Database In-Memory YouTube Channel

Ask TOM Database In-Memory Office Hours

Database In-Memory Guide (Documentation)

Additional ResourcesDatabase In-Memory Information

https://twitter.com/db_inmemory

http://www.oracle.com/goto/dbim.html


https://twitter.com/TheInMemoryGuy

https://blogs.oracle.com/In-Memory

https://www.youtube.com/channel/UCSYHgTG68nrHa5aTGfFH4pA

https://devgym.oracle.com/pls/apex/dg/office_hours/681

https://docs.oracle.com/en/database/oracle/oracle-database/18/inmem/index.html

https://twitter.com/db_inmemory

http://www.oracle.com/goto/dbim.html

oracle openworld 2019 › technetwork › database › in-memory › ...- set _smm_auto_max_io_size...

Documents