Tag Archives: Sharding SDB OracleSharding

AskTOM Office Hours: Oracle Sharding (Mar 26th, 2018 : 8 AM PST)

Exploring Oracle Sharding
AskTOM Office Hours offers free, open Q&A sessions with Oracle Database experts. Join Srinagesh Battula and other members of the Oracle Sharding team to get your questions about sharding in Oracle Database answered.
March 26, 2018 15:00 – 16:00 UTC
UTC 15:00 March 26 2018
US/Pacific 08:00 March 26 2018
US/Eastern 11:00 March 26 2018
Europe/London 16:00 March 26 2018
Asia/Calcutta 20:30 March 26 2018
Asia/Hong_Kong 23:00 March 26 2018
Australia/Sydney 02:00 March 27 2018
It’s the only one you’ll ever need for this Office Hours, taking you back to this page to join the next Q&A session, review upcoming sessions, and check out past sessions.

Visit the Zoom in advance of the session to make sure your browser is properly configured. But note that we will not be sending you the Zoom URL. Instead, you will come back to the AskTOM Office Hours page (URL above) to join the session.

We will take questions via chat (all audio lines will be muted). We will record sessions for later study.

For an audio-only connection, follow these instructions:

Dial (for higher quality, dial a number based on your current location):
US: +1 669 900 6833 or +1 646 558 8656
Meeting ID: 175942660
International numbers available: https://oracle.zoom.us/zoomconference?m=9yJ1LnAX3LsKA__-cuc3TFhsGBHdaMym
Click here for more information on joining a Zoom session by phone.

Oracle Sharding Product Overview

Oracle Sharding is a scale-out relational database architecture where data is horizontally partitioned across multiple discrete databases that share no hardware or software. It provides linear scalability, fault isolation and geographic data distribution for applications designed for a sharded architecture. Checkout this video to learn how Oracle Sharding automates the deployment of sharded databases, supports elastic scaling and automatic rebalancing, direct routing, proxy routing for multi-shard queries. It does all this while rendering strict consistency, full power of SQL, and the proven enterprise qualities of Oracle Database.

AskTOM Office Hours: Oracle Sharding

AskTOM Office Hours offers free, open Q&A sessions with Oracle Database experts. Join me, and other members of the Oracle Sharding team to get your questions about sharding in Oracle Database answered.

URL: https://devgym.oracle.com/pls/apex/dg/office_hours/3242

When: 2018-02-26   22:00 UTC  – Oracle Sharding Office Hours

Start Times around the world:

UTC 10:00 PM February 26 2018
US/Pacific 02:00 PM February 26 2018
US/Eastern 05:00 PM February 26 2018
Europe/London 10:00 PM February 26 2018
Asia/Calcutta 03:30 AM February 27 2018
Asia/Hong_Kong 06:00 AM February 27 2018
Australia/Sydney 09:00 AM February 27 2018

And here are some general resources for the Office Hours program:

Landing page https://asktom.oracle.com/pls/apex/f?p=100:500
Promotional Video https://www.youtube.com/watch?v=7_-46aL0xU0

Hope you can join us.

Oracle Sharding – A Comprehensive White Paper

Oracle Sharding is a scalability, availability and geo-distribution feature for OLTP applications that enables distribution and replication of data across a pool of Oracle databases that share no hardware or software. Applications elastically scale (data, transactions and concurrent users) to any level, on any platform, simply by adding additional databases (shards) to the pool. Oracle Sharding allows applications to perform high velocity relational transactions.

Oracle Sharding automates the entire lifecycle of a sharded database – deployment, schema creation, data-dependent routing with superior run-time performance, elastic scaling and simpler life-cycle management.

This white paper on Oracle Sharding is intended for Enterprise Architects, Database Architects, Database Administrators, Application Architects and those who are involved in the design and architecture of distributed database systems. In this paper, I cover various aspects of Oracle Sharding – concepts, architecture, capabilities, application requirements, benchmark results, licensing and many more. Read this paper to get answers to many of the questions that you may have on Oracle Sharding.

Oracle Sharding Benchmark on Oracle Bare Metal Cloud (IaaS)

As Oracle Sharding is built on shared-nothing hardware architecture, OLTP applications designed to run on it can elastically scale (data, transactions and concurrent users) to any level, on any platform, simply by deploying new shards on additional stand-alone servers. Also, the unavailability or slowdown of a shard due to either an unplanned outage or planned maintenance affects only the users of that shard; it does not affect the availability or performance of the application for users of other shards (i.e. faults are contained). This blog covers the results of the Oracle Sharding MAA benchmark on Oracle Bare Metal Cloud.

