Monthly Archives: September 2014

12c: Does PDB Have An SPfile?

In a multi-tenant container database, since there are many PDB’s per CDB, it is possible for set some parameters for each individual PDB. The SPFILE for CDB stores parameter values associated with the root which apply to the root, and serve as default values for all other containers. Different values can be set in PDBs for those parameters where the column ISPDB_MODIFIABLE in V$PARAMETER is TRUE. The parameters are set for a PDB and  are stored in table PDB_SPFILE$ remembered across PDB close/open and across restart of the CDB.

– Currently  I have a CDB called CDB1 having one PDB – PDB1.

SQL> sho con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> sho pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED                       READ ONLY  NO
3 PDB1                           READ WRITE NO

– There is a table pdb_spfile for each of the containers (cdb$root and PDB1)

SQL>  select con_id,  table_name from cdb_tables  where table_name = 'PDB_SPFILE$';

CON_ID TABLE_NAME
---------- --------------------
3 PDB_SPFILE$
1 PDB_SPFILE$

– pdb_spfile contains only those parameters which have been specifically   set for a container hence currently there are  no records

SQL>   col container_name for a10
col parameter for a20
col value$ for a30

select container.name container_name, par.name PARAMETER,
par.value$
from pdb_spfile$ par, v$containers container
where par.pdb_uid = container.con_uid
and par.name = 'cursor_sharing';

no rows selected

– Let’s explicitly set cursor_sharing = ‘similar’ in root and check if   it is reflected in pdb_spfile$

SQL> alter system set cursor_sharing='similar';

col container_name for a10
col parameter for a20
col value$ for a30
select container.name container_name, par.name PARAMETER,
par.value$
from pdb_spfile$ par, v$containers container
where par.pdb_uid = container.con_uid
and par.name = 'cursor_sharing';

no rows selected

– It does not show any results but v$spparameter has been updated    probably implying that spfile for the root is maintained in the    operating system only and pdb_spfile does not contain info about parameters in cdb$root.

SQL> select name, value from v$spparameter where name='cursor_sharing';

NAME                           VALUE
------------------------------ -------
cursor_sharing                 similar

-- v$parameter shows the value of parameter for root

SQL> col name for a30
col value for a30

select con_id, name, value from v$parameter
where name = 'cursor_sharing';


CON_ID NAME                           VALUE
---------- ------------------------------ --------
1 cursor_sharing                 similar

– To see parameters for the CDB and all the PDB’s (except PDB$SEED),  v$system_parameter can be accessed. It can be seen that currently it shows only the value for the CDB which will be inherited by all the PDB’s.

SQL>select con_id, name, value from v$system_parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ ---------
0 cursor_sharing                 similar

– Change container to PDB1 and verify that PDB has inherited the value from CDB

SQL> alter session set container=pdb1;

sho parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ -------     ---------------
cursor_sharing                       string      similar

– Since parameter has not been explicitly specified    in PDB ,  v$spparameter shows record  for con_id = 0 and null in value column

SQL> select con_id, name, value from v$spparameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ ----------
0 cursor_sharing

– Let’s check if the parameter can be modified for the PDB

SQL> col ispdb_modifiable for a17
select con_id, name, value, ispdb_modifiable

from v$parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE      ISPDB_MODIFIABLE
---------- ------------------------------ ---------- -----------------
3 cursor_sharing                 similar    TRUE

– Since the parameter can be modified in PDB, let us modify its value in PDB to ‘FORCE’

SQL> alter system set cursor_sharing = 'FORCE';

sho parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
cursor_sharing                       string      FORCE

SQL> select con_id, name, value from v$parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ ----------
3 cursor_sharing                 FORCE

– v$spparameter shows updated value but con_id is still 0 (bug??)

SQL> select con_id, name, value from v$spparameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ ----------
 0 cursor_sharing                 FORCE

– Current value of the parameter  for PDB can be viewed from root using v$system_parameter

SQL> alter session set container=cdb$root;
select con_id, name, value from v$system_parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ -------
0 cursor_sharing                 similar
3 cursor_sharing                 FORCE

– Current value in spfile of PDB can be seen from pdb_spfile$

SQL> col value$ for a30
select pdb.name PDB_NAME, par.name PARAMETER, par.value$
from pdb_spfile$ par, v$pdbs pdb
where par.pdb_uid = pdb.con_uid
and par.name = 'cursor_sharing';

