Consolidating datamarts

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. Pluggable database do not need the multitenant option as you can plug/unplug database in single-tenant.You see that I’ve executed exactly the same statement – SELECT * FROM DEMO – in both connections. Let’s look at the cursors: The optimization tried to share the same cursor.The parent cursor is the same because the sql text is the same. Imagine what happened on my database where I had 3000 identical queries on different schemas.I’m talking about consolidating the similar production databases. We had to move those schemas from one database to another, from one Storage system to another… Transportable tablespaces was there, but see next point. At first, each datamart had its set of tablespaces.And all the plug/unplug has the same problem as transportable tablespaces: source must be made read-only. But 5000 datafiles on a database was too much for several reasons. I remember a duplicate with skip tablespace took 2 days to initialize… Did you ever try to query DBA_EXTENTS on a 5000 datafiles database?

Let’s take the Oracle white paper presenting multitenancy.

Then it follows the child list in order to see if a child can be shared. We didn’t have ‘perf flame graphs’ at that time, or we would have seen a large flame over .

But semantic verification sees that it’s not the same ‘DEMO’ table and it had to hard parse. Looking at thousand of child cursors in the hope to find one that can be shared is very expensive.

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. 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. Finally, multitenant is nice because of pluggable databases.

Search for consolidating datamarts:

consolidating datamarts-85consolidating datamarts-42

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “consolidating datamarts”