This article will complete our discussion on achieving multi-tenancy in the Oracle database by addressing Quality of Service (QoS) concerns. We’ll bring together the concepts introduced in this series and apply them to an Oracle database consolidation scenario, paying special attention to database features that keep a multi-user application within target service levels.
Quality of Service (QoS) is of particular concern in a shared environment. The Oracle database has many QoS capabilities which allow for the configuration and management of pooled resources. We’ll take a look at those capabilities which affect CPU, memory and I/O resources.
Oracle Architecture review
Let’s begin with a high level review of the Oracle Architecture, focusing on three main components affecting QoS: services, instances and the database.
A service connects users to an instance, transparently masking a single instance node or the individual nodes in the cluster. Services can logically differentiate the needs of clients within an application and between applications. Real Application Cluster (RAC) services load balance across instances in the cluster and can target workload to specific nodes providing the best possible throughput or transaction response time to the application.
More on Oracle database consolidation
Part 1 of this series explains how database consolidation is a paradigm shift
Part 2 looks at consolidating databases using session schema management
Part 3 is on Oracle Virtual Private Database
An instance consists of memory structures and processes running on a node which manage physical data files. Oracle can be architected as a single instance or a cluster comprised of two or more instances running on multiple clustered machines accessing a single shared physical database.
A database is physical data files residing on storage media, i.e. solid state or spinning disk, shared among one or more instances. Data files include application data, control files and redo logs.
Continuing our use case, the ABC Gum company has decided to offer their Customer Information System (CIS) as a hosted Internet service. Concerned with maximizing their return on investment (ROI) they have decided to service all clients from a single database.
The DBA team wants to leverage several native Oracle architecture components addressing several QoS attributes: performance, scalability, availability, agility, security and manageability. Looking to physically separate their larger clients and combine smaller clients, they choose a grid architecture. The plan is to configure an Oracle Real Application Cluster (RAC) ensuring dedicated CPU and memory resources and a matching storage grid ensuring I/O isolation. Virtual Private Database (VPD) schema object data security will provide client-level logical data separation.
The first challenge is to direct clients to specific nodes and instances in the cluster. Using RAC services to route workload to target instances, the team creates a service for each large client and a single service for smaller clients. Services will manage QoS at connection time by one of several algorithms providing automatic workload distribution across the instances defined in the service configurations.
Next, the team focuses on the cluster instances. RAC combines the processing power of multiple interconnected computers to provide system redundancy, near linear scalability, and high availability (protection from node and instance failures). The ability of RAC to dynamically add or remove capacity, i.e. nodes and instances, in the cluster provides agility to address many QoS concerns. By targeting workload to specific instances, client performance can also be individually managed. Many initialization parameters are configurable at the instance level (listing 1), providing flexibility for client optimization. For example, the team can maximize resource utilization by setting different values for instance memory (sga_target); perhaps 25gb for one big client and 15gb for another.
select name from v$parameter where isinstance_modifiable = 'TRUE';
230 rows selected.
Furthermore, recalling the team’s VPD implementation using sys_context for the application context enables the database to improve performance through static policies which cache the predicate and dynamically change the rows that are returned without rerunning the function. For smaller clients who share instances, Oracle’s Database Resource Manger will be used to throttle CPU resources at times of contention.
Finally, having isolated and optimized client CPU and Memory resources, the team considers options for I/O separation and increased storage utilization. Recalling that sharing database schema objects with VPD ensures logical data separation, they decide to use Oracle’s partitioning option to physically separate table data transparently to the application. The first step is to create discrete, non-competing LUNs in the shared SAN storage array. Next, LUNs are divided among the nodes in the cluster and presented to the operating systems as raw devices. Leveraging Oracle’s Automatic Storage Management (ASM) to provide storage consolidation via pooling, Database Files (DBFs) are created from the raw devices. Now, tables defined with list partitioning, based on clients, associate DBFs at the partition level guaranteeing that client specific data blocks will be requested from only the target instances of the client.
Jeff McCormick is an architecture director at a major health service company and president of the Connecticut Oracle User Group. McCormick has worked in IT for more than 20 years as a data and infrastructure architect/administrator. He holds several Oracle, Microsoft and Sybase professional certificates.
This was first published in March 2012