PDB_NAME   PARAMETER            VALUE$
---------- -------------------- ------------------------------
PDB1       cursor_sharing       'FORCE'

– The parameter still has earlier value of similar for cdb$root

SQL> sho parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
cursor_sharing                       string      similar

SQL> col name for a30
col value for a30

select con_id, name, value from v$parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ --------
1 cursor_sharing                 similar

– Let’s check if modified value persists across closing / opening of the PDB

SQL> alter pluggable database pdb1 close;

– After PDB is closed, entry in its spfile is still visible    but current value cannot be seen as PDB is closed

SQL> col value$ for a30
select pdb.name PDB_NAME, par.name PARAMETER, par.value$
from pdb_spfile$ par, v$pdbs pdb
where par.pdb_uid = pdb.con_uid
and par.name = 'cursor_sharing';

PDB_NAME   PARAMETER            VALUE$
---------- -------------------- -------------
PDB1       cursor_sharing       'FORCE'

SQL> select con_id, name, value from v$system_parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ ------------
0 cursor_sharing                 similar

– It can be seen that after PDB is re-opened, the updated
   value still persists

SQL>  alter pluggable database pdb1 open;

select con_id, name, value from v$system_parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ -------------
0 cursor_sharing                 similar
3 cursor_sharing                 FORCE

– Let’s verify that parameter change persists across CDB shutdown

SQL> shu immediate;
     startup
     alter pluggable Database  pdb1 open;

     select con_id, name, value 
     from   v$system_parameter
     where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ -----------
0 cursor_sharing                 similar
3 cursor_sharing                 FORCE

SQL> col value$ for a30
     select pdb.name PDB_NAME, par.name 
            PARAMETER, par.value$
     from pdb_spfile$ par, v$pdbs pdb
     where par.pdb_uid = pdb.con_uid
      and par.name = 'cursor_sharing';

PDB_NAME   PARAMETER            VALUE$
---------- -------------------- ------------------------------
PDB1       cursor_sharing       'FORCE'

– Now we will change the parameter in PDB spfile only

SQL> alter session set container=pdb1;

     alter system set cursor_sharing = 'EXACT' scope=spfile;

– Current value still remains FORCE

sho parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
cursor_sharing                       string      FORCE

–Value has been changed to EXACT in SPfile only

SQL> select con_id, name, value
     from     v$spparameter
     where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ -----------
0 cursor_sharing                 EXACT

– The above changes can be seen from root as well

SQL> alter session set container=cdb$root;

-- The current value is shown as FORCE

SQL> select con_id, name, value 
      from  v$system_parameter
     where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ ----------
0 cursor_sharing                 similar
3 cursor_sharing                 FORCE

– The value in SPFILE is ‘EXACT’ as set

SQL> col value$ for a30
     select pdb.name PDB_NAME, par.name 
            PARAMETER, par.value$
     from pdb_spfile$ par, v$pdbs pdb
      where par.pdb_uid = pdb.con_uid
     and par.name = 'cursor_sharing';

PDB_NAME   PARAMETER            VALUE$
---------- -------------------- ------------------------------
PDB1       cursor_sharing       'EXACT'

– Let’s close and re-open PDB to vefify that value in spfile is
   applied

SQL> alter pluggable database pdb1 close;

    alter pluggable database pdb1 open;
  
     select con_id, name, value 
     from   v$system_parameter
     where name = 'cursor_sharing';  2

CON_ID NAME                           VALUE
---------- ------------------------------ ---------------
0 cursor_sharing                 similar
3 cursor_sharing                 EXACT

– Since the value in spfile is same as default, we can remove this
entry by resetting the value of the parameter.

SQL> alter session set container=pdb1;
     alter system reset cursor_sharing;
     sho parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
cursor_sharing                       string      EXACT

– The entry has been deleted from spfile

SQL> select con_id, name, value 
     from v$spparameter
     where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ -------------
0 cursor_sharing

SQL> alter session set container=cdb$root;

     col value$ for a30
     select pdb.name PDB_NAME, par.name 
            PARAMETER, par.value$
     from pdb_spfile$ par, v$pdbs pdb
     where par.pdb_uid = pdb.con_uid
     and par.name = 'cursor_sharing';

no rows selected

I hope this post was useful. Your comments and suggestions are always welcome!!

References:

Oracle documentation

——————————————————————————————-

Related Links:

Home

Oracle 12c Index

 

 

12c: Optimizer_Dynamic_Sampling = 11

