Database, SQL and PL/SQL

On Oracle Database 12c, Part 4

Our technologist talks with Oracle product management about Oracle Database 12c and the Oracle Multitenant option.

By Tom Kyte Oracle Employee ACE

March/April 2014

Usually I take three or four user-submitted questions from the past two months and present those questions and answers here in each Ask Tom column. In the last three and in this and the next column, however, I take a look at some key Oracle Database 12c features. These features are all part of the “12 Things About Oracle Database 12c” presentation I gave at Oracle OpenWorld 2012 in San Francisco. (You can find the slides for that presentation on on the Files tab.) The first three Oracle Database 12c features I looked at were improved defaults, bigger datatypes, and top-n queries. Next I discussed a new row-pattern-matching clause and how undo for temporary tables has changed in Oracle Database 12c. In the last issue, I covered some partitioning improvements, adaptive execution plans, and enhanced statistics.

In this issue, I’m taking a different approach to discuss the #1 new capability of Oracle Database 12c. What follows is a conversation I had with the product manager for the Oracle Multitenant option, Patrick Wheeler. I hope you enjoy this change from my usual style.

Kyte: Please tell us a little about your history with Oracle.

Wheeler: I joined Oracle in 1986 and worked as a consultant and consulting practice manager for several large financial institutions in London and San Francisco. I was closely involved with the development of Oracle CASE and was responsible for its worldwide launch.

In 1995 I joined Siebel Systems and was a member of the Founder’s Circle. At Siebel I was a data architect and director of data modeling. Now back at Oracle, I am responsible for product management of the Oracle Multitenant option.

Kyte: Oracle Multitenant is the theme of our conversation today—one of my 12 things about Oracle Database 12c. Briefly, what is Oracle Multitenant?

Wheeler: Oracle Multitenant is a new option in Oracle Database 12c that provides a multitenant architecture for consolidating databases and simplifying operations in the cloud. It is in production and available today. With this new architecture, we can help customers reduce capital expenses and operating expenses and increase agility—and it’s easy to adopt.

Kyte: We’ve both been at Oracle for a long time, and Oracle has always had the best database system. Why did we need to change our architecture?

Wheeler: Well, our customers’ requirements are constantly evolving, and so must our technology. Companies are facing enormous pressure to consolidate and to realize the benefits of cloud computing. There are economic pressures to consolidate—to reduce floor space, power costs, and license costs. There are operational pressures to provide cloud-based agility, including rapid, low-cost self-service provisioning and delivery of standardized services. What’s needed is the right balance of database isolation and an ability to manage many databases as one.

Kyte: You’ve just laid out a good case for consolidation, but it’s not new. The question these days is not so much whether to consolidate as how to consolidate.

Wheeler: Exactly. Companies have tried various approaches to consolidating databases. They might have tried virtual machines, for instance, but with VMs you replace physical sprawl with virtual sprawl, and virtual sprawl is still expensive to manage. Additionally, you replicate the overhead of database and OS software in each VM, and this limits the consolidation density or the number of applications you can host in a single server.

Kyte: Yes, but you can get better consolidation density by stacking multiple databases in a single server.

Wheeler: You can, but it’s still not optimal. When you fire up a new database, you have to allocate a new shared-memory area and kick off a new set of background processes—20 of them. You repeat this for every new database you start, and these replicated processes quickly consume the available resources in the system, thereby limiting the ultimate consolidation density.

Kyte: Prior to Oracle Database 12c, some of our customers took a schema consolidation approach, with a single large server and a single big database. Each application “back end” is hosted in a single schema. It’s typically been a struggle to change the applications to achieve this, but where these customers have been successful, they have realized significant gains in consolidation. Then they find that there’s a trade-off between these consolidation gains and agility. They’ve lost granular control.

Wheeler: These were precisely our considerations as we designed the Oracle multitenant architecture.

Kyte: Will you please describe the new multitenant architecture?

Wheeler: With the multitenant architecture, each application’s back-end database becomes a pluggable database [PDB]. The pluggable part is new, but what hasn’t changed is that a pluggable database is a fully functional, self-contained Oracle Database instance. From an application’s point of view, it hasn’t changed in any way.

