12c Multitenant internals: AWR tables and views

By Franck Pachot

.
I received a question about multitenant. AWR tables are often raised as an example of Object Links because automatic workload repository is stored only in CDB$ROOT. However, neither the views DBA_HIST… nor the tables WRH$_… are defined as Object Link.
Let’s explain on an example: DBA_HIST_SYSSTAT

I’ll talk about all those multitenant internals in DOAG 2016 DATENBANK Am 10. und 11. Mai 2016 in Düsseldorf #DOAGDB16 and that question was the occasion to prepare the following draft slide for it:

Since 12.1.0.2 the AWR views are a bit more complex and the switch to CDB$ROOT is done by the Object Link intermediate view: INT$DBA_HIST_SYSSTAT

Let’s show it. When you query DBA_HIST_SYSSTAT, if you’re not from SYS, you query the synonym:

SQL> select owner,object_name,object_type,sharing from dba_objects where object_name='DBA_HIST_SYSSTAT';
 
OWNER      OBJECT_NAME          OBJECT_TYPE          SHARING
---------- -------------------- -------------------- -------------
SYS        DBA_HIST_SYSSTAT     VIEW                 METADATA LINK
PUBLIC     DBA_HIST_SYSSTAT     SYNONYM              METADATA LINK
 
SQL> select owner,synonym_name,table_owner,table_name from dba_synonyms where synonym_name='DBA_HIST_SYSSTAT';
 
OWNER      SYNONYM_NAME         TABLE_OWNE TABLE_NAME
---------- -------------------- ---------- --------------------
PUBLIC     DBA_HIST_SYSSTAT     SYS        DBA_HIST_SYSSTAT

Both the synonym and the view are metadata link: we stay in our pluggable database.

Let’s check the definition of the view:

SQL> select owner,name,type,referenced_owner,referenced_name,referenced_type from dba_dependencies where name='INT$DBA_HIST_SYSSTAT';
 
OWNER      NAME                 TYPE               REFERENCED REFERENCED_NAME      REFERENCED_TYPE
---------- -------------------- ------------------ ---------- -------------------- --------------------
SYS        INT$DBA_HIST_SYSSTAT VIEW               SYS        WRH$_SYSSTAT         TABLE
SYS        INT$DBA_HIST_SYSSTAT VIEW               SYS        WRH$_STAT_NAME       TABLE
SYS        INT$DBA_HIST_SYSSTAT VIEW               SYS        DBA_HIST_SNAPSHOT    VIEW
 
SQL> select text from dba_views where view_name='DBA_HIST_SYSSTAT';
 
TEXT
--------------------------------------------------------------------------------
select "SNAP_ID","DBID","INSTANCE_NUMBER","STAT_ID","STAT_NAME","VALUE","CON_DBI
D","CON_ID" from INT$DBA_HIST_SYSSTAT

For each DBA_HIST view there is an underlying INT$DBA_HIST one, and this one is an Object Link:

SQL> select owner,object_name,object_type,sharing from dba_objects where object_name='INT$DBA_HIST_SYSSTAT';
 
OWNER      OBJECT_NAME          OBJECT_TYPE          SHARING
---------- -------------------- -------------------- -------------
SYS        INT$DBA_HIST_SYSSTAT VIEW                 OBJECT LINK

This is where the query switches to the CDB$ROOT to query data because of the Object Link.
This view reads the WRH$ table stored in the CDB$ROOT even when the query is run from a pluggable database.

SQL> select owner,name,type,referenced_owner,referenced_name,referenced_type from dba_dependencies where name='INT$DBA_HIST_SYSSTAT';
 
OWNER      NAME                 TYPE               REFERENCED REFERENCED_NAME      REFERENCED_TYPE
---------- -------------------- ------------------ ---------- -------------------- --------------------
SYS        INT$DBA_HIST_SYSSTAT VIEW               SYS        WRH$_SYSSTAT         TABLE
SYS        INT$DBA_HIST_SYSSTAT VIEW               SYS        WRH$_STAT_NAME       TABLE
SYS        INT$DBA_HIST_SYSSTAT VIEW               SYS        DBA_HIST_SNAPSHOT    VIEW
 
SQL> select text from dba_views where view_name='INT$DBA_HIST_SYSSTAT';
 
TEXT
--------------------------------------------------------------------------------
select s.snap_id, s.dbid, s.instance_number,
       s.stat_id, nm.stat_name, value,
       decode(s.con_dbid, 0, s.dbid, s.con_dbid),
       0 con_id
from DBA_HIST_SNAPSHOT sn, WRH$_SYSSTAT s, WRH$_STAT_NAME nm
where      s.stat_id          = nm.stat_id
      and  s.dbid             = nm.dbid
      and  s.snap_id          = sn.snap_id
      and  s.dbid             = sn.dbid
      and  s.instance_number  = sn.instance_number

This table is defined as Metadata Link. No need to have it as an Object Link because nobody is supposed to query it directly.

SQL> select owner,object_name,object_type,sharing from dba_objects where object_name='WRH$_SYSSTAT';
 
OWNER      OBJECT_NAME          OBJECT_TYPE          SHARING
---------- -------------------- -------------------- -------------
SYS        WRH$_SYSSTAT         TABLE PARTITION      NONE
SYS        WRH$_SYSSTAT         TABLE PARTITION      NONE
SYS        WRH$_SYSSTAT         TABLE PARTITION      NONE
SYS        WRH$_SYSSTAT         TABLE PARTITION      NONE
SYS        WRH$_SYSSTAT         TABLE PARTITION      NONE
SYS        WRH$_SYSSTAT         TABLE PARTITION      NONE
SYS        WRH$_SYSSTAT         TABLE PARTITION      NONE
SYS        WRH$_SYSSTAT         TABLE PARTITION      NONE
SYS        WRH$_SYSSTAT         TABLE                METADATA LINK

The multitenant dictionary may appear complex, but the basic mechanisms are simple and reliable. Think of Metadata Link and Object Link as flags that tells your session to switch to CDB$ROOT when getting metadata or data. The term ‘link’ is misleading in my opinion. It’s not a link. Just a runtime directive.