Forum Stats

  • 3,852,723 Users
  • 2,264,130 Discussions
  • 7,905,127 Comments

Discussions

Strange behaviour with V$PDBS and CDB_SEGMENTS views on Oracle 12c

user13386370
user13386370 Member Posts: 9
edited Sep 9, 2013 8:03PM in Multitenant

Hi,

I am trying to execute a query between the CDB_SEGMENTS and the V$PDBS views on the ROOT container but I have a strange behavior:

SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 6 12:33:25 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[email protected] 06.09.2013> show con_id

CON_ID
------------------------------
1
[email protected] 06.09.2013> show con_name

CON_NAME
------------------------------
CDB$ROOT
[email protected] 06.09.2013> SELECT B.NAME
2 , A.CON_ID
3 , A.TABLESPACE_NAME
4 FROM CDB_SEGMENTS A
5 INNER JOIN V$PDBS B
6 ON B.CON_ID=A.CON_ID;

no rows selected

[email protected] 06.09.2013 12:34:17> SELECT B.NAME
2 , A.CON_ID
3 , A.TABLESPACE_NAME
4 FROM CDB_TABLESPACES A
5 INNER JOIN V$PDBS B
6 ON B.CON_ID=A.CON_ID;

NAME CON_ID TABLESPACE_NAME
------------------------------ ---------- ------------------------------
PDB$SEED 2 SYSTEM
PDB$SEED 2 SYSAUX
PDB$SEED 2 TEMP
PDBORCL 3 SYSTEM
PDBORCL 3 SYSAUX
PDBORCL 3 TEMP
PDBORCL 3 USERS
PDBORCL 3 EXAMPLE
...

23 rows selected.

[email protected] 06.09.2013> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('asa03y45g99zb'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID asa03y45g99zb, child number 0
-------------------------------------
SELECT B.NAME , A.CON_ID , A.TABLESPACE_NAME FROM
CDB_SEGMENTS A INNER JOIN V$PDBS B ON B.CON_ID=A.CON_ID

Plan hash value: 3590150304

-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| | | | | |
| 1 | NESTED LOOPS | | 333 | 14985 | 0 (0)| | | | | |
|* 2 | FIXED TABLE FULL | X$CON | 2 | 30 | 0 (0)| | | | | |
| 3 | PX COORDINATOR | | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10000 | 167 | 5010 | 0 (0)| | | Q1,00 | P->S | QC (RAND) |
| 5 | PX PARTITION LIST AND | | 167 | 5010 | 0 (0)|KEY(AP)|KEY(AP)| Q1,00 | PCWC | |
|* 6 | FIXED TABLE FIXED INDEX| X$CDBVW$e7cdf8a6 (ind:11) | 167 | 5010 | 0 (0)| | | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(("CON_ID">1 AND "INST_ID"=USERENV('INSTANCE')))
6 - filter("CON_ID"="CON_ID")


25 rows selected.

[email protected] 06.09.2013> select con_id,count(*) from cdb_segments group by con_id;

CON_ID COUNT(*)
---------- ----------
1 5795
4 4397
3 3923
2 3401

4 rows selected.

Basically I would like to display the container_name of the segments within all the PDBs, but the join between CDB_SEGMENTS and V$PDBS views gives no result (lines 20-25) meanwhile the join between CDB_TABLESPACES and V$PDBS works fine (lines 29-34). I have displayed the executed plan (lines 50-82).

Do you have any ideas on this behaviour?

Thank you in advance,

Arnaud.

Best Answer

  • Richard Harrison .
    Richard Harrison . Member Posts: 2,065 Gold Trophy
    Answer ✓

    Hi,

    Not 100% sure this is a bug or just something about the way containers work now - however this select seems to work OK:

    SELECT  B.NAME   , A.CON_ID     , A.TABLESPACE_NAME   

    FROM CDB_SEGMENTS A   ,V$PDBS B   

    where to_char(B.CON_ID)=to_char(A.CON_ID)

    See if that works for you too?

    Cheers,

    Harry

Answers

  • Richard Harrison .
    Richard Harrison . Member Posts: 2,065 Gold Trophy
    Answer ✓

    Hi,

    Not 100% sure this is a bug or just something about the way containers work now - however this select seems to work OK:

    SELECT  B.NAME   , A.CON_ID     , A.TABLESPACE_NAME   

    FROM CDB_SEGMENTS A   ,V$PDBS B   

    where to_char(B.CON_ID)=to_char(A.CON_ID)

    See if that works for you too?

    Cheers,

    Harry

  • Hello,

    Thank you for your solution that works for me too.

    You will find below the executed plan of the modified query:

    [email protected] 09.09.2013> SELECT *
      2    FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR('4vtqyb60b2huq'));
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    
    SQL_ID  4vtqyb60b2huq, child number 0
    -------------------------------------
    SELECT B.NAME  ,      A.CON_ID ,      A.TABLESPACE_NAME FROM
    CDB_SEGMENTS A INNER JOIN V$PDBS B   ON
    TO_CHAR(B.CON_ID)=TO_CHAR(A.CON_ID)
    
    Plan hash value: 2597912358
    
    -------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
    -------------------------------------------------------------------------------------------------------------------------------
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    
    |   0 | SELECT STATEMENT         |                  |       |       |     1 (100)|       |       |        |      |            |
    |*  1 |  HASH JOIN               |                  |  6667 |   292K|     0   (0)|       |       |        |      |            |
    |*  2 |   FIXED TABLE FULL       | X$CON            |     2 |    30 |     0   (0)|       |       |        |      |            |
    |   3 |   PX COORDINATOR         |                  |       |       |            |       |       |        |      |            |
    |   4 |    PX SEND QC (RANDOM)   | :TQ10000         | 10000 |   292K|     0   (0)|       |       |  Q1,00 | P->S | QC (RAND)  |
    |   5 |     PX PARTITION LIST ALL|                  | 10000 |   292K|     0   (0)|     1 |   254 |  Q1,00 | PCWC |            |
    |   6 |      FIXED TABLE FULL    | X$CDBVW$e7cdf8a6 | 10000 |   292K|     0   (0)|       |       |  Q1,00 | PCWP |            |
    -------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    
    
       1 - access(TO_CHAR("CON_ID")=TO_CHAR("CON_ID"))
       2 - filter(("CON_ID">1 AND "INST_ID"=USERENV('INSTANCE')))
    
    
    26 rows selected.
    

    This plan is different from the previous one e.g. HASH JOIN instead of NESTED LOOPS and FIXED TABLE FULL instead of FIXED TABLE FIXED INDEX.

    I have collected the fixed object statitics using:

    [email protected] 09.09.2013> exec dbms_stats.gather_fixed_objects_stats();
    
    PL/SQL procedure successfully completed.
    
    

    but the previous query still does not return any results. It might be linked to the CBO behaviour.

    Regards,

    Arnaud.

  • thbaby
    thbaby Member Posts: 5

    We are aware of this issue. We are tracking this as a bug. A fix is being worked on.

    Thanks,

    Thomas

This discussion has been closed.