津島博士のOracle Database In-Memory (DBIM) の基礎と最新情報

Slide 1

Slide 1 text

Oracle Database In-Memory Oracle Technology Night

Slide 2

Slide 2 text

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. Safe harbor statement Copyright © 2020, Oracle and/or its affiliates. 2

Slide 3

Slide 3 text

Program agenda 3 Copyright © 2020, Oracle and/or its affiliates. Database In-Memoryの基本機能 Database In-Memoryとは Database In-Memoryの拡張機能 Database In-Memory Base Level Database In-Memoryの基本機能 Database In-Memoryとは Database In-Memoryの拡張機能 Database In-Memory Base Level 2 3 4 1 2 3 4 1

Slide 4

Slide 4 text

4 Database In-Memory (DBIM) リアルタイム分析実装が簡単メモリによって制限されないアプリケーションの変更なし 100X ミックスワークロードの高速化運用システムで分析を実行するリアルタイムのビジネス決定を可能にする 100X リスク・フリー実証済みのスケールアウト、可用性、セキュリティ Copyright © 2020, Oracle and/or its affiliates.

Slide 5

Slide 5 text

Slide 6

Slide 6 text

Slide 7

Slide 7 text

• SGA • INMEMORY_SIZE ( 100M ) • SGA_TARGET IM (Doc ID 1903683.1 ) • ( ) – (12.2) 1. 使用するメモリ容量を設定 inmemory_size = XXX GB 2. メモリー上に格納するテーブル、パーティションを選択 alter table | partition … inmemory; 3. インデックスを削除（分析用） IM inmemory_size = XXX GB alter table | partition … inmemory; システム・グローバル領域(SGA) バッファキャッシュ共有プールログバッファラージプールその他インメモリ・カラムストア Copyright © 2020, Oracle and/or its affiliates. 7 Database In-Memory

Slide 8

Slide 8 text

Slide 9

Slide 9 text

Slide 10

Slide 10 text

Slide 11

Slide 11 text

Slide 12

Slide 12 text

Slide 13

Slide 13 text

Database In-Memory Oracle Database Enterprise Edition Database In-Memory 12.1 (12.1.0.2) Oracle Database : - Database Cloud Service – Virtual Machines: Extreme Performance - Database Cloud Service – Bare Metal: Extreme Performance - Exadata Cloud Service - Exadata Cloud at Customer - Autonomous Data Warehouse (Flash only) - Note: DBIM Database In-Memory Copyright © 2020, Oracle and/or its affiliates. 13

Slide 14

Slide 14 text

Slide 15

Slide 15 text

Slide 16

Slide 16 text

Slide 17

Slide 17 text

