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.