Infrastructure at your Service

Franck Pachot

Multitenant vs. schema based consolidation

By Franck Pachot

.
If you want to install multiple instances of a software, for example you host the ERP for several companies or subsidiaries, you have 3 solutions:

  • have one database and multiple schema
  • have multiple databases
  • have one database and multiple pluggable databases

Of course, this is exactly the reason for pluggable databases: multitenant. You have good isolation but still share resources. A lot of reasons have been given why multiple schema – or schema based consolidation – is not a good solution. I don’t agree with most of them. But there is one very good reason that I’ll show later and it’s about cursor sharing.

schema based consolidation

Let’s take the Oracle white paper presenting multitenancy.

Name collision might prevent schema-based consolidation

Yes some applications have a fixed schema name. If your ERP must be installed in SYSERP schema, then you cannot install several ones in the same database.

However, you should challenge your application provider for that before changing all your infrastructure and buying expensive options. Maybe I’m too optimistic here, but I  think it’s something from the past. I remember a telco billing software I’ve installed 15 years ago. The schema was ‘PB’. It had nothing to do with the software name or the vendor name. But when I asked if I can change, answer was No. That schema name was hard-coded everywhere. It got it when the main developer came to visit us… his name was Pierre B.

About public synonyms, and public database links… please just avoid them.

Schema-based consolidation brings weak security

Same idea. If your application requires a ‘SELECT ANY PRIVILEGE’ then don’t do it. In 12c you have privilege analysis that can help to identify the minimal rights you need to grant.

 

Per application backend point-in-time recovery is prohibitively difficult

I don’t see the point. Currently multitenant do not give us more options because pluggable database point in time recovery, nor flashback pluggable database, is currently possible in-place. But I know it’s planned for the future. You can already read about it at http://www.google.com/patents/US20140095452

Of course, when using schema-based consolidation you should used different tablespaces and you have TSPITR.

 

Resource management between application backends is difficult

Well you don’t need pluggable database to use services. Multitenant is just an easy way to force the application to use specific services.

 

Patching the Oracle version for a single application backend is not possible

Yes, plugging a PDB into a different version CDB can be faster for those applications that have lot of objects. But it is not as easy as the doc says. The PDB dictionary must be patched. It’s still a good think when the system metadata is a lot smaller than the application metadata

 

Cloning a single application backend is difficult

Cloning a PDB is easy. Right.

Finally, multitenant is nice because of pluggable databases. Do you know that all occurrence of ‘multitenant’ in 12c code or documentation was ‘pluggable database’ one month before the release?

But wait a minute, I’m not talking about test environments here. I’m talking about consolidating the similar production databases. And all the plug/unplug has the same problem as transportable tablespaces: source must be made read-only.

 

Cursor sharing in schema based consolidation

 

Time to show you what is the big advantage of multitenant.

 

10 years ago I worked on a database that had 3000 schemas. Well we had 5 databases like that. You can think of them as specialized datamarts: same code, same data model, but different data, used by application services provided to different customers. A total of 45TB was quite nice at that time.

That was growing very fast and we had 3 issues.

Issue one was capacity planning. The growth was difficult to predict. We had to move those schemas from one database to another, from one Storage system to another… It was 10g – no online datafile move at that time. Transportable tablespaces was there, but see next point.

The second issue was the number of files. At first, each datamart had its set of tablespaces. But >5000 datafiles on a database was too much for several reasons. One of the reason was RMAN. I remember a duplicate with skip tablespace took 2 days to initialize…

Then we have consolidated several datamarts into same tablespaces. When I think about it, the multitenant database we can have today (12c) would not have been an easy solution. Lot of pluggable databases mean lot of datafiles. I hope those RMAN issues have been fixed. But there are other ones. Did you ever try to query DBA_EXTENTS on a >5000 datafiles database? I had to when we had some block corruption on the SAN (you know, because of issue 1 we did lot of online reorg of the filesystems, and SAN software had a bug) This is where I made my alternative to DBA_EXTENTS.

Then the third issue was cursor sharing.

Let me give you an example

I create the same table in two schemas (DEMO1 and DEMO2) of same database.

SQL> connect demo1/[email protected]//192.168.78.113/PDB1
Connected.
SQL> create table DEMO as select * from dual;

Table created.

SQL> select * from DEMO;

D
-
X

SQL> select prev_sql_id from v$session where sid=sys_context('userenv','sid');

PREV_SQL_ID
-------------
0m8kbvzchkytt

SQL>
SQL> connect demo2/[email protected]//192.168.78.113/PDB1
Connected.
SQL> create table DEMO as select * from dual;

Table created.

SQL> select * from DEMO;

D
-
X

I’m in multitenant here because of the second test I’ll do, but it’s the same pluggable database PDB1.

You see that I’ve executed exactly the same statement – SELECT * FROM DEMO – in both connections. Same statement but on different tables. Let’s look at the cursors:

b2ap3_thumbnail_CaptureShared1.JPG

The optimization tried to share the same cursor. The parent cursor is the same because the sql text is the same. Then it follows the child list in order to see if a child can be shared. But semantic verification sees that it’s not the same ‘DEMO’ table and it had to hard parse.

The problem is not hard parse. It’s not the same table, then it’s another cursor. Only the name is the same.

Imagine what happened on my database where I had 3000 identical queries on different schemas. We didn’t have ‘perf flame graphs’ at that time, or we would have seen a large flame over kkscsSearchChildList.

Looking at thousand of child cursors in the hope to find one that can be shared is very expensive. And because it’s the same parent cursor, there is a high contention on the latch protecting the parent.

The solution at that time was to add a comment into the sql statements with the name of the datamart, so that each one is a different sql text – different parent cursor. But that was a big change of code with dynamic SQL.

Cursor sharing in multitenant consolidation

So, in 12c if I run the same query on different pluggable databases. After the previous test where I had two child cursors in the PDB1 (CON_ID=5) I have run the same in PDB2 (CON_ID=4) and here is the view of parent and child cursors from the CDB:

b2ap3_thumbnail_CaptureShared2.JPG

We have the two child cursors from the previous test and we have a new child for CON_ID=4

The child number may be misleading but the search for shareable cursor is done only for the current container, so the same query when run from another pluggable database did not try to share a previous cursor. We can see that because there is not an additional ‘reason’ in V$SQL_SHARED_CURSOR.

SQL> select con_id,sql_id,version_count from v$sqlarea where sql_id='0m8kbvzchkytt';

    CON_ID SQL_ID        VERSION_COUNT
---------- ------------- -------------
         5 0m8kbvzchkytt             3
         4 0m8kbvzchkytt             3

The V$SQLAREA is also misleading because VERSION_COUNT aggregates the versions across containers.

But the real behavior is visible in V$SQL_SHARED_CURSOR above and if you run that with a lot of child cursor you will see the difference in CPU time, latching activity, etc.

Conclusion

I’m not talking about pluggable databases here. Pluggable database do not need the multitenant option as you can plug/unplug database in single-tenant. Pluggable database is a nice evolution of transportable database.

When it comes to multitenant – having several pluggable database in the same container, in order to have several ‘instances’ of your software without demultiplicating the instances of your RDBMS – then here is the big point: consolidation scalability.

You can add new pluggable databases, and run same application code on them, without increasing contention, because most of the instance resources are isolated to one container.

4 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist
Oracle ACE Director, Oracle Database OCM 12c
AWS Database Specialty certified, AWS Community Builder
Oak Table member

RSS for this blog: feed
Twitter: @FranckPachot
LinkedIn : www.linkedin.com/in/franckpachot
Podcast en français: DBPod