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

Transcript

1. Oracle Database In-Memory Oracle Technology Night
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, 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
3. 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
4. 4 Database In-Memory (DBIM) リアルタイム 分析 実装が簡単 メモリによって制限されない アプリケーションの変更なし 100X ミックスワークロード の高速化 運用システムで 分析を実行する リアルタイムのビジネス 決定を可能にする 100X リスク・フリー 実証済みの スケールアウト、 可用性、セキュリティ Copyright © 2020, Oracle and/or its affiliates.
5. vs. Database In-Memory ( ) • – : – ( ) • – : – SALES Query SALES Query Query Copyright © 2020, Oracle and/or its affiliates. 5
6. Copyright © 2020, Oracle and/or its affiliates. • ( ) • • OLTP • • Oracle Database • Buffer Cache IM SALES SALES SALES Database In-Memory 6
7. • 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
8. Copyright © 2020, Oracle and/or its affiliates. • In-Memory – OTN (11.2.0.3 DB ) • AWR ASH DB • IM Note: Tuning Pack In-Memory Database In-Memory 8
9. • • DBMS_COMPRESSION • MEMCOMPRESS • In-Memory Database In-Memory Copyright © 2020, Oracle and/or its affiliates. 9
10. OLTP • 1 10 20 • • IM • IM • • ( ) 1 – 3 OLTP 10 – 20 1 – 3 OLTP ( ) Database In-Memory Copyright © 2020, Oracle and/or its affiliates. 10
11. Copyright © 2020, Oracle and/or its affiliates. 11 • • • - - ( ) - • • SQL - 12.2 IM DBIM ( ) Database In-Memory 10 20%
12. : - - - Group BY GroupBy Copyright © 2020, Oracle and/or its affiliates. 12 DBIM Database In-Memory
13. 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
14. Database In-Memoryとは Program agenda 1 2 3 4 Database In-Memoryの基本機能 Database In-Memoryの拡張機能 Database In-Memory Base Level 14 Copyright © 2020, Oracle and/or its affiliates.
15. Copyright © 2020, Oracle and/or its affiliates. 15 • • DBIM • • Real Application Clusters • • • Database In-memory
16. Copyright © 2020, Oracle and/or its affiliates. • - - - Undo Redo • MView - - ( IM ) • - 2 20 SALES SALES 16 Store ID Amount
17. • 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 /
18. Copyright © 2020, Oracle and/or its affiliates. • - - 20c IM • 1. INMEMORY 2. ALTER TABLE sales INMEMORY NO INMEMORY (delivery_note); 18
19. (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 ( )
20. • - • INMEMORY - 20c • ( ’NONE’ ) - IMCO (IM ) ( 2 3 ) • (g)V$IM_SEGMENTS (Tips) Copyright © 2020, Oracle and/or its affiliates. 20 A B C A B C E A
21. 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
22. Copyright © 2020, Oracle and/or its affiliates. PL/SQL DBMS_INMEMORY. POPULATE_WAIT(); • DBMS_INMEMORY POPULATE_WAIT • • API – SLA 22
23. Copyright © 2020, Oracle and/or its affiliates. Database In-Memory SIMD 1 CPU ベクター・レジスタ 複数の データを ロード 一度の命 令で全て の値をベ クター演 算 CPU CA CA CA CA 23
24. 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
25. Copyright © 2020, Oracle and/or its affiliates. • : store_id 8 - CU ( ) - Min / Max CU - Memory SALES In-Memory ! 25
26. Copyright © 2020, Oracle and/or its affiliates. SIMD ベクター・レジスタ 一度の命令 で全ての値 をベクター演 算 CPU CA CA CA CA : > 100 • CPU • SIMD - • CPU / - / Memory 26
27. • メモリによる高速化 • 必要な列のみをスキャン してフィルターする • ディクショナリ圧縮 • ベクター命令 (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)
28. Copyright © 2020, Oracle and/or its affiliates. • ( ) - - SIMD 28
29. • 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
30. Copyright © 2020, Oracle and/or its affiliates. • • (JOIN FILTER CREATE & USE) • Exadata • 30 (FACT)
31. Copyright © 2020, Oracle and/or its affiliates. • DBIM • 31
32. 例: アウトレットでの靴の売上を集計 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) 結合キー
33. 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.
34. 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
35. Copyright © 2020, Oracle and/or its affiliates 35 • • • •
36. • ( ) • • IMCU • IMCU - インメモリ列ストア バッファ・キャッシュ C1 C2 C3 IMCU トランザクション・ ジャーナル C1 C2 C3 Update 3 Invalid Commit; IMCU Copyright © 2020, Oracle and/or its affiliates. 36 DML (Update/Delete )
37. 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
38. • • 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
39. (DUPLICATE ALL) D D D D F D D D D F D D D D F Engineered Systems Tip: DBIM RAC Copyright © 2020, Oracle and/or its affiliates. 39
40. 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
41. TDE IM • - IM ◦ IM - IM • - IM - IM Oracle Virtual Private Database VPD • / • VPD - DBIM SGA データベース バッファキャッシュ IM列ストア Disk I/O / Copyright © 2020, Oracle and/or its affiliates. 41
42. DWH ETL ETL OLTP DWH ETL DB OLTP レプリケーション （GoldenGate, 12.2でActive DataGuardをサポート） ①既存DWHをインメモリ化（現在の主流事例） ②既存（新規）データマートをインメモリ化 ③OLTPシステムを直接インメモリ化 • 分析、レポーティングの 高速化 • ダッシュボード画面の高速化 （経営／業務系） • リアルタイム分析の促進 ④基幹系OLTPシステムとの分離構成 既存システムを極力変更しない 分析処理を負荷分散 Copyright © 2020, Oracle and/or its affiliates. 42
43. Database In-Memoryとは Program agenda 1 2 3 4 Database In-Memoryの基本機能 Database In-Memoryの拡張機能 Database In-Memory Base Level 43 Copyright © 2020, Oracle and/or its affiliates.
44. 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
45. 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
46. Copyright © 2020, Oracle and/or its affiliates. • • • 1 Month In-Memory Production Standby 1 Year In-Memory 46 Active Data Guard In-Memory IM
47. Copyright © 2020, Oracle and/or its affiliates. • • - - • 18c ( ) SALES VEHICLES NAME : 47 (Join Groups): ( 61 )
48. Copyright © 2020, Oracle and/or its affiliates. • - • - SQL ( ) - ( ) • - 1 CPU • 3-5 Net = Price + Price * Tax : Sales Tax Price Price + Price * Tax 48 : ( 61 )
49. 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
50. Copyright © 2020, Oracle and/or its affiliates. • • IMCU - • Resource Manager - 19c Resource Manager • CPU • 2 SELECT SUM(total) FROM sales WHERE region = ‘CA’; SALES Resource Manager 50 In-Memory ( )
51. 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 ( )
52. 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 ( )
53. 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
54. 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
55. Database In-Memoryとは Program agenda 1 2 3 4 Database In-Memoryの基本機能 Database In-Memoryの拡張機能 Database In-Memory Base Level 55 Copyright © 2020, Oracle and/or its affiliates.
56. Copyright © 2020, Oracle and/or its affiliates. • Database In-Memory (DBIM) 16GB IM • Base Level DBIM • DBIM Base Level • 20.3 • 19c RU(19.8) 56
57. 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
58. Copyright © 2020, Oracle and/or its affiliates. • INMEMORY_FORCE = BASE_LEVEL CDB • PDB INMEMORY_FORCE = BASE_LEVEL • INMEMORY_FORCE = BASE_LEVEL (DEFAULT ) • IM 1 16GB - RAC 16GB - CDB 16GB 58
59. 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
61. Our mission is to help people see data in new ways, discover insights, unlock endless possibilities.