Oracle … as usual

February 25, 2014

Oracle Database 12c CDB$VIEW function

Filed under: 12c, CBO, Internals, tuning — Laurent @ 4:50 PM

In Oracle 12c multitenant container, there are some new views that complement the USER_, ALL_ and DBA_Views, the CDB_ views.

For example, CDB_TABLES, CDB_OBJECTS, CDB_VIEWS etc. Those views give information for all containers. For example, CDB_TABLES references all tables information in all containers (CDB$ROOT, PDB$SEED and all plugabble databases). Those views have a new column CON_ID that reference the container id, for example:

[SYS@CDB$ROOT | SID:CDB]> select con_id,count(*) from cdb_tables group by con_id;

    CON_ID   COUNT(*)
---------- ----------
         2       2316
         3       2316
         1       2324
 If you have a closer look to the view definition, you will find that every CDB_ view uses a specific function named CDB$VIEW:
[SYS@CDB$ROOT | SID:CDB]> select dbms_metadata.get_ddl('VIEW','CDB_TABLES') from dual;

DBMS_METADATA.GET_DDL('VIEW','CDB_TABLES')
--------------------------------------------------------------------------------
  CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."CDB_TABLES"  CONTAINER_DATA
 ("OWNER", "TABLE_NAME", "TABLESPACE_NAME", "CLUSTER_NAME", "IOT_NAME", "STATUS"
, "PCT_FREE", "PCT_USED", "INI_TRANS", "MAX_TRANS", "INITIAL_EXTENT", "NEXT_EXTE
NT", "MIN_EXTENTS", "MAX_EXTENTS", "PCT_INCREASE", "FREELISTS", "FREELIST_GROUPS
", "LOGGING", "BACKED_UP", "NUM_ROWS", "BLOCKS", "EMPTY_BLOCKS", "AVG_SPACE", "C
HAIN_CNT", "AVG_ROW_LEN", "AVG_SPACE_FREELIST_BLOCKS", "NUM_FREELIST_BLOCKS", "D
EGREE", "INSTANCES", "CACHE", "TABLE_LOCK", "SAMPLE_SIZE", "LAST_ANALYZED", "PAR
TITIONED", "IOT_TYPE", "TEMPORARY", "SECONDARY", "NESTED", "BUFFER_POOL", "FLASH
_CACHE", "CELL_FLASH_CACHE", "ROW_MOVEMENT", "GLOBAL_STATS", "USER_STATS", "DURA
TION", "SKIP_CORRUPT", "MONITORING", "CLUSTER_OWNER", "DEPENDENCIES", "COMPRESSI
ON", "COMPRESS_FOR", "DROPPED", "READ_ONLY", "SEGMENT_CREATED", "RESULT_CACHE",
"CLUSTERING", "ACTIVITY_TRACKING", "DML_TIMESTAMP", "HAS_IDENTITY", "CONTAINER_D
ATA", "CON_ID") AS
  SELECT "OWNER","TABLE_NAME","TABLESPACE_NAME","CLUSTER_NAME","IOT_NAME","STATU
S","PCT_FREE","PCT_USED","INI_TRANS","MAX_TRANS","INITIAL_EXTENT","NEXT_EXTENT",
"MIN_EXTENTS","MAX_EXTENTS","PCT_INCREASE","FREELISTS","FREELIST_GROUPS","LOGGIN
G","BACKED_UP","NUM_ROWS","BLOCKS","EMPTY_BLOCKS","AVG_SPACE","CHAIN_CNT","AVG_R
OW_LEN","AVG_SPACE_FREELIST_BLOCKS","NUM_FREELIST_BLOCKS","DEGREE","INSTANCES","
CACHE","TABLE_LOCK","SAMPLE_SIZE","LAST_ANALYZED","PARTITIONED","IOT_TYPE","TEMP
ORARY","SECONDARY","NESTED","BUFFER_POOL","FLASH_CACHE","CELL_FLASH_CACHE","ROW_
MOVEMENT","GLOBAL_STATS","USER_STATS","DURATION","SKIP_CORRUPT","MONITORING","CL
USTER_OWNER","DEPENDENCIES","COMPRESSION","COMPRESS_FOR","DROPPED","READ_ONLY","
SEGMENT_CREATED","RESULT_CACHE","CLUSTERING","ACTIVITY_TRACKING","DML_TIMESTAMP"
,"HAS_IDENTITY","CONTAINER_DATA","CON_ID" FROM CDB$VIEW("SYS"."DBA_TABLES")
After searching a long time, I didn’t find any definition of this function, so I decided to search more about its behaviour.
First of all, this function seems “to transform” the view or the table given in parameter by adding a CON_ID table. We will see later that it’s not a CBO transformation as we know it but a “low level” transformation performed before SQL parsing.
CDB$VIEW function can be used with static views (DBA_TABLES, DBA_OBJECTS etc.), dictionary tables (OBJ$, USER$, FILE$ etc.), on dynamic performance views and X$ fixed tables, but on those two last items, the result is a little bit different than other.
[SYS@CDB$ROOT | SID:CDB]> select count(*) from cdb$view("DBA_TABLES");

  COUNT(*)
----------
      6956

[SYS@CDB$ROOT | SID:CDB]> select count(*) from cdb$view("OBJ$");

  COUNT(*)
----------
    272326
In a multitenant database, each container has its own dictionary, for example, CDB$ROOT has its OBJ$ table, the PDB$SEED has its own one and every pluggable database has its own OBJ$ table. So, the goal of this function is to give a global view from all containers.
But, as the OBJ$ table has the same name in every container, and as there’s no method to access a specific dictionary table of a pluggable database from the root container, the CDB$VIEW function is used to aggregate data from a specific view or table executed in each container:
[SYS@CDB$ROOT | SID:CDB]> show con_id;

CON_ID
------------------------------
1
[SYS@CDB$ROOT | SID:CDB]> select count(*) from obj$;

  COUNT(*)
----------
     90847

[SYS@CDB$ROOT | SID:CDB]>  select con_id,count(*) from cdb$view("OBJ$") group by con_id;

    CON_ID   COUNT(*)
---------- ----------
         1      90847
         2      90716
         3      90763
Now, let’s have a closer look to the generated plan:
[SYS@CDB$ROOT | SID:CDB]> select count(*) from obj$;

  COUNT(*)
----------
     90847

Execution Plan
----------------------------------------------------------
Plan hash value: 3951003077

------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |    91   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |        |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| I_OBJ1 | 90910 |    91   (0)| 00:00:01 |
------------------------------------------------------------------------
[SYS@CDB$ROOT | SID:CDB]>  select count(*) from cdb$view("OBJ$");

  COUNT(*)
----------
    272326

Execution Plan
----------------------------------------------------------
Plan hash value: 2345629731

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name             | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                  |     1 |     2 (100)| 00:00:01 |       |       |        |      |            |
|   1 |  SORT AGGREGATE           |                  |     1 |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR          |                  |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000         |     1 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |                  |     1 |            |          |       |       |  Q1,00 | PCWP |            |
|   5 |      PX PARTITION LIST ALL|                  | 90910 |     2 (100)| 00:00:01 |     1 |   254 |  Q1,00 | PCWC |            |
|   6 |       FIXED TABLE FULL    | X$CDBVW$c2fad3da | 90910 |     2 (100)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------
There are two interesting things to note:
  • CDB$VIEW transform the statement to query an X$CDBVW$c2fad3da fixed table
  • This fixed table is read in parallel

About the transformation, I noticed many things.

First, if we query another table or view, another X$CDBVW$ is generated:

[SYS@CDB$ROOT | SID:CDB]> select count(*) from cdb$view("TAB$");

  COUNT(*)
----------
      7098

Execution Plan
----------------------------------------------------------
Plan hash value: 111784239

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name             | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                  |     1 |     0   (0)| 00:00:01 |       |       |        |      |            |
|   1 |  SORT AGGREGATE           |                  |     1 |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR          |                  |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000         |     1 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |                  |     1 |            |          |       |       |  Q1,00 | PCWP |            |
|   5 |      PX PARTITION LIST ALL|                  |  2372 |     0   (0)| 00:00:01 |     1 |   254 |  Q1,00 | PCWC |            |
|   6 |       FIXED TABLE FULL    | X$CDBVW$00303d4d |  2372 |     0   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------
So, it seems that there’s a hash added at the end of this X$CDBVW$. It’s interesting to see that the fixed table X$CDBVW$ exists in the instance but have no rows recorded in:
[SYS@CDB$ROOT | SID:CDB]> desc X$CDBVW$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 CON_ID                                             NUMBER

[SYS@CDB$ROOT | SID:CDB]> select * from X$CDBVW$;

no rows selected
Second thing to note is this transformation is not a CBO transformation, indeed if you have a closer look to a 10053 trace file, the query has already been rewrited before parsing:
Stmt: ******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "SYS"."X$CDBVW$00303d4d" "TAB$"
Objects referenced in the statement
  X$CDBVW$00303d4d[TAB$] 4, type = 1
Objects in the hash table
  Hash table Object 4, type = 1, ownerid = 795352840147398986:
    Dynamic Sampling Directives at location 1:
       dirid = 11259849835960924452, state = 5, flags = 1, loc = 1 {E(4)[30]}
Return code in qosdInitDirCtx: ENBLD
Concerning the parallel execution, I noticed partition pruning is made upon the CON_ID column:
[SYS@CDB$ROOT | SID:CDB]> select count(*) from cdb$view("TAB$") where con_id=1;

  COUNT(*)
----------
      2372

Execution Plan
----------------------------------------------------------
Plan hash value: 2579428923

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     1 |    13 |     0   (0)| 00:00:01 |       |       |        |      |            |
|   1 |  SORT AGGREGATE              |                  |     1 |    13 |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR             |                  |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)       | :TQ10000         |     1 |    13 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE           |                  |     1 |    13 |            |          |       |       |  Q1,00 | PCWP |            |
|   5 |      PX PARTITION LIST SINGLE|                  |    24 |   312 |     0   (0)| 00:00:01 |     1 |     1 |  Q1,00 | PCWC |            |
|   6 |       FIXED TABLE FULL       | X$CDBVW$00303d4d |    24 |   312 |     0   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------------
 To sum up, cdb$view function creates a fixed table on the fly and load data taken from all containers, the CON_ID column in this fixed table references the container id from where data are read. This fixed table is partitioned by CON_ID and reading this table is made by default in parallel mode (partition pruning is made on the CON_ID column).
