12.1.0.2 CDB views are now using CONTAINERS()

By Franck Pachot

.
I’ve blogged about multitenant internals at the time when 12.1.0.1 was just released. Something has changed in 12.1.0.2 and blogging about it was in my todo list for a long time. Now the occasion to do it has been triggered by a question on Oracle Forums about CDB_DATA_FILES not showing PDB$SEED datafiles because there is an important change we must be aware of.

In the previous blog, I have described how you can query PDB information using ‘container data objects’, like the CDB_ views that are created by catcdbviews.sql, defined with the CDB$VIEW clause, which looks like a table function (but is not). It was not documented, and implemented internally with parallel query, partitioning and fixed table. Lot of internal details from Laurent Leturgez here.

12.1.0.2 has introduced the CONTAINERS() table function which does the same but is documented.

And CDB_ views are now using the CONTAINERS() clause instead of CDB$VIEW() one.

However, there is something different. By default the PDB$SEED is not queried. This comes from the initialization parameter exclude_seed_cdb_view which is true by default:
SQL> show parameter exclude_seed_cdb_view
 
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
exclude_seed_cdb_view boolean TRUE
 
SQL> select * from containers(dual);
 
D CON_ID
- ----------
X 3
X 1

You don’t see PDB$SEED here which is container id 2

We can get back to the 12.1.0.1 behaviour by setting the parameter to false:
SQL> alter session set exclude_seed_cdb_view=false;
 
Session altered.
 
SQL> select * from containers(dual);
 
D CON_ID
- ----------
X 2
X 1
X 3


So, what’s the point about that? If you are used to list the database datafiles by doing a select from DBA_DATA_FILES then you probably query CDB_DATA_FILES from root in multitenant. And you expect to have all files. You add control files and log file members and you have an exhaustive list of your database files. Maybe you use that in a backup or maintenance script.

Then if you do that in 12.1.0.2 you will miss the PDB$SEED. Except if you set exclude_seed_cdb_view to false. You can’t rely on CDB_DATA_FILES and that’s the important point raised in the Oracle Forum post.

I usually prefer to use RMAN for that and RMAN is right:


RMAN> connect target /
 
connected to target database: CDB1 (DBID=836194344)
 
RMAN> report schema;
 
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name CDB1
 
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 790 SYSTEM YES /ZFS01/oradata/CDB1/system01.dbf
3 610 SYSAUX NO /ZFS01/oradata/CDB1/sysaux01.dbf
4 160 UNDOTBS1 YES /ZFS01/oradata/CDB1/undotbs01.dbf
5 250 PDB$SEED:SYSTEM NO /ZFS01/oradata/CDB1/pdbseed/system01.dbf
6 5 USERS NO /ZFS01/oradata/CDB1/users01.dbf
7 490 PDB$SEED:SYSAUX NO /ZFS01/oradata/CDB1/pdbseed/sysaux01.dbf
8 270 PDB1:SYSTEM NO /ZFS01/oradata/CDB1/PDB1/system01.dbf
9 530 PDB1:SYSAUX NO /ZFS01/oradata/CDB1/PDB1/sysaux01.dbf
10 40 PDB1:USERS NO /ZFS01/oradata/CDB1/PDB1/SAMPLE_SCHEMA_users01.dbf
11 1345 PDB1:EXAMPLE NO /ZFS01/oradata/CDB1/PDB1/example01.dbf
 
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 275 TEMP 32767 /ZFS01/oradata/CDB1/temp01.dbf
2 20 PDB$SEED:TEMP 32767 /ZFS01/oradata/CDB1/pdbseed/pdbseed_temp012014-11-01_08-57-07-AM.dbf
3 20 PDB1:TEMP 32767 /ZFS01/oradata/CDB1/PDB1/PDB1_temp012014-11-01_09-10-16-AM.dbf

Where CDB_DATA_FILES by default shows only:
SQL> select con_id,file_id,file_name from cdb_data_files;
 
CON_ID FILE_ID FILE_NAME
---------- ---------- ----------------------------------------------------
1 1 /ZFS01/oradata/CDB1/system01.dbf
1 3 /ZFS01/oradata/CDB1/sysaux01.dbf
1 6 /ZFS01/oradata/CDB1/users01.dbf
1 4 /ZFS01/oradata/CDB1/undotbs01.dbf
3 8 /ZFS01/oradata/CDB1/PDB1/system01.dbf
3 9 /ZFS01/oradata/CDB1/PDB1/sysaux01.dbf
3 10 /ZFS01/oradata/CDB1/PDB1/SAMPLE_SCHEMA_users01.dbf
3 11 /ZFS01/oradata/CDB1/PDB1/example01.dbf


More internals…

Now what has change about CONTAINERS vs CDB$VIEW?

SQL> set autotrace trace explain
SQL> select * from cdb_data_files;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1439328272
 
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Pstart| Pstop |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20004 | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 20004 | | |
| 3 | PX PARTITION LIST ALL| | 20004 | 1 | 254 |
| 4 | FIXED TABLE FULL | X$CDBVW$ | 20004 | | |
--------------------------------------------------------------------

It’s still using parallel processes on a partitioned fixed table

SQL> alter session set "_px_cdb_view_enabled"=FALSE;
 
Session altered.
 
SQL> select * from cdb_data_files;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2351439557
 
---------------------------------------------------------------
| Id | Operation | Name | Rows | Pstart| Pstop |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20004 | | |
| 1 | PARTITION LIST ALL| | 20004 | 1 | 254 |
| 2 | FIXED TABLE FULL | X$CDBVW$ | 20004 | | |
---------------------------------------------------------------
 
SQL> alter session set "_partition_cdb_view_enabled"=FALSE;
 
Session altered.
 
SQL> select * from cdb_data_files;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1784620524
 
-----------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 20004 | 8185K|
| 1 | FIXED TABLE FULL| X$CDBVW$ | 20004 | 8185K|
-----------------------------------------------------

And documented parameters _px_cdb_view_enabled and _partition_cdb_view_enabled still control it.

Each partition is estimated to return 10000 rows (that’s hardcoded) so the estimation for 2 partitions (CDB$ROOT and PDB1) is about 20000.

However, that was after I tried to gather statistics for cdb views:

SQL> select dbms_pdb.update_cdbvw_stats from dual;

and I got statistics:

SQL> select * from cdbvw_stats$ where objname='DBA_DATA_FILES';
 
OBJNAME TIMESTAMP FLAGS ROWCNT SPARE1
------------------------------ --------- ---------- ---------- ----------
DBA_DATA_FILES 11-NOV-14 1 4

which are the number of datafiles in my CDB$ROOT. So I expected the estimation to be about 8. But that will probably be for another blog post…

What is important to know for the moment is that by default CDB_DATA_FILES don’t show all your database files.