AWR architecture in a PDB CDB environment

In the previous post we discussed how data sharing works across the PDBs. In this post we will understand the AWR framework works in the multitenant environment. Note that Oracle has made a lot changes between 12.1 and 12.2 and this blog focusses on the 12.2 architecture. Since Oracle 12.2, we can have two different AWR snapshots.

1. AWR snapshot taken at the CDB level. The underlying tables such as WRH$_SQLSTAT will contain data related to root and all of the PDBs.

2. AWR snapshot taken at the PDB level. The underlying tables such as WRH$_SQLSTAT will contain only the current PDBs data.

AWR snapshot at PDB level is disabled by default. To enable it, we need to modify the parameter AWR_PDB_AUTO_FLUSH_ENABLED to TRUE. This parameter is modifiable at PDB level. Since a PDB level and a CDB level snapshot are two different snapshots - it is possible to configure different retention and frequency settings for them. Since we have two different snapshots, this implies that some of the data is redundant, it is stored both in the PDB and the CDB. We will see it shortly.

Underlying tables such as WRH$_SQLSTAT or WRH$_SNAPSHOT are Metadata Linked tables. This means that the dictionary is in the root container but each PDB has its own private data segment. You can consider these as separate tables in each of the PDBs.

Now Oracle exposes this table in the form of views. In earlier versions we had  the DBA_HIST_* views such as DBA_HIST_SQLSTAT or DBA_HIST_SNAPSHOT. But from 12.2 these tables are exposed in different ways.

As you can see, underlying table WRH$_SQLSTAT is exposed via three different views.

AWR_ROOT_SQLSTAT - This is a data link view. If you select from this view you get the data from the CDB's AWR repository.

AWR_PDB_SQLSTAT - This is a metadata link view. If you select from this view you get the data from the current PDB

AWR_CDB_SQLSTAT - This is an extended data link view. If you select from this view you get the data from both the current PDB and the CDB.

DBA_HIST_SQLSTAT is just a view that points to AWR_CDB_SQLSTAT

This is just one example, all other AWR related tables are exposed via similar views.
If we query AWR_ROOT_SQLSTAT, being a data link view, it picks up data from the root container. Remember that CDB's tables contain data from all the PDBs. And if you read this view from a PDB, Oracle is intelligent enough to automatically apply the filter on CON_ID .

SQL> alter session set container=PDB1 ;

SQL> explain plan for
           select * from AWR_ROOT_SQLSTAT ;

Plan hash value: 2631641967

-------------------------------------------------------------------------------------
| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |                  |   100 |   209K|     1 (100)| 00:00:01 |
|*  1 |  DATA LINK FULL  | AWR_ROOT_SQLSTAT |   100 |   209K|     1 (100)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CON_ID"=0 OR "CON_ID"=3)

As you can see, since I queried this view from a PDB, Oracle has applied the CON_ID filter.
Therefore, AWR_ROOT_SQLSTAT gets the data from the CDB's WRH$_SQLSTAT after applying the con_id filter. It brings the PDB's data along with the CDB's data itself.