There are two hidden parameters used to change this behaviour:
  • _partition_cdb_view_enabled: this parameter seems to disable the partitioning of the X$CDBVW$ view
[SYS@CDB$ROOT | SID:CDB]> alter session set "_partition_cdb_view_enabled"=FALSE;

Session altered.

[SYS@CDB$ROOT | SID:CDB]> select count(*) from cdb$view("TAB$");

  COUNT(*)
----------
      7098

Execution Plan
----------------------------------------------------------
Plan hash value: 4209588238

------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |     1 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                  |     1 |            |          |
|   2 |   FIXED TABLE FULL| X$CDBVW$00303d4d |  2372 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------
  • _px_cdb_view_enabled: this parameter seems to disable parallel scan of the X$CDBVW$ view
[SYS@CDB$ROOT | SID:CDB]> alter session set "_px_cdb_view_enabled"=FALSE;

Session altered.

[SYS@CDB$ROOT | SID:CDB]> select count(*) from cdb$view("TAB$");

  COUNT(*)
----------
      7098

Execution Plan
----------------------------------------------------------
Plan hash value: 3630495286

------------------------------------------------------------------------------------------------
| Id  | Operation           | Name             | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                  |     1 |     0   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE     |                  |     1 |            |          |       |       |
|   2 |   PARTITION LIST ALL|                  |  2372 |     0   (0)| 00:00:01 |     1 |   254 |
|   3 |    FIXED TABLE FULL | X$CDBVW$00303d4d |  2372 |     0   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------

On the CBO side, I tried to have a closer look on how statistics are evaluated for this particular view.

It seems that the CBO uses the cardinality of the dictionary table available in the CDB$ROOT container and ignore the statistics of the other dictionary table (taken from the other containers):

[SYS@CDB$ROOT | SID:CDB]> select con_id,table_name,num_rows from cdb_tables where table_name='TAB$';

    CON_ID TABLE_NAME        NUM_ROWS
---------- --------------- ----------
         1 TAB$                  2372
         2 TAB$                  1462
         3 TAB$                  2363

[SYS@CDB$ROOT | SID:CDB]> alter session set events '10053 trace name context forever, level 1';

Session altered.

[SYS@CDB$ROOT | SID:CDB]> select /* PARSE_ME */ count(*) from cdb$view("TAB$");

  COUNT(*)
----------
      7098
 But If we have a closer look to the generated 10053 trace file, and the cardinalities seems to be a little bit different from reality:
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: X$CDBVW$00303d4d  Alias: TAB$
  #Rows: 2372  #Blks:  1487  AvgRowLen:  134.00  ChainCnt:  0.00
  FixedTabRowCost:  1000.00
