Oracle 11g and fine-grained invalidation

From: Michael Rosenblum <mrosenblum_at_dulcian.com>
Date: Wed, 8 Sep 2010 19:21:25 -0400
Message-ID: <2673A0170447634DA7F7ABCA51E89FA9A6B3E16AC2_at_MAIL2.dulcian.local>

Hello, everybody!

At last in Dulcian we started to look at 11g and immediately hit a major puzzle. The topic is fine-grained invalidation. Sorry for quoting Oracle docs, but I just want everybody to be on the same page:

---

Fine-Grained Invalidation
Before Release 11.1, a DDL statement that changed a referenced object invalidated all of its dependents. As of Release 11.1, a DDL statement that changes a referenced object invalidates only the dependents for which either of these statements is true: The dependent relies on the attribute of the referenced object that the DDL statement changed. The compiled metadata of the dependent is no longer correct for the changed referenced object. For example, if view v selects columns c1 and c2 from table t, a DDL statement that changes only column c3 of t does not invalidate v.

---

Good news – this feature pretty much works as expected. Bad news – as far as I can see, Oracle did not provide any mechanism for end-users to retrieve appropriate info from the data dictionary. It does mean than in DBA_DEPENDENCIES I see only main objects, but not columns/packaged subprograms.

In 2008 Rob Van Wijk and Toon Koppelaars started to uncover at least some of the base logic, but sadly it was the only post about this feature that I was able to find.

http://rwijk.blogspot.com/2008/10/dbadependencycolumns.html

Just to summarize the article – in the table SYS.DEPENDENCY$ there is a special column D_ATTRS, that has the following format:

<Type>|<fine-grained dependency>

Currently I discovered a couple of types:
- No detailed dependencies:

0001 – reference without touching any column at all (select count(*) from emp) 000101 - %rowtype reference (return type, package variable, package type).

    May also include previous type without changing the value 0003 – processing without getting any columns   delete from emp where rownum < 10,
 select ‘Y’ into v_yn from emp where rownum = 1
- Detailed dependencies

00010000, 00010100, 00030000,00030100 – I cannot yet pinpoint exact meaning of them. But looks like only 00010000 is used for both table/view dependencies and PL/SQL dependencies, while all other cases cover some permutations for tables/views/its synonyms.

As Toon and Rob stated, what follows the intial 8-digit key (second kind of types) is reversed byte-wise grouping of columns in the table (from DBA_TAB_COLUMNS.COLUMN_ID or its underlying SYS.COL$.COL#). Something like that:

Referenced attribute D_ATTRS

------------------------------ ----------------------------------------
ATTRIBUTE1                        0001000002
ATTRIBUTE2                        0001000004
ATTRIBUTE3                        0001000008
ATTRIBUTE4                        0001000010
ATTRIBUTE5                        0001000020
ATTRIBUTE6                        0001000040
ATTRIBUTE7                        0001000080
ATTRIBUTE8                        000100000001
ATTRIBUTE9                        000100000002
ATTRIBUTE10                      000100000004

I was able to follow the proposed logic and discovered that in case of PL/SQL Oracle is using something very close to the number of procedural unit in the package (SYS.PROCEDUREINFO$.PROCEDURE#). Unfortunately if in the package there are declarations of types/variables/cursors, the whole count is off by the number of non-procedural unit – and I at the current point I could not find any other metadata table to get what I need.

So, finally, my questions in reverse order:

1. Did I miss some doc/article and it is already covered?
2. Is there anybody else who was working in that area?
3. Should we politely ask Oracle (in 2 weeks I will be at OpenWorld and may get some answers there) to provide us a real functionality?
4. Anybody cares about fine-grained invalidation metadata at all?

Thanks a lot!
Michael Rosenblum
Dulcian Inc

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 08 2010 - 18:21:25 CDT