When 12.1.0.2 came out, one of the features I wanted to play was the Cross-container functionality, and I finally had time to play with it.
First here is the description of the feature.
The
I decided to play with this and see what it really means.... And mostly to see the explain plan to see what happens under the covers.
One thing to mention at this point is that I granted DBA role to my new common user c##bgrenn. You also need to set individual privileges at each PDB level
I then went into all 3 PDB's and created a local copy of DBA_OBJECTS and DBA_TABLES.
Step 3 - Now that the same objects are created with data in all 3 PDB's, I can now do a combined query from the root PDB. The next step is create a table in the CDB (root PDB) that is empty.
Then finally query and see the rows in the local_objects tables across all the PDB's
Step 4 - Now to look at the explain plan for one of the tables across containers
Finally, this is the explain plan for a join query to get all the tables
This looks like a very useful feature to get a high level view of all PDB's. The things to note are.
1) You need to use a common user and this user needs to be the schema owner in all PDB's
2) By looking at the plan, I'm sure there is some high level view that does this using partitions.
First here is the description of the feature.
The
CONTAINERS clause is a new way of looking at
multitenant container databases (CDBs). With this clause, data can be
aggregated from a single identical table or view across many pluggable
databases (PDBs) from the root container. The CONTAINERS clause accepts a
table or view name as an input parameter that is expected to exist in
all PDBs in that container. Data from a single PDB or a set of PDBs can
be included with the use of CON_ID in the WHERE clause. I decided to play with this and see what it really means.... And mostly to see the explain plan to see what happens under the covers.
Step 1 -- The first thing you need to do is create a "common user". A common user is a new term that comes with Pluggable databases. A common user is a user which is created in the CDB (the Root container), and is then available as a user in all the PDB's that are part of the CDB. There are some rules around this.
- In Oracle Database 12c Release 1 (12.1.0.1), the name of a common user must begin with
C##orc##and the name of a local user must not begin withC##orc##. - Starting with Oracle Database 12c Release 1 (12.1.0.2):
- The name of a common user must begin with characters that are a case-insensitive match to the prefix specified by the
COMMON_USER_PREFIXinitialization parameter. By default, the prefix isC##. - The name of a local user must not begin with characters that are a case-insensitive match to the prefix specified by the
COMMON_USER_PREFIXinitialization parameter. Regardless of the value ofCOMMON_USER_PREFIX, the name of a local user can never begin withC##orc##.
So here goes for step 1 ..
$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 7 22:23:26 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL create user c##bgrenn identified by bgrenn;
grant dba to c##bgrenn;
User created.
SQL
Grant succeeded.
One thing to mention at this point is that I granted DBA role to my new common user c##bgrenn. You also need to set individual privileges at each PDB level
Step 2 -- Now that I have a common user (c##bgrenn), I now need to go into my pdb's and create the objects that I want to have shared across PDB's.
For simplicity, I chose DBA_TABLES, and DBA_OBJECTS.
The 3 PDBS I want to create this in are "orclpdb", "orclpdba" and "orcldbb"
SQL> COLUMN NAME FORMAT A15
COLUMN RESTRICTED FORMAT A10
COLUMN OPEN_TIME FORMAT A30
SELECT NAME, OPEN_MODE, con_id FROM V$PDBS
SQL /
NAME OPEN_MODE CON_ID
--------------- ---------- ----------
PDB$SEED READ ONLY 2
ORCLPDB READ WRITE 3
ORCLPDBA READ WRITE 4
ORCLPDBB READ WRITE 5
I then went into all 3 PDB's and created a local copy of DBA_OBJECTS and DBA_TABLES.
connect sys/oracle@localhost:1521/orclpdb as sysdba
grant dba to c##bgrenn;
create table c##bgrenn.local_objects as select * from dba_objects;
create table c##bgrenn.local_tables as select * from dba_tables; Connected.
SQLGrant succeeded.
SQL
Table created.
SQL Table created.
SQL
Step 3 - Now that the same objects are created with data in all 3 PDB's, I can now do a combined query from the root PDB. The next step is create a table in the CDB (root PDB) that is empty.
create table c##bgrenn.local_objects as select * from dba_objects where 1=0;
create table c##bgrenn.local_tables as select * from dba_tables where 1=0;
Table created.
Table created.
Then finally query and see the rows in the local_objects tables across all the PDB's
select count(*) from containers(c##bgrenn.local_objects) where con_id in (3) ;
COUNT(*)
----------
90925
select count(*) from containers(c##bgrenn.local_objects) where con_id in (4) ;
COUNT(*)
----------
90923
select count(*) from containers(c##bgrenn.local_objects) where con_id in (5) ;
COUNT(*)
----------
90925
select count(*) from containers(c##bgrenn.local_objects) ;
COUNT(*)
----------
272773
Step 4 - Now to look at the explain plan for one of the tables across containers
SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
SQL> SQL> Plan hash value: 1439328272
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 381 | 0 (0)| | | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 381 | | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX PARTITION LIST ALL | | 1 | 381 | | 1 | 254 | Q1,00 | PCWC | |
| 4 | FIXED TABLE FULL | X$CDBVW$ | 1 | 381 | | | | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------
Finally, this is the explain plan for a join query to get all the tables
explain plan for select * from containers(c##bgrenn.local_objects) a, containers(c##bgrenn.local_tables) b where a.object_name = b.table_name and a.object_type = 'TABLE'; SET LINESIZE 150 SET PAGESIZE 0 SELECT * FROM table(DBMS_XPLAN.DISPLAY); 2 3 4 5 6 7 Explained. SQL> SQL> SQL> SQL> Plan hash value: 198107036 ------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1226 | 0 (0)| 00:00:01| | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 1226 | 0 (0)| 00:00:01| | | Q1,02 | P-S | QC (RAND) | |* 3 | HASH JOIN BUFFERED | | 1 | 1226 | 0 (0)| 00:00:01| | | Q1,02 | PCWP | | | 4 | PX RECEIVE | | 1 | 381 | | | | | Q1,02 | PCWP | | | 5 | PX SEND HYBRID HASH | :TQ10000 | 1 | 381 | | | | | Q1,00 | P-P | HYBRID HASH| | 6 | STATISTICS COLLECTOR | | | | | | | | Q1,00 | PCWC | | | 7 | PX PARTITION LIST ALL | | 1 | 381 | | | 1 | 254 | Q1,00 | PCWC | | |* 8 | FIXED TABLE FULL | X$CDBVW$ | 1 | 381 | | | | | Q1,00 | PCWP | | | 9 | PX RECEIVE | | 1 | 845 ||| | | Q1,02 | PCWP | | | 10 | PX SEND HYBRID HASH | :TQ10001 | 1 | 845 | | | | | Q1,01 | P-P | HYBRID HASH| | 11 | PX PARTITION LIST ALL | | 1 | 845 | | | 1 | 254 | Q1,01 | PCWC | | | 12 | FIXED TABLE FULL | X$CDBVW$ | 1 | 845 | | | | | Q1,01 | PCWP | | ------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."OBJECT_NAME"="B"."TABLE_NAME") 8 - filter("A"."OBJECT_TYPE"='TABLE') 25 rows selected.
This looks like a very useful feature to get a high level view of all PDB's. The things to note are.
1) You need to use a common user and this user needs to be the schema owner in all PDB's
2) By looking at the plan, I'm sure there is some high level view that does this using partitions.