oracle 12c introduced pdb level parameters:
select name from v$parameter where ISPDB_MODIFIABLE='TRUE';
NAME
--------------------------------------------------------------------------------
sessions
timed_statistics
timed_os_statistics
resource_limit
....
all of these parameters can be set per pdb, so lets have a look at one of the parameters. I’ll choose optimizer_dynamic_sampling for this little demonstration.
at the cdb level my value of optimizer_dynamic_sampling is:
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show parameter optimizer_dynamic_sampling
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
this is reflected in the spfile, too:
[oracle@localhost dbs]$ strings spfileorcl.ora | grep optimizer_dynamic_sampling
*.optimizer_dynamic_sampling=2
lets connect to the pdb and see what is the value there:
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> show parameter optimizer_dynamic_sampling
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
same as in the cdb. lets change it:
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> alter system set optimizer_dynamic_sampling=4 scope=both;
System altered.
SQL> show parameter optimizer_dynamic_sampling
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 4
going back to the cdb we can see that the cdb level parameter was not touched:
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show parameter optimizer_dynamic_sampling
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
SQL>
but where does oracle store the pdb level parameters? in the spfile?
[oracle@localhost dbs]$ strings spfileorcl.ora | grep optimizer_dynamic_sampling
*.optimizer_dynamic_sampling=2
obviously not. so, lets trace:
SQL> alter session set tracefile_identifier=PARAMETER_CHECK;
Session altered.
alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> show parameter optimizer_dynamic_sampling
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 4
looking at the trace file we can see that the following happens:
PARSING IN CURSOR #139664056367904 len=289 dep=0 uid=0 oct=3 lid=0 tim=14602003422 hv=2462394820 ad='75206100' sqlid='7cfz5wy9caaf4'
SELECT NAME NAME_COL_PLUS_SHOW_PARAM
, DECODE(TYPE,1,'boolean',2,'string',3,'integer'
, 4,'file',5,'number', 6,'big integer', 'unknown') TYPE
, DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM
FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ)
ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM
END OF STMT
substituting the bind variable gives exactly the results we expect:
SELECT NAME NAME_COL_PLUS_SHOW_PARAM
, DECODE(TYPE,1,'boolean',2,'string',3,'integer'
, 4,'file',5,'number', 6,'big integer', 'unknown') TYPE
, DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM
FROM V$PARAMETER
WHERE UPPER(NAME) LIKE UPPER('OPTIMIZER_DYNAMIC_SAMPLING')
ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 4
but this does not really help, does it? the magic must be hidden in the v$parameter view, so lets see what is being done there (v$parameter is based on gv$parameter):
select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl
, ksppstdf, decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE')
, decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED'
, 3,'IMMEDIATE','FALSE')
, decode(bitand(ksppiflg/524288,1),1,'TRUE','FALSE')
, decode(bitand(ksppiflg,4),4,'FALSE'
, decode(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE'))
, decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')
, decode(bitand(ksppstvf,2),2,'TRUE','FALSE')
, decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE')
, decode(bitand(ksppilrmflg/268435456, 1), 1, 'TRUE', 'FALSE')
, ksppdesc
, ksppstcmnt
, ksppihash
, x.con_id
from x$ksppi x, x$ksppcv y
where (x.indx = y.indx) and bitand(ksppiflg,268435456) = 0
and ((translate(ksppinm,'_','#') not like '##%')
and ((translate(ksppinm,'_','#') not like '#%')
or (ksppstdf = 'FALSE')
or (bitand(ksppstvf,5) > 0)))
ah, there is a con_id column in x$ksppi. but no where clause on this column, so:
SQL> select distinct con_id from x$ksppi;
CON_ID
----------
3
… only container 3 is reflected in the x$ table, which is my pdb:
SQL> select con_id, name from v$pdbs;
CON_ID NAME
---------- ------------------------------
2 PDB$SEED
3 PDB1
doing the same in the cdb lists all containers:
SQL> select distinct con_id from x$ksppi;
CON_ID
----------
1
2
3
the answer is: the pdb level parameters are stored in the data dictionary of the pdb and each pdb just sees the paramters which are valid for just the pdb and nothing else. makes sense, as you may unplug and plug pdbs and for sure are happy to have the same parameters at the pdb level once you plug into a different instance.
Daniel Westermann's Blog 