With default sampling level of 2 (from 10g onwards) , dynamic sampling is performed only for the objects for which statistics do not exist. If the statistics are stale or insufficient, dynamic  sampling is not done.

12c introduces a new value of 11 for OPTIMIZER_DYNAMIC_SAMPLING . This value allows the optimizer to automatically perform dynamic sampling using an appropriate level for a SQL statement, even if all basic table statistics exist but they are found to be stale or insufficient. The results of the dynamically sampled queries are persisted in the cache, as dynamic statistics, allowing other SQL statements to share these statistics. This level will no doubt generate a better plan during the first execution of the statement itself but will also lead to dynamic sampling being triggered more frequently and sometime unnecessarily as well.

This example demonstrates that if  OPTIMIZER_DYNAMIC_SAMPLING is set to  11, dynamic sampling will be performed even in case of stale or insufficient statistics. Moreover dynamic sampling  may be unnecessary triggered in some scenarios.

Insufficient Statistics 

I have created a table HR.BIRTHDAYS having 10000 rows whose column MM is indexed and contains numeric month of birth with NDV = 12. The data distribution in the column is skewed . Statistics have been gathered for the table without histogram.

DB12c>select mm, count(*) from hr.birthdays group by mm order by mm;

MM   COUNT(*)
---------- ----------
1       9989
2         1
3         1
4         1
5         1
6         1
7         1
8         1
9         1
10        1
11        1
12        1

12 rows selected.

If OPTIMIZER_DYNAMIC_SAMPLING were set to 2 (default), dynamic sampling will not be done, as statistics are present for the table. However, if the parameter is set to 11 (new in 12c), in view of skewed data distribution, existing statistics  are found to be insufficient (missing histogram) and  dynamic sampling is performed  leading to accurate cardinality estimates for both  MM = 1 which occurs 0.01% times and MM = 12 which occurs 99.89% times .

DB12c>alter session set optimizer_dynamic_sampling=11;
set autot trace explain

select * from hr.birthdays where mm = 12;

Execution Plan
----------------------------------------------------------
Plan hash value: 3569291752
-----------------------------------------------------------------------------
|Id |Operation                          |Name    |Rows|Bytes|Cost(%CPU)|Time|
-----------------------------------------------------------------------------
|  0|SELECT STATEMENT                    |         | 1 |  37| 2(0)| 00:00:01|
|  1| TABLE ACCESS BY INDEX ROWID BATCHED|BIRTHDAYS| 1 |  37| 2(0)| 00:00:01|
|* 2|  INDEX RANGE SCAN                  |BDAY_IDX | 1 |    | 1(0)| 00:00:01|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MM"=12)

Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)

DB12c>select * from hr.birthdays where mm = 1;
set autot off

Execution Plan
----------------------------------------------------------
Plan hash value: 3605468880

-----------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time   |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |  9989 |   360K|    17   (0)|00:00:01|
|*  1 |  TABLE ACCESS FULL| BIRTHDAYS |  9989 |   360K|    17   (0)|00:00:01|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MM"=1)
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
Stale Statistics

Now to make the statistics stale, I have modified the data in the table and have not refreshed  statistics. Note that there are no rows for MM = 1 or 2, so that actual NDV = 10.
Although there are 8388 rows in table, dictionary statistics still shows NUM_ROWS = 10000 and NDV = 12

DB12c> select count(*), count(distinct mm) from hr.birthdays;

COUNT(*) COUNT(DISTINCTMM)
---------- -----------------
 8388                10

DB12c>select owner, table_name, num_rows
from dba_tables
where owner = 'HR'
and table_name = 'BIRTHDAYS';

OWNER   TABLE_NAME        NUM_ROWS
------- --------------- ----------
HR      BIRTHDAYS            10000

DB12c>select owner, table_name, column_name, num_distinct
from dba_tab_cols
where table_name= 'BIRTHDAYS' and column_name = 'MM';

OWNER   TABLE_NAME      COLUMN_NAME     NUM_DISTINCT
------- --------------- --------------- ------------
HR      BIRTHDAYS       MM                        12

If OPTIMIZER_DYNAMIC_SAMPLING = 2 (default),  dynamic sampling will not be done, as statistics (although stale) are present for the table.

With  OPTIMIZER_DYNAMIC_SAMPLING =11, since statistics are stale, dynamic sampling is performed  and almost correct no. of rows are estimated for both MM = 1 and 4

