RE: Oracle 11g and fine-grained invalidation

From: Yong Huang <yong321_at_yahoo.com>
Date: Thu, 9 Sep 2010 19:35:06 -0700 (PDT)
Message-ID: <370920.1612.qm_at_web80603.mail.mud.yahoo.com>

Yes, at least the name fine grained dependency already exists in 10g. The corresponding function is kqlidpfgr:

$ pwd

/u01/app/oracle/product/10.2.0/db/lib
$ nm libserver10.a | grep kqlidpfgr

                 U kqlidpfgr ...

Bug 8507754 explains it as "KQL Invalidate all DePendent objects with Finer GRained stuff". Before 10g, there's only kqlidpc (Invalidate all DePendent objects).

11g's new fine grained dependency must be using a different function (it no longer has this function). kqlidpfgr in 10g probably only handles synonyms as you said. The difference between these versions is explained in Bug 9322994. But details you and we all wish to know have to come from Oracle.

Yong Huang

• On Thu, 9/9/10, Michael Rosenblum <mrosenblum_at_dulcian.com> wrote: I just checked my 10.2.0.4 database for SYS.DEPENDENCY$.D_ATTRS - and looks like Oracle already started to use that column even in 10g and in my instance there are only the following values:   02,06,0E,1E,3E,7E,FE   It appears that the column represents references to synonyms (including Oracle's own, like, DUAL or USER_TABLES) by articulating whether you have %ROWTYPE reference, COUNT(*), explicit list of columns etc (btw, the list of values reminds me what we have in 11g after initial 8-byte type).   Michael Rosenblum Dulcian Inc     　 -----Original Message----- From: Yong Huang [mailto:yong321_at_yahoo.com] Sent: Thursday, September 09, 2010 12:38 PM To: Michael Rosenblum Cc: oracle-l_at_freelists.org Subject: Re: Oracle 11g and fine-grained invalidation 　 Michael, Indeed Rob Van Wijk and Toon Koppelaars's research is impressive. I want to add that you can disable fine-grained invalidation by setting _IGNORE_FG_DEPS to NONE. Also, even in 10g, dependency$ has d_attrs column with the same comment (as seen in sql.bsq), "Finer grain attr. numbers if finer grained". But we know 10g doesn't have fine-grained invalidation yet. Yong Huang -- http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 09 2010 - 21:35:06 CDT