Archives For October 2013

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.

Advertisements

another little, but fine improvement in oracle 12c: Now you may issue sql statements directly in rman without the need to use the “SQL ‘…'” syntax:

rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Sat Jun 29 08:40:34 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBS300 (DBID=3276277814)

RMAN> select instance_name from v$instance;

using target database control file instead of recovery catalog
INSTANCE_NAME   
----------------
dbs300          

RMAN> alter system switch logfile;

Statement processed

RMAN> shutdown;

database closed
database dismounted
Oracle instance shut down

RMAN> startup

connected to target database (not started)
Oracle instance started
database mounted
database opened

Total System Global Area     334036992 bytes

Fixed Size                     2288104 bytes
Variable Size                268437016 bytes
Database Buffers              58720256 bytes
Redo Buffers                   4591616 bytes

RMAN> select status from v$instance;

STATUS      
------------
OPEN        

RMAN> 

a lot of people define varchar2 columns this way:

create table t1 ( a varchar2(1) );

… and expect that they will be able to insert one character into this column. well, this is not always true. the default for a varchar2 column is byte, so the above statement is equal to:

create table t1 ( a varchar2(1 byte) );

The length semantics of character data types are measurable in bytes or characters. The treatment of strings as a sequence of bytes is called byte semantics. This is the default for character data types. The treatment of strings as a sequence of characters is called character semantics. A character is a code point of the database character set.

the data dictionary does reflect this if you know what the default is:

SQL> create table t1 ( a varchar2(1 char), b varchar2(1));

Table created.

SQL> desc t1;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 A						    VARCHAR2(1 CHAR)
 B						    VARCHAR2(1)

SQL> 

so, if you work with multibyte character sets and do not take care of this, this may happen:

create table t1 ( a varchar2(1 char), b varchar2(1));
insert into t1 (a) values (chr(1000));

1 row created.

SQL> insert into t1 (b) values (chr(1000));
insert into t1 (b) values (chr(1000))
                           *
ERROR at line 1:
ORA-12899: value too large for column "SYS"."T1"."B" (actual: 2, maximum: 1)