=======================================
SPD: BEGIN context at query block level
=======================================
Query Block SEL$1 (#0)
Return code in qosdSetupDirCtx4QB: NOCTX
=====================================
SPD: END context at query block level
=====================================
Access path analysis for X$CDBVW$00303d4d
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for X$CDBVW$00303d4d[TAB$]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Table: X$CDBVW$00303d4d  Alias: TAB$
    Card: Original: 2372.000000  Rounded: 2372  Computed: 2372.00  Non Adjusted: 2372.00
  Access Path: TableScan
    Cost:  0.06  Resp: 0.06  Degree: 0
      Cost_io: 0.00  Cost_cpu: 2372000
      Resp_io: 0.00  Resp_cpu: 2372000
  Best:: AccessPath: TableScan
         Cost: 0.06  Degree: 1  Resp: 0.06  Card: 2372.00  Bytes: 0

***************************************
 .../...
============
Plan Table
============
-----------------------------------------------------+-----------------------------------+-------------------------+---------------+
| Id  | Operation                  | Name            | Rows  | Bytes | Cost  | Time      |  TQ  |IN-OUT|PQ Distrib | Pstart| Pstop |
-----------------------------------------------------+-----------------------------------+-------------------------+---------------+
| 0   | SELECT STATEMENT           |                 |       |       |     1 |           |      |      |           |       |       |
| 1   |  SORT AGGREGATE            |                 |     1 |       |       |           |      |      |           |       |       |
| 2   |   PX COORDINATOR           |                 |       |       |       |           |      |      |           |       |       |
| 3   |    PX SEND QC (RANDOM)     | :TQ10000        |     1 |       |       |           |:Q1000| P->S |QC (RANDOM)|       |       |
| 4   |     SORT AGGREGATE         |                 |     1 |       |       |           |:Q1000| PCWP |           |       |       |
| 5   |      PX PARTITION LIST ALL |                 |  2372 |       |     0 |           |:Q1000| PCWC |           | 1     | 254   |
| 6   |       FIXED TABLE FULL     | X$CDBVW$00303d4d|  2372 |       |     0 |           |:Q1000| PCWP |           |       |       |
-----------------------------------------------------+-----------------------------------+-------------------------+---------------+
Now imagine, you have consolidated a multitenant database with 200 pdbs, each pdb host an ERP with tons of table, you will have a small difference between evaluation and real row count.
To conclude this part, If you perform the same analysis but you query the CDB_TABLES view, this one will be rewrite in CDB$VIEW(“DBA_TABLES”). As the DBA_TABLES is a view and is not analyzed, Oracle seems to set the cardinality to 10000 rows. I have made some tests on many views and it’s always the same cardinality : 10000 rows and 0 block.
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: X$CDBVW$73c6ce97  Alias: DBA_TABLES  (NOT ANALYZED)
  #Rows: 10000  #Blks:  0  AvgRowLen:  464.00  ChainCnt:  0.00
  FixedTabRowCost:  1000.00
=======================================
SPD: BEGIN context at query block level
=======================================
Query Block SEL$F5BB74E1 (#0)
Return code in qosdSetupDirCtx4QB: NOCTX
=====================================
SPD: END context at query block level
=====================================
Access path analysis for X$CDBVW$73c6ce97
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for X$CDBVW$73c6ce97[DBA_TABLES]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Table: X$CDBVW$73c6ce97  Alias: DBA_TABLES
    Card: Original: 10000.000000  Rounded: 10000  Computed: 10000.00  Non Adjusted: 10000.00
  Access Path: TableScan
    Cost:  0.25  Resp: 0.25  Degree: 0
      Cost_io: 0.00  Cost_cpu: 10000000
      Resp_io: 0.00  Resp_cpu: 10000000
  Best:: AccessPath: TableScan
         Cost: 0.25  Degree: 1  Resp: 0.25  Card: 10000.00  Bytes: 0

***************************************
To conclude this part, even if statistics on CDB$VIEW(“DBA_TABLES”) are always set to 10000 rows, it is safe to use CDB_ views directly instead on CDB$VIEW function on a dictionary table, even if I didn’t find any more accurate method.
The last researches I’ve made is on how the Oracle kernel will transform the query before parsing.
As we saw previously, the CBO receives an unparsed query which is already rewritten with the X$CDBVW$ view, so the transformation might be performed at a low level.
In Oracle 12c there’s a new object file fitted in libserver.a library (you can get this file in the 12.1 PSU2 files directory). This object file is named kpdbcv.o and it contains many function named kpdbcv* (I think this means Kernel PDB Cdb View).
[oracle@oel63 libserver12.a]$ pwd
/home/oracle/patch/17552800/files/lib/libserver12.a
[oracle@oel63 libserver12.a]$ readelf -a kpdbcv.o | grep FUNC
     7: 0000000000000270   448 FUNC    LOCAL  DEFAULT    4 kpdbcvRwtToInlineView
    10: 00000000000008f0  1216 FUNC    LOCAL  DEFAULT    4 kpdbcvLoadFragDescr
    13: 0000000000001c60    96 FUNC    LOCAL  DEFAULT    4 kpdbcvLoadBaseInfo
    14: 00000000000003b0    80 FUNC    LOCAL  DEFAULT    3 kpdbcvBuildColNames
    15: 0000000000009d50  1680 FUNC    LOCAL  DEFAULT    3 kpdbcvGetSysCtxCurrUser
    16: 0000000000001940  7616 FUNC    LOCAL  DEFAULT    3 kpdbcvFetchCbkCon
    17: 0000000000003700    96 FUNC    LOCAL  DEFAULT    3 kpdbcvFetchCbkSys
    18: 0000000000004ff0  1680 FUNC    LOCAL  DEFAULT    3 kpdbcvInsBaseViewStats
    19: 0000000000005680  1744 FUNC    LOCAL  DEFAULT    3 kpdbcvGetBaseViewRowCnt
    20: 0000000000006bc0  1792 FUNC    LOCAL  DEFAULT    3 kpdbcvGetBaseViewStats
    21: 00000000000072c0  1136 FUNC    LOCAL  DEFAULT    3 kpdbcvGetFxdViewStats
    22: 0000000000007730   544 FUNC    LOCAL  DEFAULT    3 kpdbcvFxdViewStatsCbk
    23: 0000000000007b30    80 FUNC    LOCAL  DEFAULT    3 kpdbcvGetConIdCnt
    24: 0000000000007b80   448 FUNC    LOCAL  DEFAULT    3 kpdbcvConIdMorph
    25: 0000000000007d40    80 FUNC    LOCAL  DEFAULT    3 kpdbcvGatherBind
    26: 0000000000007d90  2304 FUNC    LOCAL  DEFAULT    3 kpdbcvPrintPredText
    27: 0000000000008690   304 FUNC    LOCAL  DEFAULT    3 kpdbcvVisitPreds
    28: 00000000000087c0   384 FUNC    LOCAL  DEFAULT    3 kpdbcvVisitOpns
    29: 0000000000008940   384 FUNC    LOCAL  DEFAULT    3 kpdbcvReplaceBind
    30: 0000000000008ac0  2944 FUNC    LOCAL  DEFAULT    3 kpdbcvIsValidWhere
    31: 0000000000009640  1648 FUNC    LOCAL  DEFAULT    3 kpdbcvPredicateToText
    37: 0000000000000010    32 FUNC    GLOBAL DEFAULT    3 kpdbcdbvwcbk
    38: 0000000000000030    16 FUNC    GLOBAL DEFAULT    3 kpdbcvComDataCbk
    40: 0000000000000010   608 FUNC    GLOBAL DEFAULT    4 kpdbcvXformFrodef
    52: 0000000000000430  1216 FUNC    GLOBAL DEFAULT    4 kpdbcvLoadPartDescr
    73: 0000000000000db0   320 FUNC    GLOBAL DEFAULT    4 kpdbcvIsPartitioned
    88: 0000000000000050   784 FUNC    GLOBAL DEFAULT    3 kpdbcvIsParallelizable
    93: 0000000000000360    64 FUNC    GLOBAL DEFAULT    3 kpdbcvGetDOP
    99: 0000000000000f00  3232 FUNC    GLOBAL DEFAULT    4 kpdbcvLoad
   135: 0000000000000400  5440 FUNC    GLOBAL DEFAULT    3 kpdbcvFetchStart
   201: 0000000000003760  4560 FUNC    GLOBAL DEFAULT    3 kpdbcvFetch
   216: 0000000000004930   304 FUNC    GLOBAL DEFAULT    3 kpdbcvCleanup
   217: 0000000000001bb0   160 FUNC    GLOBAL DEFAULT    4 kpdbcvIsCDBViewOwner
   219: 0000000000004a70  1408 FUNC    GLOBAL DEFAULT    3 kpdbcvDelALLBaseViewStats
   221: 0000000000005d50  3696 FUNC    GLOBAL DEFAULT    3 kpdbcvInsALLBaseViewStats
   222: 0000000000007950    48 FUNC    GLOBAL DEFAULT    3 kpdbcvCDBViewStatsCbk
   223: 0000000000007980    48 FUNC    GLOBAL DEFAULT    3 kpdbcvComDataStatsCbk
   224: 00000000000079b0   384 FUNC    GLOBAL DEFAULT    3 kpdbcvGetParamValue
   241: 0000000000009cb0   160 FUNC    GLOBAL DEFAULT    3 kpdbcvAllocate
Depending on the context (cdb view partitoning enabled or not, parallel scan enabled or not), you will have a different call stack, but with gdb I found that the common point is the first function called which is kpdbcvXformFrodef function.
If you are curious and want to view the kpdbcv* function call stack during a select on CDB_ view, you can use gdb:
(gdb) rbreak ^kpdbcv
(gdb) commands
 Type commands for breakpoint(s) 1-38, one per line.
 End with a line saying just "end".
 >continue
 >end
(gdb) c
Continuing.

Breakpoint 1, 0x0000000002578620 in kpdbcvXformFrodef ()

Breakpoint 6, 0x0000000002579510 in kpdbcvLoad ()

Breakpoint 5, 0x00000000025793c0 in kpdbcvIsPartitioned ()

Breakpoint 5, 0x00000000025793c0 in kpdbcvIsPartitioned ()

Breakpoint 3, 0x0000000002578a40 in kpdbcvLoadPartDescr ()

Breakpoint 4, 0x0000000002578f00 in kpdbcvLoadFragDescr ()

Breakpoint 4, 0x0000000002578f00 in kpdbcvLoadFragDescr ()

.../...
Breakpoint 4, 0x0000000002578f00 in kpdbcvLoadFragDescr ()

Breakpoint 4, 0x0000000002578f00 in kpdbcvLoadFragDescr ()

Breakpoint 4, 0x0000000002578f00 in kpdbcvLoadFragDescr ()

Breakpoint 7, 0x000000000257a1c0 in kpdbcvIsCDBViewOwner ()

Breakpoint 25, 0x00000000083cd580 in kpdbcvCDBViewStatsCbk ()

Breakpoint 24, 0x00000000083cd360 in kpdbcvFxdViewStatsCbk ()

Breakpoint 23, 0x00000000083ccef0 in kpdbcvGetFxdViewStats ()

Breakpoint 22, 0x00000000083cc7f0 in kpdbcvGetBaseViewStats ()

Breakpoint 22, 0x00000000083cc7f0 in kpdbcvGetBaseViewStats ()

Breakpoint 10, 0x00000000083c5c80 in kpdbcvIsParallelizable ()

Breakpoint 5, 0x00000000025793c0 in kpdbcvIsPartitioned ()

Breakpoint 11, 0x00000000083c5f90 in kpdbcvGetDOP ()

Breakpoint 37, 0x00000000083cf8e0 in kpdbcvAllocate ()

Breakpoint 36, 0x00000000083cf270 in kpdbcvPredicateToText ()
To sum up this post, we have to keep in mind that queries on CDB_ views use an internal function CDB$VIEW that transform the sql text before parsing and as a result, there is a new fixed table X$CDBVW$ concatenated to a hash that might identify the source table or view. The statement that will run by default in parallel mode because the X$CDBVW$ fixed table is build as partitioned (by default).
On the CBO side, the result of the CDB_ view transformation give an object which have a default cardinality that seems to be always set at a value of 10000 rows. The result is quite different if you try to run CDB$VIEW on dictionary tables (OBJ$, TAB$ etc) and can give some trouble if you have a lot of PDBs opened in your container.
All this transformations seem to be hard coded (partially) in an object file kpdbcv.o included in the libserver12.a library.

January 14, 2014

Linux monitoring of oracle 12c multi-threaded instances

Filed under: 12c, Administration, Linux, tools — Laurent @ 11:05 PM

Oracle 12c comes with a new feature: multithreaded server. In summary, main processes like real time scheduled processes (vktm, lms), or main processes like pmon or dbwn continue to run as processes. For other ones (lgwr, mmon, server processes etc.), they run now in a thread.

This feature has been developed to optimize oracle to be run on new processors with many core and many threads per core (for example SPARC T Processors), but the DBA will have to change many methods he use to analyze problems in a multi-threaded server.

If for some problems, you usually analyze the OS side, top, ps, and other tools have to be used in a different way. Let’s see different tools that can be used to analyze processes and thread in linux (Tools mentioned here has been tested with Oracle Enterprise Linux 6).

For all example above, I used an orcl instance which run in a multi-threaded configuration

  • ps

If I run a simple ps under my config, there are only 6 processes:

[oracle@oel64-12c ~]$ ps -ef | grep [o]rcl
oracle    9871     1  0 21:02 ?        00:00:00 ora_pmon_orcl
oracle    9873     1  0 21:02 ?        00:00:00 ora_psp0_orcl
oracle    9878     1  5 21:02 ?        00:01:35 ora_vktm_orcl
oracle    9882     1  0 21:02 ?        00:00:02 ora_u004_orcl
oracle    9888     1  0 21:02 ?        00:00:11 ora_u005_orcl
oracle    9894     1  0 21:02 ?        00:00:00 ora_dbw0_orcl
 If I want to print all threads that run in these processes, I can run this command:
[oracle@oel64-12c ~]$ ps -eLo pid,pcpu,tid,user,comm,cmd | sed -n -e '1p' -e '/orcl/p'
  PID %CPU   TID USER     COMMAND         CMD
 9871  0.0  9871 oracle   ora_pmon_orcl   ora_pmon_orcl
 9873  0.0  9873 oracle   ora_psp0_orcl   ora_psp0_orcl
 9878  5.2  9878 oracle   ora_vktm_orcl   ora_vktm_orcl
 9882  0.0  9882 oracle   ora_scmn_orcl   ora_u004_orcl
 9882  0.0  9883 oracle   oracle          ora_u004_orcl
 9882  0.0  9884 oracle   ora_gen0_orcl   ora_u004_orcl
 9882  0.0  9885 oracle   ora_mman_orcl   ora_u004_orcl
 9882  0.0  9891 oracle   ora_dbrm_orcl   ora_u004_orcl
 9882  0.0  9895 oracle   ora_lgwr_orcl   ora_u004_orcl
 9882  0.0  9896 oracle   ora_ckpt_orcl   ora_u004_orcl
 9882  0.0  9897 oracle   ora_lg00_orcl   ora_u004_orcl
 9882  0.0  9898 oracle   ora_lg01_orcl   ora_u004_orcl
 9882  0.0  9899 oracle   ora_smon_orcl   ora_u004_orcl
 9882  0.0  9901 oracle   ora_lreg_orcl   ora_u004_orcl
 9888  0.0  9888 oracle   ora_scmn_orcl   ora_u005_orcl
 9888  0.0  9889 oracle   oracle          ora_u005_orcl
 9888  0.0  9890 oracle   ora_diag_orcl   ora_u005_orcl
 9888  0.0  9892 oracle   ora_dia0_orcl   ora_u005_orcl
 9888  0.0  9900 oracle   ora_reco_orcl   ora_u005_orcl
 9888  0.0  9902 oracle   ora_mmon_orcl   ora_u005_orcl
 9888  0.0  9903 oracle   ora_mmnl_orcl   ora_u005_orcl
 9888  0.0  9904 oracle   ora_d000_orcl   ora_u005_orcl
 9888  0.0  9905 oracle   ora_s000_orcl   ora_u005_orcl
 9888  0.0  9906 oracle   ora_n000_orcl   ora_u005_orcl
 9888  1.3  9931 oracle   oracle_9931_orc ora_u005_orcl
 9888  0.0  9932 oracle   ora_tmon_orcl   ora_u005_orcl
 9888  0.0  9933 oracle   ora_tt00_orcl   ora_u005_orcl
 9888  0.0  9934 oracle   ora_smco_orcl   ora_u005_orcl
 9888  0.0  9938 oracle   ora_fbda_orcl   ora_u005_orcl
 9888  0.0  9939 oracle   ora_aqpc_orcl   ora_u005_orcl
 9888  0.0  9944 oracle   ora_p000_orcl   ora_u005_orcl
 9888  0.0  9945 oracle   ora_p001_orcl   ora_u005_orcl
 9888  0.0  9946 oracle   ora_p002_orcl   ora_u005_orcl
 9888  0.0  9947 oracle   ora_p003_orcl   ora_u005_orcl
 9888  0.0  9948 oracle   ora_p004_orcl   ora_u005_orcl
 9888  0.0  9949 oracle   ora_p005_orcl   ora_u005_orcl
 9888  0.0  9950 oracle   ora_p006_orcl   ora_u005_orcl
 9888  0.0  9951 oracle   ora_p007_orcl   ora_u005_orcl
 9888  0.0  9952 oracle   ora_cjq0_orcl   ora_u005_orcl
 9888  0.0  9996 oracle   ora_qm02_orcl   ora_u005_orcl
 9888  0.0  9998 oracle   ora_q002_orcl   ora_u005_orcl
 9888  0.0  9999 oracle   ora_q003_orcl   ora_u005_orcl
 9888  0.0 16009 oracle   ora_w000_orcl   ora_u005_orcl
 9894  0.0  9894 oracle   ora_dbw0_orcl   ora_dbw0_orcl
16414  0.0 16414 oracle   sed             sed -n -e 1p -e /orcl/p
 First column is the PID, second column is the CPU percent burn by the thread, third column is the thread Id, next column is the thread owner, the second last column is the oracle thread name and the last is the process name.
With ps, you can have a static view and possibly identify problematic processes
  • top

With top, you can see you processes or threads in a more dynamic fashion. Top option used to see threads is -H, but you have to mention which processes you want to analyze with -p parameter followed by pids. The main drawback of this command is that -p is limited to 20 pids but for a mid size multi-threaded instance, it’s ok.

[oracle@oel64-12c ~]$ top -p $(pgrep -d',' orcl$) -H
top - 21:55:37 up 1 day,  2:03,  6 users,  load average: 1.10, 1.04, 1.13
Tasks:  43 total,   0 running,  43 sleeping,   0 stopped,   0 zombie
Cpu0  : 25.8%us, 46.5%sy,  0.0%ni, 14.5%id, 10.3%wa,  0.0%hi,  3.0%si,  0.0%st
Cpu1  : 16.3%us, 46.8%sy,  0.0%ni, 28.9%id,  7.8%wa,  0.0%hi,  0.3%si,  0.0%st
Mem:   4055296k total,  3052640k used,  1002656k free,    20360k buffers
Swap:  8388604k total,  1475216k used,  6913388k free,  2302200k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 9878 oracle    -2   0 1489m  17m  15m S  6.7  0.4   3:03.07 ora_vktm_orcl
 9892 oracle    20   0 3457m 340m 252m S  0.5  8.6   0:01.27 ora_dia0_orcl
 9902 oracle    20   0 3457m 340m 252m S  0.5  8.6   0:02.00 ora_mmon_orcl
 9871 oracle    20   0 1489m  21m  19m S  0.0  0.5   0:00.33 ora_pmon_orcl
 9873 oracle    20   0 1489m  17m  15m S  0.0  0.4   0:01.17 ora_psp0_orcl
 9882 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.07 ora_scmn_orcl
 9883 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.00 oracle
 9884 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.25 ora_gen0_orcl
 9885 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.25 ora_mman_orcl
 9891 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.24 ora_dbrm_orcl
 9895 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.33 ora_lgwr_orcl
 9896 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:01.08 ora_ckpt_orcl
 9897 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.12 ora_lg00_orcl
 9898 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.03 ora_lg01_orcl
 9899 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.07 ora_smon_orcl
 9901 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.13 ora_lreg_orcl
 9888 oracle    20   0 3457m 340m 252m S  0.0  8.6   0:00.40 ora_scmn_orcl
 9889 oracle    20   0 3457m 340m 252m S  0.0  8.6   0:00.01 oracle
  • pidstat

pidstat is a command which appears in OEL6. It runs like a vmstat or mpstat with an interval and a counter, but it gives information of how evolve cpu, io, memory consumption for a specific process.

For example, to see cpu consumption every second for the process with pid 9888

[oracle@oel64-12c ~]$ pidstat -p 9888 -u 1
Linux 2.6.39-400.17.1.el6uek.x86_64 (oel64-12c.localdomain)     01/14/2014      _x86_64_        (2 CPU)

10:03:14 PM       PID    %usr %system  %guest    %CPU   CPU  Command
10:03:15 PM      9888    0.00    0.00    0.00    0.00     0  ora_scmn_orcl
10:03:16 PM      9888    1.00    0.00    0.00    1.00     1  ora_scmn_orcl
10:03:17 PM      9888    0.00    0.00    0.00    0.00     1  ora_scmn_orcl
10:03:18 PM      9888    1.00    1.00    0.00    2.00     1  ora_scmn_orcl
Please note that the process name is ora_u005_orcl but it’s printed with the command name which is, in fact, the thread name.

So if you want to see every thread in this process, you need to use -t option:

[oracle@oel64-12c ~]$ pidstat -p 9888 -u -t 1
Linux 2.6.39-400.17.1.el6uek.x86_64 (oel64-12c.localdomain)     01/14/2014      _x86_64_        (2 CPU)

10:08:42 PM      TGID       TID    %usr %system  %guest    %CPU   CPU  Command
10:08:43 PM      9888         -    0.00    0.00    0.00    0.00     1  ora_scmn_orcl
10:08:43 PM         -      9888    0.00    0.00    0.00    0.00     1  |__ora_scmn_orcl
10:08:43 PM         -      9889    0.00    0.00    0.00    0.00     1  |__oracle
10:08:43 PM         -      9890    0.00    0.00    0.00    0.00     0  |__ora_diag_orcl
10:08:43 PM         -      9892    0.00    0.00    0.00    0.00     0  |__ora_dia0_orcl
10:08:43 PM         -      9900    0.00    0.00    0.00    0.00     0  |__ora_reco_orcl
10:08:43 PM         -      9902    0.00    0.00    0.00    0.00     1  |__ora_mmon_orcl
10:08:43 PM         -      9903    0.00    0.00    0.00    0.00     0  |__ora_mmnl_orcl
10:08:43 PM         -      9904    0.00    0.00    0.00    0.00     0  |__ora_d000_orcl
10:08:43 PM         -      9905    0.00    0.00    0.00    0.00     0  |__ora_s000_orcl
10:08:43 PM         -      9906    0.00    0.00    0.00    0.00     0  |__ora_n000_orcl
10:08:43 PM         -      9932    0.00    0.00    0.00    0.00     0  |__ora_tmon_orcl
10:08:43 PM         -      9933    0.00    0.00    0.00    0.00     0  |__ora_tt00_orcl
10:08:43 PM         -      9934    0.00    0.00    0.00    0.00     1  |__ora_smco_orcl
10:08:43 PM         -      9938    0.00    0.00    0.00    0.00     1  |__ora_fbda_orcl
10:08:43 PM         -      9939    0.00    0.00    0.00    0.00     1  |__ora_aqpc_orcl
10:08:43 PM         -      9944    0.00    0.00    0.00    0.00     0  |__ora_p000_orcl
10:08:43 PM         -      9945    0.00    0.00    0.00    0.00     0  |__ora_p001_orcl
10:08:43 PM         -      9946    0.00    0.00    0.00    0.00     1  |__ora_p002_orcl
10:08:43 PM         -      9947    0.00    0.00    0.00    0.00     0  |__ora_p003_orcl
10:08:43 PM         -      9948    0.00    0.00    0.00    0.00     0  |__ora_p004_orcl
10:08:43 PM         -      9949    0.00    0.00    0.00    0.00     1  |__ora_p005_orcl
10:08:43 PM         -      9950    0.00    0.00    0.00    0.00     1  |__ora_p006_orcl
10:08:43 PM         -      9951    0.00    0.00    0.00    0.00     1  |__ora_p007_orcl
10:08:43 PM         -      9952    0.00    0.00    0.00    0.00     1  |__ora_cjq0_orcl
10:08:43 PM         -      9996    0.00    0.00    0.00    0.00     0  |__ora_qm02_orcl
10:08:43 PM         -      9998    0.00    0.00    0.00    0.00     1  |__ora_q002_orcl
10:08:43 PM         -      9999    0.00    0.00    0.00    0.00     0  |__ora_q003_orcl
10:08:43 PM         -     16009    0.00    0.00    0.00    0.00     1  |__ora_w000_orcl
10:08:43 PM         -     21462    0.00    1.00    0.00    1.00     1  |__ora_vkrm_orcl
10:08:43 PM         -     22117    0.00    0.00    0.00    0.00     1  |__ora_w001_orcl
10:08:43 PM         -     22128    0.00    0.00    0.00    0.00     0  |__ora_w002_orcl
10:08:43 PM         -     22689    0.00    0.00    0.00    0.00     1  |__ora_w003_orcl
10:08:43 PM         -     22703    0.00    0.00    0.00    0.00     0  |__ora_w004_orcl
10:08:43 PM         -     22713    0.00    0.00    0.00    0.00     0  |__ora_w005_orcl
There are other interesting options to monitor IO (-d), page faults and memory (-r), CPU utilization seen above (-u), switching activities (-w).
For example:
[oracle@oel64-12c ~]$ pidstat -p 9888 -w -t 1
Linux 2.6.39-400.17.1.el6uek.x86_64 (oel64-12c.localdomain)     01/14/2014      _x86_64_        (2 CPU)

10:57:54 PM      TGID       TID   cswch/s nvcswch/s  Command
10:57:55 PM      9888         -      1.00      1.00  ora_scmn_orcl
10:57:55 PM         -      9888      1.00      1.00  |__ora_scmn_orcl
10:57:55 PM         -      9889      0.00      0.00  |__oracle
10:57:55 PM         -      9890      1.00      0.00  |__ora_diag_orcl
10:57:55 PM         -      9892      1.00      0.00  |__ora_dia0_orcl
10:57:55 PM         -      9900      1.00      0.00  |__ora_reco_orcl
10:57:55 PM         -      9902      1.00      0.00  |__ora_mmon_orcl
10:57:55 PM         -      9903      1.00      1.00  |__ora_mmnl_orcl
10:57:55 PM         -      9904      1.00      0.00  |__ora_d000_orcl
10:57:55 PM         -      9905      1.00      0.00  |__ora_s000_orcl
10:57:55 PM         -      9906      1.00      0.00  |__ora_n000_orcl
10:57:55 PM         -      9932      1.00      0.00  |__ora_tmon_orcl
10:57:55 PM         -      9933      1.00      0.00  |__ora_tt00_orcl
10:57:55 PM         -      9934      1.00      1.00  |__ora_smco_orcl
10:57:55 PM         -      9938      1.00      0.00  |__ora_fbda_orcl
10:57:55 PM         -      9939      1.00      0.00  |__ora_aqpc_orcl
10:57:55 PM         -      9944      0.00      0.00  |__ora_p000_orcl
10:57:55 PM         -      9945      0.00      0.00  |__ora_p001_orcl
10:57:55 PM         -      9946      0.00      0.00  |__ora_p002_orcl
10:57:55 PM         -      9947      0.00      0.00  |__ora_p003_orcl
10:57:55 PM         -      9948      0.00      0.00  |__ora_p004_orcl
10:57:55 PM         -      9949      0.00      0.00  |__ora_p005_orcl
10:57:55 PM         -      9950      0.00      0.00  |__ora_p006_orcl
10:57:55 PM         -      9951      0.00      0.00  |__ora_p007_orcl
10:57:55 PM         -      9952      1.00      0.00  |__ora_cjq0_orcl
10:57:55 PM         -      9996      0.00      0.00  |__ora_qm02_orcl
10:57:55 PM         -      9998      0.00      0.00  |__ora_q002_orcl
10:57:55 PM         -      9999      1.00      0.00  |__ora_q003_orcl
10:57:55 PM         -     21462     96.00      3.00  |__ora_vkrm_orcl
10:57:55 PM         -     22713      1.00      0.00  |__ora_w005_orcl
10:57:55 PM         -     29708      0.00      0.00  |__ora_q001_orcl
10:57:55 PM         -     29709      0.00      0.00  |__oracle_29709_or
10:57:55 PM         -     26975      1.00      0.00  |__ora_w004_orcl
  • gdb (for debug)

If you want to trace system calls made by threads, you can use linux debugger (gdb). I don’t have a deep knowledge of gdb, but you can attach gdb to a process with the -p option.

[oracle@oel64-12c ~]$ gdb -p 9888
GNU gdb (GDB) Red Hat Enterprise Linux (7.2-60.el6)
Copyright (C) 2010 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>.
Attaching to process 9888

.../...
After this, you have a command which prints threads information (LWP (for Light Weight Process ???) indicates the Thread Id:
(gdb) info threads
  31 Thread 0x7f5a89ff6700 (LWP 29709)  0x0000003abe00e75d in read () from /lib64/libpthread.so.0  <<< my session is located here and is waiting for a command (read syscall)
  30 Thread 0x7f5a81ff2700 (LWP 29708)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  29 Thread 0x7f5b10beb700 (LWP 9889)  0x0000003abdcdf343 in poll () from /lib64/libc.so.6
  28 Thread 0x7f5b0ea2a700 (LWP 9890)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  27 Thread 0x7f5b07fff700 (LWP 9892)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  26 Thread 0x7f5afbfff700 (LWP 9900)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  25 Thread 0x7f5af3fff700 (LWP 9902)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  24 Thread 0x7f5aebfff700 (LWP 9903)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  23 Thread 0x7f5ae3fff700 (LWP 9904)  0x0000003abdce9163 in epoll_wait () from /lib64/libc.so.6
  22 Thread 0x7f5adbfff700 (LWP 9905)  0x0000003abdce9163 in epoll_wait () from /lib64/libc.so.6
  21 Thread 0x7f5ad3fff700 (LWP 9906)  0x0000003abdce9163 in epoll_wait () from /lib64/libc.so.6
  20 Thread 0x7f5acbfff700 (LWP 9932)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  19 Thread 0x7f5ac3fff700 (LWP 9933)  0x0000003abe00ef3d in nanosleep () from /lib64/libpthread.so.0
  18 Thread 0x7f5ab3fff700 (LWP 9934)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  17 Thread 0x7f5aabfff700 (LWP 9938)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  16 Thread 0x7f5aa3fff700 (LWP 9939)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  15 Thread 0x7f5a99ffe700 (LWP 9944)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  14 Thread 0x7f5a97ffd700 (LWP 9945)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  13 Thread 0x7f5a95ffc700 (LWP 9946)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  12 Thread 0x7f5a93ffb700 (LWP 9947)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  11 Thread 0x7f5a91ffa700 (LWP 9948)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  10 Thread 0x7f5a8fff9700 (LWP 9949)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  9 Thread 0x7f5a8dff8700 (LWP 9950)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  8 Thread 0x7f5a8bff7700 (LWP 9951)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  7 Thread 0x7f5a9bfff700 (LWP 9952)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  6 Thread 0x7f5a83ff3700 (LWP 9996)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  5 Thread 0x7f5a87ff5700 (LWP 9998)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  4 Thread 0x7f5a85ff4700 (LWP 9999)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  3 Thread 0x7f5abbfff700 (LWP 21462)  0x0000003abe00ef3d in nanosleep () from /lib64/libpthread.so.0
  2 Thread 0x7f5a79fee700 (LWP 22713)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
* 1 Thread 0x7f5b10f2a9e0 (LWP 9888)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
 Next, you can select a specific thread with the gdb “thread” command:
(gdb) thread 31
[Switching to thread 31 (Thread 0x7f5a89ff6700 (LWP 29709))]#0  0x0000003abe00e75d in read () from /lib64/libpthread.so.0
(gdb) info threads
* 31 Thread 0x7f5a89ff6700 (LWP 29709)  0x0000003abe00e75d in read () from /lib64/libpthread.so.0
  30 Thread 0x7f5a81ff2700 (LWP 29708)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  29 Thread 0x7f5b10beb700 (LWP 9889)  0x0000003abdcdf343 in poll () from /lib64/libc.so.6
  28 Thread 0x7f5b0ea2a700 (LWP 9890)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  27 Thread 0x7f5b07fff700 (LWP 9892)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  26 Thread 0x7f5afbfff700 (LWP 9900)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
 .../...
Next, you can use breakpoints, watchpoint etc. to debug oracle calls etc.
If you are interested by tracing oracle system calls with gdb, Frits Hoogland have written many articles on this subject:

January 10, 2014

Oracle 12cR1, Shutdown abort of a PDB seems to perform commit

Filed under: 12c, Administration, SQL — Laurent @ 9:16 PM

A rapid post to show you a little thing I detect today.

In an oracle pluggable database, syntaxes to control them are :

alter pluggable database open
alter pluggable database open read write
alter pluggable database open read only
alter pluggable database open restrict
alter pluggable database close
alter pluggable database close immediate

But if you are an experienced oracle dba, you usually use STARTUP and SHUTDOWN commands and these ones are still available is a PDB.

To close a PDB, SHUTDOWN and SHUTDOWN IMMEDIATE makes sense, but SHUTDOWN ABORT doesn’t because the transactional layer is managed by the root container. But SHUTDOWN ABORT seems to be functional in a PDB context with a strange behaviour.

[oracle@oel64-12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jan 10 20:47:08 2014

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

SQL> alter session set container=ORCL_PDB;

Session altered.

SQL> alter session set current_schema=HR;

Session altered.

SQL> select * from regions;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

SQL> update regions set region_name=region_name||'*';

4 rows updated.

SQL> shutdown abort;
Pluggable Database closed.
So, if you are an Oracle DBA with a little bit of oracle knowledge and if I ask you what will be the content of the REGIONS table, you will answer me that each region_name will not have any * at the end.
But ….
SQL> connect / as sysdba
Connected.
SQL> alter session set container=ORCL_PDB;

Session altered.

SQL> startup
Pluggable Database opened.
SQL> show con_name

CON_NAME
------------------------------
ORCL_PDB
SQL> alter session set current_schema=HR;

Session altered.

SQL> select * from regions;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe*
         2 Americas*
         3 Asia*
         4 Middle East and Africa*
The transaction has been commited even with the shutdown abort command !
Stranger … if you try to do that with a SHUTDOWN IMMEDIATE, the transaction is committed too !
Ok, It’s my fault … I have to write correct statement with the official syntax … let’do it !
[oracle@oel64-12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jan 10 20:58:41 2014

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

SQL> alter session set container=ORCL_PDB;

Session altered.

SQL> alter session set current_schema=HR;

Session altered.

SQL> select * from regions;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

SQL> update regions set region_name=region_name||'*';

4 rows updated.

SQL> alter pluggable database close immediate;

Pluggable database altered.
Now the result:
SQL> alter pluggable database orcl_pdb open;

Pluggable database altered.

SQL> alter session set container=orcl_pdb;

Session altered.

SQL> alter session set current_schema=HR;

Session altered.

SQL> select * from regions;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe*
         2 Americas*
         3 Asia*
         4 Middle East and Africa*
Same problem …
Let’s see what’s the official doc say:
IMMEDIATE If you specify the optional IMMEDIATE keyword, then this clause is the PDB equivalent of the SQL*Plus SHUTDOWN command with the immediate mode. Otherwise, the PDB is shut down with the normal mode.
So the pluggable database should have been closed with the immediate behaviour so with a rollback of my transaction, or throw an ORA-01097: cannot shutdown while in a transaction – commit or rollback first … but it’s not the case, worse it acts like an implicit commit.
Fortunately, if you perform the shutdown from another session, the transaction is correctly rolled back.
I don’t know if it’s a bug (I did’nt find anything in MOS) or a feature … but it’s weird !

October 17, 2013

Step by step Solaris 11.1 installation guide on a virtual box VM (x86-64)

Filed under: Oracle, Solaris 11, VirtualBox — Laurent @ 5:19 PM

Solaris is an OS with many tools like dtrace. In my case, I installed it because I would like to test Oracle 12c and solaris specific views that detect long IO operation: V$KERNEL_IO_OUTLIER.

First of all, you need to download the installation medium which is available at https://edelivery.oracle.com. You need to download the Interactive text install medium :

S11_00

Next, you have to create a new VM. This can be done by following steps below:

S11_01

S11_02

S11_03

S11_04

S11_05

S11_06

Ok, the VM is created, now let’s configure it by attaching the S11.1 installation media, and adding a network adapter (I will add a another NIC later for my LAN access) :

S11_07

And boot the VM …

After booting the VM, the installation process will prompt you for many information (keyboard, hostname etc.):

S11_08

S11_09

Select your keyboard layout

S11_10

And the language for your installation.

S11_11

Select 1 to install Oracle Solaris

S11_12

During the installation process, you can go to the next step by pressing F2 and go back by pressing F3. Everything you can do is mentioned on the bottom of the screen :

S11_13

S11_14

In the next screen, I will use the local discovery method because my disk is locally attached.

S11_15

Select the discovered disk where you want to install S11 and then press F2.

S11_16

In this screen, I choose to use the entire disk for my installation. You can select another partition by selecting “Use a GPT partition of the disk”.

S11_17

Enter the computer name (short name), and choose how you want to configure your network. I decided to configure my network automatically (DHCP).

S11_18

No alternative naming service.

S11_19

Ok, now it’s time to configure time related information, first of all … Time zone

S11_20

S11_21

Ok timezone set.

S11_22

next, set date and time.

S11_23

Set the root password and eventually, create a user account.

S11_24

No need to register your system … it’s a VM

S11_25

Next screen is to configure the method to access oracle servers for OCM and Automatic SR features. Here it doesn’t make sense.

S11_26

Review your configuration and press F2 to install.

S11_27

Install in progress …

At the end of the process, you have to reboot your VM by pressing F8. In my case, instead of rebooting the server, I shut it down and took the time to detach the installation medium and to configure another Network adapter plugged into my LAN.

S11_28

Now I can boot my VM … I’m ready to configure it.

To configure network, I used the same method I described in this blog post : http://laurent-leturgez.com/2012/08/01/build-a-basic-network-configuration-on-solaris-11/

S11_29

Net1/v4 has to be configured S11_30

Link is up,so we can configure this NIC.

S11_31

S11_32

As we configured DHCP client on a NAT Virtualbox NIC, we retrieved DNS information:

S11_33

Now I can access to my VM from a putty client. But root access is disabled by default in sshd config. So you have to modify the sshd config  and restart the daemon

S11_34

Next thing I recommend to configure is the package repository. By default, if you have an internet access configured to your machine (which is my case because of my NATted network adapter), you don’t have to configure anything because your default repository is already configured to access Oracle remote repository.

root@S11:~# pkg publisher
PUBLISHER                   TYPE     STATUS P LOCATION
solaris                     origin   online F http://pkg.oracle.com/solaris/release/

root@S11:~# pkg publisher solaris

            Publisher: solaris
                Alias:
           Origin URI: http://pkg.oracle.com/solaris/release/
              SSL Key: None
             SSL Cert: None
          Client UUID: ddee2130-0292-11e2-b9e5-80144f013e20
      Catalog Updated: November  9, 2011 03:34:27 PM
              Enabled: Yes

If you want to configure other repositories (for example local repo), you can follow instructions at this link : http://docs.oracle.com/cd/E23824_01/html/E21802/publisher-config.html

Maybe you will need to install basic XWindow libraries (for example, if you want to export a display to your local X Server). If your repository is configured, you can execute the commands below:

root@S11:~# pkg install SUNWarc SUNWbtool SUNWhea SUNWlibms SUNWmfrun SUNWxorg-client-programs SUNWxorg-clientlibs SUNWxwfsw pkg://solaris/SUNWxwplt truetype/fonts-core
           Packages to install: 65
       Create boot environment: No
Create backup boot environment: No
            Services to change:  5

DOWNLOAD                                PKGS         FILES    XFER (MB)   SPEED
Completed                              65/65     5045/5045    40.9/40.9  280k/s

PHASE                                          ITEMS
Installing new actions                     6797/6797
Updating package state database                 Done
Updating image state                            Done
Creating fast lookup database                   Done

Then logoff and re-login, you can now export your display and launch X Programs:

$ ssh root@192.168.99.130
Last login: Wed Oct 16 14:14:47 2013 from 192.168.99.1
Oracle Corporation      SunOS 5.11      11.1    September 2012
root@S11:~# echo $DISPLAY
localhost:10.0
root@S11:~# xlogo

To conclude this installation, if you want to configure a complete Desktop Manager, you have to install the slim_install package which deploys 315 packages to your system … so it can take a while.

root@S11:~# pkg install slim_install
           Packages to install: 315
       Create boot environment:  No
Create backup boot environment: Yes
            Services to change:  13

DOWNLOAD                                PKGS         FILES    XFER (MB)   SPEED
Completed                            315/315   50633/50633  524.2/524.2  259k/s

PHASE                                          ITEMS
Installing new actions                   81666/81666
Updating package state database                 Done
Updating image state                            Done
Creating fast lookup database                   Done

Reboot your machine and you will display a nice Desktop manager environment:

S11_36

This step by step guide is available at this address : http://www.slideshare.net/lolo115/install-solat

October 9, 2013

Oracle extra cost options licensing on multitenant databases

Filed under: 12c, Administration, licensing, multitenant, Oracle — Tags: , , , — Laurent @ 9:47 PM
Oracle database 12c has been announced few weeks ago with its main feature: multitenant database. This feature is build to consolidate many databases (aka. pluggable databases or PDB) into a unique container database (CDB).
Multitenancy is an extra cost option for the Enterprise Edition if you use more than one pdb in your container. But if you want to consolidate, you probably want to consolidate all the options you bought before for many servers.
In Oracle server, each usage of a specific feature you made is recorded in a dictionary table and can be accessed through the view: DBA_FEATURE_USAGE_STATISTICS.
In a Standard Edition database, features of the Enterprise Edition are tracked. If you want to use an extra cost option, you will probably raise an ORA-00439 (Feature not enabled):
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT              VERSION              STATUS
-------------------- -------------------- --------------------
NLSRTL               12.1.0.1.0           Production
Oracle Database 12c  12.1.0.1.0           64bit Production
PL/SQL               12.1.0.1.0           Production
TNS for Linux:       12.1.0.1.0           Production
We are working on a Standard Edition.
SQL> select NAME,DETECTED_USAGES,CURRENTLY_USED,LAST_USAGE_DATE
  2  from dba_feature_usage_statistics
  3  where name in ('Segment Shrink')
  4  /
NAME                                               DETECTED_USAGES CURRE LAST_USAGE_DATE
-------------------------------------------------- --------------- ----- -------------------
Segment Shrink                                                   0 FALSE

SQL> alter table t shrink space;
Table altered.
SQL> exec dbms_feature_usage_internal.sample_one_feature('Segment Shrink');

PL/SQL procedure successfully completed.

SQL> select NAME,DETECTED_USAGES,CURRENTLY_USED,LAST_USAGE_DATE
   2  from dba_feature_usage_statistics
   3  where name in ('Segment Shrink')
   4  /

NAME                                               DETECTED_USAGES CURRE LAST_USAGE_DATE
 -------------------------------------------------- --------------- ----- -------------------
 Segment Shrink                                                   1 TRUE  2013-10-09 20:46:22
SQL> BEGIN
  2   DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
  3    name          => 'public_privs_capture',
  4    description   => 'Captures privilege use by PUBLIC',
  5    type          => DBMS_PRIVILEGE_CAPTURE.G_ROLE,
  6    roles         => role_name_list('public')
  7  );
  8  end;
  9  /
BEGIN
*
ERROR at line 1:
ORA-00439: feature not enabled: Privilege Analysis
ORA-06512: at "SYS.DBMS_PRIVILEGE_CAPTURE", line 3
ORA-06512: at line 2
Notice that I used dbms_feature_usage_internal.sample_one_feature to force the update of feature usage data (They are normally updated every 7 days).
In an Enterprise Edition database, the behaviour is similar but only extra cost options usages are recorded, features included in the Edition are not (for example, Segment Shrink will stay at 0).
Now let’s see how it’s recorded in a multitenant database. In my case, I have a container database (ORACLE_SID=cdb) which hosts 10 pluggable databases. Those PDBs have been consolidated from many databases hosted on dedicated servers. As we bought database vault option for the database consolidated into PDB10 (for example), we would like to use Privilege analysis function (included in database vault option).
Let’s check how it works in the PDB10:
idle> connect sys/oracle@oel63:1521/pdb10 as sysdba
Connected.

[SYS@PDB10 | SID:CDB]> BEGIN
  2    DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
  3     name          => 'public_privs_capture',
  4     description   => 'Captures privilege use by PUBLIC',
  5     type          => DBMS_PRIVILEGE_CAPTURE.G_ROLE,
  6     roles         => role_name_list('public')
  7     );
  8  END;
  9  /

PL/SQL procedure successfully completed.

[SYS@PDB10 | SID:CDB]> EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('public_privs_capture');

PL/SQL procedure successfully completed.

[SYS@PDB10 | SID:CDB]> exec dbms_feature_usage_internal.sample_one_feature('Privilege Capture');
BEGIN dbms_feature_usage_internal.sample_one_feature('Privilege Capture'); END;

*
ERROR at line 1:
ORA-20009: Unknown Feature
ORA-06512: at "SYS.DBMS_FEATURE_USAGE_INTERNAL", line 614
ORA-06512: at line 1
Well it seems that we cannot call this package in a PDB. So we will do it the CDB$ROOT and check DBA_FEATURE_USAGE_STATISTICS (in the PDB) and CDB_FEATURE_USAGE_STATISTICS (in the CDB$ROOT).
[SYS@CDB$ROOT | SID:CDB]> select con_id,NAME,DETECTED_USAGES,CURRENTLY_USED,LAST_USAGE_DATE
  2  from cdb_feature_usage_statistics
  3  where name in ('Privilege Capture')
  4  order by 1;

    CON_ID NAME                                               DETECTED_USAGES CURRE LAST_USAGE_DATE
---------- -------------------------------------------------- --------------- ----- -------------------
         1 Privilege Capture                                                1 FALSE 2013-10-09 21:59:40
         2 Privilege Capture                                                0 FALSE
         3 Privilege Capture                                                0 FALSE
         4 Privilege Capture                                                0 FALSE
         5 Privilege Capture                                                0 FALSE
         6 Privilege Capture                                                0 FALSE
         7 Privilege Capture                                                0 FALSE
         8 Privilege Capture                                                0 FALSE
         9 Privilege Capture                                                0 FALSE
        10 Privilege Capture                                                0 FALSE
        11 Privilege Capture                                                0 FALSE
        12 Privilege Capture                                                0 FALSE

12 rows selected.

[SYS@CDB$ROOT | SID:CDB]> connect sys/oracle@oel63:1521/pdb10 as sysdba
Connected.
[SYS@PDB10 | SID:CDB]> select NAME,DETECTED_USAGES,CURRENTLY_USED,LAST_USAGE_DATE
  2  from dba_feature_usage_statistics
  3  where name in ('Privilege Capture')
  4  /

NAME                                               DETECTED_USAGES CURRE LAST_USAG
-------------------------------------------------- --------------- ----- ---------
Privilege Capture                                                0 FALSE
We have the proof that feature usage is recorded in the root container and licensing is logically done at the CDB$ROOT level.
If I bought database vault option in my 11g database for 2 CPUs, and now my multitenant is located on a strong server with 24 CPUs, I have to buy 22CPUs of this option, even if it run on a single. On the other side, you can use the option and all of its features in all your PDBs even you don’t need it ;)
To proove it, I will reproduce the same steps in another PDB (the PDB4 for example), and we will see that usage is recorded in the CDB.
idle> connect sys/oracle@oel63:1521/pdb4 as sysdba
Connected.
[SYS@PDB4 | SID:CDB]> BEGIN
  2    DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
  3     name          => 'public_privs_capture',
  4     description   => 'Captures privilege use by PUBLIC',
  5     type          => DBMS_PRIVILEGE_CAPTURE.G_ROLE,
  6     roles         => role_name_list('public')
  7     );
  8  END;
  9  /

PL/SQL procedure successfully completed.

[SYS@PDB4 | SID:CDB]> EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('public_privs_capture');

PL/SQL procedure successfully completed.

[SYS@PDB4 | SID:CDB]> connect sys/oracle as sysdba
Connected.
[SYS@CDB$ROOT | SID:CDB]> exec dbms_feature_usage_internal.sample_one_feature('Privilege Capture');

PL/SQL procedure successfully completed.

[SYS@CDB$ROOT | SID:CDB]> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

[SYS@CDB$ROOT | SID:CDB]> set lines 500
[SYS@CDB$ROOT | SID:CDB]> set pages 500
[SYS@CDB$ROOT | SID:CDB]> select con_id,NAME,DETECTED_USAGES,CURRENTLY_USED,LAST_USAGE_DATE
  2  from cdb_feature_usage_statistics
  3  where name in ('Privilege Capture')
  4  order by 1;

    CON_ID NAME                                               DETECTED_USAGES CURRE LAST_USAGE_DATE
---------- -------------------------------------------------- --------------- ----- -------------------
         1 Privilege Capture                                                2 TRUE  2013-10-09 22:11:37
         2 Privilege Capture                                                0 FALSE
         3 Privilege Capture                                                0 FALSE
         4 Privilege Capture                                                0 FALSE
         5 Privilege Capture                                                0 FALSE
         6 Privilege Capture                                                0 FALSE
         7 Privilege Capture                                                0 FALSE
         8 Privilege Capture                                                0 FALSE
         9 Privilege Capture                                                0 FALSE
        10 Privilege Capture                                                0 FALSE
        11 Privilege Capture                                                0 FALSE
        12 Privilege Capture                                                0 FALSE

12 rows selected.
If you plan to implement multitenant database, you have to be clever and think about options you bought and how you will implement them into your consolidated databases.
Older Posts »

The WordPress Classic Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 214 other followers