Overview of Oracle Sharding in Oracle Database 12.2
What is Sharding?
In today’s growing demand for highly scalable solutions, Oracle has come up with an all new feature called Sharding also known as share nothing (SN). Sharding is a type of database partitioning that separates larger databases into smaller, faster and easily manageable subsets known as data shards.
Oracle Sharding can be used for custom-designed OLTP applications enabling distribution and replication of data across a pool of Oracle databases that do not have any hardware or software in common (share nothing). First release of Oracle Database 12.2.0.1 supports scaling upto 1000 shards.
Sharding enables you to split your information between different servers thereby allowing better scalability and high availability. In sharding, every node is referred to as a shard, each holding a specific portion of data. Different shards can hold the same data to provide better availability and scalability options. In order to access the required data, you need to know the exact shards holding the relevant information
Data is split amongst the shards in a round-robin fashion. Say for instance you are saving 20 items in shard. If you have 2 shards, each shard will comprise of 10 items. Similarly, if you have 5 shrads; each shard will comprise of 4 items and so on.
Oracle Sharding provides superior runtime performance and is easy to manage when compared to home grown deployments that use a similar approach.
Components of the Oracle Sharding Architecture
Shards – independent physical Oracle databases hosting a subset of the sharded database
Sharded database (SDB) – a single logical Oracle Database that is horizontally partitioned across a pool of physical Oracle Databases (shards) that do not share any hardware or software
Shard directors – network listeners that enable high performance connection routing, based on a sharding key
Shard catalog – An Oracle Database that supports automated shard deployment, centralized management of a sharded database, and multi-shard queries
Global service – database services that provide access to data in an SDB
Connection pools – connection pools act as shard directors by routing database requests across pooled connections at runtime
Management interfaces – GDSCTL (command-line utility) and Oracle Enterprise Manager (GUI)
Note: Sharding key affects the entire physical architecture, hence you need to be very careful while choosing the sharding Architecture and key.
General Architecture of Oracle Sharding with two shards and one shard catalog database.
Figure 1 Oracle Sharding Architecture with two shards and one catalog
Figure 2 Oracle Sharding Architecture
Sharding Methods
Sharding can be done using three different methods as discussed below:
System-Managed Sharding
With system managed sharding, data is automatically distributed across multiple shards using the partitioning algorithm. Partitioning algorithm is responsible for evenly distributing data across shards. Here, user need not specify the mapping of data across shards.
Composite Sharding
The composite sharding method allows you to create multiple shardspaces for different subsets of data in a table partitioned by consistent hash. A shardspace is set of shards that store data that corresponds to a range or list of key values.
Using Subpartitions with Sharding
All of the subpartitioning methods provided by Oracle Database are also supported for sharding as Oracle Sharding is based on table partitioning.
Benefits of Sharding
Fault Containment. The SN infrastructure eliminates single point of failure and hence provides strong fault isolation. Failure or slow-down of one shard does not affect the performance or availability of other shards.
Linear Scalability. Shards are highly scalability making it convenient to add new data into the shards without impacting the previously available shards. Sharding eliminates performance bottlenecks which makes it possible to linearly scale performance and capacity by adding shards.
Rolling Upgrades. Applying configuration changes on one shard at a time does not affect other shards, and allows administrators to first test the changes on a small subset of data.
Geographical Distribution of Data. Sharding makes it possible to store particular data close to its consumers and satisfy regulatory requirements when data must be located in a particular region.
Simplicity of Cloud Deployment. Sharding is well suited to deployment in the cloud. Shards may be sized as required to accommodate whatever cloud infrastructure is available and still achieve required service levels. Oracle Sharding supports on-premises, cloud, and hybrid deployment models
Reduced Downtime. Maintenance can perform on specific shards, allowing only a local downtime.
Oracle Sharding supports the following features
Relational schemas
SQL and other programmatic interfaces
Online schema changes
Compression
Multi-core scalability
High Availability
Advanced security
Read consistency and ACID properties (very rich feature when compared to other databases)
Database partitioning
Enterprise-scale backup and recovery
Developer agility with JSON