The objectives of this benchmark are to:

  • Elastically scale-out the Sharded Database (SDB) on Oracle Bare Metal IaaS Cloud – following the Oracle Maximum Availability Architecture (MAA) best practices
  • Demonstrate the linear scalability of relational transactions with Oracle Sharding
  • Observe the fault isolation aspects of a sharded database

SDB Benchmark

Figure 1. Sharded Database Topology on Oracle Bare Metal Cloud

As shown in Figure 1, the sharded database used in this benchmark has two shard groups – one in each of the availability domains of the Oracle Bare Metal Cloud. The network latency between the availability domains is less than 1ms. Shardgroup1 in Availability_Domain1 has 100 primary shards and the shardgroup2 in Availability_Domain2 has 100 HA standby shards. Each shard is hosted on dedicated bare metal server, which has 36 cores, 512G RAM and four 12.8TB NVMe flash disks. These flash disks are used for the creation of two ASM Diskgroups (+DATA, +RECO) with normal redundancy. Three shard directors were deployed in each of the Availability Domains for high availability. The shard catalog is placed in Availability_Domain1 and its standby is located in Availability_Domain2.

For this benchmark, Swingbench Order-entry application has been used. Customer_id column is defined as the sharding key. The data model has been modified so that every sharded table in the table family contains the sharding key. Tables used for reference data have been defined as duplicated tables. From the application stand point, the connection pooling code has been modified to use the new Oracle Sharding API calls – to check out a connection on a given shard based on the sharding key. Once the connection is checked out, transactions and queries are executed within the scope of that shard.

Role-based global services have been created so that read-write transactions run on primary shards and queries run on standby shards. The total size of the sharded database is 50TB with 500G on each of the 200 shards.

The replication chosen for this sharded database is Active Data Guard in Max Availability mode. SDB automated deployment method using the “CREATE SHARD” command is used. So, the creation of shards and the configuration of the Active Data Guard replication setup are automatically done by the Oracle Sharding management tier. After which the Data Guard Broker configurations with Fast-Start Failover (FSFO) are automatically enabled for all the shards. The FSFO observers are also automatically started on the regional shard director.

Here are the steps taken for the execution of the benchmark:

  • Begin the application workload on 25 primary shards and 25 standby shards
  • Bring up 25 more primary and 25 more standby shards
  • Repeat step #2 until all the 200 shards are online on both Availability Domains
  • Observe linear scaling of transactions per second (TPS) and queries per second (QPS)
  • Compute the total read-write transaction per second and queries per second across all shards in the sharded database

The following table (Table #1) shows the raw data of the TPS and QPS observed, as the sharded database is elastically scaled-out.

Primary shards Standby  shards Read/Write
Transactions per Second
Ready Only
Queries per Second
25 25 1,180,000 1,625,000
50 50 2,110,000 3,260,000
75 75 3,570,000 5,050,000
100 100 4,380,000 6,820,000

Table 1. Linear scalability of relational transactions as shards are added

linear scaling - graph

Figure 2. Transactions and queries scaled linearly as shards are added

Figure 2 illustrates that as shards were doubled, tripled and quadrupled, we were able to observe that the rate of transactions and queries doubled, tripled and quadrupled accordingly. This demonstrated the frictionless linear scaling due to shared-nothing hardware architecture of Oracle Sharding. Altogether we were able to execute 11.2 Million transactions per second that includes 4.38 Million read-write transactions per second across all the 100 primary shards and 6.82 Million read-only transactions per second across all the 100 active standby shards.

The study also illustrated that Oracle Sharding provides extreme data availability. When a fault was induced on a given shard, there was absolutely no impact to the other shards in the SDB. This is due to zero shared hardware or software among the shards.

fault isolationFigure 3. A shard outage has zero impact on surviving shards

Figure 3 depicts that the application remained available on other shards even when a fault is induced on a given shard.

This blog covered the results of the Oracle MAA study on Oracle Sharding and highlighted the linear scalability and fault isolation benefits. This study showcased that high-performance and reliable Oracle Bare Metal Cloud infrastructure is an ideal platform for running Oracle sharded databases. Visit www.oracle.com/goto/oraclesharding for cookbooks on SDB deployment on Oracle Cloud and Oracle Sharding MAA Best Practices White Paper (To be published soon). Follow me on Twitter @nageshbattula