3 Replies Latest reply: Sep 9, 2013 7:03 PM by thbaby RSS

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

    user13386370

      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
      
      SYS@orcl12c 06.09.2013> show con_id
      
      CON_ID
      ------------------------------
      1
      SYS@orcl12c 06.09.2013> show con_name
      
      CON_NAME
      ------------------------------
      CDB$ROOT
      SYS@orcl12c 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
      
      SYS@orcl12c 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.
      
      SYS@orcl12c 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.
      
      SYS@orcl12c 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.

        • 1. Re: Strange behaviour with V$PDBS and CDB_SEGMENTS views on Oracle 12c
          Richard Harrison .

          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

          • 2. Re: Strange behaviour with V$PDBS and CDB_SEGMENTS views on Oracle 12c
            user13386370

            Hello,

             

            Thank you for your solution that works for me too.

             

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

             

            SYS@orcl12c 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:

             

            SYS@orcl12c 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.

            • 3. Re: Strange behaviour with V$PDBS and CDB_SEGMENTS views on Oracle 12c
              thbaby

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

               

              Thanks,

               

              Thomas