By Franck Pachot

.
This is a second part of the previous post about metadata link. I’ve shown how a sharing=metadata function becomes a sharing=none function when it is changed in the pdb – i.e when not having the same DDL, not having a different signature.

Here is another experimentation doing the opposite: change the function in root and see what happens in the pdb. Again playing with internals in order to understand the ‘upgrade by unplug-plug’ feature available in 12c multi-tenant (and single-tenant).

Create shared function

I’m doing the same as in the previous post:

SQL> alter session set "_oracle_script"=true;
Session altered.

SQL> alter session set container=cdb$root;
Session altered.

SQL> show con_name

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

SQL> create function DEMO_FUNCTION1 sharing=metadata return varchar2 as begin return 'demo'; end;
  2  /
Function created.

SQL> create function DEMO_FUNCTION0 sharing=metadata return varchar2 as begin return DEMO_FUNCTION1; end;
  2  /
Function created.

SQL> grant execute on DEMO_FUNCTION0 to public;
Grant succeeded.
now in the pdb:
SQL> alter session set container=pdb1;
Session altered.

SQL> create function DEMO_FUNCTION1 sharing=metadata return varchar2 as begin return 'demo'; end;
  2  /
Function created.

SQL> create function DEMO_FUNCTION0 sharing=metadata return varchar2 as begin return DEMO_FUNCTION1; end;
  2  /
Function created.

Check multitenant dictionary

First in root:

SQL> alter session set container=cdb$root;
Session altered.

SQL> select obj#,name,status,flags,decode(bitand(flags,196608),65536,'MDL',131072,'OBL','NONE') sharing,signature from obj$ where name like 'DEMO_FUNCTION_';

 OBJ# NAME           STATUS   FLAGS SHAR SIGNATURE
----- -------------- ------ ------- ---- --------------------------------
92370 DEMO_FUNCTION0      1 5308416 MDL  ECE1890DECB2F100A2F755CC0B76166D
92369 DEMO_FUNCTION1      1 5308416 MDL  0BCDC8C960AD833F95F0595EEBB70745

And in the pdb:

SQL> alter session set container=pdb1;
Session altered.

SQL> select obj#,name,status,flags,decode(bitand(flags,196608),65536,'MDL',131072,'OBL','NONE') sharing,signature from obj$ where name like 'DEMO_FUNCTION_';

 OBJ# NAME           STATUS   FLAGS SHAR SIGNATURE
----- -------------- ------ ------- ---- --------------------------------
91829 DEMO_FUNCTION0      1 5308416 MDL  ECE1890DECB2F100A2F755CC0B76166D
91828 DEMO_FUNCTION1      1 5308416 MDL  0BCDC8C960AD833F95F0595EEBB70745

Nothing special here: different object_id but same signature and sharing=metadata.

Upgrade the root

I’m now ‘upgrading’ the function DEMO_FUNCTION1 in the root:

SQL> alter session set container=cdb$root;
Session altered.

SQL> create or replace function DEMO_FUNCTION1 sharing=metadata return varchar2 as begin return 'demo v2'; end;
  2  /
Function created.

and nothing in the pdb

Here is what is stored in both container dictionaries:

SQL> alter session set container=cdb$root;
Session altered.

SQL> select obj#,name,status,flags,decode(bitand(flags,196608),65536,'MDL',131072,'OBL','NONE') sharing,signature from obj$ where name like 'DEMO_FUNCTION_';

 OBJ# NAME           STATUS      FLAGS SHAR SIGNATURE
----- -------------- ------ ---------- ---- --------------------------------
92370 DEMO_FUNCTION0      1    5308416 MDL  ECE1890DECB2F100A2F755CC0B76166D
92369 DEMO_FUNCTION1      1    5308416 MDL  9772AA08AFD3F9F90BC9BDB9DE35A1F8

SQL> select * from source$ where obj# in (select obj# from obj$ where name like 'DEMO_FUNCTION_');

 OBJ# LINE SOURCE
