Saturday, January 31, 2009

Edition-based redifinition (a speculation)

As you can see from this link, there is a feature called Edition-based redefinition mentioned in the 11GR1 documentation. Unfortunately, all it says is that this feature is unavailable as of now so there is really not much you can tell about it.

A speculation

I'm usually not a great fun of doing any sort of a guesswork, however, in this case it might be an interesting exercise to observe some bits and pieces presented in the current release of 11GR1 in order to see whether it can give us some clues as to what expect from this new feature as well as what the potential underpinning might look like. As well as prepare yourself for some implications...

SYS.OBJ$

Perhaps this will be the first thing what you'll notice as soon as 11G's data dictionary is concerned. For example, this is how dba_synonyms view is defined in 10GR2:
create or replace view dba_synonyms
(owner, synonym_name, table_owner, table_name, db_link)
as
select u.name, o.name, s.owner, s.name, s.node
from sys.user$ u, sys.syn$ s, sys.obj$ o
where o.obj# = s.obj#
and o.type# = 5
and o.owner# = u.user#;
Now, take a look at the same view's definition in 11GR1:
create or replace view dba_synonyms
(owner, synonym_name, table_owner, table_name, db_link)
as
select u.name, o.name, s.owner, s.name, s.node
from sys.user$ u, sys.syn$ s, sys."_CURRENT_EDITION_OBJ" o
where o.obj# = s.obj#
and o.type# = 5
and o.owner# = u.user#;
For you see, the reference to SYS.OBJ$ was replaced with SYS."_CURRENT_EDITION_OBJ". This replacement occurs all around the place in the data dictionary which alone makes it interesting enough to attract a bit of attention.

SYS._CURRENT_EDITION_OBJ

What is _CURRENT_EDITION_OBJ? It's a view:
SQL> select object_type
2 from dba_objects
3 where object_name='_CURRENT_EDITION_OBJ';

OBJECT_TYPE
-------------------
VIEW
Let's take a look at this view's definition (I've omitted fields list for the sake of clarity):
select ...
from obj$ o, user$ u
where o.owner# = u.user#
and ( /* non-versionable object */
( o.type# not in (4,5,7,8,9,10,11,12,13,14,22,87,88)
or bitand(u.spare1, 16) = 0)
/* versionable object visible in current edition */
or ( o.type# in (4,5,7,8,9,10,11,12,13,14,22,87)
and ( (u.type# <> 2 and
sys_context('userenv', 'current_edition_name') = 'ORA$BASE')
or (u.type# = 2 and
u.spare2 = sys_context('userenv', 'current_edition_id'))
or exists (select 1 from obj$ o2, user$ u2
where o2.type# = 88
and o2.dataobj# = o.obj#
and o2.owner# = u2.user#
and u2.type# = 2
and u2.spare2 =
sys_context('userenv', 'current_edition_id'))
)
)
);
First of all, it mentions about (A) versionable objects and (B) current edition which is referenced through session's context sys_context('userenv', 'current_edition_id').

Versionable objects

Only the objects of the following types seems to be versionable:
  • VIEW
  • SYNONYM
  • PROCEDURE
  • FUNCTION
  • PACKAGE
  • PACKAGE BODY
  • TRIGGER
  • TYPE
  • TYPE BODY
  • LIBRARY
  • ASSEMBLY
Object type# = 88 seems to be something special as I couldn't find what it is from sql.bsq (nor dba_objects) and it is being referenced using somewhat special way: if obj# matches dataobj# of some entry with type# = 88 and this entry is part if the current edition, the object is considered to be a part of the current edition as well. Note that the join itself permits this special object to exist in some other schema, though I don't know whether this has any practical meaning or not.

New userenv attributes

There are two (at least) new userenv context attributes:
SQL> select sys_context('userenv', 'current_edition_name')
2 current_edition_name from dual;

CURRENT_EDITION_NAME
--------------------------------------------------------------------------------
ORA$BASE
SQL> select sys_context('userenv', 'current_edition_id')
2 current_edition_id from dual;

CURRENT_EDITION_ID
--------------------------------------------------------------------------------
100
Changing these will change data returned by _CURRENT_EDITION_OBJ view as well and, given that that view is referenced instead of obj$, it looks like these contexts will be the primary mechanism responsible for referencing one version (edition) of the object or the other.

New user type

Again, by looking at the view definition, we may spot that versionable objects are being concerned as far as user$.type# = 2. To remind you, 0 is a role and 1 is a regular user, so there will be something new, perhaps this new feature could be enabled on a per-user level and/or versioned objects will be owned by some other "ghost" schema (my comment about object with type 88 seems to be allowing this, at least technically).

Some interesting clues might be get from _CURRENT_EDITION_OBJ's field definition itself:
when (u.type# = 2) then
(select eo.name from obj$ eo where eo.obj# = u.spare2)
else
'ORA$BASE'
end
The objects which are owned by this new user type will have editition_id as their object_id and will be named after edition name. That also means that object_id is no longer unique:
create unique index i_obj1 on obj$(obj#, owner#, type#) (11GR1)
create unique index i_obj1 on obj$(obj#) (10GR2)
(from sql.bsq)
I wander whether this have some chances to break legacy code which references sys.obj$ by obj# and expect one row to be returned at most...

Does all that, again, points out that object versions will be kept under this special user and will be linked back using data_object_id? Is this also a reason why objects which do have "proper" data_object_id are not versionable?

A speculation (again)

Pleases note that everything said in this post is based on some (random) observations of preliminary data available in the current release (11.1.0.7). There are chances that some (if not all) of this data may became altered or even completely invalid when this feature will be officially announced.

However, one thing seems to be clear -- in order to support this new feature, some serious alterations of the data dictionary are taking place and you should keep yourself alerted...

5 comments:

  1. The teasers for 11g did cover a multi-edition stuff as part of high-availability. It was all to do with having multiple editions of code so that, as you upgrade,existing sessions can still use the 'pre-upgrade' editions while new sessions use the new upgraded editions.
    There was a bit about it here
    :http://www.rittmanmead.com/2006/10/23/oracle-open-world-days-12-database-roadmaps/
    and under "Edition-Based Redefinition" here
    http://www.cc.gatech.edu/classes/AY2007/cs6400_fall/11g_SNEAK_PREVIEW_Availability_Juan.ppt

    ReplyDelete
  2. Thanks Gary,

    it would be very interesting to observe how the final implementation will work, though we have some ideas now...

    ReplyDelete
  3. B.Polarski8:35 AM

    Looks like they insert code for versionned data dictionary like in Streams. However instead of determining the version of datadict following your scn, The instance ID, through a sys_context var will set the right key to access the correct view for your local version. At the end your data dictionary has now a variable part that you can alter dynamically following the instance you are on. So much fun ahdead.

    ReplyDelete
  4. Anonymous12:12 AM

    Would you like to update this? Edition-based redefinition was productized with Oracle Database 11g Release 2, generally available since the Fall of 2009. Regards, Bryn Llewellyn, Product Manager, Oracle Database Server Technologies Division, Oracle HQ.

    ReplyDelete
  5. Bryn,

    the update might come as a separate post.

    ReplyDelete