• INMEMORY • - - - - • - IOT - Hash clusters - LOB CREATE TABLE customers …… PARTITION BY LIST (PARTITION p1 …… INMEMORY, (PARTITION p2 …… NO INMEMORY); ALTER TABLE sales INMEMORY; ALTER TABLE sales NO INMEMORY; OLTP Copyright © 2020, Oracle and/or its affiliates. 17 /

Slide 18

Slide 18 text

Slide 19

Slide 19 text

(ora_w001_orcl) ( ) • - INMEMORY_MAX_POPULATE_SERVERS • • - ALTER TABLE sales INMEMORY PRIORITY HIGH; Note CRITICAL HIGH CRITICAL MEDIUM HIGH LOW MEDIUM NONE → Copyright © 2020, Oracle and/or its affiliates. 19 ( )

Slide 20

Slide 20 text

Slide 21

Slide 21 text

V$IM_SEGMENTS • • • SQL> SELECT segment_name, populate_status, inmemory_priority, inmemory_size, bytes_not_populated FROM v$im_segments; SEGMENT_NAME POPULATE_STATUS INMEM_PRIORITY INME_SIZE BYTES_NOT_POPULATED ------------ --------------- -------------- ----------- ------------------- ACCOUNTS STARTED HIGH 196606 2434886912 SALES COMPLETED CRITICAL 135790592 0 Copyright © 2020, Oracle and/or its affiliates. 21

Slide 22

Slide 22 text

Slide 23

Slide 23 text

Slide 24

Slide 24 text

Copyright © 2020, Oracle and/or its affiliates. 24 • • - NO MEMCOMPRESS ( ) - FOR DML - FOR QUERY LOW/HIGH - FOR CAPACITY LOW/HIGH • (2 20 ) - Query Low High ( ) - Capacity Low High ”zip-like” • NAME ID AUDI 0 BMW 1 CADILLAC 2 PORSCHE 3 TESLA 4 VW 5 IMCU IMCU IMCU IMCU IMCU IMCU IMCU IMCU 2 2 2 2 2 0 0 0 1 1 1 1 4 4 4 4 5 5 5 5 3 3 3 4 3 3 4 1 5 2 4 2 VEHICLES ALTER MATERIALIZED VIEW mv1 INMEMORY MEMCOMPRESS FOR QUERY LOW; CREATE TABLE trades (name varchar(20), desc varchar(200)) INMEMORY MEMCOMPRESS FOR DML(desc); where NAME = ‘BMW’ where NAME = 1

Slide 25

Slide 25 text

Slide 26

Slide 26 text

Slide 27

Slide 27 text

• メモリによる高速化 • 必要な列のみをスキャンしてフィルターする • ディクショナリ圧縮 • ベクター命令 (SIMD) Copyright © 2020, Oracle and/or its affiliates. •結合で件数があまり削減できないクエリを、ファクト表の高速スキャン中に結合と集計を同時に行う (第54回) •スター結合の小さな表(ディメンション) に一致する値を、大きな表 (ファクト) のフィルタとして検索する (Right-deep join、ブルーム・フィルタ) <=第46回、第54回 HASH JOIN Table A Table B SALES STATE = CA 27 Customer 2.25GB Supplier 168MB Lineorder 338GB Date_DiM 0.2MB Part 144MB (FACT)

Slide 28

Slide 28 text

Slide 29

Slide 29 text

• SQL> select * from SALES where ORDER_DATE between '2013-01-01' and '2014-01-01'; ---------------------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | SORT AGGREGATE | | | | | 2 | VIEW | VW_TE_2 | | | | 3 | UNION-ALL | | | | | 4 | PARTITION RANGE SINGLE | | 1 | 1 | |* 5 | TABLE ACCESS INMEMORY FULL| SALES | 1 | 1 | | 6 | PARTITION RANGE SINGLE | | 2 | 2 | |* 7 | INDEX RANGE SCAN | IDX_SALES | 2 | 2 | ---------------------------------------------------------------------- 5 - inmemory("ORDER_DATE">=TO_DATE('2013-01-01 00:00:00','syyyy-mm-dd hh24:mi:ss') AND "ORDER_DATE"<=TO_DATE('2014-01-01 00:00:00','syyyy-mm-dd hh24:mi:ss')) filter("ORDER_DATE">=TO_DATE('2013-01-01 00:00:00','syyyy-mm-dd hh24:mi:ss') AND "ORDER_DATE"<=TO_DATE('2014-01-01 00:00:00','syyyy-mm-dd hh24:mi:ss')) 7 - access("ORDER_DATE">=TO_DATE('2013-01-01 00:00:00','syyyy-mm-dd hh24:mi:ss') AND "ORDER_DATE"<=TO_DATE('2014-01-01 00:00:00','syyyy-mm-dd hh24:mi:ss')) Table Expansion ( ) Copyright © 2020, Oracle and/or its affiliates. 29

Slide 30

Slide 30 text

Slide 31

Slide 31 text

Slide 32

Slide 32 text

例: アウトレットでの靴の売上を集計 Sales Stores Products インメモリ・レポートアウトライン BRAND REGION $ $$ $ $$$ Footwear Sales Outlets ▪ ( ) ▪ Group By (Vector Group By) Copyright © 2020, Oracle and/or its affiliates. 32 SELECT st.region, p.brand, sum( s.sales ) FROM stores st, products p, sales s WHERE st.id = s.store_id AND p.id = s.prod_id AND st.Stype = ‘Outlet’ ANd p.category = ‘Footwear’ GROUP BY st.region, p.brand フィルタ条件グループキー (Key Vector) 結合キー

Slide 33

Slide 33 text

1 2 3 4 5 6 1 2 3 TIME_ID 10 20 15 Stores Products Sales 1 2 3 4 5 6 7 … 0 3 1 5 0 4 3 … 1 2 3 4 5 6 7 8 9 10 … 0 1 3 2 1 0 0 0 1 3 … Stores DGK (REGION) インメモリ・レポート・アウトライン Products DGK (BRAND) PROD_ID BRAND (Key Vector) STORE_ID REGION (Key Vector) PROD_ID SALES STORE_ID ①フィルタ条件 ②結合キー＋Key Vector配列を作成しフィルタ条件に一致しない Key Vector値は「0」を設定 (Key Vector値＝グループ集計カラム値) ③DGK(Dense Grouping Key)値で構成される集計値を格納するインメモリ配列の作成 Sales 33 Copyright © 2020, Oracle and/or its affiliates.

Slide 34

Slide 34 text

Copyright © 2020, Oracle and/or its affiliates. (Key Vector Use Vector Group By) Region Brand Sales 34 ------------------------------------------------------------------------- | Id | Operation | Name | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TEMP TABLE TRANSFORMATION | | | 2 | LOAD AS SELECT | SYS_TEMP_0FD9D662E_2D4940 | | 3 | VECTOR GROUP BY | | | 4 | KEY VECTOR CREATE BUFFERED | :KV0000 | |* 5 | TABLE ACCESS INMEMORY FULL | PRODUCTS | | 6 | LOAD AS SELECT | SYS_TEMP_0FD9D662E_2D4940 | | 7 | VECTOR GROUP BY | | | 8 | KEY VECTOR CREATE BUFFERED | :KV0001 | |* 9 | TABLE ACCESS INMEMORY FULL | STORES | | 10 | HASH GROUP BY | | |* 11 | HASH JOIN | | | 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662F_2D4940 | |* 13 | HASH JOIN | | | 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662E_2D4940 | | 15 | VIEW | VW_VT_80F21617 | | 16 | VECTOR GROUP BY | | | 17 | HASH GROUP BY | | | 18 | KEY VECTOR USE | :KV0000 | | 19 | KEY VECTOR USE | :KV0001 | |* 20 | TABLE ACCESS INMEMORY FULL| SALES | ------------------------------------------------------------------------- ①ディメンション（PRODUCTS, STORES）表をスキャンして Key Vector (結合とグループ・キー配列) の作成と Vector Group Byの実行(DGK の生成）を行い、結果に必要な列を一時表に保存する ②KEY VECTORでファクト（SALES）表の絞り込みと集計（DGKに設定） ③PRODUCTSとSTORESのグルーピング列の値を求めるために一時表を結合 DGK

Slide 35

Slide 35 text

Slide 36

Slide 36 text

Slide 37

Slide 37 text

RAC • • CPU • Active-Active - DBIM RAC • RAC • - DOP DBIM Oracle Real Application Clusters (RAC) RAC Copyright © 2020, Oracle and/or its affiliates. 37 CPU CPU CPU CPU CPU CPU Active Active Active IM列ストア IM列ストア IM列ストアメモリー空間パラレル・クエリー RAC

Slide 38

Slide 38 text

• • INMEMORY_SIZE • - INMEMORY_SIZE 0 • INMEMORY RAC (DISTRIBUTE & DUPLICATE ) - DISTRIBUTE (AUTO, ROWID RANGE, [SUB]PARTITION) - DUPLICATE ◦ NO DUPLICATE , DUPLICATE , DUPLICATE ALL ( ) DUPLICATE Oracle Engineered System DBIM RAC インスタンス1 インメモリ・列ストア P1 P1 P3 P2 パーティション表インメモリ・列ストア P3 インメモリ・列ストア P2 P3 P2 P1 INMEMORY_SIZE パラメータインスタンス2 インスタンス3 ポピュレーション方法（例） ⚫ 分散方法：パーティションごと ⚫ 複製方法：データの二重化 Copyright © 2020, Oracle and/or its affiliates. 38

Slide 39

Slide 39 text

Slide 40

Slide 40 text

ERP CRM DW Container Database INMEMORY_SIZE=0G INMEMORY_SIZE=4G INMEMORY_SIZE=16G CDB INMEMORY_SIZE=20G Oracle Multitenantで定義されたCDB構成にインメモリ領域を定義する場合、CDB全体の設定値（メモリ領域確保）と各PDB単位の設定値（メモリ領域確保はされず予約のみ）を行い、PDB単位で各インメモリ領域を占有する CDBのインメモリ領域は物理的にインメモリ領域を確保するバッファ・キャッシュと異なりインメモリ領域はPDB単位に占有 PDBのインメモリ領域の合計値がCDBのインメモリ領域を超える設定（オーバー・サブスクリプション）も可能 DBIM Copyright © 2020, Oracle and/or its affiliates. 40

Slide 41

Slide 41 text

Slide 42

Slide 42 text

DWH ETL ETL OLTP DWH ETL DB OLTP レプリケーション（GoldenGate, 12.2でActive DataGuardをサポート） ①既存DWHをインメモリ化（現在の主流事例） ②既存（新規）データマートをインメモリ化 ③OLTPシステムを直接インメモリ化 • 分析、レポーティングの高速化 • ダッシュボード画面の高速化（経営／業務系） • リアルタイム分析の促進 ④基幹系OLTPシステムとの分離構成既存システムを極力変更しない分析処理を負荷分散 Copyright © 2020, Oracle and/or its affiliates. 42

Slide 43

Slide 43 text

Slide 44

Slide 44 text

Database In-Memory • Pure Columnar Format Caching • Scan & Filter • • • SIMD • • • • JSON/OSON • - Exadata flash (HCC) • • Fast-Start • Active Data Guard 12.1 12.2 18c • • • • • Memoptimized Rowstore – Fast Lookup 19c • • (Hive & HDFS) • Memoptimized Rowstore – Fast Ingest 12.2 18c 12.1 19c 20c 20c • Self Managing In-Memory • In-Memory Spatial Analytics • In-Memory Text Analytics • Hybrid Scans • Vector Joins Self-Managing, Convergence , , Copyright © 2020, Oracle and/or its affiliates. 44

Slide 45

Slide 45 text

Copyright © 2020, Oracle and/or its affiliates. In-Memory Columnar scans In-Flash Columnar scans • Exadata Exadata Flash Cache DBIM – – CELLMEMORY ( ) • OLTP (Exadata System Software 18.1 ) • • – CPU • Up to 1.5 TB DRAM per Server Up to 25.6 TB Flash per Server 45 IM

Slide 46

Slide 46 text

Slide 47

Slide 47 text

Slide 48

Slide 48 text

Slide 49

Slide 49 text

Copyright © 2020, Oracle and/or its affiliates. • JSON JSON ( ) • • JSON • JSON_VALUE • JSON IM • ) "movie.name" Jurassic” • 20-60 In-Memory In-Memory JSON { "Theater":"AMC 15", "Movie":”Sully", "Time“:2016-09-09T18:45:00", "Tickets":{ "Adults":2 } } Relational Virtual JSON 49 / : In-Memory JSON

Slide 50

Slide 50 text

Slide 51

Slide 51 text

Copyright © 2020, Oracle and/or its affiliates. • DB • In-Memory IM • - JSON • 100 • 19c - ORACLE_HIVE ORACLE_BIGDATA - - In-memory 外部表 RDBMS In-memory データベース表外部データ DB TABLES Object Storage Files Hadoop (19c ) 51 In-Memory ( )

Slide 52

Slide 52 text

Copyright © 2020, Oracle and/or its affiliates. • NUMBER (Query Low ) - NUMBER - (inmemory_optimized_arithmetic) • SIMD • 40 • NUMBER • 15% 20% Price 02 35 1F Price 1.52 4.05 10 45.22 Price 05 06 Price 0B Price 2E 17 15 → 1.52 → 4.05 → 10 → 45.22 Oracle Number (SUM) SUM(Price) Oracle SIMD S I M D S U M SUM(Price) { 52 In-Memory ( )

Slide 53

Slide 53 text

Copyright © 2020, Oracle and/or its affiliates. • “trial and error” • : - (hot, intermediate, cold) - Hot - Cold - • inmemory_automatic_level - ADO ( ) • In-Memory Hot Data Intermediate Data Cold Data Remove Cold Table The DBA Hot Data React Remove cold data, Populate hot data 53

Slide 54

Slide 54 text

Copyright © 2020, Oracle and/or its affiliates. 54 In-Memory Spatial and Text In-Memory ベクター結合 In-Memory ハイブリッド・スキャン Self-Managing In-Memory In-Memory (Buffer Cache) • • 10 In-Memory Spatial Relational Text • 10 • 3 Resume Experience Education • SIMD • 5-10 In-Memory • / • INMEMORY SALES ITEMS Vector Join Database In-Memory 20c

Slide 55

Slide 55 text

Slide 56

Slide 56 text

Slide 57

Slide 57 text

System Global Area (SGA) Buffer Cache Large Pool Log Buffer Shared Pool In-Memory Area (Max 16GB) Other Copyright © 2020, Oracle and/or its affiliates. • INMEMORY_FORCE - BASE_LEVEL • INMEMORY_SIZE 16GB • “In-Memory Base Level” 57 57

Slide 58

Slide 58 text

Slide 59

Slide 59 text

Copyright © 2020, Oracle and/or its affiliates. • MEMCOMPRESS FOR QUERY LOW • (Automatic In-Memory) • ( ) - • Exadata CELLMEMORY • ( Base Level ) - DBA_FEATURE_USAGE_STATISTICS • Authorized Cloud Environments ( ) 59

Slide 60

Slide 60 text

No content

Slide 61

Slide 61 text

Our mission is to help people see data in new ways, discover insights, unlock endless possibilities.