Kyte: Right, because requiring our customers to make application changes to adopt a new database architecture is not a reasonable expectation.

Wheeler: Exactly, and no application changes are required to upgrade the database to be a PDB in the multitenant architecture.

Kyte: So that’s the database part. What about the pluggable part?

Wheeler: In the multitenant architecture, we introduce a new concept, the root database. Lots of individual PDBs can be plugged into a single root and can together form a multitenant container database [CDB]. From the operations point of view, this is the database. We now have a single shared system global area [SGA] and a single set of background processes that are shared by all the PDBs in that CDB.

Kyte: This is what gives us the great consolidation density in the multitenant architecture, right? With 20 background processes per database, if I stack, say, 30 databases into a single server, I’ll need 600 background processes. But with the multitenant architecture, consolidating each of these 30 databases as PDBs in a single CDB, I’ll need only 20 background processes.

Wheeler: Exactly. And it’s not just the number of background processes that matters here; it’s also their nature. We’ve done various consolidation scalability tests that demonstrate this. Let’s say some of your 30 example databases are OLTP-heavy. That means there are lots of database writes and log writes. The database writer [DBWR] and the log writer [LGWR] are both heavy, high-priority processes. With separate databases, you have these various DBWR and LGWR processes competing with each other, leapfrogging to the front of the queue. We observed increases in L3-cache misses, and this was a major factor limiting scalability.

In contrast, running the same workloads in the same number of PDBs consolidated into a single CDB, we had a single LGWR with two slaves purring along very comfortably.

Kyte: Oracle Multitenant delivers better throughput and better scalability.

Wheeler: Yes, exactly.

Kyte: You’ve explained how Oracle Multitenant can save customers capital expenses by consolidating more applications per server. Earlier on you mentioned that customers could also reduce operating expenses. How is that?

Wheeler: The simple question “What is a database?” gets different answers, depending on the vantage point. From an application’s point of view, the PDB is the database, but from the operations point of view, the CDB is the database.

Kyte: So when you say “from an operations point of view,” you’re talking about typical DBA tasks such as patching, backup, or configuring Oracle Real Application Clusters [Oracle RAC] or Oracle Active Data Guard.

Wheeler: Yes, and these can be expensive, when you consider how many databases our typical customers have to manage.

Kyte: Right. Many of our customers have hundreds of databases—some have thousands.

Wheeler: And now you can consolidate those hundreds of databases into a handful of CDBs—maybe a dozen or so. Instead of having hundreds of databases to back up, patch, and so on, you’ve got only those few. This ability to manage many databases as one (or at least as a few) greatly reduces operating expenses.

Kyte: Another way to look at this is that DBAs are freed from the time-consuming and error-prone chore of doing all this administration on all these databases and are able to add value and improve quality of service generally. Rather than struggling to keep their head above water, they can add value in a more strategic way.

I can see the benefits of being able to “manage many as one,” as you put it, but in reality, you don’t always want all of your databases to be managed in lockstep. That was one of the big downsides of the schema consolidation model we talked about earlier. I’ll give you a specific example: patching. When you consider the whole stack, you’ve got the server with the Oracle Database instance running on it. But on top of all that are the application tiers, and it’s the application that the customer cares about. If any given application is not yet certified at a particular patch level, it means that all applications must wait to be upgraded until that one laggard is ready to go. This is the sort of real-world problem that customers complain to me about.

Wheeler: Now you’re touching on another theme of Oracle Multitenant. We’ve discussed savings in capital expenses and operating expenses. The phrase I like to use for operating expense reduction is “manage many as one but retain granular control where appropriate.” And the theme we’re moving to now is agility.

Kyte: Typically there’s been a trade-off between operating expense reduction and agility.

Wheeler: Not with Oracle Multitenant. I’ll give you a few examples:

For backups, you can configure a single backup for the entire CDB, but you can do a point-in-time recovery of an individual PDB.

