Oracle Database Options : Performance

Oracle Databaseのオプション機能であるPertitioning, Database In-Memory, Advanced Compressionの概要資料です。

Transcript

1. Oracle Database Options 1 : 2021 2
2. Oracle Java Oracle Corporation Oracle Java Oracle Corporation Copyright © 2021 Oracle and/or its affiliates. 2
3. Database Core Enterprise Edition Standard Edition 2 Options & Other Products • Standard Edition 2は2ソケットのサー バーまで搭載可能 • Enterprise EditionにはOptionが追加 可能 Multitenant Diagnostics Pack Tuning Pack Lifecycle Management Pack Cloud Management Pack Engineered Systems Audit Vault & DB Firewall Advanced Security Database Vault Label Security KeyVault セキュリティ Database In-memory Partitioning Real Application Clusters Active Data Guard GoldenGate Advanced Compression TimesTen 高可用性 性能 マルチテナント • Option製品はEnterprise Editionと同じ数量を購入する必要あり • TimesTen Application Tier Database CacheのみOption稼働のプロセッサ数 • Spatial & Graph, Advanced Analyticsは2019/12/4より基本機能に変更 • GoldenGate, KeyVault, Audit Vault & Database Firewallは別製品 Exadata Database Appliance Zero Data Loss Recovery Appliance Big Data Appliance • ExadataとDatabase Applianceは Enterprise EditionとOptionライセンスをそのま ま搭載 3 Copyright © 2021 Oracle and/or its affiliates. Real Application Testing 運 用 管 理
4. ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ 4 Copyright © 2021 Oracle and/or its affiliates.
5. 5 Copyright © 2021 Oracle and/or its affiliates. • • - - • - - • • • •
6. 6 Copyright © 2021, Oracle and/or its affiliates Oracle Database Enterprise Edition (CPU/ ) 1 2 4
7. 7 Copyright © 2021, Oracle and/or its affiliates Exadata X8M-2 (2019 ) 1RU Intel Xeon 2 CPU • Intel Xeon 8260 Cascade Lake (24 ) • 24 2 = 48 DRAM (DDR4) • 32GB DIMM 12 = 384GB • 32GB DIMM 24 = 768GB • 64GB DIMM 24 = 1536GB CPU 1RU 2
8. 8 Copyright © 2021, Oracle and/or its affiliates • SELECT • DML (INSERT/UPDATE/DELETE/MERGE) • PL/SQL • / • DDL (CREATE TABLE AS SELECT) • CREATE/ALTER INDEX / • Data Pump • RMAN BACKUP/RESTORE ( ) • RMAN RECOVER (REDO ) • ( ) • Data Guard REDO Apply
9. 1.Partitioning 1 , Exadata
10. Copyright © 2021, Oracle and/or its affiliates 10 ( ) SQL WHERE ( ) 1 SELECT * FROM t1 WHERE column1 BETWEEN value1 AND value2 2 3
11. Copyright © 2021, Oracle and/or its affiliates 11 (Interval*) : ( ), ( *) : : 2020 1 2020 2 ( ) function(c1), range, list function(c1), range, list hash(c1) = value1 hash(c1) = value2 * Interval
12. Copyright © 2021, Oracle and/or its affiliates 12 2020/01/01 2020/02/01 2020/03/01 2020/02/01 2020/01/01 2020/01/01 1 1 SQL 1
13. Copyright © 2021, Oracle and/or its affiliates 13 ( ) SQL WHERE SQL 1 2
14. Copyright © 2021, Oracle and/or its affiliates 14 Partitioning Oracle 8 Range partitioning Global Range indexes Static partition pruning : ADD, DROP, EXCHANGE Oracle 8i Hash partitioning Range-Hash partitioning Partition-wise joins Expanded maintenance: MERGE Oracle 9i List partitioning Global index maintenance Oracle 9i R2 Range-List partitioning SPLIT Oracle 10g Global Hash indexes Local Index maintenance Oracle 10g R2 100 Multi-dimensional pruning DROP TABLE Oracle 11g Virtual column based partitioning Reference partitioning - Interval partitioning - Partition Advisor - optimizer Oracle 11g R2 Hash-* partitioning Expanded REF partitioning “AND” pruning Multi-branch execution Oracle 12c R1 Interval-REF partitioning - Partition Maintenance on multiple partitions - Partial local and global indexes - Asynchronous global index maintenance for DROP/TRUNCATE - Online partition MOVE - Cascading TRUNCATE/EXCHANGE Oracle 12c R2 - - - - Online partition maintenance operations - Online table conversion to partitioned table - DDL - Filtered partition maintenance operations - Read only partitions - Create table for exchange
15. 2.Database In-Memory 12c
16. Copyright © 2021, Oracle and/or its affiliates 16 2 IM SALES SALES SALES 1 SALES 2 • • : • : (IM ) • • • •
17. Copyright © 2021, Oracle and/or its affiliates 17 ▪ – – ▪ – –
18. Copyright © 2021, Oracle and/or its affiliates 18
19. Copyright © 2021, Oracle and/or its affiliates 19 SQL select * from SALES where order_id = ‘ABC123’; select region, sum(amount) from SALES group by region; Oracle Database
20. Min 1 Max 3 Min 4 Max 7 Min 8 Max 12 Min 13 Max 15 C1 C2 C4 C5 C6 C3 ポイント1： 集計に必要なカラムのみアク セス＋効果的な圧縮技術によ り圧縮した状態で検索が可能 (ディクショナリ圧縮) ポイント3： 最新のプロセッサで搭載されてい るSIMDにより高速スキャン ポイント4： パラレル・クエリーとパーティション表 によりさらに高速化可能 ポイント2： インメモリ・ストレージ索引により最 小限のIMCUのみスキャン 例) where storeid > 8 ベクター・レジスタ 複数の データを ロード 一度の命令で 全ての値を ベクター演算 CPU CA CA CA CA Copyright © 2021, Oracle and/or its affiliates. 20
21. • • In-Memory • In-Memory Column Store OLTP • • 10 • Using Database In-Memory resulted in: • Triple the volume of data • No changes required to Business Objects reports • 50X performance improvement on reports - Reports that took days now return in less than 1 hour Using Database In- Memory resulted in: - Analytic queries up to 5X faster - Real-time analytics dashboard Using Database In-Memory resulted in: - Performance Gains: 1.8X to 12X - Space savings and reduced contention on DML by dropping analytic indexes 21 Copyright © 2021 Oracle and/or its affiliates
22. DWH ETL ETL OLTP DWH ETL DB OLTP レプリケーション （GoldenGate, 12.2以降でActive DataGuardをサポート） ①既存DWHをインメモリ化（現在の主流事例） ②既存（新規）データマートをインメモリ化 ③OLTPシステムを直接インメモリ化 • 分析、レポーティングの 高速化 • ダッシュボード画面の高速化 （経営／業務系） • リアルタイム分析の促進 ④基幹系OLTPシステムとの分離構成 既存システムを極力変更しない 分析処理を負荷分散 Copyright © 2021, Oracle and/or its affiliates. 22
23. 23 Die Mobiliar – Mixed Workload • • 50-240 • BOSCH – SAP CRM • • 2-20 DML( / / ) 2-3 • Villeroy & Boch – SAP BW • SAP BW COPA 30 – 33 • SAP Transaction 4 – 4,800 • S4/HANA AT&T WiFi – Data Warehouse • Business Objects 100 • ETL 50% • SAP Business Objects Copyright © 2021 Oracle and/or its affiliates
24. • Oracle Database 19c 19.8 Oracle Database 20c Preview • 16GB In-Memory column store (Enterprise Edition) • RAC 16GB • Multitenant CDB 16GB • • Compression – MEMCOMPRESS FOR QUERY LOW • Excluded columns – • Automatic In-Memory (AIM) • Exadata CELLMEMORY System Global Area (SGA) Buffer Cache Shared Pool Log Buffer Large Pool In-Memory Area (Max 16GB) Other 24 Copyright © 2021 Oracle and/or its affiliates
25. Application Application Application Application Application Application Oracle TimesTen In-Memory Database • • • • • • Oracle Database In-Memory Option • In-Memory Database • • • / • Exadata In-Memory for OLTP In-Memory for Analytics 25 Copyright © 2021 Oracle and/or its affiliates.
26. 3.Advanced Compression 11g (ADO) Copyright © 2021 Oracle and/or its affiliates 26
27. • 1/2 1/4 • • I/O • (ADO) • Copyright © 2021, Oracle and/or its affiliates 27 CPU I/O CPU I/O OLTP
28. 28 1. ➢ 2. ➢ 3. / ➢ 4. ➢ Copyright © 2021, Oracle and/or its affiliates I/O
29. Copyright © 2021, Oracle and/or its affiliates. 29 BBB AAA CCC 1, , 2, , 3, , 4, , 5,BBB, DDD • • • • • ( ) • • • • • • • • • CU
30. Copyright © 2021, Oracle and/or its affiliates 30 • DML • • OLTP - 2 4 • DML • • I/O
31. Copyright © 2021, Oracle and/or its affiliates 31 • – ADO (ADO) • ADO • ADO = ( ) ( ) ADO
32. Copyright © 2021, Oracle and/or its affiliates 32 / ADO Policy1: 90 ADO Policy2: 180 ALTER TABLE sales ILM ADD POLICY ROW STORE COMPRESS ADVANCED SEGMENT AFTER 90 days of creation; ALTER TABLE sales ILM ADD POLICY COLUMN STORE COMPRESS FOR ARCHIVE HIGH SEGMENT AFTER 180 days of no modification;
33. Copyright © 2021, Oracle and/or its affiliates 33 LOB LOB RMAN RMAN DEFAULTCOMPRESS COMPRESSION=METADATA_ONLY (ADO) Data Guard REDO ( ) Exadata Exadata Exadata Oracle Database Appliance Supercluster ZFSSA : EE EE-ES :
34. ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ ⚫ 34 Copyright © 2021 Oracle and/or its affiliates.