And when we query AWR_CDB_SQLSTAT -since this is an extended data link view it queries the data from the CDB and the current PDB. And since CDB snapshots has the PDB data, you will see duplicate rows here. A SQL will show up twice in this view. One coming from CDB snapshots and other coming from the PDB snapshot. ( This is why it isn't a good idea to collect a PDB level snapshot). If you have a customized script on DBA_HIST_SQLSTAT you may have to modify the script and query AWR_PDB_SQLSTAT instead.

Now that we have covered how the AWR data is stored internally, let us look at the options we have to generate the AWR report.

From a PDB, if you run awrrpt, Oracle asks you to choose the location of AWR data
We get to choose the location of AWR data.  This is because, as explained above, we have different AWR repository, one in the CDB and others in each of the PDBs.
If you choose AWR_ROOT - Oracle then queries AWR_ROOT_* views. Pulls the data from the CDB after applying the con_id filters.

If you choose AWR_PDB - Oracle then queries AWR_PDB_* views. Pulls the data from the current PDBs AWR data. This option is available only when PDB level snapshots are available.

And if you run an AWR report from CDB, you don't get to choose the location. Oracle picks up the data from the root itself using the AWR_PDB_* views.  AWR from CDB is a consolidated report. It displays the information about the each of the PDBs. Since this is a mutitenant architecture we have just one instance, some information such as background wait events are displayed only in the CDB level AWR report.

Conclusion :

PDB level snapshot is clearly an overhead. And this causes same data to be stored in two different places. Probably this is the reason why Oracle chose to disable this by default.
My recommendation would be to keep it disabled. CDB level AWR snaps are enough to get us the diagnostic data. And we can generate the report from either the CDB or the PDB based on what you need to investigate.

Demystifying Data Sharing in Oracle 19c Multitenant

Oracle introduced multitenant architecture in 12c. Purpose of multitenant is consolidation. Instead of having multiple databases on a server we can now create one consolidated database called the CDB. This can contain multiple pluggable databases (PDBs). To an application a PDB would appear as if it is a separate database. But behind the scenes we now have one instance, one set of background processes, one shared pool and so on. This architecture is based on data sharing between CDBs and PDBs. Oracle manages to keep the dictionary information in the CDB while the application data segments remain private to the PDB.

In this article we will understand how data sharing works across PDBs and CDBs.

Oracle allows three different techniques of sharing 

1. Metadata Link

2. Data Link

3. Extended Data Link

These sharing methods is what Oracle uses internally to achieve multitenant architecture. From 12.2 Oracle allows users to create their own application root containers and then create PDBs inside this application container. This root application container is actually a PDB to its main root container CDB$ROOT.

Let's get started with a test case. We will first create an application container and then a PDB inside this container. I'm running Oracle database 19c on a Windows Platform. I have a root container already installed called NC001.

Log in as SYSDBA on NC001

SQL> create pluggable database app_container as application container admin user admin identified by oracle file_name_convert=('D:\app\beriv\oradata\NC001','D:\app\beriv\oradata\app_container');

Pluggable database created.

SQL> alter session set container=app_container;

Session altered.

SQL> alter pluggable database open;

Pluggable database altered.

SQL> create pluggable database APP_PDB1 admin user  pdb1admin identified by oracle file_name_convert=('D:\app\beriv\oradata','D:\app\beriv\oradata\APP_PDB1');

Pluggable database created.

SQL> alter pluggable database APP_PDB1 open;

Pluggable database altered.

We now have an application container called APP_CONTAINER. And this has a PDB called APP_PDB1.

Now let us log in into this APP_CONTAINER and create shared objects.We have created three tables here.

ML_T   : A metadata linked table

EL_T    : An extended data linked table

DL_T   : A data link table

To be able to create shared objects, the application root container must be in install or upgrade mode.

SQL> alter session set container=APP_CONTAINER;

Session altered.

SQL> alter pluggable database application app_container begin install '1';

Pluggable database altered.

SQL> create table ML_T SHARING=METADATA (A NUMBER);

Table created.

SQL> create table EL_T SHARING=EXTENDED DATA(A NUMBER);

Table created.

SQL> create table DL_T SHARING=DATA (A NUMBER);

Table created.

SQL> alter pluggable database application app_container end install '1';

Pluggable database altered.

SQL> alter session set container=APP_PDB1;

Session altered.

SQL> alter pluggable database application app_container sync;

Pluggable database altered.

METADATA LINK TABLE

======================

Table ML_T is a metadata linked table. The table definition or metadata is in the application container but the actual data segment is private to each of the containers. Sync command that I ran above is just a one time copy. So that Oracle can create the actual data segment in the PDBs. Now this table ML_T exists in APP_CONTAINER and also in APP_PDB1. They share the dictionary but the data is private to each of these containers.

SQL> alter session set container=APP_CONTAINER;

Session altered.

SQL> insert into ML_T values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from ML_T;

         A

----------

         1

SQL> alter session set container=APP_PDB1;

Session altered.

SQL> insert into ML_T values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from ML_T;

         A

----------

         2

As  you can see, ML_T data segment is private. APP_PDB1 can only see its own data (2) . While APP_CONTAINER can see its own version of data  (1).

EXTENDED DATA LINK TABLE
===========================

Table EL_T is an extended data link table. Metadata is shared and data exists in both the containers.
 Now the PDB can access its own data and also the data from the application container. 

SQL> alter session set container=APP_CONTAINER;

Session altered.

SQL> insert into EL_T values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from EL_T;

         A

----------

         1

SQL> alter session set container=APP_PDB1;

Session altered.

SQL> insert into EL_T values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from EL_T;

         A

----------

         2

         1

As you can see, this returns 1,2. It shows data from both the app_container and app_pdb1.
APP_PDB1 can modify its own rows but not the rows coming from the application container. 

DATA LINK TABLE
=================

DL_T is a data linked table. Data segment exists only in the app_container. Data cannot be modified in the PDB. But this PDB can view the data that's stored in the root container. 

SQL> alter session set container=APP_CONTAINER;

Session altered.

SQL> insert into DL_T values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter session set container=APP_PDB1;

Session altered.

SQL> select * from DL_T;

         A

----------

         1

Any attempt to modify this data would result in an error. 

SQL> update DL_T set A=99;

update DL_T set A=99

       *

ERROR at line 1:

ORA-65097: DML into a data link table is outside an application action

So far we have seen shared tables, similarly we can also create shared views. Let's look at couple of examples

SHARED VIEWS

==============

Here I have created a data link view DL_V. And this view selects from table ML_T which is a metadata linked table we created above.
I created another view EL_V which is an extended data link view. And this view also selects from the same metadata linked table ML_T.

SQL> alter session set container=APP_CONTAINER;

SQL> alter pluggable database application app_container begin upgrade '1' to '2';

Pluggable database altered.

SQL> create view DL_V SHARING=DATA AS (SELECT * FROM ML_T);

View created.

SQL> create view EL_V SHARING=EXTENDED DATA AS (SELECT * FROM ML_T);

View created.

SQL> alter pluggable database application app_container end upgrade;

Pluggable database altered.

SQL> alter session set container=APP_PDB1;

Session altered.

SQL> alter pluggable database application app_container sync;

Pluggable database altered.

SQL> select * from DL_V;

         A

----------

         1

Here I'm logged in as APP_PDB1. We know that selecting from table ML_T gives us the output of '2'. But selecting from this view DL_V we get '1'. This is because the view is itself defined as a data link. This instructs Oracle to pull the data from the application root container and not from the current PDB. If you want to pull the data from the current APP_PDB1 you can use the Oracle supplied function NO_OBJECT_LINK

SQL> select * from NO_OBJECT_LINK(DL_V);

         A

----------

         2

SQL> select * from EL_V;

         A

----------

         2

         1

We see the similar behaviour here. We know that selecting from the table ML_T gives us the output of '2'. But selecting from this view EL_V we get 1,2. Because the view is an extended data link, Oracle pulls the data both from the application root container(app_container) and the current PDB(app_pdb1). Again, if you want to pull the data only from the current PDB, use the function NO_OBJECT_LINK

SQL> select * from NO_OBJECT_LINK(DL_V);

         A

----------

         2

In the next article we will see how Oracle uses this data sharing framework to consolidate the AWR data in CDB-PDB.