In the case of patches, database version is an operational concept, so it applies at the CDB level. If you apply a patch at the CDB level, all the PDBs in that CDB will be upgraded in one fell swoop. But—back to your real-world example—this isn’t always practical. In that case, create a second CDB and patch that to the new level. You can upgrade individual PDBs by unplugging them from the first CDB (let’s say at Oracle Database 12c Release and plugging them into the second one (perhaps at Release

Kyte: Nice! And the point is that any PDB that isn’t ready to upgrade doesn’t have to move. Presumably it’s a faster way to apply the patch as well.

Wheeler: Yes, and yes.

Kyte: Well, that’s certainly an improvement from the way things were with schema consolidation. But when people talk about cloud-based agility, they mean much more than patching and backups. They’re also talking about flexibly adapting to changing workloads, efficient migration of applications through changing service-level agreements [SLAs], and so on. What can Oracle Multitenant deliver in these areas?

Wheeler: That’s a great question—two great questions, in fact. Let me take them in turn.

First, flexible adaptation to changing workloads. This is where our tight integration with Oracle RAC comes into play. Configuring Oracle RAC is an operational task, so it follows that in Oracle RAC, the CDB is opened across the entire cluster, in keeping with the “manage many as one” theme. However, consistent with the “granular control where appropriate” theme, individual PDBs can be opened selectively in specific nodes—strictly speaking, instances—of the cluster. We call this affinitization of a PDB to an instance, and technically we achieve it by opening the services of the specific PDBs in the desired nodes. Now, let’s say that at a particular stage of the business cycle, the cluster is heavily loaded. By moving the services of a PDB from a more heavily loaded node to a less heavily loaded one, we can flexibly adjust to this changing workload. It is easy to imagine various comparable scenarios. This is really unprecedented flexibility, so I like to say that Oracle Multitenant makes Oracle RAC better, and Oracle RAC makes Oracle Multitenant better.

Kyte: What about the second question?

Wheeler: Oracle Multitenant and moving between SLA tiers: in many cases, SLA tiers—which typically define things such as performance and availability—correspond to specific combinations of database options such as Oracle RAC and Oracle Active Data Guard. As discussed, these are things defined at the CDB level. So, for example, you might define Gold service as “Oracle RAC and Oracle Active Data Guard,” Silver as “Oracle Active Data Guard only,” and so on. And because these are operational things, you’d have a CDB for each SLA. Now, a pluggable database is a portable database. If you want to upgrade (or downgrade) the SLA for a specific PDB, simply unplug it from its current CDB and plug it into a CDB managed at the appropriate SLA.

Kyte: Another thing I gather from what you’re describing here is that just because you can “manage many as one,” it doesn’t mean that you should have only one CDB.

Wheeler: Exactly. You could easily have a dozen CDBs—say, for different SLA tiers or for upgrade via unplug/plug—and still be doing it right.

Kyte: Great! Now, you know that I talk to a lot of DBAs. Typically there are two major pain points. You’ve talked about one of them: patching. The other is provisioning of new databases.

Wheeler: Pain is the word! You can tell who the DBAs are in an audience by mentioning provisioning and seeing who winces! Seriously, though, we’ve got a great story to tell about provisioning in Oracle Multitenant. It’s all based on a capability we call cloning. We can create local clones (from a PDB in the same CDB) or remote clones (across a database link to a remote CDB). You’re a SQL guy, so you’ll like this: cloning a PDB is achieved by a single SQL statement. Security guys like this, too, because all that is required is database access—not OS access. And everyone likes it because it is fast. What we call full clones are fast. Even faster—ridiculously fast—are snapshot clones. Snapshot clones are built on a capability of the underlying file system—where available—called copy-on-write.

Kyte: Copy-on-write is established, proven technology. So we’re leveraging this to deliver thin provisioning of pluggable databases. I am imagining the productivity gains in a development and testing environment. You know the situation: dozens, maybe hundreds, of engineers each working on individual copies of a handful of “golden master databases.” With snapshot cloning, they can rapidly refresh these databases with minimal incremental storage or server load. I can even imagine this being done on a self-service basis.

Wheeler: Cloning is a great use case for Oracle Multitenant and a great feature to justify migrating to it. By definition, your development and testing environments are not production, so the very development and test engineers you want to become proficient in the new technology will use it in their day-to-day business. And as you mentioned, the organization’s productivity increases and the quality of work goes up as hidden development and testing costs are eliminated.

Kyte: Rapid, low-cost provisioning of databases is a huge leap forward from the painful process of the past. Sometimes it can take weeks—even months—to work through the bureaucracy to create a new database.

Wheeler: Yes. We’ve already covered reductions in capital expenses and operating expenses. These can be very significant, but they are incremental reductions. Now, if you can create a database in minutes—say, for ad hoc analysis of a new opportunity—you might be able to seize it and be first to market. If it’s going to take weeks to create that database, the opportunity may well have slipped away and it won’t even be worth bothering. With rapid self-service provisioning, we’re talking about a difference in kind, not in degree. Rather than incremental savings off the bottom line, we’re talking about the potential of adding significantly to the top line.

Kyte: I like it! We could talk much more about provisioning, but there must be many other use cases for Oracle Multitenant. Can you name a few?

Wheeler: Yes, it offers a very flexible architecture. A few obvious use cases include consolidation of disparate applications, software as a service [SaaS], database as a service [DBaaS], and distribution of packaged apps and data.

Kyte: All of this sounds great, but when I talk to customers about consolidation, a big concern is always contention for shared resources. I mean, you don’t want any individual PDB to run away with all the resources of the system.

Wheeler: No, that is a very reasonable concern, but we’ve got a great solution. We enhanced the Resource Manager feature of Oracle Database to be able to control things such as CPU, sessions, parallel execution servers, and even I/O (on Oracle Exadata). By using the simple-yet-powerful concepts of shares and caps, you can simply define policies that determine how resources are shared between the PDBs.

Kyte: So a high-priority system would have lots of shares, and a low-priority system would have just a few shares.

Wheeler: Yes, and you’d probably want to cap the lower-priority system to limit the resources it can use. The shares represent a guaranteed minimum share of resources allocated to a PDB—if they’re needed. But if they’re not needed, those resources will be available for the other PDBs. Sometimes this is called overprovisioning, which might sound like a bad thing, but in many situations it’s a great thing and a key advantage of consolidation.

Kyte: It’s so much more flexible than instance caging, where you’re capped at a specific number of CPUs. Even if the other CPUs are idle, you’re boxed in and can’t use them.

This really sounds like a tremendous advance in our database architecture. Many customers are already adopting it or planning to do so shortly. How easy is it to upgrade to Oracle Multitenant?

Wheeler: It’s very easy. Easy as 1-2-3: Step 1: Upgrade your database system in place, just as you always have done. Direct upgrades from Oracle Database 10g Release 2 and Oracle Database 11g Release 1 and Release 2 are supported. The result is Oracle Database 12c in what we call non-CDB—the old architecture.

Step 2: Adopt this non-CDB as a PDB. Basically this means plugging it into a precreated CDB. (You can simply create one of these with Oracle Database Configuration Assistant.)

Then change the applications to work with Oracle Multitenant. But wait! No application changes are required to adopt Oracle Multitenant.

Kyte: So there is no Step 3.

Wheeler: There is no Step 3!

Kyte: This really is a very dramatic advance in our database architecture. We’ve discussed several benefits here in some detail. Please summarize them for me.

Wheeler: With Oracle Multitenant, you can

  • Reduce capital expenses by supporting more applications per server

  • Reduce operating expenses by managing many databases as one, delivering standardized services—even self-service

  • Increase agility with rapid, low-cost provisioning and through “pluggable portability”

  • Adopt this new architecture easily

Next Steps

 ASK Tom
Tom Kyte answers your most difficult technology questions. Highlights from that forum appear in this column.

 FOLLOW Tom on Twitter

 more Tom
 Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions, Second Edition

 DOWNLOAD Oracle Database 12c

LEARN more about
 Oracle Database 12c
 Oracle Multitenant

FOLLOW Oracle Database
 on Twitter
 on Facebook


Photography by Dmitri Popov, Unsplash