DB12c>alter session set optimizer_dynamic_sampling=11;
select /*+ gather_plan_statistics */ count(*)
from hr.birthdays where mm = 1;

select * from table(dbms_xplan.display_cursor(format => 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID  ghg0pr81m1ha3, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from hr.birthdays where
mm = 1

Plan hash value: 4218648105
-----------------------------------------------------------------------------
|Id  |Operation             |Name    |Starts|E-Rows|A-Rows|  A-Time| Buffers|
-----------------------------------------------------------------------------
|  0 |SELECT STATEMENT      |        |    1 |      |    1 |00:00:00.01|  40 |
|  1 | SORT AGGREGATE       |        |    1 |    1 |    1 |00:00:00.01|  40 |
|* 2 |  INDEX FAST FULL SCAN|BDAY_IDX|    1 |    1 |    0 |00:00:00.01|  40 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MM"=1)
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)

DB12c>select /*+ gather_plan_statistics */ count(*)
from hr.birthdays where mm = 4;

select * from table(dbms_xplan.display_cursor(format => 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------SQL_ID  bhrdb027v2pnt, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from hr.birthdays where
mm = 4

Plan hash value: 3164848757
-----------------------------------------------------------------------------
|Id  |Operation         |Name    |Starts|E-Rows|A-Rows|  A-Time   | Buffers |
-----------------------------------------------------------------------------|  0 |SELECT STATEMENT  |        |    1 |      |    1 |00:00:00.0 |       5 |
|  1 | SORT AGGREGATE   |        |    1 |    1 |    1 |00:00:00.0 |       5 |
|* 2 |  INDEX RANGE SCAN|BDAY_IDX|    1 |  810810 |00:00:00.0 |       5 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MM"=4)

Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)  
 Unnecessary Sampling

To demonstrate unnecessary sampling due to sampling level = 11, I have created unique index on NAME column and deleted the statistics for the table.
A search for a NAME would not have triggered dynamic sampling with a  sampling level of  2  since there is a unique index on NAME column.
On the contrary, if  OPTIMIZER_DYNAMIC_SAMPLING =11, dynamic sampling is unnecessarily performed .

DB12c> alter session set optimizer_dynamic_sampling=11;

select /*+ gather_plan_statistics */ count(*)
from hr.birthdays where NAME = 'NAME OCTOBER  8802';


select * from table(dbms_xplan.display_cursor(format => 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------SQL_ID  d79yg9wq02swy, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from hr.birthdays where
NAME = 'NAME OCTOBER  8802'

Plan hash value: 480407801
-----------------------------------------------------------------------------
|Id  |Operation          |Name         |Starts|E-Rows|A-Rows|A-Time |Buffers|
-----------------------------------------------------------------------------
|  0 |SELECT STATEMENT   |             |    1 |      |    1 |00:00:00.01| 2 |
|  1 | SORT AGGREGATE    |             |    1 |    1 |    1 |00:00:00.01| 2 |
|* 2 |  INDEX UNIQUE SCAN|BDAY_NAME_IDX|    1 |    1 |    1 |00:00:00.01| 2 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME"='NAME OCTOBER  8802')
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)

Hence, If OPTIMIZER_DYNAMIC_SAMPLING = 11, sampling is performed in case of
•    missing statistics (as earlier with sampling level of 2),
•    stale statistics and
•    insufficient statistics
possibly leading to an optimal plan during first execution of the statement itself. But this has the disadvantage that dynamic sampling will be unnecessarily triggered in some cases leading to performance degradation. To have the best of both worlds, sampling level can be set to its default value of 2 and SPD’s  can be employed to guide the optimizer to perform dynamic sampling in case of stale / insufficient statistics too.

References:

http://www.google.co.in/url?sa=t&rct=j&q=&esrc=s&source=web&cd=5&ved=0CDQQFjAE&url=http%3A%2F%2Fwww.hroug.hr%2Fhr%2Fcontent%2Fdownload%2F14418%2F236628%2Ffile%2F401_Senegacnik_What_is_new_in_CBO.pdf&ei=p_AjVIjqHYyPuATD4IDAAQ&usg=AFQjCNEAXoYoFbMqYIXNimGgXLQ7N2Ra8Q&sig2=WbVNvkAsluX2Y0rp-N0QNw&bvm=bv.76247554,d.c2E
http://docs.oracle.com/cd/E11882_01/server.112/e16638/stats.htm#PFGRF30101

————————————————————————————

Related Links:

Home

Database 12c Index