----- ---- --------------------------------------------------
92370    1 function DEMO_FUNCTION0                  return va
           rchar2 as begin return DEMO_FUNCTION1; end;

92369    1 function DEMO_FUNCTION1                  return va
                      rchar2 as begin return 'demo v2'; end;
SQL> alter session set container=pdb1;
Session altered.

SQL> select obj#,name,status,flags,decode(bitand(flags,196608),65536,'MDL',131072,'OBL','NONE') sharing,signature from obj$ where name like 'DEMO_FUNCTION_';

 OBJ# NAME           STATUS      FLAGS SHAR SIGNATURE
----- -------------- ------ ---------- ---- --------------------------------
91829 DEMO_FUNCTION0      1    5308416 MDL  ECE1890DECB2F100A2F755CC0B76166D
91828 DEMO_FUNCTION1      1    5308416 MDL  0BCDC8C960AD833F95F0595EEBB70745

SQL> select * from source$ where obj# in (select obj# from obj$ where name like 'DEMO_FUNCTION_');

no rows selected

Ok. In the root the function has changed, and has a new signature. But nothing changed in the pdb. The DDL you did in the root has changed only the root dictionary.

Then, what happens if we execute the function from the pdb?

SQL> alter session set container=cdb$root;
Session altered.

SQL> select DEMO_FUNCTION1 from dual;

DEMO_FUNCTION1
--------------------------------------------
demo v2

SQL> alter session set container=pdb1;
Session altered.

SQL> select DEMO_FUNCTION1 from dual;

DEMO_FUNCTION1
--------------------------------------------
demo

The pdb function returns the old value. Do you think it’s an expected result because pdb has not been upgraded? No. we have a problem here. Look at the above queries on SOURCE$: the code that returns ‘demo’ is not stored anywhere.

When we run something that is not stored anywhere, it probably comes from a cache invalidation that did not occur. Let’s flush the shared pool:

SQL> alter session set container=cdb$root;
Session altered.

SQL> alter system flush shared_pool;
System altered.

SQL> alter session set container=pdb1;
Session altered.

SQL> select DEMO_FUNCTION1 from dual;

DEMO_FUNCTION1
--------------------------------------------
demo v2

Good. We see the upgraded version now. We didn’t have anything to do in the pdb. The metadata link is still use and this is how ‘upgrade by unplug-plug’ works: when the upgrade concerns only sharing=metadata oracle objects that are stored only in root, then the pdb is automatically upgraded as it follows the links. This is how we can apply quickly a patch or a PSU: plug it into a CDB that already has the patch applied.

So, what happened to the signature?

SQL> select obj#,name,status,flags,decode(bitand(flags,196608),65536,'MDL',131072,'OBL','NONE') sharing,signature from obj$ where name like 'DEMO_FUNCTION_';

 OBJ# NAME           STATUS      FLAGS SHAR SIGNATURE
----- -------------- ------ ---------- ---- --------------------------------
91829 DEMO_FUNCTION0      5    5308416 MDL  ECE1890DECB2F100A2F755CC0B76166D
91828 DEMO_FUNCTION1      1    5308416 MDL  9772AA08AFD3F9F90BC9BDB9DE35A1F8

SQL> select * from source$ where obj# in (select obj# from obj$ where name like 'DEMO_FUNCTION_');

no rows selected

The function has been recompiled when it was executed. It has never been invalidated (and that’s why we had to flush the shared pool) but the load which has followed the metadata link has detected that the signature is not the same and has recompiled it. The signature is the same. And dependent objects are invalidated (they will be recompiled when executed).

This is a correct behaviour except the fact that we had to flush the shared pool. Anyway, that occurs only when we upgrade the root which should flush the shared pool or when we plug a new pdb which has nothing yet in shared pool.

However, when you upgrade from 12.1.0.1 to 12.1.0.2 there are a lot of changes and a lot of dependencies and updating the signatures is not sufficient. The datapatch script has to update our application metadata, which is stored into the pdb dictionary. And this is why ‘upgrade by unplug-plug’ is not a lot faster than a non-CDB upgrade. But that may be improved in future versions.