How Oracle Globally Distributed Database Delivers Extreme Scale, Performance, and Data Tenancy and Sovereignty APAC Tour '23 December 11, 2023 Auckland, New Zealand @ViscosityNA www.viscosityna.com Sean Scott

Database Reliability Engineering 
 Business Continuity ⁘ HA & DR 
 Automation ⁘ Observability Real Application Clusters ⁘ Data Guard ⁘ Sharding Containerization ⁘ Terraform ⁘ Ansible Exadata & Engineered Systems AHF ⁘ TFA ⁘ GIMR ⁘ CHA Sean Scott Oracle ACE Director Data on Kubernetes Community Ambassador Managing Principal Consultant Viscosity North America @ViscosityNA www.viscosityna.com

No content

An Open Community for 
 Data on Kubernetes https://dok.community

@ViscosityNA www.viscosityna.com Oracle on Docker Running Oracle Databases in Linux Containers Free sample chapter: 
 https://oraclesean.com

No content

BOI - SEA 642 km SEA - ICN 8,394 km ICN - NRT 1,260 km NRT - MEL 8,144 km MEL - AKL 2,644 km AKL - LAX 10,467 km LAX - BOI 1,085 km 32,635 km

@ViscosityNA www.viscosityna.com A long time ago at a conference far, far away…

@ViscosityNA www.viscosityna.com Sharding is complicated!

@ViscosityNA www.viscosityna.com Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Linear Scalability with Oracle Sharding and Data Guard Swingbench Order-Entry Application # of Shards TPS 38 0 2,000,000 4,000,000 6,000,000 8,000,000 10,000,000 12,000,000 50 100 150 200

@ViscosityNA www.viscosityna.com Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Oracle Sharding Demo on Oracle Cloud Infrastructure • Elastically scaled-out to 200 shards on Oracle Cloud – Demonstrated linear scalability of Relational transactions – Demonstrated 11 Million transactions per sec that includes: • 4.5 Million Read-Write Transactions per sec across all 100 Primary shards • 6.5 Million Read-Only Transactions per sec across all 100 Active Standby shards • MAA Sharding provides highest availability – Each shard is protected by Data Guard Fast-Start Failover across Availability Domains – Single Shard Failure resulted in 100% availability for 99% of the application • 1% of the application experienced only 15 seconds blackout Conclusions

@ViscosityNA www.viscosityna.com Copyright © 2018, Or Infrastructure • Infrastructure – Each shard hosted on dedicated server Av (10 Component Resources per shard CPU 36 Cores Memory 512 GB Flash 12.8 TB NVMe Network 10 GbE Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Database Relational Oracle Cloud server 37 Oracle Cloud Infrastructure Availability Domain 1 (100 Primary Shards) Availability Domain 2 (100 HA Standby Shards) Shard-level Active Data Guard (Max Availability) shard Benchmark Topology

@ViscosityNA www.viscosityna.com Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | • Korea's number one mobile operator • 65 billion transactions per day • 18TB of data per day • All data processing occurs on Oracle RAC Database running on Exadata 40 • World’s largest stock exchange • ~1 billion database transactions per day • 180,000 messages/sec • ~ 15 TB of data per day • All data captured and processed in an Oracle RAC Database on Exadata Massive Scale Workloads with Oracle Database • One of world's largest law enforcement orgs • ~3 billion transactions per day • ~32 billion queries per day • Database is over 1PB • Deployed on Oracle RAC Database on Exadata

@ViscosityNA www.viscosityna.com People attending this session Environments with scale & volume issues 


@ViscosityNA www.viscosityna.com People attending this session Environments with other issues potentially solved by sharding

@ViscosityNA www.viscosityna.com NA 
 EU Restricted 
 APAC 
 Locate Data to Satify Tenancy Regulations

@ViscosityNA www.viscosityna.com Latency SLA Range NA Latency SLA Range EU Latency SLA Range APAC Locate Data to Meet Latency Objectives

Tiered Service Models • Operate separate infrastructure to 
 satisfy performance, availability SLA • Locate data with intention 
 based on free vs. paid users 
 or by subscription level Free Tier 
 No Performance/Availability SLA Bronze Tier Silver Tier Gold

@ViscosityNA www.viscosityna.com Database Shard 1 Shard 2 Shard 3 Multiple Physical Shards Appear to be One Database Application, Users

@ViscosityNA www.viscosityna.com System 
 Managed Composite User Defined Massive scale & performance, no control of data location Top level control of data location Complete control over data location Shard by 
 Consistent Hash Shard by 
 Value/Range Shard by Value/Range + Hash

@ViscosityNA www.viscosityna.com User Defined Complete control over data location

@ViscosityNA www.viscosityna.com Sharding = Partitioning

@ViscosityNA www.viscosityna.com Database Partition Candidates Customers Accounts Transactions Exchange Rates Currencies

@ViscosityNA www.viscosityna.com Database Customers Transactions Partition NA Partition APAC Partition EU Customers Accounts Partition NA Partition APAC Partition EU Customers Partition NA Partition APAC Partition EU

@ViscosityNA www.viscosityna.com Database EU Partitions Customers Accounts Transactions NA Partitions Customers Accounts Transactions APAC Partitions Customers Accounts Transactions

@ViscosityNA www.viscosityna.com Shard Database Database EU EU Shard Customers Accounts Transactions Database NA NA Shard Customers Accounts Transactions Database APAC APAC Shard Customers Accounts Transactions

@ViscosityNA www.viscosityna.com Shard Database Database EU Database APAC Database NA

@ViscosityNA www.viscosityna.com Shard Database GDSCTL
 SQL*Plus OEM Cloud Control Database NA Database EU Database APAC Shard Catalog Database

@ViscosityNA www.viscosityna.com Database Partitioned Tables Customers Accounts Transactions Exchange Rates Currencies Common Tables

@ViscosityNA www.viscosityna.com Database Exchange Rates Currencies Common Tables Database NA Database EU Database APAC Database Shards

@ViscosityNA www.viscosityna.com Shard Database Database NA MViews Database EU MViews Database APAC Shard Catalog Duplicated Tables Exchange Rates Currencies MViews

@ViscosityNA www.viscosityna.com Shard Database Database NA Database EU Database APAC Shard Directors z z Connection Pool Client

@ViscosityNA www.viscosityna.com Shard Database Reporting Client Database NA Database EU Database APAC Query Coordinator Result NA EU APAC

@ViscosityNA www.viscosityna.com Shard Database DataGuard Database EU DataGuard Database NA DataGuard Database APAC Database EU Database NA Database APAC

@ViscosityNA www.viscosityna.com Shard Database Catalog 
 ADG Database EU Database APAC Catalog Database NA

Application Design Considerations • Transactional only • Oracle integrated connection pools: • JDBC, UCP, OCI, ODP.NET • Direct Connections • <21c - Requires sharding key • <21c - Without key, must use query coordinator • 21c+ - No shard key needed for JDBC sharding

Schema Design Considerations • Well-defined data model • Parent-child design • Access primarily by the sharding key • Sharding key present in all sharded tables/levels • Join on sharding key • Many attributes become fixed once deployed • 21c - Sharding Advisor Schema Analysis Tool

Flexible Architecture • Mix versions • RAC or single instance • ASM or disk • On-premises, Cloud, Hybrid • 18c - Mix Legacy and Multi-Tenant • 19c - Multiple PDB in a CDB • 21c - Federated Sharding

@ViscosityNA www.viscosityna.com Questions

@ViscosityNA www.viscosityna.com Contact Me: [email protected] https://linktr.ee/oraclesean