12.1.0.2 CDB views are now using CONTAINERS()

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.